View previous topic :: View next topic
|
Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007 Posts: 130 Location: Bangalore
|
|
|
|
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 |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2454 Location: Hampshire, UK
|
|
|
|
What did the DB2 manuals for z/OS or z/OS have to say about it? |
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 130 Location: Bangalore
|
|
|
|
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 |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2454 Location: Hampshire, UK
|
|
|
|
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. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
Quote: |
... if a manual does not mention 'washing the dishes' it is because the software does not 'wash the dishes' |
|
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 130 Location: Bangalore
|
|
|
|
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 |
|
Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 130 Location: Bangalore
|
|
|
|
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 |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2454 Location: Hampshire, UK
|
|
|
|
It may be 'huge' but it is provided with an index and Contents. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
Quote: |
BUT now I have come across to a situation where this CURSOR is creating hell lots of problems. |
it is not the cursor that is creating problems, but the application design
as usual easier to blame somebody else |
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 130 Location: Bangalore
|
|
|
|
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.
Quote: |
Now both these MAIN programs and Subprograms are very old and many modifications were done on them. Design was Very bad.. |
See above I have already mentioned that the application design was bad. It is not a new news to me..
Question is though the design is bad (there are many old programs whose designs are not good but we can't just go ahead and change everything because of many dependencies on the code) how can I resolve the issue.
Anyway I am hitting my head and trying to figure it out where exactly the flag variable is getting set in a wrong way..
Thanks
Subrata |
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 130 Location: Bangalore
|
|
|
|
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..
Code: |
AVBRYT2: PROC(TEXT1,P2,P3,ROLLBACK1,TEXTNR1);
DCL TEXT1 CHAR(*),
P2 POINTER,
P3 FIXED BIN(15),
ROLLBACK1 CHAR(1), /* J = ROLLBACK */
TEXTNR1 FIXED BIN(31);
DCL 1 WAREA BASED(P2),
2 WFÄLTL FIXED BIN(15),
2 WFÄLTA CHAR (1),
2 WFÄLTH CHAR (1);
IF ROLLBACK1ª='N' THEN DO;
CALL ROLLBACK;
END;
|
Code: |
ROLLBACK: PROC;
DCL I FIXED BIN(31);
EXEC CICS SYNCPOINT ROLLBACK;
|
|
|
Back to top |
|
|
|