View previous topic :: View next topic
|
Author |
Message |
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. |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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. . . |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2454 Location: Hampshire, UK
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
|
|
|
|
"Test"? I'm sorry, Nic, but this is a English-only forum |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
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:
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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010 Posts: 765 Location: Whitby, ON, Canada
|
|
|
|
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 |
|
|
|