Last year I wrote a tip about REBIND using APREUSE(WARN) and now I have some practical experience in using this option. On my installation we have decided to do REBIND on version 11 of all packages bound on version 10 or earlier if they use more than a certain amount of CPU. For such a purpose APREUSE(WARN) is just perfect.

REBIND nicely displays some extra output around the usage of APREUSE(WARN). Here is an example:

REBIND PACKAGE(MYCOLL.MYDB2PGM.()) APREUSE(WARN)   
DSNT286I  -DB2A: DSNTBBP2 REBIND FOR PACKAGE =                                 
DB2A.MYCOLL.MYDB2PGM,                                                     
           USE OF APREUSE RESULTS IN:                                          
           3 STATEMENTS WHERE APREUSE IS SUCCESSFUL                            
           1 STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL                 
             OR PARTIALLY SUCCESSFUL                                           
           0 STATEMENTS WHERE APREUSE COULD NOT BE PERFORMED                   
           0 STATEMENTS WHERE APREUSE WAS SUPPRESSED BY OTHER HINTS.           

and on top of that there is also all the usual output from the REBIND. Yet I have not seen anything than 0 in the two last lines above. The interesting thing is what made DB2 decide that a statement was not bound with a succesrate of 100%. If your DB2 package was bound with EXPLAIN(YES) you can learn a lot more by looking into PLAN_TABLE. I have created a condensed extract from PLAN_TABLE from the above REBIND. The columns HINT_USED and REMARKS are the interesting ones:

PROGNAME  STMT HINT_USED  REMARKS                                       
--------+-----+----------+------------------------------------------------------------------
MYDB2PGM  1183                                                          
MYDB2PGM  1183            APREUSE FAILURE (REASON: 40) APCOMPARE FAILURE  (COLUMN: MIXOPSEQ)
MYDB2PGM  1641 APREUSE                                                  
MYDB2PGM  1684 APREUSE                                                  
MYDB2PGM  1730 APREUSE                                                  

Here I can see that it is the first SQL statement in the program which DB2 11 cannot do a 100% reuse of. And the reason is apparently an error 40. Now where do you look this error up. Well, you can do it for SQLCODE +395. Here I can read that the index used before the REBIND cannot be used any more and thus DB2 has selected another access path. Now you need to be on your toes. Is the new access path better or worse compared to the old one, but you probably know all about that already. Please note that column HINT_USED has the value APREUSE for all the statements where the old access patch was reused. This makes it an extremely bad idea to use the value APREUSE as OPTHINT. And please also note the parenthesis where it is described which column in PLAN_TABLE caused problems for REBIND.

I was hit by a little detail around not specifying any of the other (RE)BIND options. Then you expect DB2 to inherit the values used during the previous BIND or REBIND. This is unfortunately not correct for all options. APPLCOMPAT does not use the previous value, but instead it defaults to the value specified in the new APPLCOMPAT DSNZPARM value. I got seriously injured by the fact that APPLCOMPAT(V11R1) is more restrictive than APPLCOMPAT(V10R1) when it comes to acceptable formats of timestamps. Here the famous IBM backward compability once again failed (did I hear anyone say CHAR function?).