Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2011
    Posts
    25

    Answered: Anonymous block execution

    I want to execute one anonymous block in DB2. with this statement db2 -td@ -vf v1.sql "

    The v1.sql contains:
    BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE T1';
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END@

    Please assist me how to execute this block?

  2. Best Answer
    Posted by db2mor

    "Your syntax inside v1.sql is not valid.

    Also it is often simpler to code the terminator detail inside the file via this syntax
    --#SET TERMINATOR @
    because that lets you omit the -td@ command line swtich and lets you change the terminator on the fly inside the script

    Next you can fix the syntax inside v1.sql in various ways, but one dumb way is:

    begin
    declare rc integer default 0;
    declare continue handler for sqlexception set rc=1;
    execute immediate 'drop table T1';
    end
    @

    But you should probably be more robust if you want to do this on a production system, to record the sqlcode, sqlstate of any failure etc."


  3. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Your syntax inside v1.sql is not valid.

    Also it is often simpler to code the terminator detail inside the file via this syntax
    --#SET TERMINATOR @
    because that lets you omit the -td@ command line swtich and lets you change the terminator on the fly inside the script

    Next you can fix the syntax inside v1.sql in various ways, but one dumb way is:

    begin
    declare rc integer default 0;
    declare continue handler for sqlexception set rc=1;
    execute immediate 'drop table T1';
    end
    @

    But you should probably be more robust if you want to do this on a production system, to record the sqlcode, sqlstate of any failure etc.

  4. #3
    Join Date
    Nov 2011
    Posts
    25
    Thanks for providing the answer

Posting Permissions

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