DB2 Basics: Statement Terminators

You may also like...

6 Responses

  1. Joachim Klassen says:

    I always like using a semikolon followed by a sql comment as terminator for statements in a trigger or procedure body, then I can use a solely semikolon as terminator for the whole statement:

    BEGIN
    DECLARE v_txt VARCHAR(128);–
    SET v_txt = VALUES CURRENT USER;–
    END;

    Cheers
    Joachim

    • Ember Crooks says:

      Yes, that works most of the time. However, there are some scenarios where I have seen problems with that approach, and the alternate delimiter resolves those situations.

  2. Wayne Zhu says:

    Here is an example from the “famous” SAMPLE db. To save space, only the pertinent portions are pasted:
    [db2inst0@s0 ~]$ db2level
    DB21085I This instance or install (instance name, where applicable:
    “db2inst0”) uses “64” bits and DB2 code release “SQL11012” with level
    identifier “0203010F”.
    Informational tokens are “DB2 v11.1.2.2”, “s1706091900”, “DYN1701310100AMD64”,
    and Fix Pack “2”.
    Product is installed at “/opt/ibm/db2/V11.1”.

    [db2inst0@s0 ~]$ db2sampl

    Creating database “SAMPLE”…
    Connecting to database “SAMPLE”…
    Creating tables and data in schema “DB2INST0″…
    Creating tables with XML columns and XML data in schema “DB2INST0″…

    ‘db2sampl’ processing complete.
    [db2inst0@s0 ~]$ db2look -d sample -e 2>/dev/null| sed -n ‘/CREATE TRIGGER do_not_del_sales/,/END/p’ | tee create_trig.sql
    CREATE TRIGGER do_not_del_sales NO CASCADE BEFORE DELETE ON staff REFERENCING
    OLD AS oldstaff FOR EACH ROW MODE DB2SQL WHEN(oldstaff.job = ‘Sales’) BEGIN
    ATOMIC SIGNAL SQLSTATE ‘75000’ (‘Sales staff cannot be deleted… see the DO_NOT_DEL_SALES trigger.’);
    END;
    [db2inst0@s0 ~]$ # drop the trigger
    [db2inst0@s0 ~]$ db2 “drop trigger do_not_del_sales”
    DB20000I The SQL command completed successfully.
    [db2inst0@s0 ~]$
    [db2inst0@s0 ~]$ # try to recreate the trigger and error out
    [db2inst0@s0 ~]$ db2 -tf create_trig.sql
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token “END-OF-STATEMENT” was found following
    “DEL_SALES trigger.’)”. Expected tokens may include: “”.
    LINE NUMBER=3. SQLSTATE=42601

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token “END-OF-STATEMENT” was found following “END”.
    Expected tokens may include: “JOIN “. SQLSTATE=42601

    • Ember Crooks says:

      Wayne Zhu’s example using the td option:
      Now let use the td option
      [db2inst0@s0 ~]$ db2 drop db sample
      DB20000I The DROP DATABASE command completed successfully.
      [db2inst0@s0 ~]$ db2sampl

      Creating database “SAMPLE”…
      Connecting to database “SAMPLE”…
      Creating tables and data in schema “DB2INST0″…
      Creating tables with XML columns and XML data in schema “DB2INST0″…

      ‘db2sampl’ processing complete.

      [db2inst0@s0 ~]$ db2look -d sample -td @ -e 2>/dev/null| sed -n ‘/CREATE TRIGGER do_not_del_sales/,/END/p’ | tee create_trig.sql
      CREATE TRIGGER do_not_del_sales NO CASCADE BEFORE DELETE ON staff REFERENCING
      OLD AS oldstaff FOR EACH ROW MODE DB2SQL WHEN(oldstaff.job = ‘Sales’) BEGIN
      ATOMIC SIGNAL SQLSTATE ‘75000’ (‘Sales staff cannot be deleted… see the DO_NOT_DEL_SALES trigger.’);
      END@

      [db2inst0@s0 ~]$ db2 connect to sample

      Database Connection Information

      Database server = DB2/LINUXX8664 11.1.2.2
      SQL authorization ID = DB2INST0
      Local database alias = SAMPLE

      [db2inst0@s0 ~]$ db2 “drop trigger do_not_del_sales”
      DB20000I The SQL command completed successfully.
      [db2inst0@s0 ~]$ db2 -td@ -f create_trig.sql
      DB20000I The SQL command completed successfully.

    • Ember Crooks says:

      Awesome, thank you for the simple example!

  3. Wayne Zhu says:

    Thank You for helping the DB2 community:)

Leave a Reply

Your email address will not be published. Required fields are marked *