Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    9

    Unanswered: Alter Table with Stored Procedure

    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
    LANGUAGE SQL
    P1: BEGIN
    IF NOT EXISTS (SELECT * FROM SYSCOLUMNS WHERE TABLE_NAME = 'PFCOMAGTM' AND COLUMN_NAME = 'MAGTSTAT') THEN
    ALTER TABLE PFCOMAGTM ADD MAGTSTAT CHAR(10);
    END IF;
    END P1
    CALL PROD00016805_DDL;
    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:

    Deploy GI35ILC.PROD00016805_DDL
    Running
    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
    07.01.0000 V7R1m0
    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.

    Thank you very much for your help.
    Ken

  2. #2
    Join Date
    Aug 2013
    Posts
    9

    Issue Resolved

    Well, sometimes a guy just has to make a fool out of himself before he sees the light. My issue was in the first three lines.

    I changed...
    CREATE PROCEDURE PROD00016805_DDL ()
    RESULT SETS 1
    LANGUAGE SQL

    to..
    CREATE PROCEDURE PROD00016805_DDL ()
    LANGUAGE SQL
    MODIFIES SQL DATA

    Now the procedure can be recreated and executed multiple times without issue.

Posting Permissions

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