DB2:Add Column if not present

Question

I have a schema called backup and a table called test in that. I need to add a column to the table called Test1 if it is not present in the table. Can you please tell me how to do it?

The following query returns me back an error.

BEGIN ATOMIC
IF (NOT EXISTS(
SELECT 1 FROM SYSCAT.COLUMNS WHERE TABNAME ='TEST_TABLE' AND COLNAME = 'TEST2'))
THEN 
     ALTER TABLE TEST_TABLE ADD TEST2 varchar(255);
END IF;
END
GO

The error I'm recieving is this:

[Error] Script lines: 1-8 --------------------------
 DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=ALTER TABLE TEST_TABLE ADD TEST;E = 'TEST2'))
THEN 
;<compound_return>, DRIVER=3.50.152
 Message: An unexpected token "ALTER TABLE TEST_TABLE ADD TEST" was found following "E = 'TEST2'))
THEN 
".  Expected tokens may include:  "<compound_return>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.50.152
 Line: 5 

Thanks, -Mike

Answer 1

You can not execute dynamic query inside procedure. Do it with EXECUTE IMMEDIATE.

CREATE PROCEDURE ASP.CHECKTEST
     DYNAMIC RESULT SETS 0
     MODIFIES SQL DATA
     LANGUAGE SQL
BEGIN
IF (NOT EXISTS(
SELECT 1 FROM SYSCAT.COLUMNS WHERE TABNAME ='TEST_TABLE' AND COLNAME = 'TEST2'))
THEN
    EXECUTE IMMEDIATE 'ALTER TABLE ASP.TEST_TABLE ADD COLUMN TEST2 varchar(255)';
END IF; 
END