Why am I getting this SQL/DB error?

Question

Hey everyone,

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 1

You have a comma after the name line

Change:

NAME VARCHAR(20) NOT NULL,

to:

NAME VARCHAR(20) NOT NULL

Answer 2

I would insert a space or a line separator between CREATE TABLE PERSONS and (
just to be safe.

Answer 3

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.

Answer 4

If I include semicolons and use the -t flag, I get:

SQL0104N  An unexpected token "/" was found following "BEGIN-OF-STATEMENT".  
Expected tokens may include:  "<values>".  SQLSTATE=42601