For many years, DBAs at a lot of Db2 for z/OS sites have been challenged in performing an important operational task: rebinding a frequently executed package. This can be tricky because a package cannot be rebound while it is in use, and if a package is executed with great frequency - in some environments, certain packages are executed hundreds of times per second during busy periods - then its use count may only rarely go to zero. When a REBIND command is issued for such a package, there are two likely outcomes, neither of which is desirable: either the REBIND command times out because the target package is continuously in-use, or the REBIND gets going thanks to a little gap that emerges in the package's in-use status. Why is that latter outcome potentially undesirable? It is undesirable because once the rebind of a package is underway, a subsequent request to execute the package must wait for the rebind to complete. Yes, a package rebind is usually completed quite quickly, but if rebind processing for a package is delayed for some reason, even if only for a few seconds, that could trigger application-side time-outs - not good.
Could you just rebind a high-use package at a less-busy time? Perhaps, but sometimes there is a pressing need to rebind a package now, not later. Besides that, in an increasingly 24X7 world you might have packages that are rarely not-in-use even outside of peak periods. Think, for example, about the packages associated with the IBM Data Server Driver (which you can think of as the successor to Db2 Connect). These packages, whose default collection is named NULLID, are used by applications (other than REST clients) that access a Db2 for z/OS system via Db2's distributed data facility (DDF). A DDF transaction rate in the thousands per second is not terribly unusual these days, and that rate can remain pretty high around the clock, making rebind of the NULLID packages a daunting prospect for some DBA teams.
The rebind challenges associated with ever-higher and ever more round-the-clock package execution frequency are made more serious by the growing need for package rebind actions. There was a time when an organization might rebind packages (and plans) only infrequently, outside of the rebinds that are recommended when you migrate a system to a new version of Db2 (if your organization does not rebind all plans and packages following migration to a new version of Db2, I recommend that you address that situation). Nowadays, we have newer reasons to rebind packages - you might change a RELEASE specification to DEALLOCATE, perhaps to enable high-performance DBAT functionality; you might rebind a package to get a performance gain after executing RUNSTATS to leverage the statistics profile enhancements introduced with Db2 12; you might rebind a package with the new (with Db2 12) CONCENTRATESTMT option, to "turn on" Db2's statement concentration feature for an application from the server side (statement concentration could formerly only be activated by a client-side action); and so on.
The IBM Db2 for z/OS development team recognized some time ago that rebind concurrency was an issue that needed to be addressed. The bind concurrency feature provided by Db2 11 for z/OS definitely helped, but more was needed. That "more" was largely delivered via the rebind phase-in capability enabled by way of Db2 12 function level 505. Although the fix (for APAR PH09191) that takes a Db2 system's code to the 505 level came out about 18 months ago, I have found that quite a few Db2 for z/OS people are not aware of what rebind phase-in is and how it works; so, I'll provide explanatory information in the remainder of this blog entry.
In a nutshell, rebind phase-in allows a package to be immediately and successfully rebound even if the package is in-use at the time the REBIND PACKAGE command is issued. Not only that, but when rebind phase-in is in effect, a package execution request that comes in following the issuance of the REBIND command will not be held up, even if the rebind of the package has not yet completed. Gotta love that.
So, how is this goodness made possible? Well, it's based on a Db2 capability, called plan management, that was introduced with Db2 9. When plan management is fully in effect (when the PLANMGMT parameter of ZPARM is set to its default value of EXTENDED), Db2 can retain up to three instances (referred to as copies) of a given package: the original copy, the previous copy, and the current copy. These package copies are retained primarily for the purpose of "taking the fear out of rebinding" (as a former colleague of mine cleverly put it) - if you rebind a package and access paths change in a way that negatively impacts performance, you can quickly and easily go back to the previous copy of the package by issuing a REBIND PACKAGE command with the SWITCH(PREVIOUS) option.
The copies of a package retained by way of plan management functioning in extended mode are differentiated by a number: copy 0 is the current instance of a package, copy 1 is the previous instance, and copy 2 is the original instance. Rebind phase-in builds on this functionality, and in fact having PLANMGMT in ZPARM set to EXTENDED is required if you want to take advantage of the rebind phase-in feature. I'll illustrate with an example: suppose that you issue a REBIND command for package ABC, and Db2 (Version 12, with function level 505 or higher activated) sees that the package is currently in use. In that case, Db2 will utilize rebind phase-in processing (if the package is not in-use at the time of the issuance of the REBIND PACKAGE command, the rebind request will be processed in the traditional way). A new instance of package ABC will be generated, even as the current instance of the package is being used, and this will be do-able because the newly-generated instance of the package will be identified as copy 4 (we jump from copy identifier 0 to 4 because - as noted above - 1 identifies the previous instance of the package and 2 identifies the original, and identifier 3 has been reserved for future use by Db2). If a request to execute package ABC comes in before generation of the new copy 4 has completed, copy 0 of the package will be allocated to the requester's Db2 thread for execution. Requests for execution of package ABC that come in after copy 4 has been generated will result in allocation of copy 4. In time (quickly if the package is bound with RELEASE(COMMIT) - a little longer if the package is bound with RELEASE(DEALLOCATE) and is allocated to a persistent thread such as a high-performance DBAT), instances of copy 0 of package ABC will be released from the threads to which they had been allocated for execution, and copy identifier 0 will then go back into the pool of numbers available for package differentiation when package ABC is again involved in rebind phase-in processing. That pool, for any given package, is the number 0 together with the numbers 4-16. When package ABC, the current copy of which is now 4, is again rebound while in-use, the new instance of the package will be identified as number 5. If package ABC is rebound in a phase-in way enough times for its current copy identifier to be 16, and it is again rebound while in-use, the new copy will be identified as 0, after which the numbers 4-16 will again be used as needed.
Note, by the way, that rebind phase-in processing is also used when a REBIND PACKAGE command with a SWITCH specification is issued and the target package is found to be in-use. Let's say that the current instance of package XYZ is copy 4 (meaning that copy 0 was current when package XYZ was rebound in a phase-in way). If a REBIND PACKAGE command with SWITCH(PREVIOUS) is issued for package XYZ while the package is in-use, the previous instance of the package (always designated as copy 1) will be replicated to generate the new current instance of the package, which will be identified as copy 5. New requests to execute package XYZ will result in copy 5 being allocated to the requester's thread. Copy 4 of package XYZ, the previously current copy which is now the phased-out copy, will be replicated to become the new previous instance of the package (copy 1, as always).
Also note that rebind phase-in functionality is not applicable to packages associated with SQL PL routines (native SQL procedures, compiled SQL scalar functions and advanced triggers). This is not as big a deal as it may seem, owing to the fact that there were already ways to non-disruptively generate and activate a new instance of a SQL PL routine. For example, a new instance of a native SQL procedure can be non-disruptively generated and activated via an ALTER PROCEDURE statement with an ADD VERSION specification, followed by a second ALTER PROCEDURE statement with ACTIVATE VERSION specified (referencing the version added via the ALTER PROCEDURE with ADD VERSION).
Rebind phase-in, enabling successful and non-disruptive rebind operations even for packages executed with great frequency, is one more way in which the IBM Db2 for z/OS development team has made life easier for Db2 DBAs. I hope that you will find this functionality to be useful at your site.
robert,can you give us some insight on indoubt,inflight,postponed,inabort threads.ReplyDelete
Each of these terms is defined on this page in the Db2 for z/OS Knowledge Center on IBM's Web site: https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/admin/src/tpc/db2z_consistencyafterfailure.htmlDelete
Thank you so much for the post!!. In my shop, we need to REBIND plans that are used by CICS transactions.
From https://www.ibm.com/docs/en/db2-for-zos/12?topic=commands-rebind-plan-dsn, I see that "You cannot rebind a plan while that plan is executing."
Is there any way to REBIND plans used by CICS regions without taking down the CICS regions ?
First, let me ask: why are you rebinding plans? Assuming the use of collection-name.* in the plan's PKLIST, which would allow for the non-disruptive addition of packages that can be executed via the plan, what is causing you to have to do plan re-binds? I believe that at many sites, plan rebinds are quite unusual - much less frequently needed versus package binds.Delete
Thanks for your reply!..We are trying to replace the "asterisk location" in the PKLIST "*.ORAPROD.*" with actual location to reduce Package Authorization Checks as you explained in your post http://robertsdb2blog.blogspot.com/2021/01/db2-for-zos-why-you-might-see-lot-of.html
OK - that certainly makes sense.Delete
As you've pointed out, a plan cannot be rebound while it is in use. What can make this kind of tricky in a CICS-Db2 environment is the fact that quite often there are just a few Db2 plans that are used for Db2-accessing CICS transactions. In other words, it is often the case that quite a few different CICS transactions are associated with one Db2 plan. That can make it hard to find a period in which the plan is not in-use - that transaction arrival rate might have to go way down for that to happen, and maybe there is enough CICS-Db2 transaction volume going around the clock to keep the plans used for those transactions in an in-use state all the time. In such a case, what can you do?
Yes, stopping the CICS regions would do the trick, but that would obviously be disruptive for the associated transactional workload. It may be that by way of TCLASS specifications on the CICS side, the flow of CICS-Db2 transactions could temporarily be halted while the CICS-Db2 plan or plans are rebound - that might be a little less disruptive than stopping the CICS regions themselves.
If you need to get this done with absolutely minimal disruption of the transactional workload, here is a possible approach: set up a new CICS region, and specify for the associated DB2ENTRY resources the names of Db2 plans that have already been bound with PKLIST entries that do not have an * in the location-name position. Then, when that region has been set up, open it up for transaction requests while quiescing the work in the region you're using now. Just a thought. I don't know CICS as well as I know Db2, but I think an approach like this might be possible.
Thank you so much for your ideas!!!
You're most welcome, Ravikumar.Delete
great post, great feature! Can you explain when I have the rebind error with message DSNT500I reason 00E30307 (max copyIDs reached)?
That is a technically possible, but highly unlikely situation. To encounter that situation would require that all 13 copy IDs for a package (the 13 IDs other than the ID of the "currently current" instance of the package) be still allocated to threads, and for THAT to happen the package in question would have to be rebound 13 times, and be in-use for each of those rebinds (so that rebind phase-in functionality would be in effect), and the "formerly current" instances of the package would have to remain in use for a long time. What if the package were rebound once per week (not likely, but not impossible)? In that case, the DSNT500I with reason code 00E30307 error would require that a package remain continuously allocated to a thread for 13 weeks, and that would not happen unless a process executing a RELEASE(COMMIT) package did not commit for 13 weeks, or that the persistent thread of a process executing a RELEASE(DEALLOCATE) package did not terminate even after 13 weeks. Again, not technically impossible, but very, very unlikely.Delete
Hi rob,after adding columns to the table i have seen packages with 'A' and 'N' .when we see status as 'N' we do explicit rebinds.At what criteria/column types we are required to do explicit rebinds for packages ?ReplyDelete
When the value in the VALID column of SYSIBM.SYSPACKAGE for a package is 'N', you will need to rebind the package to make it again executable (if you do not do this, there will be an auto-rebind action taken by Db2 when the package is next requested for execution). If the value in the VALID column for the package is 'A', you might have to rebind the package in order for the package to pick up the change (or changes) made to a table on which the package depends by the ALTER TABLE that caused the package to get a status of 'A' for VALID in SYSPACKAGE.Delete
Information about the VALID column of SYSPACKAGE can be found on this page: https://www.ibm.com/docs/en/db2-for-zos/12?topic=tables-syspackage.
Information about table changes that can cause a package's VALID value (in the SYSPACKAGE catalog table) to go to 'A' can be found on this page: https://www.ibm.com/docs/en/db2-for-zos/12?topic=applications-changes-that-might-require-package-rebinds.
Rob,I see from ibm manual that auto rebind is applicable only if valid column is 'N'.if we do not rebind the package with valid column as 'A' ,what will happen with the package? or table? if i dont rebind at all ,so the pkg is gonna stay with value 'A' forever?Delete
As the documentation states, the 'A' value in the VALID column for a package in SYSIBM.SYSPACKAGE means that the package might have to be rebound if it (the package) is to pick up the table change that caused the package's VALID status to go to 'A'. If you don't rebind the package, it will continue to not reflect the table change that caused the package's VALID status to go to 'A'. If you're OK with that, no action is needed on your part. I would expect that package's VALID status to remain 'A' until it is rebound. The fact that the package's VALID status is 'A' does not affect the table. The package is dependent on the table, not the other way around.Delete
In my shop,rebound(if required) has to be done only by dba's manually,neither scheduled jobs nor application team does it.so we have to make a call either rebind or leave as such.but none of users reported with any issues bcz of this 'a' status.Since db2 does not do auto rebind(only 'n' valid status) for 'a' status packages.i think rebinds explicitly need to be done by us.It would have been good if auto rebinds includes 'a' status pkgs as well,not sure if there is any reason behind the exclusion.Delete
f you look at the changes that could cause the VALID value for a package in the SYSPACKAGE table to go to 'A' (see https://www.ibm.com/docs/en/db2-for-zos/12?topic=applications-changes-that-might-require-package-rebinds), you'll see several that should not matter for read-only packages. For such packages, you might opt not to rebind, and leave the VALID status at 'A'. If you don't want to spend the time deciding if VALID = 'A' is OK or not-OK for a given package, you can rebind it just to be on the safe side.Delete