DB2 Basics: Statement Terminators

The default statement terminator for DB2 on Linux, UNIX, and Windows platforms is the semi-colon (;). If you are executing a script of SQL using the -tf options, and do not specify an alternate terminator, DB2 will assume you are using the semi-colon.

There are some statements that require semi-colons within the statements, though. For these kinds of statements, the semi-colon doesn’t work well as a terminator.

Perhaps the most obvious way you will see issues with this is if you try to use db2look to re-create triggers or stored procedures. Some triggers and stored procedures may be re-created just fine, while others will not work correctly or even be created incorrectly.

The solution to this is to use an alternate statement terminator. My favorite tends to be ‘@’. For Oracle compatibility, ‘/’ may be a good choice.

To use an alternate statement terminator, simply end every statement in the file of SQL you wish to execute with that alternate terminator, and then specify the terminator when executing the file. For example, if you chose @ as your alternate terminator:

db2 -td@ -vf filename.sql

Notice that there is no space between the d and the @, and other options are specified after another (-) dash.

db2look is a tool commonly used for generating SQL, and it also has an option to use an alternate delimiter. I often generate two files when using db2look to extract the ddl for a database – one with the standard terminator and one with an alternate terminator. This gives me more options when using the file later.

To use an alternate terminator (@) with db2look:

db2look -d sample -td @ -e -a -o db2look_altdelim.ddl

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 *