IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Could program results differ after move to Package Based...


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Thu Jun 21, 2012 9:01 pm
Reply with quote

Quick note before I head into a meeting:

Could the processing by a batch db2 program change after conversion from DBRM based Plans to Package based plans?

We're seeing a situation where at first glance it appears that processing has changed on a program after conversion. The program processing records off a DB2 table and then deletes the records. The records are being deleted but were not seeing the expected results from them being processed.

And I'll include the classic line: Nothing else has changed.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Thu Jun 21, 2012 9:23 pm
Reply with quote

Now we're thinking it may be that the default bind parms being used now may be different then when program was originally bound a few years back....

Any thoughts or experience with this out there?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Thu Jun 21, 2012 9:40 pm
Reply with quote

Hello,

Quote:
Could the processing by a batch db2 program change after conversion from DBRM based Plans to Package based plans?
If everything else remainded the same, the processing should not change. . .

Quote:
but were not seeing the expected results from them being processed.
Time to do some digging. We can't help much with site-specific issues.

I suspect that intentional or not, SomeThiong has changed somewhere. The trivck will be finding it if no one believes there could have been a change. . . icon_wink.gif
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Thu Jun 21, 2012 10:28 pm
Reply with quote

Well, yes of course something has changed and I imagine it is the default settings on the bind now vs five years ago. Now it is to figure out what the change was.

Having had a few minutes to look at the program it looks like the issue has to do with a cursor that joins two tables. Now remember the program has not been recompiled, only rebound. Kind of stuck for now with the (crazy) way it processes: After the two tables are joined it starts to process the records one at a time and then delete them from underlying tables. The thing is, the processing is not process a row and then delete a row, and repeat till end of cursor. Instead it processes a row, then deletes not just one row but all rows with a common field value. It then expects to go on and process these deleted records since they should still be in the cursor (but already deleted from underlying tables). However now it looks like the delete of the records is also taking immediate effect on the cursor, i.e. the rows disappear from the open cursor as the deletes occur. Crazy logic? Yes, but it worked before and now does not... Need to figure out the setting impacting this.

I.e. what setting decides whether or not a delete of a record in a table should also cause the record to immediately delete from an open cursor?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Thu Jun 21, 2012 11:27 pm
Reply with quote

Hello,

Which means that the code has always been less than supportable. . .

I suspect that previously a temporary table was created and when the process ran, it ran from the already gathered rows.

Was there a db2 upgrade in these same years?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Fri Jun 22, 2012 12:24 am
Reply with quote

I agree with Dick that this was always a risky design. Cursor sensitivity (i.e. whether a cursor is aware of changes as they occur) is influenced by several factors, some of which have changed from release to release.

The good news is that newer releases of DB2 give the programmer more control over cursor sensitivity, so I would suggest that you check the manual to see whether declaring the cursor as Insensitive might be what you need.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Fri Jun 22, 2012 12:41 am
Reply with quote

Yes, making a program change to make the cursor insensitive would likely make this problem go away with this specific program. But that is not our concern. Our concern is that we've converted all binds to Package from DBRM at one time. Users quickly noticed the problem that developed with this program. Our concern is are there other programs with problems that have not been discovered yet. We need to know what caused the change in processing with this program so that we can evaluate what to do with respect to the thousands of of other programs we have running.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix

PostPosted: Fri Jun 22, 2012 2:00 am
Reply with quote

Hello,

To repeat:
Quote:
Was there a db2 upgrade in these same years?


And, was there any customization to the db2 environment?

Was there a change in the cardinality of the data since the last bind?

You might create a test new DBRM, clone the problem program, and bind it "the old way". I believe there is a pretty good chance that the test program will have the same issue. . .
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Fri Jun 22, 2012 6:30 am
Reply with quote

dick scherrer wrote:
Hello,

To repeat:
Quote:
Was there a db2 upgrade in these same years?


And, was there any customization to the db2 environment?

Was there a change in the cardinality of the data since the last bind?

You might create a test new DBRM, clone the problem program, and bind it "the old way". I believe there is a pretty good chance that the test program will have the same issue. . .
Agree. I think you will find that this is a bind / DB2 version issue and has nothing to do with converting to packages.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK

PostPosted: Fri Jun 22, 2012 12:37 pm
Reply with quote

And, presumably, it was tested before going live so why did the testers not pick it up if the users pick it up quickly?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1787
Location: Bloomington, IL

PostPosted: Fri Jun 22, 2012 3:21 pm
Reply with quote

"Test"? I'm sorry, Nic, but this is a English-only forum icon_razz.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 22, 2012 3:36 pm
Reply with quote

Sensitive cursors behave differently depending on accesspath. So it's possible that these programs reacted correctly in test and incorrect (as in "not the expected result") in prod.
The danger (of changing behaviour after rebind) has always existed, but you were just lucky that it didn't occur (or you never rebound).

If you still have the explains from before the rebind, you might be able to do an accesspath compare to see the programs at risk.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Fri Jun 22, 2012 6:32 pm
Reply with quote

GuyC wrote:
Sensitive cursors behave differently depending on accesspath. So it's possible that these programs reacted correctly in test and incorrect (as in "not the expected result") in prod.
The danger (of changing behaviour after rebind) has always existed, but you were just lucky that it didn't occur (or you never rebound).
This is known as "working by coincidence".

Unfortunately it is difficult to predict, without a detailed code examination, which programs may be afflicted by this.
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Fri Jun 22, 2012 6:36 pm
Reply with quote

I went down the path of looking at accesspath and that does not seem to be the issue either. The cursor is unsorted and so yes order of processing could differ but if the cursor is insensitive then this should not be an issue. And that seems to be root of the issue, why after the rebind is a cursor that previously behaved as insensitive now behaves as if sensitive.

The cursor is a join of multiple tables so is read only. It does not specify Scroll so it is unscrollable. Read only unscrollable cursors are also insensitive so I don't know what is going on.

We're doing a quick mod to the program to change the processing so that the cursor will work whether sensitive or insensitive. The bad thing is of course we still do not know why this happened and so can't evaluate the risk to everything else that was rebound.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Fri Jun 22, 2012 8:34 pm
Reply with quote

Are you sure that read only unscrollable cursors are insensitive?

I am asking that because I have seen this situation several years ago, before scrollable cursors were even available.

At the time, , my understanding was that, unless the results table was materialized, the cursor would pick up changes to the data.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Jun 25, 2012 12:59 pm
Reply with quote

Like Don-Leahy said:

Do you have the accespath from before ?
Let's assume it was an Merge-scan, a Hybrid-join, anything with List Prefetch,... => work-tables are materialized => insensitive
Do you have the accesspath now ?
Let's assume it is Nested Loop => sensitive
Back to top
View user's profile Send private message
Div Grad

New User


Joined: 08 Apr 2005
Posts: 45

PostPosted: Mon Jun 25, 2012 9:14 pm
Reply with quote

Here's where we found what happened:
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_open.htm


Here's the relevant section:

Quote:
Effect of a temporary copy of a result table: DB2 can process a cursor in two different ways:

It can create a temporary copy of the result table during the execution of the OPEN statement. You can specify INSENSITIVE SCROLL on the cursor to force the use of a temporary copy of the result table.
It can derive the result table rows as they are needed during the execution of later FETCH statements.

If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used.


So a read only cursor may or may not be Insensitive depending on whether or not a temp copy was made. The decision of whether or not to make a temp copy or go directly against the table(s) depends on runstats (and other conditions).

The last bind of this program was five years ago and since then the runstats have changed. After the rebind a temp table is now not being used and instead the tables are being read directly. So our deletes were impacting the cursor.

The fix could be to explicitly request Insensitive or to alter the cursor to force the use of a temp table, say include an Order By clause for example.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Mon Jun 25, 2012 10:09 pm
Reply with quote

Suggest you request Insensitive rather than rely on influencing the access path. As we have seen, the access path can change without warning; requesting Insensitive would be a more robust solution.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Sort based on the record type DFSORT/ICETOOL 1
No new posts Error to invoke MPP program through B... IMS DB/DC 3
No new posts To Omit records based n SORT condition DFSORT/ICETOOL 6
No new posts Using API Gateway from CICS program CICS 0
No new posts DB2 Event passed to the Application P... DB2 1
Search our Forums:

Back to Top