Tuesday, October 9, 2012

DB2 Quiz: Why the error? (create function)

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.