db2 alter auto increment query stored procedure

Question

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 ESTORE.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

Answer 1

Dynamic SQL statements must not include statement terminators. Remove the trailing semicolon from stmt1.

Answer 2

The procedure itself is not a problem, all of the syntax is correct (if you're talking about a current version of DB2 on Linux, UNIX or Windows) and it will compile.

However there are a number of other potential errors:

1. You query the table ESTORE.APPLICATION_LABELS, but then you alter APTR.APPLICATION_LABELS.

2. When you alter the table you should just use ALTER TABLE ... ALTER COLUMN ... RESTART WITH X instead of trying to change the entire GENERATED clause.

3. Not sure why you're declaring cursor1 with return when you're consuming the cursor within the stored procedure. For that matter, not sure why you're even using a cursor in the first place, when you can just SELECT max(label_id) INTO v_max FROM ...

4. You don't need to PREPARE your statement if you're going to EXECUTE IMMEDIATE. Just EXECUTE IMMEDIATE stmt1;