Last week during a Proof-of-Concept (PoC) with an ISV we tried to port and deploy database objects from Oracle to DB2. During some testing I ran into a strange error (at least at first) and only stepping back and getting some coffee helped to explain it. Can you guess what went wrong and why we ran into the error?
For the actual porting we used the IBM Data Movement Tool, but the DB2 Command Line Processor for some side testing. Here is what we did on the command line:
>>> db2 connect to mydb
Database Connection Information
Database server = DB2/LINUX 10.1.0
SQL authorization ID = HLOESER
Local database alias = MYDB
>>> db2 -tf script1.sql
DB20000I The SQL command completed successfully.
>>> db2 -tf scriptN.sql
DB20000I The SQL command completed successfully.
>>> db2 -tf func.sql
DB21007E End of file reached while reading the command.
>>> cat func.sql
CREATE OR REPLACE FUNCTION TAN (X DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN SIN(X)/COS(X);
>>> db2 -tf func.sql
DB21007E End of file reached while reading the command.
Basically we successfully tested several SQL scripts before we ran into the "end of file" error. There were no special hidden characters in the file. Opening another database connection in another window and executing the script there succeeded.
Do you know why? Any guesses? I will post the solution in a follow-up article.