Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: RESET identity column using Dynamic SQL

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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);
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2008
    Posts
    3

    RESET identity column using Dynamic SQL

    Thanks stolze,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •