Db2 Multi Insert using LOOP

Question

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.

Answer 1

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

Answer 2

Thanks Mark, your solution and the doc reference really helped.

I wanted to add the Stored procedure that I went ahead with with regards to generating data:

CREATE PROCEDURE HEROI.SP_INSERT
LANGUAGE SQL
DISABLE DEBUG MODE
BEGIN
    DECLARE cnt int DEFAULT 0;
    WHILE cnt <= 1000000 DO
    insert into HEROI.TESTD (PK,SMALLINT_TYPE) VALUES (cnt, 'Hello World'); 
    COMMIT;
    SET cnt = cnt + 1;
    END WHILE ;
END

CALL HEROI.SP_INSERT;