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
select * from syscat.tables#
select * from syscat.columns$
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:
SELECT * FROM SYSCAT.TABLES FETCH FIRST 1 ROW ONLY;
--#SET TERMINATOR #
SELECT * FROM SYSCAT.COLUMNS FETCH FIRST 1 ROW ONLY#
--#SET TERMINATOR $
Similar approach can be adopted in Java DB2 programming by submitting it as part of the query you sent to DB2.