Why am I getting this SQL/DB error?

Question

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,

What happens when you type your statements into the db2 command interpreter? — Jim Counts
– Jim Counts, Commented Feb 20, 2009 at 6:17

eugensk · Accepted Answer · 2009-02-20 06:22:09Z

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.

Element · Accepted Answer · 2009-02-20 04:27:10Z

2

You have a comma after the name line

Change:

NAME VARCHAR(20) NOT NULL,

to:

NAME VARCHAR(20) NOT NULL

answered Feb 20, 2009 at 4:27

Element

4,0597 gold badges45 silver badges51 bronze badges

1 Comment

barfoon Over a year ago

Thanks for noticing this - however I still receive the same error.

barfoon · Accepted Answer · 2009-02-20 06:01:24Z

1

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

answered Feb 20, 2009 at 6:01

barfoon

28.4k29 gold badges97 silver badges140 bronze badges

1 Comment

eugensk Over a year ago

looks like it treated your path or part of it as a script. Thats the only explanation for "/" that I have.

eugensk · Accepted Answer · 2009-02-20 05:10:19Z

0

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

answered Feb 20, 2009 at 5:10

eugensk

1,9621 gold badge14 silver badges20 bronze badges

Comments

asharma · Accepted Answer · 2016-11-11 14:21:27Z

0

The following syntax without spaces works right on the command line,

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

formatting a file with this and not the paragraph style worked for my assignment here - http://www.eecs.yorku.ca/course_archive/2016-17/F/3421/project/create/yrb-create.txt

answered Nov 11, 2016 at 14:21

asharma

11 bronze badge

Collectives™ on Stack Overflow

Why am I getting this SQL/DB error?

5 Answers 5

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related