Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2013
    Posts
    9

    Unanswered: Unable to Create Procedure that Alters Table

    I have the following sql script that I'm attempting to execute.


    /**LEAVE AT TOP OF SCRIPT**/
    --DB2 Commands instructing the system to auto reply to messges.
    CL: CHGCURLIB CURLIB(GIAS_UTILS);
    CL: ADDRPYL;
    CL: CHGJOB INQMSGRPY(*SYSRPYL);

    CREATE PROCEDURE PROD00016805_DDL ()
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    DECLARE DUP INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42711'
    SET DUP = 1;
    IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN
    IF (DUP <> 1) THEN
    ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10);
    SET DUP = 0;
    END IF;
    END IF;
    END;
    CALL PROD00016805_DDL;
    DROP PROCEDURE PROD00016805_DDL;

    /**LEAVE AT END**/
    --DB2 Commands instructing the system not to auto reply to messges.
    CL: RMVRPYL;


    The first time I execute the script, which is before the PFCOMAGTM.MAGTSSTAT column exists, the results are positive rendering the following output.


    > CL: CHGCURLIB CURLIB(GIAS_UTILS)
    CPC2198: Current library changed to GIAS_UTILS.
    Statement ran successfully (2254 ms = 2.254 sec)
    > CL: ADDRPYL
    CPC2403: Reply List entry with sequence number 9666 added to system reply list.
    CPC2403: Reply List entry with sequence number 9667 added to system reply list.
    CPC2403: Reply List entry with sequence number 9668 added to system reply list.
    Statement ran successfully (2259 ms = 2.259 sec)
    > CL: CHGJOB INQMSGRPY(*SYSRPYL)
    Statement ran successfully (2288 ms = 2.288 sec)

    > CREATE PROCEDURE PROD00016805_DDL () LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE DUP INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '42711' SET DUP = 1; IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN IF (DUP <> 1) THEN ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10); SET DUP = 0; END IF; END IF; END
    Statement ran successfully (500 ms)

    > CALL PROD00016805_DDL
    Return Code = 0
    Statement ran successfully (944 ms)

    > DROP PROCEDURE PROD00016805_DDL
    Statement ran successfully (433 ms)
    > CL: RMVRPYL
    CPC2406: Reply list entry with sequence number 9666 removed from system reply list.
    CPC2406: Reply list entry with sequence number 9667 removed from system reply list.
    CPC2406: Reply list entry with sequence number 9668 removed from system reply list.
    Statement ran successfully (2583 ms = 2.583 sec)


    If I attempt to execute the script again the following string of errors are returned.


    > CL: CHGCURLIB CURLIB(GIAS_UTILS)
    CPC2198: Current library changed to GIAS_UTILS.
    Statement ran successfully (2264 ms = 2.264 sec)
    > CL: ADDRPYL
    CPC2403: Reply List entry with sequence number 9666 added to system reply list.
    CPC2403: Reply List entry with sequence number 9667 added to system reply list.
    CPC2403: Reply List entry with sequence number 9668 added to system reply list.
    Statement ran successfully (2276 ms = 2.276 sec)
    > CL: CHGJOB INQMSGRPY(*SYSRPYL)
    Statement ran successfully (2277 ms = 2.277 sec)

    > CREATE PROCEDURE PROD00016805_DDL () LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE DUP INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '42711' SET DUP = 1; IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN IF (DUP <> 1) THEN ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10); SET DUP = 0; END IF; END IF; END

    SQL State: 42711
    Vendor Code: -612
    Message: [SQL0612] MAGTSTAT is a duplicate column name. Cause . . . . . : One of the following errors has occurred: -- Column MAGTSTAT is specified more than once on a CREATE TABLE or CREATE VIEW statement. Column names and system column names must be unique in a table or view. -- Column MAGTSTAT is specified in the ADD clause of an ALTER TABLE statement. Column MAGTSTAT already exists in the table. -- Column MAGTSTAT is specified more than once in the ALTER, DROP, or ADD clauses of an ALTER TABLE statement. -- Column MAGTSTAT is specified more than once in the column list of a common table expression or in the correlation clause for a table or derived table. -- Column MAGTSTAT is specified more than once in the column list of an UPDATE trigger. -- Column MAGTSTAT is specified more than once in the RETURNS TABLE clause of a CREATE FUNCTION statement. The return column names for a user defined table function must be unique. -- Column MAGTSTAT is specified more than once in the CYCLE column list of the recursive common table expression. Recovery . . . : Do one of the following and try the request again: -- Specify unique names for each of the columns. -- Remove the column from all but one clause of a single ALTER TABLE statement. Multiple statements can be specified, if required.

    > CALL PROD00016805_DDL
    SQL State: 42704
    Vendor Code: -204
    Message: [SQL0204] PROD00016805_DDL in STURGEON type *N not found. Cause . . . . . : PROD00016805_DDL in STURGEON type *N was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, PROD00016805_DDL is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

    > DROP PROCEDURE PROD00016805_DDL
    SQL State: 42704
    Vendor Code: -204
    Message: [SQL0204] PROD00016805_DDL in STURGEON type *N not found. Cause . . . . . : PROD00016805_DDL in STURGEON type *N was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, PROD00016805_DDL is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

    > CL: RMVRPYL
    CPC2406: Reply list entry with sequence number 9666 removed from system reply list.
    CPC2406: Reply list entry with sequence number 9667 removed from system reply list.
    CPC2406: Reply list entry with sequence number 9668 removed from system reply list.
    Statement ran successfully (2835 ms = 2.835 sec)


    It's imperative that I manufacture a repeatable script that will not produce errors on subsequent executions.

    I am using IBM System i Navigator v6r1 against
    DB2 UDB for AS/400
    07.01.0000 V7R1m0
    AS/400 Toolbox for Java JDBC Driver
    Driver Version: 8.11

    I'm really out of my league with DB2 as well as with the iSeries and would sure appreciate some help.

    Thanks to all in advance.
    Ken

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try changing
    Code:
    IF (DUP <> 1) THEN
     ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10);
     SET DUP = 0;
    END IF;
    to
    Code:
    IF (DUP <> 1) THEN
     EXECUTE IMMEDIATE 'ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10)';
     SET DUP = 0;
    END IF;
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2013
    Posts
    9

    Wohoo!! It works!! ---thank you very much

    Thanks very much for the suggestion. I wasn't able to use it as the final solution but it caused me to think about using a prepared statement which works splendidly!

    The final solution follows:


    CREATE PROCEDURE PROD00016805_DDL ()
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    DECLARE SQL_STMT VARCHAR(128);
    IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN
    SET SQL_STMT = 'ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10)';
    PREPARE S1 FROM SQL_STMT;
    EXECUTE S1;
    END IF;
    END;

    CALL PROD00016805_DDL;
    DROP PROCEDURE PROD00016805_DDL;


    Thanks again for your suggestion as I never would have thought of this approach without it.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ksturgeon View Post
    I wasn't able to use it as the final solution
    Could you please comment on why, just for my education?
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2013
    Posts
    9

    Error When Using EXECUTE IMMEDIATE

    I'm sorry, I should have provided this info in my previous post. While I can't really say why it wouldn't work using EXECUTE IMMEDIATE I can provide the output that will hopefully provide you with an answer.


    > CL: CHGCURLIB CURLIB(GIAS_UTILS)
    CPC2198: Current library changed to GIAS_UTILS.
    Statement ran successfully (839 ms)

    > CL: ADDRPYL
    CPC2403: Reply List entry with sequence number 9666 added to system reply list.
    CPC2403: Reply List entry with sequence number 9667 added to system reply list.
    CPC2403: Reply List entry with sequence number 9668 added to system reply list.

    Statement ran successfully (1087 ms = 1.087 sec)
    > CL: CHGJOB INQMSGRPY(*SYSRPYL)
    Statement ran successfully (1538 ms = 1.538 sec)

    > CREATE PROCEDURE PROD00016805_DDL () LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE DUP INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '42704' SET DUP = 1; IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN IF (DUP <> 1) THEN EXECUTE IMMEDIATE 'ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10)'; SET DUP = 0; END IF; END IF; END
    SQL State: 42601
    Vendor Code: -104
    Message: [SQL0104] Token 'ALTER TABLE PFCOMAGTM ADD MAG was not valid. Valid tokens: : <IDENTIFIER> <PLI_STRING>. Cause . . . . . : A syntax error was detected at token 'ALTER TABLE PFCOMAGTM ADD MAG. Token 'ALTER TABLE PFCOMAGTM ADD MAG is not a valid token. A partial list of valid tokens is : <IDENTIFIER> <PLI_STRING>. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token 'ALTER TABLE PFCOMAGTM ADD MAG. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

    > CALL PROD00016805_DDL
    SQL State: 42704
    Vendor Code: -204
    Message: [SQL0204] PROD00016805_DDL in GI35ILC type *N not found. Cause . . . . . : PROD00016805_DDL in GI35ILC type *N was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, PROD00016805_DDL is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

    > DROP PROCEDURE PROD00016805_DDL
    SQL State: 42704
    Vendor Code: -204
    Message: [SQL0204] PROD00016805_DDL in GI35ILC type *N not found. Cause . . . . . : PROD00016805_DDL in GI35ILC type *N was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, PROD00016805_DDL is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

    > CL: RMVRPYL
    CPC2406: Reply list entry with sequence number 9666 removed from system reply list.
    CPC2406: Reply list entry with sequence number 9667 removed from system reply list.
    CPC2406: Reply list entry with sequence number 9668 removed from system reply list.
    Statement ran successfully (2803 ms = 2.803 sec)


    Thanks again for your help and forgive that I failed to provide this in my earlier posting.

Posting Permissions

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