IBM Support

Does the statement termination symbol ';' get detected by a DB2 script even if it is commented out?

Question & Answer


Question

Does the statement termination symbol ';' get detected by a DB2 script even if it is commented out?

Answer

Yes, the statement termination symbol will get detected by a DB2 script even if it is placed within a comment.

This behavior can be observed by running the following tests :


Create a table as follows :

>db2 create table tpo(id int)
DB20000I The SQL command completed successfully.

Test 1 :

Create a batch file tpo.bat, with the following contents :
<tpo.bat>
insert into tpo values(10);
insert into tpo values(20);
</tpo.bat>

Execute the script :

>db2 -tvf tpo.bat
insert into tpo values(10)
DB20000I The SQL command completed successfully.

insert into tpo values(20)
DB20000I The SQL command completed successfully.


>db2 select * from tpo

ID
-----------
10
20

ie. it works properly as expected.


Test 2 :

Alter the batch file tpo.bat, with the following contents :
<tpo.bat>
insert into tpo values(10);
insert into tpo values(20);--insert into tpo values(30);
</tpo.bat>

Execute the script :

>db2 -tvf tpo.bat
insert into tpo values(10)
DB20000I The SQL command completed successfully.

insert into tpo values(20);--insert into tpo values(30)
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 ";" was found following "into TPO values(20)".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601


>db2 select * from tpo

ID
-----------
10


Observation : After the comment '--', the text "insert into tpo values(30)" gets ignored because it is commented out, but however the statement termination character(Semicolon) gets detected even though it is also commented out.
It therefore causes the following error to be returned :

SQL0104N An unexpected token ";" was found following "into TPO values(20)".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601


Resolving the issue :

Alter the batch file tpo.bat, with the following contents(ie. remove the semicolon before the comment) :
<tpo.bat>
insert into tpo values(10);
insert into tpo values(20) --insert into tpo values(30);
</tpo.bat>


>db2 -tvf tpo.bat
insert into tpo values(10)
DB20000I The SQL command completed successfully.

insert into tpo values(20) --insert into tpo values(30)
DB20000I The SQL command completed successfully.


>db2 select * from tpo

ID
-----------
20
10

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"HADR - Scripts","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21614891