Ambiguous Cursors - A cursory(!) question
grai...@platinum.co.uk
Just a quick one for all you 'real world' people out there.
At a recent roadshow here in the UK, I made what is about the ONLy bold
statement I've ever made about DB2.
I stated that I couldn't think of a single circumstance when a programmer
wouldn't know whether to code FOR FETCH ONLY or FOR UPDATE OF when
declaring a cursor - but of course I also said I was sure there'd be
exceptions. As yet, none of the expected exceptions have come to mind.
So......
Can anyone suggest a GOOD reason for not coding one of the above clauses on
cursor definitions, mainly for my own interests.
Cheers
Phil Grainger
Product Consultant
PLATINUM technology UK
Venkat R. Pillay
Phil
If I have "join/subquery/order by/group by" etc etc. Why do I code FOR
FETCH ONLY ?
They are unambiguously "FOR FETCH ONLY", Does DB2 higher versions still
consider them unambiguous cursor ?
I guess there is no GOOD reason for not coding "FOR FETCH ONLY" .
Unfortunately programmers forget coding extra line especially they feel
they have more important things to worry about and performance is the last
issue.
I have seen I/O parallelism taking effect in all those kind (join/order by
etc) of cursor without coding "FOR FETCH ONLY". Probably DB2 (V4.1) does
not consider them as ambiguous cursor
Pillay
-------------
Original Text
From: grai...@PLATINUM.CO.UK, on 2/17/98 3:11 PM:
Jon Heinecke
Phil:
Discussion of "ambiguous cursors" has been driving me crazy lately;
should we rewrite all of our cursors where we do not use "FOR UPDATE
OF" to say "FOR FETCH ONLY" so as to make them unambiguous?
After further research, it appears that DB2 only considers a cursor
to be ambiguous if the cursor is not READ-ONLY (based on the SQL provided:
no ORDER BY, JOINs, etc), and there is DYNAMIC SQL in the program.
Our shop is entirely mainframe static SQL based. Teaching the developers
about "FOR FETCH ONLY" is not a bad idea, but the precompiler/bind
process will not generate any ambiguous cursors in our case. If we
add it to our "standard", developers will believe that they have to
change existing code when performing maintenance. Not necessarily
a bad idea, but the old adage "If it ain't broke, don't fix it" may
apply...
I agree that all Client-Server based applications processing dynamic
SQL (through an ODBC layer, for example) without bound packages/plans
should ALWAYS specify "FOR FETCH ONLY" or "FOR UPDATE OF"; this, however,
would fall under a "standard" that applies when dynamic SQL is in use.
Please let me know whether I am incorrect in my statement regarding
"ambiguous" cursors.
Regards,
Jon Heinecke
Senior Technical Specialist
Compuware Corporation
Opinions expressed are my own, and do not necessarily reflect those
of my client or employer.
=================================================
Michael S. Bancale
I asked a question some time ago on this subject and some of the answers
I received varied a bit. The best answer I received was from Willie
Favaro (I'm almost certain), he told me why not tell DB2 what you
intend.
I also feel it better documents the cursor to the full intent of the
cursor. Sure it's a little more typing to do but I'd rather take the
extra few key strokes and be certain what DB2 will do.
Michael Bancale
TFBIC
Only my opinions, not the companies.
On Tue, 17 Feb 1998, Venkat R. Pillay wrote:
> Phil
>
> If I have "join/subquery/order by/group by" etc etc. Why do I code FOR
> FETCH ONLY ?
> They are unambiguously "FOR FETCH ONLY", Does DB2 higher versions still
> consider them unambiguous cursor ?
>
> I guess there is no GOOD reason for not coding "FOR FETCH ONLY" .
> Unfortunately programmers forget coding extra line especially they feel
> they have more important things to worry about and performance is the last
> issue.
>
> I have seen I/O parallelism taking effect in all those kind (join/order by
> etc) of cursor without coding "FOR FETCH ONLY". Probably DB2 (V4.1) does
> not consider them as ambiguous cursor
>
> Pillay
> -------------
> Original Text
> From: grai...@PLATINUM.CO.UK, on 2/17/98 3:11 PM:
Mair, William
Jon,
Your mail includes the following paragraph :
"After further research, it appears that DB2 only considers a cursor
to be ambiguous if the cursor is not READ-ONLY (based on the SQL
provided:
no ORDER BY, JOINs, etc), and there is DYNAMIC SQL in the program."
I believe this to be the case for distributed processing. However I
believe that local static programs with a non-read only cursor which has
no declare "for .." statement to be ambiguous.
Like you, please let me know if this statement is incorrect.
thank
Willie
> -----Original Message-----
> From: Jon Heinecke [SMTP:Jon.He...@MCI.COM]
> Sent: Tuesday, February 17, 1998 9:43 PM
> To: DB...@AMERICAN.EDU
> Subject: Ambiguous Cursors - A cursory(!) question
>
>
> *** Warning : this message originates from the Internet ***
> Just a quick one for all you 'real world' people out there.
>
> At a recent roadshow here in the UK, I made what is about the ONLy
> bold
> statement I've ever made about DB2.
>
> I stated that I couldn't think of a single circumstance when a
> programmer
> wouldn't know whether to code FOR FETCH ONLY or FOR UPDATE OF when
> declaring a cursor - but of course I also said I was sure there'd be
> exceptions. As yet, none of the expected exceptions have come to mind.
>
> So......
>
> Can anyone suggest a GOOD reason for not coding one of the above
> clauses on
> cursor definitions, mainly for my own interests.
>
> Cheers
>
> Phil Grainger
> Product Consultant
> PLATINUM technology UK
Legal disclaimer : The opinions expressed in this message are those of the
sender and not those of The Royal Bank of Scotland plc
rpolley
As one other subscriber noted, that 'For Fetch Only' mainly applies to
distributed processing, If your application is mainframe only without
dynamic sql, I think the same block fetching is used if the cursor is not
referenced in an update or delete statement. However if this is not the
case, it will not be the first time I have misunderstood something in the
DB2 realm.
Gerald Bustamente
Help!
Gotta a weird one. At least for us. At service level 9712 (and I think at 9708)
we started getting REASON=X'00E50013' for different DB2 utilities including COPY,
RECOVER and REORG.
A sysprog traced it to subpool 229 which I believe is above the line. Apparently
the error is caused by a failing GETMAIN. Anyway, these problems are intermittent
yet serious enough to prevent some maintenance from occuring.
We have 2 gig of c stor and db2 buffers taking up 400 mb. Data warehouse
environment.
Is anyone else experiencing this?
Any ideas, opinions, guesses very appreciated.
Thanks alot,
Jerry Bustamente
Lands' End
--------------------------------------------------------------------------
00E50013
Explanation: A DB2 execution unit has been abended. This abend code must
ultimately be issued by resource managers when the CANCEL exit
is driven as a result of a suspend request.
This abend reason code is issued by the following CSECTs:
+------------------------------------------------------------------------+
¦ DSNVEUS1 ¦ DSNVEUS3 ¦ DSNVASTM ¦ DSNVCST0 ¦
¦ DSNVDST0 ¦ DSNVFEB ¦ DSNVSLT0 ¦ DSNVSUL0 ¦
¦ DSNVXLT0 ¦ ¦ ¦ ¦
+------------------------------------------------------------------------+
System Action: The agent cancel processing continues.
Operator Response: Notify the system programmer only if the abend results in the
termination of the DB2 subsystem.
System Programmer Response: This reason code may be issued as a result of any
connected TCB abend, or operator -STOP DB2
MODE(FORCE) command. No further action is required.
The recovery routine for the CSECT issuing this reason code records information in
the variable recording area (VRA). If you suspect an error in
DB2, refer to Section 3 of Diagnosis Guide and Reference for information on
identifying and reporting the problem.
Problem Determination: If DB2 abends, examine the SVC dump to determine the
suspended resource manager and the state of the failing
subcomponent. For example, the ACEMC (must complete) bit should be off.
Collect the following diagnostic items listed in Appendix B, "Problem
Determination" in topic B.0: 1, 2, 5.
grai...@platinum.co.uk
Just to further seed this discussion, one area I am suggesting would
benefit from non-ambiguous cursors (ie ones which YOU know are read-only
but DB2 might not be sure about) is paralellism (I/O, CPU and SYSPLEX).....
In these cases it doesn't matter whether it's local or remote SQL, static
or dynamic. If it isn't read-only, you don't get paralellism.
Phil Grainger
PLATINUM technology UK
rpo...@R.IMAP.ITD.UMICH.EDU on 18/02/98 12:36:59
Please respond to DB...@AMERICAN.EDU
To: DB...@AMERICAN.EDU
cc: (bcc: Phil J Grainger/Pti)
Subject: Re: Ambiguous Cursors - A cursory(!) question
Marcus Green
Unambiguous to who? DB2 can probably cope well enough, but does (s)he maintain
the code?
What about the DB2 maintainence person, maybe newish to DB2 (maybe not, does
matter if it's
a complicated piece of "art"?) who comes across a cursor that MAY be updated
(maybe in another
piece of code that is copied in at compile time, for example) later, but
decides they need to
change it in some way. If the person before them had coded "For Update or
Fetch Only" then our
maintenance friend would be a really happy chappy.....
Marcus
PS: Of course, I'm still one of the lazy ones that don't code for fetch only,
but hey,
it's the thought that counts!
Scott Adams
>
> Jerry,
>
> We've had this happen once, but managed to clear it up by IPLing. DB2 uses
> subpool 229 heavily. Unfortunately, I' ve discovered several bugs in DB2
> over the years where DB2 leaves orphaned hunks of SP 229 laying around
> causing a storage creep problem. Have you IPLed lately? Also, are you
> getting the SVC dump?
>
> Sue Wild
> CUNA Mutual
>
> (Embedded
> image moved ggb...@LANDSEND.COM
> to file: 02/18/98 08:32 AM
> PIC00361.PCX)> cc: (bcc: Sue E. Wild/Madison/CMG)
> Subject: DB2 v 5.1 REASON=X'00E50013'
> ? DSNVDST0 ? DSNVFEB ? DSNVSLT0 ? DSNVSUL0 ?
> ? DSNVXLT0 ? ? ? ?
> +------------------------------------------------------------------------+
> System Action: The agent cancel processing continues.
> Operator Response: Notify the system programmer only if the abend results
> in the
> termination of the DB2 subsystem.
> System Programmer Response: This reason code may be issued as a result of
> any
> connected TCB abend, or operator -STOP DB2
> MODE(FORCE) command. No further action is required.
> The recovery routine for the CSECT issuing this reason code records
> information in
> the variable recording area (VRA). If you suspect an error in
> DB2, refer to Section 3 of Diagnosis Guide and Reference for information on
> identifying and reporting the problem.
> Problem Determination: If DB2 abends, examine the SVC dump to determine
> the
> suspended resource manager and the state of the failing
> subcomponent. For example, the ACEMC (must complete) bit should be off.
> Collect the following diagnostic items listed in Appendix B, "Problem
> Determination" in topic B.0: 1, 2, 5.
>
> ---------------------------------------------------------------
>
> Name: PIC00361.PCX
> Part 1.2 Type: unspecified type (application/octet-stream)
> Encoding: base64
We are currently running DB2 v4 and have a large portion of our
daily activity coming from distributed users. We have been experiencing
this storage creep problem in subpool 229 for some time now but have
been thus far unable to indentify the cause. We receive an abend code
of S004e-00e20003, dumps have been sent to IBM but so far they have been
unable to identify the problem. Currently it has been our misfortune to
have to "STOP DDF" on a daily(midday) basis inorder to temporarily
relieve this storage problem. Obviously this is not a permanent
solution but until we find the true solution it is all we have. Any
help or hints that anyone can give from past or present experience would
be greatly appreciated.
Gerald Bustamente
Sue,
Thanks for taking the time to respond. No, we have not IPLed but we have cycled
DB2 and that seems to clear it up.
thanks again,
Jerry
Sue E. Wild wrote:
------------------------------------------------------------------------
>
> Name: PIC00361.PCX
> PIC00361.PCX Type: Bitmap Image
(application/x-unknown-content-type-Paint.Picture)
> Encoding: base64
Jon Heinecke
Willie:
Dynamic SQL is necessary to make the cursor "ambigouous".
From the Glossary of the DB2 for MVS/ESA V4 Command Reference:
"ambiguous cursor. A database cursor that is not defined with either
the clauses FOR FETCH ONLY or FOR UPDATE OF, is not defined on a
read-only result table, is not the target of a WHERE CURRENT clause on
an SQL UPDATE or DELETE statement, and is in a plan or package that
contains SQL statements PREPARE or EXECUTE IMMEDIATE."
Without a PREPARE or EXECUTE IMMEDIATE (Dynamic SQL calls), the cursor
will not be ambiguous. DB2 will know based on other static calls whether
you expect to update/delete WHERE CURRENT OF cursor.
All this being said, I concede that all programmers should know the
intended use of a cursor, and as it says in DB2 for MVS/ESA V4
Application Programming and SQL Guide, in section 4.2.6.1.4:
"In all cases, it is a good programming technique to eliminate the
ambiguity by declaring the cursor with one of the clauses FOR FETCH
ONLY or FOR UPDATE OF."
Good to remove ambiguity in the developer's mind, if not DB2's...
Regards,
Jon Heinecke
Senior Technical Specialist
Compuware Corporation
Opinions expressed are my own, and do not reflect those
of my client or employer.
=================================================
thank
Willie
> Just a quick one for all you 'real world' people out there.
>
> At a recent roadshow here in the UK, I made what is about the ONLy
> bold
> statement I've ever made about DB2.
>
> I stated that I couldn't think of a single circumstance when a
> programmer
> wouldn't know whether to code FOR FETCH ONLY or FOR UPDATE OF when
> declaring a cursor - but of course I also said I was sure there'd be
> exceptions. As yet, none of the expected exceptions have come to mind.
>
> So......
>
> Can anyone suggest a GOOD reason for not coding one of the above
> clauses on
> cursor definitions, mainly for my own interests.
>
> Cheers
>
> Phil Grainger
> Product Consultant
> PLATINUM technology UK