while-loopdb2sql-insertdb2-zos

Db2 Multi Insert using LOOP


I am trying to write a loop basically to do bulk INSERTS for DB2 Z/OS. After referring this article, I tried to use this:

BEGIN
    DECLARE varcnt; 
    set varcnt int DEFAULT 0;
    WHILE varcnt <= 10 DO
    insert into HEROI.TESTD (PK,XML_TYPE) values(varcnt+1,'<test>Hello World</test>');  
    SET varcnt = varcnt + 1;
    END WHILE ;
END;

But the above gave the following error:

ILLEGAL SYMBOL "VARCNT". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SECTION. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.28.11 SQL Code: -104, SQL State: 42601 Error occurred in: BEGIN DECLARE varcnt

I used DBeaver, TOAD and SQL Squirrel client, thinking this might be a client issue, but doesn't seem so. The version of DB2 Z/OS i have is DSN12015.

Would be really great to get some assistance here as to how can I run this INSERT statement multiple times.


Solution

  • Compound statements in Db2 for z/OS are supported in routines and triggers only.
    You can't use them as standalone statements.
    Your DECLARE & SET statements are erroneous - you don't provide a data type in DECLARE and do it in SET. It should be:

    DECLARE varcnt int DEFAULT 0;
    

    As for you case. You may do the same with a single statement:

    insert into HEROI.TESTD (PK,XML_TYPE)
    WITH T (I) AS 
    (
    SELECT 0 FROM SYSIBM.SYSDUMMY1
        UNION ALL
    SELECT I+1 FROM T WHERE I < 10
    )
    SELECT I, '<test>Hello World</test>'
    FROM T