barfoon barfoon - 22 days ago 7
SQL Question

Why am I getting this SQL/DB error?

I am trying to run a simple SQL statement with DB2 and am having a few problems.
I would like to have a single script in a txt/db2 file and have the engine process all of the commands

Here is the script:

CONNECT TO MYDB

CREATE TABLE PERSONS(
PID SMALLINT NOT NULL,
NAME VARCHAR(20) NOT NULL
)

TERMINATE


When I run a db2 -f /pathtofile I get:

SQL0104N An unexpected token "(" was found following "CREATE TABLE PERSONS".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601


What am I doing wrong? Is there something wrong with my script?
Also, why is it working without ";" terminators at the end of my statements?

Thank you,

Answer

May be this will be of help,
http://www.uc.edu/R/r25/documentation/Version3.2/install_instructions.pdf:

The scripts use a semi-colon (;) to terminate each SQL command. If you use the DB2 Command Line Processor, you should remember to use the “-t” flag.
...
If you do not use the -t flag, you will get errors such as the
following upon running the db2ct32.sql script:
create table “ACCOUNTS” (
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token “(“ was found following “ate table “ACCOUNTS””.
Expected tokens may include: “END-OF-STATEMENT”. SQLSTATE=42601

So, I would add semicolons and invoke with -t switch whatever nonsense it stands for.
I looked into samples, they use something like

db2 -tf "pathtofile"

Also with

db2 -tvf "pathtofile"

you might get more diagnostics.
Don't push proprietary soft to the limits, they are not that wide.

Comments