How to set Collection Ids for a reopt 2, 3 or 4 (Runtime optimization) and avoid the default collection NULLID ?
Following on from my last newsletter, I have had to do some research about Collection Ids and how to set them.
Last newsletter:
2017-05 Db2 version 12: A little bit too agile?
How to handle APPLCOMPAT when it comes to Dynamic SQL. A support to manage Db2 12 „agile“ release.
Driven to distraction?
The first thing you learn, is that the cli.ini file is now gone…all of the data is now found in the db2dsdriver.cfg file. OK, how does this all hang together?
First Contact
The very first thing that happens, is it looks up the name and the address of the desired Db2 on z/OS. This is just the TCP/IP data to initiate “first contact”. Once the first contact is done, the User Id and Password are required to validate the connection. All well and good.
If your CurrentPackageSet and your reopt are *not* set, you then get the default collection NULLID under which all of your dynamic SQL executes
Reoptimize this!
Then it gets ugly… If your CurrentPackageSet and your reopt are *not* set, you then get the default collection NULLID under which all of your dynamic SQL executes (Now please re-read my last newsletter to see why that is of major interest!).
But, what happens if you want to use reopt 2, 3 or 4 ?
Quick aside:
The reopt level determines what style of run time optimization to enable on the host. You can set:
- Reopt 2 – No optimization (and this is the default),
- Reopt 3 – To get REOPT(ONCE) behavior – so the first time that SQL comes in to execute, it gets optimized with the literal values in it, and then not again, or
- Reopt 4 – To get REOPT(ALWAYS) behavior, so it drives a re-optimization every time that SQL comes in.
I have NULL idea what you are talking about…
So back to my question…
What happens if you wish to use reopt 2, 3 or 4? Well, you have a problem, as these must use the reserved collection ids NULLID, NULLIDR1 and NULLIDRA respectively, which (remember my last newsletter), all die horribly with APPLCOMPAT getting involved…
It gets worse
Naturally, there is a dark lining to this cloud! The use of reopt actually disallows the use of CurrentPackageSet, which was the only way of managing APPLCOMPAT. What on Earth can you do…I have no idea…
Help is on the way…
We are conducting a Design Council in Germany (September 4-5th, 2017) about IBMs Continuous Delivery for Db2 in September, where we will discuss all of this with customers and DB2 users, but I would love to get some feedback from you all too!
As usual, if you have any comments or queries please feel free to drop me a line!
TTFN
Roy Boxwell