Execute SQL command from within a resultset in stored procedure in DB2

Question

I have the following stored procedure:

CREATE PROCEDURE SQLTEST()
LANGUAGE SQL
BEGIN ATOMIC
DECLARE SQLCMD VARCHAR(1024);

FOR v AS cur1 CURSOR FOR SELECT ID, CMD from COMMANDTBL
DO 
  SET SQLCMD= v.CMD;
  "CALL" SQLCMD;
END FOR;
END?

The COMMANDTBL has the columns ID and CMD of which CMD has SQL Commands. CMD is 1024 VARCHAR. In this test they are 2 inserts for another table

INSERT INTO TARGETTBL(TARGET, TARINT) VALUES ('TEST', 100);
INSERT INTO TARGETTBL(TARGET, TARINT) VALUES ('TEST2', 200);

for example.

My problem is the "CALL" part. I have DB2 v9.7 but it would be great if there was a solution running on lower versions as well. I have not found any pointers on how to run this and a simple EXEC or EXECUTE does not work.

Thank you for your help.

TheVagabond

Answer 1

You need to prepare and execute the statements because they are deemed "dynamic SQL".

PREPARE myStmt FROM SQLCMD; EXECUTE myStmt;