Need help on SQL CODE -501

subratarec · Posted: Thu Jun 11, 2015 2:25 pm

Hi,

I need one small info and it is related to DB2 CURSOR. I have used CURSOR many a times in my PL/1 + DB2 application program. So as far as the concept of the cursor I am clear in that. BUT now I have come across to a situation where this CURSOR is creating hell lots of problems.

A submodule (specific for all types of CURSOR operations - it has 15 different cursors) being called by many main programs (around 22 main programs). So you can think of this submodule as a common module for cursor ops.

Now both these MAIN programs and Subprograms are very old and many modifications were done on them. Design was Very bad.. why I am telling because all these 15 different cursor operations (means opening and closing cursors) are controlled by 2 common BIT variables (Flag variables) and that is causing this -501.

Because these 2 BIT variable is being used by several programs.. Example - when one program is closing the cursor through flag.. may be other program is setting the same flag to open..
This is just a background of the situation. Now my question is while searching I found there is something called "CURSOR PREDICATES" in DB2 LUW. This will help programmer to identify whether a particular cursor is closed or is open.

Link - www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0054736.html?cp=SSEPGG_9.7.0%2F2-10-2-12-6

Do we have the same for DB2 - Z/OS ?? Please let me know.

Thanks
Subrata

Nic Clouston · Posted: Thu Jun 11, 2015 3:26 pm

What did the DB2 manuals for z/OS or z/OS have to say about it?

subratarec · Posted: Thu Jun 11, 2015 3:54 pm

Hi Nic,

Frankly every time I search for CURSOR PREDICATES I get only DB2 LUW link from IBM. DB2 for Z/OS book I searched (the old one I have with me) but couldn't find anything like CURSOR PREDICATES..

By any chance if you have an idea please let me know.. it would be of great help..

Thanks
Subrata

Nic Clouston · Posted: Thu Jun 11, 2015 9:56 pm

The manuals are linked to from the forum.

Usually if a manual does not mention 'washing the dishes' it is because the software does not 'wash the dishes' - the manuals describe what the software DOES.

enrico-sorichetti · Posted: Thu Jun 11, 2015 10:33 pm

subratarec · Posted: Fri Jun 12, 2015 9:08 am

Hmm... Anyway Thanks Nic for spending your time on my query and moreover writing the Metaphor - "washing the dishes". Though it doesn't seem very funny to me but looks like you made Enrico Very happy..

Thanks
Subrata

Bill Woodger · Posted: Fri Jun 12, 2015 12:24 pm

LUW DB2 and z/OS DB2 are different products, with similarities.

If you pick something from a LUW DB2 manual that you think would do exactly what you want, but can't find reference to it in a z/OS DB2 manual then you need to do something else, not get concerned about why it isn't in the z/OS manual (because it isn't in DB2 for z/OS, so it's not in the manual).

You were a little slow in picking that up, so Nic made it a little more explicit for easy consumption.

subratarec · Posted: Fri Jun 12, 2015 5:29 pm

Hi Bill,

Thanks for your comments. If you see top, the statements with query. There my intention was just to inquire whether "AM I MISSING SOMETHING WHICH I AM NOT AWARE OF CURSOR". When I saw something in DB2 LUW, it's very natural that question will erupt in anyone's mind that whether the same will be available for DB2 Z/OS.

As far as DB2 for ZOS manual is concerned. It's a BIG book and with loads of info in it. Reading line by line or topic by topic is not possible for most of the developers (few are exception), so the next approach could be to ask someone who is having more experience about the topic than the person who is posting the question. IF he/she also never heard of it or not aware of the same then changes are very high that what I expected is not possible.

My concern was NOT why it is not there in DB2 Z/OS and none of my statements given above express that I believe.

Thanks
Subarat

Nic Clouston · Posted: Sat Jun 13, 2015 4:35 am

It may be 'huge' but it is provided with an index and Contents.

enrico-sorichetti · Posted: Sat Jun 13, 2015 8:12 am

subratarec · Posted: Sat Jun 13, 2015 9:47 am

Ha ha.. You are absolutely correct!! but you are frying fish with my oil

. Just read below statements what I had posted in the starting of the conversation.

subratarec · Posted: Sat Jun 20, 2015 10:22 pm

Hi,

I felt it would be good if I let you all know what I had analyzed and what all my findings.

Situation was one main program A (which is a PL1 + CICS + IMS DB pgm) calling another submodule B (here B handles all DB2 operations..like SELECT, UPDATE, DELETE etc. Even CURSOR life cycle is also maintains here.. that is DECLARE, OPEN, FETCH and CLOSE).

Now twist is Main program A calls another MAIN program C (which is a PL1 + CICS + IMS DB + DB2 pgm) for doing something. While analyzing I have found that usage of CURSOR Indicator variables (through which CURSRs are controlled) are correct. My First impression was someway somehow these variables are getting wrong values so thats the reason program is trying close an already closed cursor and we are getting -501.

But after analysis what I have found is this.. Pgm A is calling pgm C first time and declare, open and fetch first row from table. Then pgm A calls pgm C for some operations. All done fine. Next again pgm B called by pgm A. Does it's work. Comes back to Pgm A and from there flow is going to Pgm C. There because of some sorts of business logic pgm C is calling another submodule for SYNCPOINT ROLLBACK. Thats the one who is closing the cursor.

SYNCPOINT ROLLBACK closing the opened cursor. Flow is coming back to pgm A and there no check given whether everything is ok in pgm C or not.
There is check on CICS response code (EIBRESP) in pgm A.
Now pgm A thinks all are correct in Pgm C and flow goes as usual then it tries to close already closed cursor (because of SYNCPOINT ROLLBACK)and we are getting -501.

Below is the excerpt of the code..