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

ocarman · #1 (**permalink**) 09-30-11, 11:44

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