Earlier this week I posted a DB2 quiz showing an error message. It showed that creating a function failed with an "end of file reached" error. I received some feedback by email and the consensus was that the error somehow was caused by the earlier executed scripts. But what exactly is the cause?
In the quiz I had mentioned that we ran into the error during a proof of concept when porting an application from Oracle to DB2. This is a first hint. The DB2 Information Center has a document describing how to set up the DB2 environment for Oracle application enablement. In there are two versions for executing SQL statements listed, one based on clpplus, another one using the DB2 Command Line Processor. For running statements with the DB2 CLP it is recommended to use "SET SQLCOMPAT PLSQL" first. That switches the CLP to taking the forwad slash ("/") on a new line as PL/SQL statement termination character. Using "SET SQLCOMPAT DB2" switches back to regular operations.
The error "end of file reached" indicates that a statement is incomplete. This could mean a statement is missing a clause (usually a syntax error) or the statement or file is missing something. Is it a statement terminator? What we tried then while debugging is to execute a simple CREATE TABLE:
>>> db2 -tf table2.sql
DB20000I The SQL command completed successfully.
Strangely, this works. Is it related to our function definition? We opened another window with a new DB2 connection and the CREATE FUNCTION when directly pasted into the CLP window succeeded. And then it dawned on us...
As with other settings, including the database connection, they are kept between invocations of DB2 CLP. In the example in the quiz we only open the database connection once. In one of the scripts that we executed, the SET SQLCOMPAT PLSQL was set, but not reset to the default using SET SQLCOMPAT DB2. Simple DDL like CREATE TABLE seems unimpacted by this setting. However, creating more complex objects like the shown function gives an error. I asked the DB2 Information Development team (the one creating the documentation) to provide details on the SQLCOMPAT switch.
What can be taken away is to make sure that when putting a switch into a file, make sure switching back to defaults is part of the same file. Sounds like best practices from coding days... ;-)
BTW: I disabled captcha codes for comments. This should make it more interactive and lead to less emails. Have a great weekend.