DB2 Changing Statement Terminator Symbol

When you pass in -t command option to DB2 CLP, the delimiter ; (Semi colon) is turn on for enabling you to enter multiple statement lines before submitting it. This is typically acceptable behavour when you need to submit a single statement that encompassing multiple lines in the console.

What if you need to submit a large and complex stored procedure creation script to CLP? You can save the codes in a physical file then use db2 -t -f myProc.sql.

Then you hit errors that doesn't make sense at all.

Most probably the query parser is confused with the statement terminator and your substatement terminator. Substatements, such as those that you embedded in your stored procedure are forced to use ; as their termination character. In this case, when the parser encounter the first embedded semicolon, it would think that the statement is ready for parsing. Ta da. it strew up.

So, a better command would be "db2 -td# -f myProc.sql" assuming you are using # symbol as your statement terminator.

Yet, you might face another issue of encountering multiple different statement terminator in the same CLP session.

So you decide to

db2 -td#
select * from syscat.tables#
quit#

db2 -td$
select * from syscat.columns$
quit$

This example is trivial, but you get my point.

So, is there a better solution? O yeah, you can use one of the DB2 Control Option in the form of:

--#SET TERMINATOR

For example:

db2 -t
SELECT * FROM SYSCAT.TABLES FETCH FIRST 1 ROW ONLY;
--#SET TERMINATOR #
SELECT * FROM SYSCAT.COLUMNS FETCH FIRST 1 ROW ONLY#
--#SET TERMINATOR $
VALUES (1)$

Similar approach can be adopted in Java DB2 programming by submitting it as part of the query you sent to DB2.