DB2 update in Loop does not execute

I have the following code:

  BEGIN
DECLARE cmd VARCHAR(1024); DECLARE attr CHAR(10); 
DECLARE attr2 CHAR(10); 
DECLARE at_end INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE updstr1 VARCHAR(1024);
DECLARE updstr2 VARCHAR(1024);


DECLARE c1 CURSOR FOR SELECT cmd, attr, attr2 
                      FROM commandtbl2;

DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;

SET updstr1 = 'update commandtbl3 t2 set t2.XXXX = attr where t2.cmd=cmd';
SET updstr2 = 'update commandtbl3 t2 set t2.XXXX= attr2 where t2.cmd=cmd';
OPEN c1;

ins_loop: LOOP

  FETCH c1 INTO cmd, attr, attr2; 

  IF at_end = 1 THEN
    LEAVE ins_loop;
  ELSEIF cmd != '' THEN
    ITERATE ins_loop;
  END  IF;

  set updstr1= REPLACE(updstr1,'XXXX','attr');
  set updstr2= REPLACE(updstr2,'XXXX','attr2');

  EXECUTE IMMEDIATE updstr1;
  EXECUTE IMMEDIATE updstr2;


END LOOP;

CLOSE c1;
END@

It all runs without error but when I check commandtbl3 nothing was updated. All values are NULL but should be replaced. There are 7 rows in both tables and 5 have matching cmd values so those 5 rows should update.

I am not sure whether the replace is not working or the Execute Immediate.

With debuging I found out, that the updstr1 and 2 variables are empty after the replace... but why?

Can you help me? Thanks, TheVagabond

As requested here are the definitions of commandtbl3 and 2 :

CREATE TABLE "TEST"."COMMANDTBL3" (
    "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 2 NO ORDER ), 
    "CMD" VARCHAR(1024 OCTETS) NOT NULL, 
    "ATTR" CHAR(10 OCTETS), 
    "ATTR2" CHAR(10 OCTETS), 
    CONSTRAINT "CC1455789123996" PRIMARY KEY
    ("ID")
)

CREATE TABLE "TEST"."COMMANDTBL2" (
    "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 2 NO ORDER ), 
    "CMD" VARCHAR(1024 OCTETS) NOT NULL, 
    "ATTR" CHAR(10 OCTETS), 
    "ATTR2" CHAR(10 OCTETS), 
    CONSTRAINT "CC1455789123996" PRIMARY KEY
    ("ID")
)

COmmandtbl3 has CMD equal to commandtbl2 except for 2 rows (7 rows total) and attr and attr2 are NMULL in commandtbl3. I want the values of commandtbl2 attr and attr2 to be written to commandtbl3 via update with an replace to the command so I can have placeholders XXXX

HERE IS THE WORKING CODE:

If someone has the same prob here is the working code, even if changed from the starting one (while loop now, etc):

BEGIN
DECLARE EOF INTEGER DEFAULT 0;
DECLARE cmd VARCHAR(1024); DECLARE attr CHAR(10); 
DECLARE attr2 CHAR(10); 
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE updstr1 VARCHAR(1024);
DECLARE updstr2 VARCHAR(1024);
DECLARE stmt1 STATEMENT;
DECLARE stmt2 STATEMENT;

DECLARE c1 CURSOR FOR SELECT cmd, attr, attr2 
                  FROM commandtbl2 order by cmd;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1;

SET updstr1 = 'update commandtbl3 t2 set t2.XXXX = ? where t2.cmd= ?';
SET updstr2 = 'update commandtbl3 t2 set t2.XXXX= ? where t2.cmd= ?';
set updstr1= REPLACE(updstr1,'XXXX','attr');
set updstr2= REPLACE(updstr2,'XXXX','attr2');
insert into temptbl(text1,text2) values(updstr1,updstr2);

prepare stmt1 from updstr1;
prepare stmt2 from updstr2;

OPEN c1;


WHILE EOF = 0 DO

FETCH from c1 INTO cmd, attr, attr2; 
EXECUTE stmt1 using attr, cmd;
EXECUTE stmt2 using attr2, cmd;

END WHILE;

CLOSE c1;
END@

Solution

You have a few logical errors in your code.

In your loop:
```
IF at_end = 1 THEN
  LEAVE ins_loop;
ELSEIF cmd != '' THEN
  ITERATE ins_loop;
END  IF;
```
The ELSEIF statement is telling DB2 that, if cmd (from commandtbl2) is anything but an empty string, it should skip the remaining steps in this iteration of the loop (i.e. it will do nothing, and just jump back to the FETCH statement at the beginning of the loop).

This is likely why you're not seeing anything happen.
As @mustaccio mentioned in the comments above, it looks like you're assuming that DB2 will magically replace variables with their values when you perform the EXECUTE IMMEDIATE statement. Your statement:
```
update commandtbl3 t2 set t2.XXXX = attr where t2.cmd=cmd
```
Your code will replace the XXXX, but it sure looks like you're assuming that the = attr and =cmd will also be replaced with the values that get set in the FETCH statement. This is not the case.

See this example:
```
DECLARE GLOBAL TEMPORARY TABLE t1 (c1 int) 
ON COMMIT PRESERVE ROWS WITH REPLACE
@

BEGIN
    DECLARE v1 INT;
    DECLARE vSQL VARCHAR(128);

    SET vSQL = 'INSERT INTO SESSION.T1 values (v1)';
    SET v1 = 12;

    EXECUTE IMMEDIATE vSQL;
END
@
```
When the database reaches the EXECUTE IMMEDIATE statement, it will not replace v1 with the value of v1 at runtime. It will execute the exact statement INSERT INTO SESSION.T1 values (v1). This will, of course, fail, because v1 doesn't mean anything to the database.

In your case, the EXECUTE IMMEDIATE statements won't fail because attr and cmd do mean something to the database – they are column names in the table commandtbl3. So your update statements executed will be (after the REPLACE statement):
```
update commandtbl3 t2 set t2.attr = attr where t2.cmd=cmd
update commandtbl3 t2 set t2.attr2 = attr2 where t2.cmd=cmd
```
These are basically no-ops - like UPDATE T1 SET C1 = C1 WHERE C2 = C2.

This is a very good example for why you should not declare variables using the same names as columns in your database. It increases confusion and can hide logical errors.