Posted by 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
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
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.
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
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.
Awesome, thank you for the simple example!
Thank You for helping the DB2 community:)
Hi
I’m using IBM i Series Navigator RunSQL GUI and didnt find where the seperator defintion is found
My articles are targeted at Db2 LUW. I don’t have experience with this GUI to tell you where to find it.