Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Cannot alter table (add unique constraint) in a SLQ PL store procedure for db2

    I am trying to create a store procedure in SQL PL for db2. A new field needs to be added as part of the unique key, so I need to drop the previous uk first, and then add the new unique key. That worked fine for MS SQLServer and Oracle, but I cannot make it for db2.

    Code:
    	CREATE PROCEDURE update_unique_key ()
    	LANGUAGE SQL
    	BEGIN 
    		DECLARE uk_constraint_name VARCHAR(50);
    		DECLARE sql_stmt VARCHAR(100);	
    		SELECT constname INTO uk_constraint_name FROM SYSCAT.TABCONST WHERE tabname = 'TABLE_NAME' AND type = 'U';
    		SET sql_stmt = 'alter table TABLE_NAME drop constraint ' || char(uk_constraint_name);
    		SET sql_stmt_2 = 'alter table TABLE_NAME add unique (F1, F2)';	
    
    		prepare s1 from sql_stmt;
    		prepare s2 from sql_stmt_2;
    		
    		EXECUTE IMMEDIATE s1;	
    		EXECUTE IMMEDIATE s2;		
    	END
    Getting this error when trying to deploy the routine with IBM Data Studio as client (the database is db2 9.5)

    Code:
    	DEV.UPDATE_UNIQUE_KEY - Deploy started.
    	Create stored procedure returns SQLCODE: -206, SQLSTATE: 42703.
    	DEV.UPDATE_UNIQUE_KEY: 8: "SQL_STMT_2" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.12.55
    	"SQL_STMT_2" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.12.55
    The thing I don't understand is:
    - If in the routine I just drop the constraint it works.
    - I can add the constraint just doing an alter table from a sql client (Squirrel)
    - If I have just a routine trying to add the constraint I get the same error.

    I appreciate any help. Thanks
    Last edited by ocarman; 09-30-11 at 13:18.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The error does not match the source code.

  3. #3
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by n_i View Post
    The error does not match the source code.

    Sorry, I tried so many things that I copied the wrong message. I guess what you meant is fixed now. Anyway, it was just a typo.

    Any suggestions??

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Any suggestions??
    Yes. You need to declare the second variable too.

  5. #5
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by n_i View Post
    Yes. You need to declare the second variable too.
    First time ever (hope last one) with SQL PL and store procedures. Still, not an excuse...

    Thanks

Tags for this Thread

Posting Permissions

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