Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2010
    Posts
    16

    Unanswered: Problems executing Scripts

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What did you change the termination character to?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2010
    Posts
    16
    I changed it to @.

    But just one time and for test...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2010
    Posts
    16
    It's ;

    I just used @ for a test.

    I tried executing piece by piece and it worked fine.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Probably some bad end-of-line charaters that copied in from your source.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Sep 2010
    Posts
    16
    I attached a file with the scripts. I coudn't find any error
    Attached Files Attached Files

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Execute from DB2 Command Window using "db2 -tvf <file name>"

  9. #9
    Join Date
    Sep 2010
    Posts
    16
    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

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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
    Attached Files Attached Files

  11. #11
    Join Date
    Oct 2010
    Posts
    19
    rafaelmcosta,
    i executed your script without any errors, although i specified schema in the script:

    CREATE TABLE DMONIN.CUSTOMERS (
    CUST_ID INTEGER NOT NULL PRIMARY KEY,
    FIRSTNME VARCHAR(100) NOT NULL,
    ... etc.

    INSERT INTO DMONIN.CUSTOMERS (CUST_ID, FIRSTNME, LASTNAME)... etc.
    I using last Toad, but i believe there no difference where to run this script. Good luck.

  12. #12
    Join Date
    Sep 2010
    Posts
    16
    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 :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •