If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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