Hello All!

I'm having problems executing scripts on DB2 Command Editor.

This script is part of Db2 on Campus training.


INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00001,'MICHAEL','THOMPSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00002,'SALLY','KWAN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00003,'JOHN','GEYER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00004,'IRVING','STERN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00005,'EVA','PULASKI');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00006,'EILEEN','HENDERSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00007,'THEODORE','SPENSER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00008,'VINCENZO','LUCCHESSI');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00009,'SEAN','O''CONNELL');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000010,'DOLORES','QUINTANA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000011,'HEATHER','NICHOLLS');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000012,'BRUCE','ADAMSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000013,'ELIZABETH','PIANKA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000014,'MASATOSHI','YOSHIMURA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000015,'MARILYN','SCOUTTEN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000016,'JAMES','WALKER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000017,'DAVID','BROWN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000018,'WILLIAM','JONES');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000019,'JENNIFER','LUTZ');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000020,'JAMES','JEFFERSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000021,'SALVATORE','MARINO');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000022,'DANIEL','SMITH');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000023,'SYBIL','JOHNSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000024,'MARIA','PEREZ');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000025,'ETHEL','SCHNEIDER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000026,'JOHN','PARKER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000027,'PHILIP','SMITH');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000028,'MAUDE','SETRIGHT');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000029,'RAMLAL','MEHTA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000030,'WING','LEE');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000031,'CHRISTINE','HAAS');
COMMIT;



I insert the script and then I click in Execute, the following error is presented:

------------------------------------------------------------------------------
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00001,'MICHAEL','THOMPSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00002,'SALLY','KWAN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00003,'JOHN','GEYER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00004,'IRVING','STERN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00005,'EVA','PULASKI');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00006,'EILEEN','HENDERSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00007,'THEODORE','SPENSER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00008,'VINCENZO','LUCCHESSI');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (00009,'SEAN','O''CONNELL');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000010,'DOLORES','QUINTANA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000011,'HEATHER','NICHOLLS');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000012,'BRUCE','ADAMSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000013,'ELIZABETH','PIANKA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000014,'MASATOSHI','YOSHIMURA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000015,'MARILYN','SCOUTTEN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000016,'JAMES','WALKER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000017,'DAVID','BROWN');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000018,'WILLIAM','JONES');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000019,'JENNIFER','LUTZ');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000020,'JAMES','JEFFERSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000021,'SALVATORE','MARINO');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000022,'DANIEL','SMITH');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000023,'SYBIL','JOHNSON');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000024,'MARIA','PEREZ');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000025,'ETHEL','SCHNEIDER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000026,'JOHN','PARKER');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000027,'PHILIP','SMITH');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000028,'MAUDE','SETRIGHT');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000029,'RAMLAL','MEHTA');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000030,'WING','LEE');
INSERT INTO CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME) VALUES (000031,'CHRISTINE','HAAS');
COMMIT
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 "(00001,'MICHAEL','THOMPSON'); INSERT INT" was
found following "ME, LASTNAME) VALUES". Expected tokens may include:
"<space>". SQLSTATE=42601

SQL0104N An unexpected token "(00001,'MICHAEL','THOMPSON');
INSERT INT" was found following "ME, LASTNAME) VALUES". Expected tokens may include: "<space>".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

sqlcode: -104

sqlstate: 42601



I did some research and the results pointed to a change in statement termination character, but also didn't work.

Need some help!

Thanks in advance

Rafael

What did you change the termination character to?

I changed it to @.

But just one time and for test...

Looking at the SQL statements you have, what do you think the termination character should be?

Try doing one statement at a time. Make sure what look like spaces are actually spaces.

It's ;

I just used @ for a test.

I tried executing piece by piece and it worked fine.

Probably some bad end-of-line charaters that copied in from your source.

I attached a file with the scripts. I coudn't find any error

Execute from DB2 Command Window using "db2 -tvf <file name>"

Hi,

I've tried this way too, but didn't work.

Output:

db2 -tvf quiklab4.db2
COMMITAINT FK2AFOREIGN KEY (CUST_ID) REFERENCES CUSTOMERS(CUST_ID)TY PRIMARY KEY,
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 "CREATE TABLE CUSTOMERS ( CUST_ID INTEGER "
was found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<space>". SQLSTATE=42601

db2inst3@rafael ~ $ db2 -tvf quiklab4.dat
COMMIT INTO SALES (BOOK_ID, CUST_ID, QTY, PRICE, PURCH_DATE) VALUES (80002, 000030, 4, 10.00, TIMESTAMP('2004-03-25-00.00.00.000000'));
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 "(00001,'MICHAEL','THOMPSON'); INSERT INT" was
found following "ME, LASTNAME) VALUES". Expected tokens may include:
"<space>". SQLSTATE=42601


Thanks

I saved your file with .txt and executed on DB2/Windows - here's the output.

There are some special characters in the file (page breaks ?). Remove them

rafaelmcosta,
i executed your script without any errors, although i specified schema in the script:

I using last Toad, but i believe there no difference where to run this script. Good luck.

I used a dos2unix tool and it solved the problem.

silly mistake! It didn't have any typical dos character ( ^M )....

Thank you all for help me :-)