SQL status '02000' too early!

[Tarek M. Nabil]

Hi everyone,

I'm facing a really wierd issue here. I can't figure out what in the
world could be going on.

I have this open cursor with some records on it. I loop on the records
until I get a status code of '02000'. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.

How can this be explained?! Is there a work around for that?

To keep it simple, I'll only list relevant code from the stored
procedure.

Start code snippet:

DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR '02000'; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop

OPEN cursorHoldings;

WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;

CLOSE cursorHoldings;

:End code snippet

By the way, I tried different approaches, like changing the continue
handler declaration to

DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop

It didn't work.

I thought I'd get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.

I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I'm in the
same row number I would exit, but I can't find anything to tell me
which row I'm on now. Besides, I don't need to do that, it should work
the way I'm doing it now. At least, this is how the reference does it.

Any help is appreciated.

[Mark A]

"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...

Hi everyone,

I'm facing a really wierd issue here. I can't figure out what in the
world could be going on.

I have this open cursor with some records on it. I loop on the records
until I get a status code of '02000'. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.

How can this be explained?! Is there a work around for that?

To keep it simple, I'll only list relevant code from the stored
procedure.

Start code snippet:

DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR '02000'; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop

OPEN cursorHoldings;

WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;

CLOSE cursorHoldings;

:End code snippet

By the way, I tried different approaches, like changing the continue
handler declaration to

DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop

It didn't work.

I thought I'd get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.

I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I'm in the
same row number I would exit, but I can't find anything to tell me
which row I'm on now. Besides, I don't need to do that, it should work
the way I'm doing it now. At least, this is how the reference does it.

Any help is appreciated.

I don't know what your programming problem is.

However, the reason that ROW_COUNT is 0 is that DB2 does not know the entire
number of rows in a select cursor until you fetch them (unless cursor
materialization is necessary to return the first row). In some cases,
depending on the isolation level, the answer set could change between the
first fetch and the last fetch.

DB2 does return to you the number of rows updated or deleted in an SQL
statement.

[Tarek M. Nabil]

ROW_COUNT was just something I tried in my efforts to work around the
problem, and now according to this, it's obviously not the solution.

It would also help if DB2 had some variable that can tell me the row
number, like ROWNUM in Oracle. That could also be a work around.

But still, this is not the original problem, the original problem is
why the handler for SQLSTATE '02000' is called while there are still
rows left in the cursor.
"Mark A" <[e-mail]> wrote in message news:<_F*****************@news.uswest.net>...

"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...

Hi everyone,

I'm facing a really wierd issue here. I can't figure out what in the
world could be going on.

I have this open cursor with some records on it. I loop on the records
until I get a status code of '02000'. The problem is, I get this
status code too early, meaning, I get out of the loop before I process
all the records in the cursor.

How can this be explained?! Is there a work around for that?

To keep it simple, I'll only list relevant code from the stored
procedure.

Start code snippet:

DECLARE NOT_END NUMERIC(1) DEFAULT 1; -- used to check for end of
data
DECLARE AT_END CONDITION FOR '02000'; -- condition for end of data
DECLARE CONTINUE HANDLER FOR AT_END SET NOT_END = 0; -- to get out of
loop

OPEN cursorHoldings;

WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;

CLOSE cursorHoldings;

:End code snippet

By the way, I tried different approaches, like changing the continue
handler declaration to

DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_END = 0; -- to get out
of loop

It didn't work.

I thought I'd get the number of rows in the result and loop a certain
number of times, but "GET DIAGNOSTICS varName = ROW_COUNT" returned 0.

I searched for something to tell me the row number so that I can store
the current row number and in the next iteration check if I'm in the
same row number I would exit, but I can't find anything to tell me
which row I'm on now. Besides, I don't need to do that, it should work
the way I'm doing it now. At least, this is how the reference does it.

Any help is appreciated.

I don't know what your programming problem is.

However, the reason that ROW_COUNT is 0 is that DB2 does not know the entire
number of rows in a select cursor until you fetch them (unless cursor
materialization is necessary to return the first row). In some cases,
depending on the isolation level, the answer set could change between the
first fetch and the last fetch.

DB2 does return to you the number of rows updated or deleted in an SQL
statement.

[Mark A]

"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...

ROW_COUNT was just something I tried in my efforts to work around the
problem, and now according to this, it's obviously not the solution.

It would also help if DB2 had some variable that can tell me the row
number, like ROWNUM in Oracle. That could also be a work around.

But still, this is not the original problem, the original problem is
why the handler for SQLSTATE '02000' is called while there are still
rows left in the cursor.

There is a row number in DB2. Look at the "row_number () over" statement.

But I think it would be better to fix your program.

[Tarek M. Nabil]

Thanks Mark.

I searched the SQL reference for DB2 for iSeries and there was no
mention of "row_number" or any other "row_something" except for
"row_count" which is in the "GET DIAGNOSTICS" command.

I totally agree with you that it would be better if I find out why
this is happening. I could put the whole stored procedure here, but it
would do nothing but complicate things. It's all just about the few
lines I posted and the problem is the continue handler for NOT FOUND
is getting called too early. I hope someone has seen this before and
is able to help.

Thanks again.

"Mark A" <[e-mail]> wrote in message news:<f8******************@news.uswest.net>...

"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...

ROW_COUNT was just something I tried in my efforts to work around the
problem, and now according to this, it's obviously not the solution.

It would also help if DB2 had some variable that can tell me the row
number, like ROWNUM in Oracle. That could also be a work around.

But still, this is not the original problem, the original problem is
why the handler for SQLSTATE '02000' is called while there are still
rows left in the cursor.

There is a row number in DB2. Look at the "row_number () over" statement.

But I think it would be better to fix your program.

[Mark A]

"Tarek M. Nabil" <Ta*********@itworx.com> wrote in message
news:c8**************************@posting.google.c om...

Thanks Mark.

I searched the SQL reference for DB2 for iSeries and there was no
mention of "row_number" or any other "row_something" except for
"row_count" which is in the "GET DIAGNOSTICS" command.

I totally agree with you that it would be better if I find out why
this is happening. I could put the whole stored procedure here, but it
would do nothing but complicate things. It's all just about the few
lines I posted and the problem is the continue handler for NOT FOUND
is getting called too early. I hope someone has seen this before and
is able to help.

Thanks again.

Sorry, I guess that I missed you mentioning you had DB2 for iSeries.

[Jeremy Rickard]

Tarek,

Are you sure that nothing in your mysterious "Do things here" section
below performs an UPDATE, SELECT or DELETE of zero rows? Any of those
would trigger a 02000 condition, causing your NOT_END flag to flip and
premature closure of the cursor.
Jeremy Rickard

WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;

[Tarek M. Nabil]

Jeremy,

This must be exactly what the problem is. I can't believe it never
crossed my mind. The thing is, I have to make those queries there.
You said there were workarounds?

jr******@unisystems.biz (Jeremy Rickard) wrote in message news:<d3**************************@posting.google. com>...

Tarek,

Are you sure that nothing in your mysterious "Do things here" section
below performs an UPDATE, SELECT or DELETE of zero rows? Any of those
would trigger a 02000 condition, causing your NOT_END flag to flip and
premature closure of the cursor.
Jeremy Rickard

WHILE (NOT_END <> 0) DO
FETCH cursorHoldings INTO var1, var2, (...and so on)
IF (NOT_END <> 0) THEN
/*
Do things here
*/
END IF;
END WHILE;

[Jeremy Rickard]

Ta*********@itworx.com (Tarek M. Nabil) wrote in message news:<c8**************************@posting.google. com>...

Jeremy,

This must be exactly what the problem is. I can't believe it never
crossed my mind. The thing is, I have to make those queries there.
You said there were workarounds?

"Workaround" was a poorly chosen word that implies a kludge, when the
solution is in fact perfectly clean. The trick is to remember that
declarations (including condition handlers) can be overridden in lower
levels of scope (BEGIN END blocks).

An example:

CREATE TABLE x(a INTEGER NOT NULL, b TIMESTAMP)@

INSERT INTO x(a)
VALUES
1, 2, 4, 5, 6@

CREATE PROCEDURE y()
LANGUAGE SQL
BEGIN
DECLARE v_at_end CHAR(1) DEFAULT 'N';
DECLARE v_a INTEGER;

-- No condition handler here - we leave the default handler
-- for 02000 in place, i.e. ignore not found conditions.

DECLARE c1 CURSOR FOR SELECT a FROM x;

OPEN c1;
WHILE v_at_end = 'N' DO
UPDATE x SET b = CURRENT TIMESTAMP WHERE a = v_a + 1;
BEGIN
-- Here's an override handler in the new scope level.
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET v_at_end = 'Y';

FETCH c1 INTO v_a;
END;
END WHILE;
END@

CALL y()@

SELECT * FROM x@

This returns:

A B
----------- --------------------------
1 -
2 2003-11-02-16.34.29.230001
4 -
5 2003-11-02-16.34.29.230003
6 2003-11-02-16.34.29.230004

5 record(s) selected.

Whereas written along the same lines as your procedure, we would exit
the cursor when it fails to update the non-existent row for key 3.
Jeremy Rickard

[Tarek M. Nabil]

That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."

I tried it anyways, and got the following error when creating the
procedure:

[SQL0777] Nested compound statements not allowed. Cause . . . . . :
Compound statements in the routine body of an SQL procedure or
function cannot be nested.

Are you sure this should work for DB2 on AS400? Maybe it works for
other versions of DB2? If not, is there any other way out of this?

Thanks for your continuous help.

jr******@unisystems.biz (Jeremy Rickard) wrote in message news:<d3**************************@posting.google. com>...

Ta*********@itworx.com (Tarek M. Nabil) wrote in message news:<c8**************************@posting.google. com>...

Jeremy,

This must be exactly what the problem is. I can't believe it never
crossed my mind. The thing is, I have to make those queries there.
You said there were workarounds?

"Workaround" was a poorly chosen word that implies a kludge, when the
solution is in fact perfectly clean. The trick is to remember that
declarations (including condition handlers) can be overridden in lower
levels of scope (BEGIN END blocks).

An example:

CREATE TABLE x(a INTEGER NOT NULL, b TIMESTAMP)@

INSERT INTO x(a)
VALUES
1, 2, 4, 5, 6@

CREATE PROCEDURE y()
LANGUAGE SQL
BEGIN
DECLARE v_at_end CHAR(1) DEFAULT 'N';
DECLARE v_a INTEGER;

-- No condition handler here - we leave the default handler
-- for 02000 in place, i.e. ignore not found conditions.

DECLARE c1 CURSOR FOR SELECT a FROM x;

OPEN c1;
WHILE v_at_end = 'N' DO
UPDATE x SET b = CURRENT TIMESTAMP WHERE a = v_a + 1;
BEGIN
-- Here's an override handler in the new scope level.
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
SET v_at_end = 'Y';

FETCH c1 INTO v_a;
END;
END WHILE;
END@

CALL y()@

SELECT * FROM x@

This returns:

A B
----------- --------------------------
1 -
2 2003-11-02-16.34.29.230001
4 -
5 2003-11-02-16.34.29.230003
6 2003-11-02-16.34.29.230004

5 record(s) selected.

Whereas written along the same lines as your procedure, we would exit
the cursor when it fails to update the non-existent row for key 3.
Jeremy Rickard

[Jeremy Rickard]

Ta*********@itworx.com (Tarek M. Nabil) wrote in message news:<c8**************************@posting.google. com>...

That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."

The nested compounds definitely work on DB2 UDB for LUW, and IMHO are
an important feature of the language - but I was forgetting you were
on AS/400. I know very little about that flavour of DB2.

As a workaround I suppose you could try setting a flag to indicate
when you are doing a FETCH versus when you are doing anything else,
and change your original condition handler to only set AT_END = 'Y'
when in "fetch mode". Hope you see what I mean.

There may be better solutions that others can suggest.
Jeremy Rickard

[Tarek M. Nabil]

This is a really good idea.

Unfortunately, data changes have caused the issue to stop happening.
I'm trying to replicate it but not with any success yet :((

Nevertheless, I have implemented the idea and it works syntactically.
I also think that if there was a chance this condition should happen
again, this would prevent it.

I would like to thank you for your continuous help and your brilliant
ideas :)

jr******@unisystems.biz (Jeremy Rickard) wrote in message news:<d3**************************@posting.google. com>...

Ta*********@itworx.com (Tarek M. Nabil) wrote in message news:<c8**************************@posting.google. com>...

That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."

The nested compounds definitely work on DB2 UDB for LUW, and IMHO are
an important feature of the language - but I was forgetting you were
on AS/400. I know very little about that flavour of DB2.

As a workaround I suppose you could try setting a flag to indicate
when you are doing a FETCH versus when you are doing anything else,
and change your original condition handler to only set AT_END = 'Y'
when in "fetch mode". Hope you see what I mean.

There may be better solutions that others can suggest.
Jeremy Rickard

[Karl Hanson]

Jeremy Rickard wrote:

Ta*********@itworx.com (Tarek M. Nabil) wrote in message news:<c8**************************@posting.google. com>...

That doesn't work for me :(

Actually, I was surprised when I read it the first time because
(although I'm a newbie to both the DB2 and AS400 worlds) I remember
reading something in the SQL reference that really upset me at the
time, so it stuck in my mind.
It says:
"Notes
Compound statements cannot be nested."

The nested compounds definitely work on DB2 UDB for LUW, and IMHO are
an important feature of the language - but I was forgetting you were
on AS/400. I know very little about that flavour of DB2.

<snip>

Jeremy Rickard

I am informed that nested compound statements are supported by DB2 UDB
for iSeries (AS/400 follow-on) in V5R2. Reference:
http://publib.boulder.ibm.com/iserie...tm#HDRWHATSNEW

--
Karl Hanson