Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2007
    Posts
    47

    Unanswered: bonehead question

    I am verse in Microsoft SQL Server, so forgive me for being this way...

    db2level

    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08025" with
    level identifier "03060106".
    Informational tokens are "DB2 v8.1.12.99", "s060429", "WR21368", and FixPak
    "12".
    Product is installed at "E:\PROGRA~1\IBM\SQLLIB".


    I'm trying to write a script that declares variables, creates cursor, opens cursors, fetches from it and loops through it until done. I have 15 million rows to delete.

    I just want to run it either on command line or java DB2 Admin Console.

    But I cannot get past step 1, declaring variables. I have something o so simple like this in my t.sql file:

    CONNECT TO TEST2;

    DECLARE freq INT DEFAULT 10000;

    E:\Jobs\archive>DB2 -TVF T.SQL
    CONNECT TO TEST2

    Database Connection Information

    Database server = DB2/NT 8.2.5
    SQL authorization ID = me
    Local database alias = TEST2


    DECLARE freq INT DEFAULT 10000
    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 "10000" was found following "ARE FREQ INT
    DEFAULT". Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

    I even got so simple as DECLARE FREQ INT; and get similar error.
    Last edited by bic; 10-22-08 at 15:13.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The DB2 command processor has very limited features. In particular, you cannot use cursors in the CLP session. You will have to create a stored procedure that does what you need, then call it from the CLP (or the java-based Command Editor).
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2007
    Posts
    47
    ok, so I try:

    CREATE PROCEDURE TEST ()
    LANGUAGE SQL

    BEGIN

    DECLARE TEST INTEGER;

    END



    DECLARE TEST INTEGER
    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 "END-OF-STATEMENT" was found following "DECLARE
    TEST INTEGER". Expected tokens may include: "<psm_semicolon>". LINE
    NUMBER=8. SQLSTATE=42601

    My terminator is ;. I even tried just INT, but no dice. It's almost like the parser is hacking away at it, note the N before the DECLARE:

    An unexpected token "END-OF-STATEMENT" was found following "N
    DECLARE TEST INT"

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    This question pops up on this board at least twice a week...

    CREATE PROCEDURE is a single [compound] statement, up the the final END, so the first statement delimiter should appear right after the END, otherwise the statement will appear as syntactically incorrect to the parser. Now, you also have to delimit the statements _inside_ the CREATE PROCEDURE statement. The way out is to redefine the statement delimiter and place the _new_ delimiter at the end of the CREATE statement, while leaving alone the "internal" delimiters.

    Thusly,
    Code:
    CREATE PROCEDURE TEST ()
    LANGUAGE SQL
    
    BEGIN
    
    DECLARE TEST INTEGER;
    
    END@
    where "@" is the new delimiter.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    By the way, if you are curious why this is so (seemingly) illogical, here's the answer:

    When you submit any SQL statement to the command processor, there are ultimately _two_ parsers that do the work. First, the _command processor_ parser needs to understand what is it that you are throwing at at: an SQL statement, a command, or some gibberish. The CLP parser uses whatever statement delimiter you've defined to split the input and analyze it. If it finds a command (such as CONNECT, LOAD, etc.) it processes it internally. Everything else is assumed to be SQL and gets sent to the database manager.

    The database manager uses an SQL parser, where the delimiter is always the semicolon, to analyze the statement and execute it.

    In your example, when you have the semicolon as the CLP statement delimiter, the CLP parser rightfully decides that "INTEGER" must be the end of the input, since there is a delimiter after that. Since "CREATE PROCEDURE .... INTEGER" does not look like a valid CLP command, the whole thing is sent to the SQL parser, which chokes on it, naturally, because the string is not a valid SQL statement either.

    If you redefine the CLP delimiter then the entire CREATE PROCEDURE ... END statement will be sent to the SQL parser.

    If, keeping this in mind, you read both error messages, they will make a bit more sense.
    Last edited by n_i; 10-22-08 at 16:48.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by n_i
    The DB2 command processor has very limited features. In particular, you cannot use cursors in the CLP session. You will have to create a stored procedure that does what you need, then call it from the CLP (or the java-based Command Editor).
    May I correct you on this?
    Code:
    db2 => declare c1 cursor for select * from sysibm.sysdummy1
    DB20000I  The SQL command completed successfully.          
    db2 => open c1                                             
    DB20000I  The SQL command completed successfully.          
    db2 => fetch c1                                            
    
    IBMREQD
    -------
    Y      
    
      1 record(s) selected.
    
    db2 => fetch c1
    
    IBMREQD
    -------
    
      0 record(s) selected.
    
    db2 => close c1
    DB20000I  The SQL command completed successfully.
    (Granted, I run this on DB2 V9.5. I don't know if V8 supported this - but it's easy to give this a try. You have to turn off auto-commit or use a WITH HOLD cursor.)

    The actual problem of the OP is that you cannot declare variables outside a compound statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I stand corrected. However, the CLP functionality is still limited and the OP would hit that limit eventually, so I thought I'd show what was in my opinion the better way.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You're right with this - no question about that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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