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

Div Grad · New User Joined: 08 Apr 2005 Posts: 45

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.

Div Grad · New User Joined: 08 Apr 2005 Posts: 45

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?

dick scherrer · Posted: Thu Jun 21, 2012 9:40 pm

Hello,

Div Grad · New User Joined: 08 Apr 2005 Posts: 45

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?

dick scherrer · Posted: Thu Jun 21, 2012 11:27 pm

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?

don.leahy · Posted: Fri Jun 22, 2012 12:24 am

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.

Div Grad · New User Joined: 08 Apr 2005 Posts: 45

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.

dick scherrer · Posted: Fri Jun 22, 2012 2:00 am

Hello,

To repeat:

don.leahy · Posted: Fri Jun 22, 2012 6:30 am

Nic Clouston · Posted: Fri Jun 22, 2012 12:37 pm

And, presumably, it was tested before going live so why did the testers not pick it up if the users pick it up quickly?

Akatsukami · Posted: Fri Jun 22, 2012 3:21 pm

"Test"? I'm sorry, Nic, but this is a English-only forum

GuyC · Posted: Fri Jun 22, 2012 3:36 pm

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.

don.leahy · Posted: Fri Jun 22, 2012 6:32 pm

Div Grad · New User Joined: 08 Apr 2005 Posts: 45

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.

don.leahy · Posted: Fri Jun 22, 2012 8:34 pm

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.

GuyC · Posted: Mon Jun 25, 2012 12:59 pm

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

Div Grad · New User Joined: 08 Apr 2005 Posts: 45

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:

don.leahy · Posted: Mon Jun 25, 2012 10:09 pm

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.