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...

2 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:

    DECLARE v_txt VARCHAR(128);–


    • 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.

Leave a Reply

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