I have the following procedure in which I attempt to query the system views to determine if a column exists within a table. If the column does not exist then it is added.
CREATE PROCEDURE PROD00016805_DDL ()
RESULT SETS 1
IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN
ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10);
DROP PROCEDURE PROD00016805_DDL;
The procedure executes fine the first time but if I attempt to deploy it a second time I get the following failure:
GI35ILC.PROD00016805_DDL - Deploy started.
[SQL0612] MAGTSTAT is a duplicate column name.
GI35ILC.PROD00016805_DDL - Deploy failed.
GI35ILC.PROD00016805_DDL - Roll back completed successfully.
I realize that the column already exists but would have thought that the logic surrounding the alter table statement would prevent the error from being raised.
I am new to DB2 but I've done similar operations countless times in SQL Server and would have thought that taking a similar approach would work in DB2.
I really need a way that I can conditionally alter table structures as some clients may have already made the alteration while others may not have done so. This small unit of work would be part of a larger script that would be issued to all clients.
DB2 UDB for AS/400
AS/400 Toolbox for Java JDBC Driver
Driver Version: 8.11
I have attempted deployment using IBM Data Studio 4.1 and IBM System i Navigator v6r1.