What is the valid syntax for the DB2 Anonymous SQL Block?

Question

I have DB2 v9.7 client driver to access DB2 instance which is installed on Z/OS environment. I am trying to write and execute an anonymous procedure by the help of Toad for DB2 4.7. Unfortunately, none of the given example codes on the internet hurdled the syntax errors. Whatever i try, even very simple ones, not worked properly. I am curious about is there an execution mode of Toad or something that i missed on my tries. Here are my tries and syntax errors that i have received:

DECLARE
  somechr VARCHAR2(255);
BEGIN
  somechr:='some value';
END;

The sample above throws these errors:

DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "VARCHAR2" was found following "". Expected tokens may include: "TABLE STATEMENT , . SCROLL INSENSITIVE SENSITIVE ASENSITIVE ". SQLSTATE=42601
DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "SOMECHR" was found following "". Expected tokens may include: "DECLARE". SQLSTATE=42601
DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "". Expected tokens may include: "DECLARE". SQLSTATE=42601

The last one vanishes when i change the 'some value'; to 'some value';--

Some examples uses following syntax:

BEGIN
  DECLARE somechr VARCHAR2(255);--
  somechr:='some value';
END;

However, this syntax throws exception too:

DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "SOMECHR" was found following "". Expected tokens may include: "SECTION". SQLSTATE=42601

At last i have tried this:

BEGIN
  DECLARE SECTION BEGIN
    somechr VARCHAR2(255);--
  END;
  somechr:='some value';--
END;

And got these:

DB2 Database Error: ERROR [42612] [IBM][DB2] SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES statement. SQLSTATE=42612
DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0104N An unexpected token "SOMECHR" was found following "". Expected tokens may include: " GET SQL SAVEPOINT HOLD FREE ASSOCIATE". SQLSTATE=42601

Besides these tries, some noted that use of '--#SET TERMINATOR @' at the beginning of the code, nevertheless it did not help as well for all examples above. I have given tries by changing the ';' to symbol '@' for all combinations (with or without '--#SET TERMINATOR @' statement), but erros does not seem to be vanished.

There is no DB2 v9.7 for z/OS. There are plenty of examples of _compound SQL statements_ [in the manuals](http://www-01.ibm.com/support/docview.wss?uid=swg27011656#manuals) -- particularly _Application Programming and SQL Guide_ and _SQL Reference_. — mustaccio, Mar 06 '16 at 16:07

score 1 · Answer 1 · edited Dec 23 '16 at 10:43

I work with DB2 LUW, not z/OS. That said, you might try this:

BEGIN
    DECLARE V_SOMECHR NVARCHAR(255);

    SET V_SOMECHR = 'some value';
END

My experience in DB2 LUW is that the declarations have to be inside the BEGIN block, and must come first, but there is no heading stating that it is a DECLARE section. You can place your procedural code immediately after the last declaration. Not sure if this will be the case in z/OS.

What is the valid syntax for the DB2 Anonymous SQL Block?

1 Answers1