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.

4 comments:

Henrik Loeser said...

I received some guesses by email suggesting missing whitespaces or that one file was created on Windows and executed on UNIX.
All that is not the reason. The problem (and the solution) can be found in the context of the statements - some of them not seen.
There are also some other hints embedded in the article. Keep on guessing... :)

Henrik Loeser said...

An answer to the quiz has been posted. The SQLCOMPAT setting is at fault...

idbit said...

I don't know the answer and I took hint from your second comment. It seems you are running the db2 command in PLSQL compatibility mode, whence forward slash be the terminating character. Change back to Db2 mode by SET SQLCOMPAT DB2 and then it should run ok! Hope I at least cheated well ;)

Sandeep Jamkar said...

i am using db2 export.out command i got error like this-
End of file reached while reading the command

my export.out command example
export to col1 of del modified by coldel^ select * from lrc4.col1;

LinkWithin

Related Posts with Thumbnails