How to handle APPLCOMPAT when it comes to Dynamic SQL?
I have been involved in testing in Db2 version 12 for a while now, and I think we need a discussion about a few features that come in with this “agile” release.
APPLCOMPAT to the rescue?
First up, is the use of APPLCOMPAT – This is now available throughout the SQL, but comes with a few problems. If you have static SQL then all is fine and dandy, as the APPLCOMPAT used is stored in the Db2 Catalog. You can easily refer to it and check which package is using what APPLCOMPAT. The real trouble starts with Dynamic SQL.
Dynamic SQL intro
A quick discourse about Dynamic SQL is now required…
All Dynamic SQL that runs, must run “under the control” of a Package.
This means that we all have loads of empty packages that are just used to run Dynamic SQL. You all have the SYSLHxxx, SYSLNxxx style Packages and probably loads more. These Packages are used for authentication, tracing, and validation of requests from remote users.
Package is the Boss
This is all good, apart from when a new Db2 12 Function Level (from now on FL) is activated. Why? Because the “package is boss” – If the package was bound at FL500 and you activate FL501, then any SQL that executes in that package that attempts to use an FL501 statement fails. If the SQL attempts to SET CURRENT APPLCOMPAT = ‘V12R1M501’ it will, of course, also fail.
REBIND the world?
To “fix” this, you simply have to REBIND the package to the new FL level.
Sounds simple, huh?
Well, what that means is that *all* SQL that uses that package will get the ability to go to FL501 straightaway, unless they are coded with SET CURRENT APPLCOMPAT = ‘V12R1M500’ or which level you would prefer…
We all have that coded in our JAVA programs don’t we?
Dynamic Packages always allocated
So the problem now is – You have hundreds of Packages that you must rebind, but you dare not rebind them! Even worse, is that you probably *cannot* rebind them anyway, as they are permanently allocated and in use!
. Imagine how many Dynamic SQLs are running in your shop?
. Can you flush the DSC and rebind all of your “empty” Packages?
. When can you plan such an outage?
COLLECTIONs can help
One work-around is to have a new COLLECTION, which the empty packages are bound to again. This works great, apart from one tiny little problem… The COLLID must then be set/changed in all of the CLI.INI or API places where it is currently set, or just defaulted to, today! At one of my customer sites that would be over 8,000 files to update!
So now my questions to the readers out there
How do you plan to manage this?
How do you plan to roll-out FL levels?
If using a new collection, how many CLI.INIs etc. must be changed?
As usual, if you have any comments or queries please feel free to drop me a line!
TTFN
Roy Boxwell