[side topic] sql error -199 sqlstate 42601

Philip Sevetson

[side topic] sql error -199 sqlstate 42601
I had to look up “Halloween effect,” because the name is idiomatic rather than descriptive –

In about 1992, we discovered a cousin to this effect; In opening a non-materialized cursor, the analyst used the current row to create an INSERT statement, with data values which caused it to be placed “ahead” of the current position. (I believe this involved using an ORDER BY statement which matched the clustering index – so you got an ordered FETCH but not a materialized cursor!).

Each INSERT added one row to the return set… we ran out of space in the tablespace, and then the UOW rolled back! So, we got an out-of-space error but there wasn’t any data there. Took us several days to figure out what was going on. Oy.

--Phil

From: Peter Vanroose [mailto:[login to unmask email]
Sent: Thursday, May 09, 2019 11:30 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: sql error -199 sqlstate 42601


In this case, with a single UPDATE statement, the Halloween effect would never happen since the Db2 optimizer will avoid using the "dangerous" index in the access path.

It's only if you (1) declare a cursor in an application program, which (2) you don't declare "for update of log_ts", and then (3) do an out-of-cursor update inside the cursor loop, that the same row could pass by multiple times.

In Reply to bernd oppolzer:
[...] I still have some worry about updating the LOG_TS in this way, because I could imagine that the UPDATED record qualifies once again and is UPDATED a second time !!! (Halloween effect)
This, of course, would depend heavily on available indexes and if they are defined ASC or DESC, and on the access path DB2 chooses. Although the risk may be minimal, I would try to avoid it. [...]

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
https://www.abis.be/ https://www.abis.be/html/enDB2Calendar.html

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

bernd oppolzer

[side topic] sql error -199 sqlstate 42601
(in response to Philip Sevetson)
We had this, too, at a former customer's site.

The application ran successfully for years, because DB2 used a work file
when
processing the cursor. Then suddenly:

some parameters in the environment changed (maybe another index or
better statistics
and REBIND). Now DB2 processed the cursor by using an index of the
table, and
now the INSERT (as in your case) was inserted AHEAD of the cursor SELECT.
Luckily in our case, the application crashed, because it didn't expect
the record to be there :-)))

It is IMO indeed a variation of the Halloween effect.

I was in charge for DB2 education at this customer's site (for more than
20 years),
and I used this example ever since to explain the different variants of
cursor processing
... even if there is no difference at the source level.

Kind regards

Bernd


Am 09.05.2019 um 17:36 schrieb Sevetson, Phil:
>
> I had to look up “Halloween effect,” because the name is idiomatic
> rather than descriptive –
>
> In about 1992, we discovered a cousin to this effect; In opening a
> non-materialized cursor, the analyst used the current row to create an
> INSERT statement, with data values which caused it to be placed
> “ahead” of the current position. (I believe this involved using an
> ORDER BY statement which matched the clustering index – so you got an
> ordered FETCH but not a materialized cursor!).
>
> Each INSERT added one row to the return set… we ran out of space in
> the tablespace, and then the UOW rolled back! So, we got an
> out-of-space error but there wasn’t any data there. Took us several
> days to figure out what was going on. Oy.
>
> --Phil
>
> *From:*Peter Vanroose [mailto:[login to unmask email]
> *Sent:* Thursday, May 09, 2019 11:30 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: sql error -199 sqlstate 42601
>
> In this case, with a single UPDATE statement, the Halloween effect
> would never happen since the Db2 optimizer will avoid using the
> "dangerous" index in the access path.
>
> It's only if you (1) declare a cursor in an application program, which
> (2) you don't declare "for update of log_ts", and then (3) do an
> out-of-cursor update inside the cursor loop, that the same row could
> pass by multiple times.
>
> In Reply to bernd oppolzer:
>
> [...] I still have some worry about updating the LOG_TS in this
> way, because I could imagine that the UPDATED record qualifies
> once again and is UPDATED a second time !!! (Halloween effect)
> This, of course, would depend heavily on available indexes and if
> they are defined ASC or DESC, and on the access path DB2 chooses.
> Although the risk may be minimal, I would try to avoid it. [...]
>
> --      Peter Vanroose
> /ABIS Training & Consulting,/
> /        Leuven, Belgium./
> https://www.abis.be/ https://www.abis.be/html/enDB2Calendar.html
>
> -----End Original Message-----
> **This e-mail, including any attachments, may be confidential,
> privileged, or otherwise legally protected. It is intended only for
> the addressee. If you received this e-mail in error or from someone
> who was not authorized to send it to you, do not disseminate, copy, or
> otherwise use this e-mail or its attachments. Please notify the sender
> immediately by reply e-mail and delete the e-mail from your system.**
> -----End Original Message-----