Hi All,

I wan to update the identity column of a table using dynamic SQL in a Stored procedure.

below is the Proc, table TBLM has A_ID column as identity.

CREATE PROCEDURE DYNSQLSAMPLE()
LANGUAGE SQL
BEGIN
DECLARE stmt VARCHAR(256);
DECLARE mgrno INT;
SET mgrno = 8888;

SET STMT1 = 'ALTER TABLE TBLM ALTER COLUMN A_ID RESTART WITH ? ';
PREPARE s1 FROM stmt;
EXECUTE s1 USING mgrno;
END

This proc is building properly but when I am running it in debug mode it if failing with the below error message

DB2USR1.DYNSQLSAMPLE - Debug started.
DYNSQLSAMPLE - Exception occurred while debugging:
A database manager error occurred.[IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "?" was found following "MN A_ID RESTART WITH". Expected tokens may include: "<signed_numeric_literal>". SQLSTATE=42601

DYNSQLSAMPLE - Roll back completed successfully.
DB2USR1.DYNSQLSAMPLE - Debug failed.


Could any one please let me know what I am doing wrong here..?


Thanks

There are 2 possible issues I see:

1. You are using a parameter marker but you have not provided type information. Thus, DB2 does not know at PREPARE time which data the host variable is going to have. You can try something like CAST(? AS INT).
2. I don't know for sure if parameter markers are allowed in DDL statements like ALTER TABLE. You can avoid them because you don't need it anyway. Just build your statement like this:
   
   Code:

   SET STMT1 = 'ALTER TABLE TBLM ALTER COLUMN A_ID RESTART WITH ' || CHAR(mgrno);

Thanks stolze,

Yes you were right i did the change you suggested and it worked.