Skip to Content
Information

This site will undergo a brief period of maintenance on Friday, 18 December at 12:30 AM Central/12:00 PM IST. During a 30 minute window, site availability may be intermittent.

Running the Migrate Access Path Statistics Extract job

The JCL that results from running a Migrate Access Path Statistics operation contains two jobs.

The first job extracts the statistics from the source Db2 subsystem. This process creates a file containing the access path statistics. This file is the input to the second job, which is a Db2 Update job that is run against the target subsystem (SSID).

Related topic

Migrating access path statistics

If you submit the job directly from the JCL without making any edits, only the Extract job executes. By default, the Update job is commented out in order to avoid any unintentional manipulation of the Db2 catalog. To run the second job, you might want to change the statistics or move your JCL to the IBM MVS image containing the target SSID.

Warning

Important

To run the Migrate Access Path Statistics jobs, you must be on the subsystem on which BMC AMI SQL Explorer for Db2 is installed.

Type SUB on the Command line of the JCL generated file, and press Enter to submit the Extract job that extracts the data from the source SSID.

The Extract job writes source object information to the UPDATE data set, along with the catalog statistic information for those objects.

Warning

Important

The comments contained in the generated UPDATE file refer to FLOAT data in generic terms. (For example, both CARD and CARDF are referenced as "CARD".) The statistics in the UPDATE file always include the correct value (for example, CARD or CARDF) for the version of Db2 from which it was extracted. The statistics that are updated are the correct fields for the version of Db2 specified as the target subsystem.

An example of the UPDATE data set is shown in the following figure.

(BMC.DB2.SPE2504)

Information
Example
#  BMC AMI SQL Explorer for Db2
#                                                                           
#  STATISTICS OBTAINED FROM DEDR 2024-11-26-09.30.34.475919  
#                                                                           
#  RECORD FORMAT FOR EACH OBJECT TYPE                                       
#                                                                           
# :TS,DBNAME,NAME,NACTIVE,STATSTIME                                         
# :TB,CREATOR,NAME,CARD,NPAGES,PCTROWCOMP,STATSTIME                         
# :TA,PARTITION,CARD,NPAGES,PCTPAGES,NACTIVE,PCTROWCOMP,IBMREQD,STATSTIME   
# :CO,NAME,COLCARD,HIGH2KEY,LOW2KEY,STATSTIME                               
# :CD,NAME,TYPE,NUMCOLUMNS,CARD,FREQUENCY,IBMREQD,COLGROUPCOLNO,COLVALUE,STATSTIME                                                           
# :CS,10,CNAME,PARTITION,COLCARD,STATSTIME,IBMREQD,STATS_FORMAT,COLCARDDATA
# :CS,20,CNAME,PARTITION,HIGHKEY                                           
# :CS,21,CNAME,PARTITION,HIGH2KEY                                          
# :CS,22,CNAME,PARTITION,LOWKEY                                            
# :CS,23,CNAME,PARTITION,LOW2KEY                                           
# :IX,CREATOR,NAME,NLEAF,NLEVELS,CLUSTERRATIO,FIRSTKEYCARD,FULLKEYCARD,STATSTIME                                                  
# :KT,10,SCHEMA,NAME,KEYSEQ,STATSTIME,STATS_FORMAT                              
# :KT,20,SCHEMA,NAME,KEYSEQ,HIGH2KEY                                            
# :KT,21,SCHEMA,NAME,KEYSEQ,LOW2KEY                                             
# :KD,10,SCHEMA,NAME,KEYSEQ,STATSTIME,CARDF,NUMKEYS,IBMREQD,FREQUENCYF,QUANTILENO,TYPE
# :KD,20,SCHEMA,NAME,KEYSEQ,KEYVALUE                                            
# :KD,21,SCHEMA,NAME,KEYSEQ,KEYGROUPKEYNO                                       
# :KD,22,SCHEMA,NAME,KEYSEQ,HIGHVALUE                                           
# :KD,23,SCHEMA,NAME,KEYSEQ,LOWVALUE                                            
# :KS,10,SCHEMA,NAME,KEYSEQ,PARTITION,STATSTIME,CARDF,IBMREQD,STATS_FORMAT      
# :KS,20,SCHEMA,NAME,KEYSEQ,PARTITION,HIGHKEY                                   
# :KS,21,SCHEMA,NAME,KEYSEQ,PARTITION,HIGH2KEY                                  
# :KS,22,SCHEMA,NAME,KEYSEQ,PARTITION,LOWKEY                                    
# :KS,23,SCHEMA,NAME,KEYSEQ,PARTITION,LOW2KEY                                   
# :KA,10,SCHEMA,NAME,KEYSEQ,PARTITION,STATSTIME,CARDF,IBMREQD,TYPE,NUMKEYS,FREQUENCYF,QUANTILENO
# :KA,20,SCHEMA,NAME,KEYSEQ,PARTITION,KEYVALUE                                  
# :KA,21,SCHEMA,NAME,KEYSEQ,PARTITION,KEYGROUPKEYNO                             
# :KA,22,SCHEMA,NAME,KEYSEQ,PARTITION,HIGHVALUE                                 
# :KA,23,SCHEMA,NAME,KEYSEQ,PARTITION,LOWVALUE                                  
# :RT,SCHEMA,NAME,ROUTINETYPE,SPECIFICNAME,PARM_COUNT,CARDINALITY,INITIAL_INSTS,INITIAL_IOS,INSTS_PER_INVOC,IOS_PER_INVOC
#                                                                               
:TS,TSSQX91 ,PSSS0191,1080      ,2021-12-12-11.08.37.831161                     
:TB,TSS,TSST01_D91S01,1910         ,114      ,0  ,2021-12-12-11.08.37.831161    
:CO,COLUMN_1,1910         ,801BCEAE,8012F7AC,2021-12-12-11.08.37.831161         
:CO,COLUMN_2,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_3,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_4,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_5,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_6,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_7,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_8,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_9,112          ,A6E7,8014,2021-12-12-11.08.37.831161                 
:CO,COLUMN_10,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_11,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_12,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_13,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_14,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_15,1910         ,20121212110833382508,20121212110833283413,2021-12-12-11.08.37.831161
:CO,COLUMN_17,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_18,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_19,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_20,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_21,-1           ,,,0001-01-01-00.00.00.000000                        
:CD,COLUMN_1,C,2  ,1910         ,-1              ,N,00010002,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,F,2  ,-1           ,0.00052356020942408,N,00010002,801BCEAFB10D,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,F,2  ,-1           ,0.00052356020942408,N,00010002,801BCEAE8000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,F,2  ,-1           ,0.00052356020942408,N,00010002,801BCEADDBCC,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,F,2  ,-1           ,0.00052356020942408,N,00010002,801BCEAC7F54,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,F,2  ,-1           ,0.00052356020942408,N,00010002,801BCEAB7440,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,H,2  ,1            ,0.00052356020942408,N,00010002,,2021-12-12-11.08.37.831161,1
:C1,8012F7A4B4BC                                                                
:C2,8012F7A4B4BC                                                                
:CD,COLUMN_1,H,2  ,1            ,0.00052356020942408,N,00010002,,2021-12-12-11.08.37.831161,5
:C1,801BCEAFB10D                                                                
:C2,801BCEAFB10D                                                                
:CD,COLUMN_1,H,2  ,635          ,0.33246073298429,N,00010002,,2021-12-12-11.08.37.831161,4
:C1,801BCA03A328                                                                
:C2,801BCEAE8000                                                                
:CD,COLUMN_1,H,2  ,636          ,0.33298429319372,N,00010002,,2021-12-12-11.08.37.831161,2
:C1,8012F7AC9900                                                                
:C2,801BC130AAC6                                                                
:CD,COLUMN_1,H,2  ,637          ,0.33350785340314,N,00010002,,2021-12-12-11.08.37.831161,3
:C1,801BC1323162                                                                
:C2,801BCA02D974                                                                
:CD,COLUMN_1,C,3  ,1910         ,-1              ,N,000100020003,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,C,4  ,1910         ,-1              ,N,0001000200030010,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,C,5  ,1910         ,-1              ,N,00010002000300100012,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_15,H,1  ,1            ,0.00052356020942408,N,,,2021-12-12-11.08.37.831161,1
:C1,20121212110833282209                                                        
:C2,20121212110833282209                                                        
:CD,COLUMN_15,H,1  ,1            ,0.00052356020942408,N,,,2021-12-12-11.08.37.831161,5
:C1,20121212110833382520                                                        
:C2,20121212110833382520                                                        
:CD,COLUMN_15,H,1  ,609          ,0.31884816753927,N,,,2021-12-12-11.08.37.831161,3
:C1,20121212110833315038                                                        
:C2,20121212110833317112                                                        
:CD,COLUMN_15,H,1  ,626          ,0.32774869109948,N,,,2021-12-12-11.08.37.831161,2
:C1,20121212110833283413                                                        
:C2,20121212110833285872                                                        
:CD,COLUMN_15,H,1  ,673          ,0.35235602094241,N,,,2021-12-12-11.08.37.831161,4
:C1,20121212110833379764                                                        
:C2,20121212110833382508                                                        
:CD,COLUMN_9,C,2  ,947          ,-1              ,N,00090002,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.012041884816754,N,00090002,A1FF8000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.013089005235602,N,00090002,A1958000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.029319371727749,N,00090002,A2468000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,C,3  ,1910         ,-1              ,N,000100020003,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,C,4  ,1910         ,-1              ,N,0001000200030010,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_1,C,5  ,1910         ,-1              ,N,00010002000300100012,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_15,H,1  ,1            ,0.00052356020942408,N,,,2021-12-12-11.08.37.831161,1
:C1,20121212110833282209                                                         
:C2,20121212110833282209                                                         
:CD,COLUMN_15,H,1  ,1            ,0.00052356020942408,N,,,2021-12-12-11.08.37.831161,5
:C1,20121212110833382520                                                         
:C2,20121212110833382520                                                         
:CD,COLUMN_15,H,1  ,609          ,0.31884816753927,N,,,2021-12-12-11.08.37.831161,3
:C1,20121212110833315038                                                         
:C2,20121212110833317112                                                         
:CD,COLUMN_15,H,1  ,626          ,0.32774869109948,N,,,2021-12-12-11.08.37.831161,2
:C1,20121212110833283413                                                         
:C2,20121212110833285872                                                         
:CD,COLUMN_15,H,1  ,673          ,0.35235602094241,N,,,2021-12-12-11.08.37.831161,4
:C1,20121212110833379764                                                         
:C2,20121212110833382508                                                         
:CD,COLUMN_9,C,2  ,947          ,-1              ,N,00090002,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.012041884816754,N,00090002,A1FF8000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.013089005235602,N,00090002,A1958000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.029319371727749,N,00090002,A2468000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.059685863874346,N,00090002,A0E08000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,F,2  ,-1           ,0.068586387434555,N,00090002,A2788000,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,H,2  ,1            ,0.00052356020942408,N,00090002,,2021-12-12-11.08.37.831161,1
:C1,80048000                                                                     
:C2,80048000                                                                     
:CD,COLUMN_9,H,2  ,25           ,0.013612565445026,N,00090002,,2021-12-12-11.08.37.831161,3
:C1,8D870B10                                                                     
:C2,934EE592                                                                     
:CD,COLUMN_9,H,2  ,123          ,0.071204188481675,N,00090002,,2021-12-12-11.08.37.831161,2
:C1,8014A99A                                                                     
:C2,8BB6A260                                                                     
:CD,COLUMN_9,H,2  ,798          ,0.91465968586387,N,00090002,,2021-12-12-11.08.37.831161,4
:C1,97AE8510                                                                     
:C2,A6FDF264                                                                     
:CD,COLUMN_9,C,3  ,1840         ,-1              ,N,000900020003,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,4  ,1885         ,-1              ,N,0009000200030004,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,5  ,1885         ,-1              ,N,00090002000300040005,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,6  ,1898         ,-1              ,N,000900020003000400050007,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,7  ,1909         ,-1              ,N,0009000200030004000500070008,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,8  ,1910         ,-1              ,N,00090002000300040005000700080001,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,9  ,1910         ,-1              ,N,00090002000300040005000700080001000A,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,10 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B,,2021-12-12-11.08.37.831161,-1
:CD,COLUMN_9,C,11 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C,,2021-12-12-11.08.37.831161
:CD,COLUMN_9,C,12 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D,,2021-12-12-11.08.37.83
:CD,COLUMN_9,C,13 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D000E,,2021-12-12-11.08.3
:CD,COLUMN_9,C,14 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D000E000F,,2021-12-12-11.
:CD,COLUMN_9,C,15 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D000E000F0010,,2021-12-12
:CD,COLUMN_9,C,16 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D000E000F00100011,,2021-1
:CD,COLUMN_9,C,17 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D000E000F001000110012,,20
:CD,COLUMN_9,C,18 ,1910         ,-1              ,N,00090002000300040005000700080001000A000B000C000D000E000F0010001100120013
:IX,TSS,PSSX01_D91S01T01,9        ,2 ,1               ,1910         ,1910         ,2021-12-12-11.08.37.831161,114
:IX,TSS,PSSX02_D91S01T01,35       ,2 ,0.9979057591623 ,1910         ,1910         ,2021-12-12-11.08.37.831161,212
:IX,TSS,PSSX03_D91S01T01,93       ,2 ,0.73350785340314,112          ,1910         ,2021-12-12-11.08.37.831161,1259
:TS,TSSQX91 ,PSSS0291,1440      ,2021-12-12-11.08.38.341014                     
:TB,TSS,TSST01_D91S02,2036         ,115      ,0  ,2021-12-12-11.08.38.341014    
:TA,1  ,0            ,0      ,0  ,360    ,0  ,N,2021-12-12-11.08.38.341014      
:TA,2  ,19           ,2      ,0  ,360    ,0  ,N,2021-12-12-11.08.38.341014      
:TA,3  ,350          ,20     ,5  ,360    ,0  ,N,2021-12-12-11.08.38.341014      
:TA,4  ,1667         ,93     ,25 ,360    ,0  ,N,2021-12-12-11.08.38.341014      
:CO,COLUMN_1,2036         ,801BCEAE,8012F7AC,2021-12-12-11.08.38.341014         
:CO,COLUMN_2,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_3,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_4,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_5,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_6,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_7,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_8,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_9,-1           ,,,0001-01-01-00.00.00.000000                         
:CO,COLUMN_10,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_11,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_12,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_13,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_14,-1           ,,,0001-01-01-00.00.00.000000                        
:CO,COLUMN_15,2036         ,20121212110835777580,20121212110835662744,2021-12-12-11.08.38.341014
:CO,COLUMN_17,-1           ,,,0001-01-01-00.00.00.000000                       
:CO,COLUMN_18,-1           ,,,0001-01-01-00.00.00.000000                       
:CO,COLUMN_19,-1           ,,,0001-01-01-00.00.00.000000                       
:CO,COLUMN_20,-1           ,,,0001-01-01-00.00.00.000000                       
:CO,COLUMN_21,-1           ,,,0001-01-01-00.00.00.000000                       
:CD,COLUMN_1,C,2  ,2036         ,-1              ,N,00010002,,2021-12-12-11.08.38.341014,-1
:CD,COLUMN_1,C,3  ,2036         ,-1              ,N,000100020003,,2021-12-12-11.08.38.341014,-1
:CD,COLUMN_1,F,3  ,-1           ,0.00049115913555992,N,000100020003,801BCEADDBCC40C1C1C2D5F0F0F0F1F4F3F3,2021-12-12-11.08.38
:CD,COLUMN_1,F,3  ,-1           ,0.00049115913555992,N,000100020003,801BCEAE800040C1C1C2D5F0F0F0F1F4F3F3,2021-12-12-11.08.38
:CD,COLUMN_1,F,3  ,-1           ,0.00049115913555992,N,000100020003,801BCEAFB10D40C1C1C2D5F0F0F0F1F4F3F3,2021-12-12-11.08.38
:CD,COLUMN_1,H,3  ,1            ,0.00049115913555992,N,000100020003,,2021-12-12-11.08.38.341014,3
:C1,801BCEAFB10D40C1C1C2D5F0F0F0F1F4F3F3                                       
. . .


 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI SQL Explorer for Db2 13.1