0

I would like to write "procedures" in DB2 9.7 without defining the "CREATE PROCEDURE" -statement. Apparently this is something called "compiled" sql statement. However, I am having problems in getting valid syntax. E.g. the syntax below does not seem to work:

BEGIN
    DECLARE V_SQL VARCHAR(1024);
    SET V_SQL = 'BEGIN
    IF EXISTS(SELECT NAME FROM SYSIBM.SYSTRIGGERS WHERE NAME = ''TRIGGER_EMPLOYEE_FOR_DELETES'') THEN
    DROP TRIGGER TRIGGER_EMPLOYEE_FOR_DELETES;
    END IF;
    END;';

    PREPARE S1 FROM V_SQL;
    EXECUTE S1;
END

I have tried adding/removing ";" and statement symbol "!" but still cannot get it to work.

user1340582
  • 19,151
  • 35
  • 115
  • 171
  • An example similar to yours worked for me. A few thoughts: (1) Is `;` set as the statement delimiter in your SQL editor? If so, it will try to break up your command and send it as chunks. (2) Have you made sure that the contents of V_SQL work when run as a regular command? (3) Please post the exact error message(s) you are getting. – dan1111 Aug 20 '12 at 10:40
  • The error is: "DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=DROP TRIGGER;FOR_INSERTS') THEN ;, DRIVER=3.63.108" – user1340582 Aug 20 '12 at 11:16
  • My statement terminator is "!", not sure about the delimiter? – user1340582 Aug 20 '12 at 11:17
  • ! is fine for the statement terminator. I believe I figured it out--see below. – dan1111 Aug 20 '12 at 12:20
  • You are aware that in DB2 9.7, you can execute `CREATE OR REPLACE TRIGGER ...`, right? – Ian Bjorhovde Aug 20 '12 at 16:05
  • Ian: yes, but these scripts are executed with Ant, so I would need to run them after deploying the DB object. Anyways, no need to create the object anyway since this is a once-per-deployment -operation – user1340582 Aug 20 '12 at 18:25

1 Answers1

1

You cannot have a DROP TRIGGER statement within a compound SQL statement. See the DB2 documentation for compound SQL.

If you are able to move the IF statement outside of V_SQL, you could do something like this:

BEGIN
    DECLARE V_SQL VARCHAR(1024);
    IF EXISTS(SELECT NAME FROM SYSIBM.SYSTRIGGERS 
                  WHERE NAME = 'TRIGGER_EMPLOYEE_FOR_DELETES'
    ) THEN
        SET V_SQL = 'DROP TRIGGER TRIGGER_EMPLOYEE_FOR_DELETES;';
        PREPARE S1 FROM V_SQL;
        EXECUTE S1;
    END IF;
END

Of course, this wouldn't work if you need to set your condition dynamically.

dan1111
  • 6,576
  • 2
  • 18
  • 29
  • Ah, I should have paid closer attention to the document syntax. This will be tricky, because the condition is indeed set dynamically and placed inside a loop, looping through a list of tables :) DB2 has some strange syntax limitations that I don't understand. With SQL Server 2008 you just write your damn procedure with the full syntax and get on with it... :) – user1340582 Aug 20 '12 at 18:27
  • I believe the above code would work for what you want to do, because you could check NAME against a variable in the above example without a problem. i.e. replace `WHERE NAME = 'TRIGGER_EMPLOYEE_FOR_DELETES'` with `WHERE NAME = `. If you did need to do more than that dynamically, however, you could create a wrapper procedure that drop s the trigger, then call this procedure within your dynamic SQL (The wrapper procedure would also have to use dynamic SQL). I agree with you: DB2 has mystifying limitations. – dan1111 Aug 21 '12 at 08:19
  • Yes, apparently I can do exactly that. However, I am (before your BEGIN statement) declaring a global temporary table and the while loop that loops through all tables in the database. The temporary table stores the tables. Apparently I am not allowed to declare a global temporary table in a compiled sql statement... any way to loop the schema tables without declaring the temporary table? – user1340582 Aug 21 '12 at 11:24
  • Ok I solved my last part. Global temporary tables cannot be declared, so I created a for loop instead that looped through all tables. Thanks for all the help! – user1340582 Aug 21 '12 at 11:55