Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2014
    Posts
    1

    Unhappy Unanswered: DB2 Stored Procedure

    hi,

    I try to alter the table using DB2 stored procedure. Below is my code. I found the issue in alter query. But that same query executed perfectly in DB2 command line editor. I want to run the same query using stored procedure. kindly help me.

    CREATE PROCEDURE MODIFYAUTOINCRE()
    SPECIFIC TEST
    RESULT SETS 1
    MODIFIES SQL DATA
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE v_max INTEGER DEFAULT 0;--
    DECLARE stmt1 VARCHAR(100);
    DECLARE stmt2 VARCHAR(100);
    DECLARE name1 VARCHAR(100);

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR

    SELECT MAX(LABEL_ID) FROM APTR.APPLICATION_LABELS;


    -- Cursor left open for client application
    OPEN cursor1;

    FETCH cursor1 INTO v_max;
    IF (v_max > 0 ) THEN

    SET v_max=v_max+1;

    SET stmt1= 'ALTER TABLE APTR.APPLICATION_LABELS ALTER COLUMN LABEL_ID SET GENERATED AS IDENTITY (START WITH '||v_max||' INCREMENT BY 1 NO CACHE)';

    PREPARE name1 FROM stmt1;

    EXECUTE IMMEDIATE name1;

    commit;
    END IF;


    END P1



    ERROR: An unexpected token "END-OF-STATEMENT" was found following "NTITY (START WITH 69". Expected tokens may include: ")".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.58.81



    Thanks in advance,
    Shyamala

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume the the column is already an identity column, so use:

    'ALTER TABLE MyTable ALTER COLUMN IdentCol RESTART WITH '||char(value)

    Note that the integer variable 'value' needs to be converted to a string to get it to concatenate.

    Andy

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    You have to enlarge the length of the stmt1 variable.
    The length of the expression you specified simply doesn't fit in 100 symbols/bytes.
    Regards,
    Mark.

Posting Permissions

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