Now we all know that DB2 10 is here – You do know that don’t you?? 🙂
Do away with DBRMs being bound directly to a PLAN
Anyway one of the points of DB2 10 is to finally do away with DBRMs being bound directly to a PLAN. Now you absolutely must go to PACKAGES and COLLECTIONS. I have seen no real need of having PLAN-based programs for years, but some people/sites/software still seem to insist upon having these things so I know that some shops are now facing the following questions:
- Let DB2 10 “do the magic” – In other words let the migration create all the collections for you?
- Bite the Bullet – Do it now in DB2 V8 or DB2 9; thus allowing *you* to decide on naming conventions, etc? Of course I would be failing in my marketing skills if I forgot to plug our DBRM Reconstruct feature of Bind ImpactExpert that can also do this for you…
Guess what I would choose?? I have two reasons for choosing answer number 2 and they are:
- I like to control my own naming standards.
- I need to create these PACKAGES *before* the Migration.
Use PLAN MANAGEMENT during a migration
Why (2) ? You may well ask. Quite simple – If you rebind/bind now with current statistics, and you happen to be on DB2 9, you can use PLAN MANAGEMENT during migration to keep your (new) current access path “locked down” so in case of regression you can fall back to a good access path. Additionally, if you are still on DB2 V8, then a rebind before skip-level migration is basically a must so you have a chance to find any problems before landing at the door of DB2 10 with unknown, weird, or downright strange access paths, and you have simply no idea where they came from. For example, could they have come in V8, or is it a new special rule access path from DB2 9, or even perhaps an artefact from bad statistics somewhere down the chain?
At this point, I should also mention that you need good consistent statistics for *any* migration. Make sure that your SYSCOLDIST and SYSKEYTGTDIST contain good trustworthy data! (See my earlier newsletter about our Statistics HealthCheck Freeware for more details there.)
What is the worst thing that could happen?
So back to this newsletter – What is the worst thing that could happen? Well, what if you happen to have two PLANs that both have a DBRM called ROYBOY, but both ROYBOY’s have different pre-compile timestamps? This is nasty – really really nasty – and should of course “never happen”, but it does happen and far too often for my taste! So now to the meat and veg of the newsletter – Here is a nice little query that will show which DBRMs you have on your system that have the same name, but different pre-compile timestamps. These all need to be “sorted out” (That’s a euphemism for FREE of course 🙂 ) before beginning the journey to DB2 10.
SELECT SUBSTR(B.NAME , 1 , 8) AS DBRM_NAME
, COUNT(*) AS NUMBER_DIFF_PRECOMPTS
FROM
(SELECT NAME
, PRECOMPTS
FROM SYSIBM.SYSDBRM
GROUP BY NAME , PRECOMPTS) AS B
GROUP BY B.NAME
HAVING COUNT(*) > 1
The output looks like this:
---------+---------+---------+--- DBRM_NAME NUMBER_DIFF_PRECOMPTS ---------+---------+---------+--- PLMEM1 2 DSNE610I NUMBER OF ROWS DISPLAYED IS 1
Using this output you can select the needed data, or you could imbed this SELECT in another SELECT to get the needed data like this:
SELECT SUBSTR(A.NAME , 1 , 8 ) AS DBRM_NAME
,SUBSTR(A.PLNAME , 1 , 8 ) AS PLAN_NAME
,SUBSTR(A.PLCREATOR , 1 , 8 ) AS CREATOR
,A.PRECOMPTS
FROM SYSIBM.SYSDBRM A
,(SELECT SUBSTR(B.NAME , 1 , 8) AS DBRM_NAME
, COUNT(*) AS NUMBER_DIFF_PRECOMPTS
FROM
(SELECT NAME
, PRECOMPTS
FROM SYSIBM.SYSDBRM
GROUP BY NAME , PRECOMPTS) AS B
GROUP BY B.NAME
HAVING COUNT(*) > 1
) AS C
WHERE C.DBRM_NAME = A.NAME
ORDER BY 1 , 2
;The output then looks like this:
---------+---------+---------+---------+---------+---------+- DBRM_NAME PLAN_NAME CREATOR PRECOMPTS ---------+---------+---------+---------+---------+---------+- PLMEM1 TESTP4 MDU 2007-11-26-19.48.41.982926 PLMEM1 TESTP5 MDU 2009-10-26-10.22.12.362131 PLMEM1 TESTP6 MDU 2007-11-26-19.48.41.982926 DSNE610I NUMBER OF ROWS DISPLAYED IS 3
This now gives the “overview” of what is possibly wrong. In this case, you can see the different timestamps and could deduce what action(s) must be taken.
I hope that this little bit of data was of interest and/or of use and, as always, comments or questions are more than welcome!
TTFN,
Roy Boxwell
Senior Architect