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.

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).

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"

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,where "@" is the new delimiter.

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.

May I correct you on this?
(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.

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.

You're right with this - no question about that.