2011-10: PLAN to PACKAGE Migration – Do you have any “bad guys”

 

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:

 

  1. Let DB2 10 “do the magic” – In other words let the migration create all the collections for you?
  2. 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:

  1. I like to control my own naming standards.
  2. 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