-- USE THE FOLLOWING QUERY TO GET THE MOST RECENT PLAN TALBE INFORMATION
-- FOR YOUR DB2 PROGRAMS. IF YOU DON'T HAVE A SUPPORTING INDEX ON THE
-- PROGRAM NAME AND BIND TIME, AND YOUR PLAN TABLE IS BIG, YOU MAY WANT
-- TO USE THE SECOND QUERY.

SELECT                                                                          
     SUBSTR(DIGITS(QUERYNO),5) CONCAT '-' CONCAT                                
     SUBSTR(DIGITS(QBLOCKNO),4) CONCAT '-' CONCAT                               
     SUBSTR(DIGITS(PLANNO),4) AS Q_QB_PL                                        
    ,PROGNAME AS PNAME                                                          
    ,SUBSTR(CHAR(METHOD),1,1) AS MT                                             
    ,SUBSTR(TNAME,1,18) AS TNAME                                                
    ,CHAR(TABNO) AS T_NO                                                        
    ,ACCESSTYPE AS AT                                                           
    ,CHAR(MATCHCOLS) AS MC                                                      
    ,SUBSTR(ACCESSNAME,1,8) AS ACC_NM                                           
    ,INDEXONLY AS IX                                                            
    ,SORTN_JOIN CONCAT                                                          
     SORTC_UNIQ CONCAT                                                          
     SORTC_JOIN CONCAT                                                          
     SORTC_ORDERBY CONCAT                                                       
     SORTC_GROUPBY AS NJ_CUJOG                                                  
    ,PREFETCH AS PF                                                             
    ,COLUMN_FN_EVAL AS CFE                                                      
    ,CHAR(MIXOPSEQ) AS MIX                                                      
    ,ACCESS_DEGREE AS A_DEG                                                     
    ,JOIN_DEGREE AS J_DEG                                                       
    ,PARALLELISM_MODE AS P_MODE                                                 
    ,MERGE_JOIN_COLS AS MJC                                                     
    ,CORRELATION_NAME AS COR_NM                                                 
    ,PAGE_RANGE AS PG_RG                                                        
    ,JOIN_TYPE AS JT                                                            
    ,WHEN_OPTIMIZE AS WH_OP                                                     
    ,QBLOCK_TYPE AS QB_TYP                                                      
    ,BIND_TIME AS B_TM                                                          
    ,HINT_USED AS HINT                                                          
    ,PRIMARY_ACCESSTYPE AS PR_ACC                                               
--SUBSTITUTE YOUR SCHEMA NAME FOR 'YLA' BELOW
FROM YLA.PLAN_TABLE A                                                        
--WHERE PROGNAME = 'YLAPROG1' <-- TARGET A SPECIFIC PROGRAM HERE                                                     
WHERE BIND_TIME =                                                             
(SELECT MAX(BIND_TIME)                                                        
 FROM YLA.PLAN_TABLE B                                                     
 WHERE A.PROGNAME = B.PROGNAME                                                
 AND A.COLLID = B.COLLID)                                                     
ORDER BY PROGNAME, BIND_TIME, Q_QB_PL, MIX;

-- USE THE FOLLOWING QUERY TO GET THE MOST RECENT PLAN TALBE INFORMATION
-- FOR YOUR DB2 PROGRAMS. THIS QUERY MAY BE A BETTER CHOICE THAN THE
-- PREVIOUS QUERY IF YOU PLAN TABLE HAS A LOT OF DATA, AND YOU DON'T
-- HAVE A SUPPORT INDEX ON PROGRAM NAME AND BIND TIME.

SELECT * FROM                                                                   
(                                                                               
SELECT                                                                          
     SUBSTR(DIGITS(QUERYNO),5) CONCAT '-' CONCAT                                
     SUBSTR(DIGITS(QBLOCKNO),4) CONCAT '-' CONCAT                               
     SUBSTR(DIGITS(PLANNO),4) AS Q_QB_PL                                        
    ,PROGNAME AS PNAME                                                          
    ,SUBSTR(CHAR(METHOD),1,1) AS MT                                             
    ,SUBSTR(TNAME,1,18) AS TNAME                                                
    ,CHAR(TABNO) AS T_NO                                                        
    ,ACCESSTYPE AS AT                                                           
    ,CHAR(MATCHCOLS) AS MC                                                      
    ,SUBSTR(ACCESSNAME,1,8) AS ACC_NM                                           
    ,INDEXONLY AS IX                                                            
    ,SORTN_JOIN CONCAT                                                          
     SORTC_UNIQ CONCAT                                                          
     SORTC_JOIN CONCAT                                                          
     SORTC_ORDERBY CONCAT                                                       
     SORTC_GROUPBY AS NJ_CUJOG                                                  
    ,PREFETCH AS PF                                                             
    ,COLUMN_FN_EVAL AS CFE                                                      
    ,CHAR(MIXOPSEQ) AS MIX                                                      
    ,ACCESS_DEGREE AS A_DEG                                                     
    ,JOIN_DEGREE AS J_DEG                                                       
    ,PARALLELISM_MODE AS P_MODE                                                 
    ,MERGE_JOIN_COLS AS MJC                                                     
    ,CORRELATION_NAME AS COR_NM                                                 
    ,PAGE_RANGE AS PG_RG                                                        
    ,JOIN_TYPE AS JT                                                            
    ,WHEN_OPTIMIZE AS WH_OP                                                     
    ,QBLOCK_TYPE AS QB_TYP                                                      
    ,BIND_TIME AS B_TM                                                          
    ,HINT_USED AS HINT                                                          
    ,PRIMARY_ACCESSTYPE AS PR_ACC 
--SUBSTITUTE YOUR SCHEMA NAME FOR 'YLA' BELOW                                              
FROM YLA.PLAN_TABLE A) AS TAB1                                               
INNER JOIN                                                                      
(                                                                               
SELECT PROGNAME AS P1, MAX(BIND_TIME) AS B1                                     
FROM YLA.PLAN_TABLE                                                          
GROUP BY PROGNAME) AS TAB2                                                      
ON TAB1.PNAME = TAB2.P1                                                         
AND TAB1.B_TM = TAB2.B1                                                         
ORDER BY TAB1.PNAME, TAB1.B_TM, TAB1.Q_QB_PL, TAB1.MIX;