Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need help on SQL CODE -501

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Jun 11, 2015 2:25 pm    Post subject: Need help on SQL CODE -501
Reply with quote

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 - http://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
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Jun 11, 2015 3:26 pm    Post subject:
Reply with quote

What did the DB2 manuals for z/OS or z/OS have to say about it?
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Jun 11, 2015 3:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Thu Jun 11, 2015 9:56 pm    Post subject:
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu Jun 11, 2015 10:33 pm    Post subject: Reply to: Need help on SQL CODE -501
Reply with quote

Quote:
... if a manual does not mention 'washing the dishes' it is because the software does not 'wash the dishes'
icon_lol.gif
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Fri Jun 12, 2015 9:08 am    Post subject:
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Fri Jun 12, 2015 12:24 pm    Post subject: Reply to: Need help on SQL CODE -501
Reply with quote

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
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Fri Jun 12, 2015 5:29 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Sat Jun 13, 2015 4:35 am    Post subject:
Reply with quote

It may be 'huge' but it is provided with an index and Contents.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Sat Jun 13, 2015 8:12 am    Post subject: Reply to: Need help on SQL CODE -501
Reply with quote

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 icon_evil.gif
Back to top
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Sat Jun 13, 2015 9:47 am    Post subject:
Reply with quote

Ha ha.. You are absolutely correct!! but you are frying fish with my oil icon_smile.gif. 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
View user's profile Send private message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Sat Jun 20, 2015 10:22 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What is the code in CLIST to enable t... jackzhang75 CLIST & REXX 1 Fri Dec 02, 2016 3:02 am
No new posts IDEAL - Code Retrieval yugendran CA Products 0 Fri Nov 25, 2016 3:27 pm
This topic is locked: you cannot edit posts or make replies. Need to code a Rexx PGM sundaram.naveen CLIST & REXX 18 Thu Oct 06, 2016 6:45 pm
No new posts Need Help in understanding what is th... mainakdalal PL/I & Assembler 9 Mon Oct 03, 2016 8:03 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us