Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2008
    Posts
    16

    Unanswered: declaring variables inside DB2

    DB2 version:
    ================================================== ==========
    About DB2 Administration Tools Environment
    ================================================== ==========
    DB2 administration tools level:
    Product identifier SQL09050
    Level identifier 03010107
    Level DB2 v9.5.0.808
    Build level s071001
    PTF NT3295
    ================================================== ==========
    Java development kit (JDK):
    Level IBM Corporation 1.5.0
    ================================================== ==========

    I am trying to use the following command enside the command editor:
    DECLARE (v_NextId) INT;

    DECLARE VARIABLE2 INTEGER;

    I get the following errors:

    DECLARE (v_NextId) INT
    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 "DECLARE ". Expected
    tokens may include: "DELIMITED_TYPE_IDENTIFIER". SQLSTATE=42601

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

    I don't understand what I'm doing wrong. Does DB2 have this functionality?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DECLARE variable is part of a Compound SQL statement. You need to put a BEGIN and END around your compound statement.

    Andy

  3. #3
    Join Date
    Sep 2008
    Posts
    16
    Still not working.

    ------------------------------ Commands Entered ------------------------------
    BEGIN
    DECLARE (v_NextId) INT;
    END;
    ------------------------------------------------------------------------------
    BEGIN DECLARE (v_NextId) INT
    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 "(v_NextId" was found following "BEGIN DECLARE
    ". Expected tokens may include: "<space>". SQLSTATE=42601

    END
    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 "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    1. Check the syntax of the DECLARE statement; parentheses don't belong there.

    2. In the command editor define the statement delimited other than the default semicolon, and put it at the end of your compound statement.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2008
    Posts
    16
    n_i thanks for the response. Im not sure what you mean by delimit. I put in the following code:

    BEGIN
    DECLARE v_NextId INT;
    END


    Result:
    ------------------------------ Commands Entered ------------------------------
    BEGIN
    DECLARE v_NextId INT;
    END;
    ------------------------------------------------------------------------------
    BEGIN DECLARE v_NextId INT
    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 "DECLARE" was found following "BEGIN ".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    END
    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 "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe it needs to be BEGIN ATOMIC...

    Andy

  7. #7
    Join Date
    Sep 2008
    Posts
    16
    no cigar:

    BEGIN ATOMIC
    DECLARE v_NextId INT;
    END


    ------------------------------ Commands Entered ------------------------------
    BEGIN ATOMIC
    DECLARE v_NextId INT;
    END;
    ------------------------------------------------------------------------------
    BEGIN ATOMIC
    DECLARE v_NextId INT
    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 "INT" was found following "MIC DECLARE
    v_NextId". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=2.
    SQLSTATE=42601

    END
    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 "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    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

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Change the statement delimiter to "@" (the default is ";" ) and run this. The delimiter is one of the options or settings, etc:

    BEGIN ATOMIC
    DECLARE v_NextId INT;
    END@
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Sep 2008
    Posts
    16
    wow! that seemed to work. its weird

    This is how i set it up

    BEGIN ATOMIC
    DECLARE v_NextId INT;
    END

    Then i set my statement termination character to @.

    How does this make any sense? can somebody explain?

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Because that kind of compound SQL statement contains intermediate ";" and you don't want to terminate the SQL statement until after the last "END".

    This is also required for UDF's and Stored Procedures when you create them because they also contain intermediate ";".
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2008
    Posts
    16
    so do i have to change all my semi colons to @? I am getting an error about some back slash if i dont change the semi colons to @s.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dbansal
    so do i have to change all my semi colons to @? I am getting an error about some back slash if i dont change the semi colons to @s.
    You only change the last one to a "@" so it will be the statement terminator.

    This problem only applies to compound SQL statements, UDF, and stored procs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Although if you are running a script that has such a compound statement, then all of the other statements will need to be terminated by the @ also (but not the ones inside the compound statement).

    Andy

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use this inside a script to switch terminator characters as you like:
    Code:
    --#SET TERMINATOR @
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  15. #15
    Join Date
    Nov 2011
    Posts
    24

    DB2 z/os script

    Iam not able to get this to work in DB2 Z/os script window.Any suggestions?
    BEGIN ATOMIC
    DECLARE v_NextId INT;
    END

    ILLEGAL USE OF KEYWORD ATOMIC. TOKEN DECLARE WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.12.55

    BEGIN
    DECLARE v_NextId INT;
    END
    ILLEGAL SYMBOL "V_NEXTID". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SECTION. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.12.55

    -- CHANGED the statement terminator to @ here.
    BEGIN ATOMIC
    DECLARE v_NextId INT;
    END @
    ILLEGAL USE OF KEYWORD ATOMIC. TOKEN DECLARE WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.12.55
    ILLEGAL SYMBOL "END@". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: <ERR_STMT> <WNG_STMT>
    GET SQL SAVEPOINT HOLD FREE ASSOCIATE. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.12.55


    BEGIN
    DECLARE v_NextId INT
    END @
    ILLEGAL SYMBOL "V_NEXTID". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: SECTION. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.12.55


    BEGIN atomic
    DECLARE v_NextId INT
    END @
    ILLEGAL USE OF KEYWORD ATOMIC. TOKEN DECLARE WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.12.55

Posting Permissions

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