IBM DB2 Control Loop

Question

I am migrating from MS SQL Server to IBM DB2 (Version 9.7). Trying to write a compound SQL While Loop with local variables (Not a part of Stored Procedure), something like -

BEGIN ATOMIC
   DECLARE i INT DEFAULT 12;
     WHILE i > 0 
     "DO ....";
     SET COUNT = COUNT - 1;
     END WHILE;
 END

But I get an error just in first line for declaring variables-

SQL0104N  An unexpected token "12" was found following "ECLARE I INT DEFAULT". Expected tokens may include:  "END-OF-STATEMENT"

Any help is greatly appreciated.

Your code has something like a bunch of syntax errors and undeclared variables. If you show your actual code instead of something like it, someone might be able to offer something like help. — mustaccio, Aug 25 '15 at 17:34
Actual code, which is very close to the 'something like' code above - BEGIN ATOMIC DECLARE i INT DEFAULT 12; WHILE i > 0 DO INSERT INTO TEMP_ITERATIONS VALUES 'IT'|| RTRIM(CHAR(COUNT)); SET COUNT = COUNT - 1; END WHILE; END — user455580, Aug 25 '15 at 18:19
Well, you declare `i INT` but then decrement `COUNT` in the loop -- not sure I understand the logic here. The `VALUES` clause in the `INSERT` requires parentheses. I suggest you review syntax diagrams in the manual. — mustaccio, Aug 25 '15 at 18:23
Thanks for pointing out the syntax errors. I pasted the wrong version. Issue is that I get the error at second like itself while declaring i. Seems it doesn't go ahead from that. Correct version BEGIN ATOMIC DECLARE i INT DEFAULT 12; WHILE i > 0 DO INSERT INTO TEMP_ITERATIONS VALUES ('IT'|| RTRIM(CHAR(i))); SET i = i - 1; END WHILE; END — user455580, Aug 25 '15 at 18:53

score 3 · Accepted Answer · answered Aug 25 '15 at 20:16

I assume that your problem has to do with statement terminator (easier to see if you provide the actual errors you get). I tested the following and it worked:

[ ... ]$ cat aa.sql 

CREATE TABLE TEMP_ITERATIONS ( X VARCHAR(50) ) @

BEGIN ATOMIC 
    DECLARE i INT DEFAULT 12; 
    WHILE i > 0 DO 
        INSERT INTO TEMP_ITERATIONS VALUES ('IT'|| RTRIM(CHAR(i))); 
        SET i = i - 1; 
    END WHILE; 
END @

[ ... ]$ db2 -td@ -f aa.sql

DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

[ ... ]$ db2 "select * from TEMP_ITERATIONS"

X                                                 
--------------------------------------------------
IT12                                              
IT11                                              
IT10
[...]
12 record(s) selected.

Here I used @ as a statement terminator since ; has a special meaning. If you don't want to change the statement terminator a trick is to "hide" the ; inside the compound statement by adding a comment at the end of the line:

[ ... ]$ cat aa.sql 

CREATE TABLE TEMP_ITERATIONS ( X VARCHAR(50) ) ;

BEGIN ATOMIC 
    DECLARE i INT DEFAULT 12; --
    WHILE i > 0 DO 
        INSERT INTO TEMP_ITERATIONS VALUES ('IT'|| RTRIM(CHAR(i))); --
        SET i = i - 1; --
    END WHILE; --
END ;

[ ... ] db2 -tf aa.sql
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.

[ ... ]$ db2 "select * from TEMP_ITERATIONS"

X                                                 
--------------------------------------------------
IT12                                              
IT11                                              
IT10
[...]
12 record(s) selected.

Thanks a lot. It was the terminator and I could get it working with both of your suggested solutions ! Appreciate it ! — user455580, Aug 25 '15 at 20:44
AFAIK the second one is an undocumented feature. Serge Rielau (at the time being one of the architect's for DB2) mentions it in this usenet post from 2004: https://groups.google.com/forum/#!topic/comp.databases.ibm-db2/goEiaG3jsAI — Lennart - Slava Ukraini, Aug 25 '15 at 21:16
@Lennart - he does say they won't take it out because it will break a feature -- still a strange feature to say the least. — Hogan, Aug 25 '15 at 22:41

IBM DB2 Control Loop

1 Answers1