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?

DECLARE variable is part of a Compound SQL statement. You need to put a BEGIN and END around your compound statement.

Andy

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

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.

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

I believe it needs to be BEGIN ATOMIC...

Andy

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

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@

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?

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 ";".

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.

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

You can use this inside a script to switch terminator characters as you like:

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