Fun when running DB2 CLP scripts

Having spent some time with DB2 and Linux recently, I hit a problem today whereby I was attempting to execute a SQL script from the DB2 command line ( CLP ) using the command: -

db2 -tvf createDb.sql

This worked perfectly for five out of six databases. However, the sixth database did not create, and I noticed that the script ran through rather too quickly ( even for my blindingly fast VMware ESX / Red Hat environment ).

When I looked at the output from the command, I saw: -

SQL0104N  An unexpected token "CONNECT" was found following "<identifier>".  

Expected tokens may include:  "RESTRICTIVE".  SQLSTATE=42601

B21034E  The command was processed as an SQL statement because it was not a 

valid Command Line Processor command.  During SQL processing it returned:

etc.

When I dug into the script ( createDb.sql ), I noticed that the commands were, for some strange reason, separated by the AT (@) character than, as I'd normally expect, the semi-colon (;) character.

Rather than hacking the script, my guru/colleague, RobT, showed me how to override the DB2 -tvf command to force it to use a different separator, as follows: -

db2 -td@ -vf createDb.sql

which did the trick.

Easy when you know, or know someone who knows how :-)