Migrating statistics

(BMC.DB2.SPE2110)

You can use the HSTATS command to copy statistics from one Db2 catalog to another, or to the same subsystem’s Db2 catalog. For example, you can migrate production statistics to a test environment to view the actual access paths that the Db2 optimizer selects, without the cost of replicating the production environment. This example assumes that your Db2 environments are the same—that buffer pools, EDM pools, and so on are of comparable sizes in both environments. You can preserve the test data and still see the access paths based on production data.

Related topics

Setting object use options

Catalog Manager commands

To migrate statistics, follow these steps:

Extract the statistics from the source Db2 subsystem. This process creates a script containing the access path statistics to update on the target Db2 catalog.
(Optional) Update extracted statistics.
Transfer statistics into the target Db2 subsystem (SSID).

Because of the method used to extract the statistics, Catalog Manager does not verify whether the objects exist in the target environment. If the objects do not exist, the Transfer job receives the following message: The following row not found for update.

Before you begin

Make sure that the user administering the Extract or the Transfer jobs has the appropriate authorities to access the Db2 catalog.

To migrate statistics

Access the Db2 subsystem that contains the statistics that you want to migrate.
Create a list of databases, table spaces, or tables. For more information, see Generating lists in Catalog Manager.
In the Cmd column of the list, enter HSTATS beside the object that you want to use as a model. Then, press Enter.

The SQL Progress Indicator panel is displayed. The indicator shows the execution of SQL that makes a list of dependents for the object specified.

When SQL creation is complete, the Confirm SQL panel is displayed as follows:

 DEBA-R                        Confirm SQL                        1 to 30 of 570
 Command ===>                                                  Scroll ===> PAGE                                                                                                                                                                    

 Current SQLID. . . . . . . .  MVSSXS2                                          
 Edit Options . . . . . . . .  N         Y/N Modify SQL processing options      
 Edit SQL . . . . . . . . . .  N         Y/N Edit SQL before executing          
 Save in SQL table. . . . . .  N         A/Y/R/N A/Y-Append, R-Replace          
  Name of saved data. . . . .  20220222_094815                                  
 Save in PDS. . . . . . . . .  N         Y/N Save in PDS                        
  PDS(member) . . . . . . . .  MVSSXS2.ACT.HSTAT(ACTS0219)                      
                                                                                
 Execute SQL. . . . . . . . .  N         Remote Db2 . NONE      Enter ? for list
 -------------------------------------  SQL  -----------------------------------
 UPDATE SYSIBM.SYSTABLESPACE SET                                                
        STATSTIME     = '2021-07-22-07.47.17.664999' ,                          
        SPACEF        = 288 ,                                                   
        AVGROWLEN     = 165 ,                                                   
        NACTIVEF      = 72                                                      
        WHERE DBNAME  = 'ACTQX19 '                                              
          AND NAME    = 'ACTS0219' ;                                            
 ------                                                                         
 COMMIT ;                                                                       
 ------                                                                         
 UPDATE SYSIBM.SYSTABLES SET                                                    
        STATSTIME     = '2021-07-22-07.47.17.664999' ,                          
        AVGROWLEN     = 165 ,                                                   
        CARDF         = 1 ,                                                     
        NPAGES        = 1 ,                                                     
        NPAGESF       = 1 ,                                                     
        PCTPAGES      = 12 ,                                                    
        PCTROWCOMP    = 0                                                       
        WHERE CREATOR = 'ACT     '                                              
          AND NAME    = 'ACTT01_D19S02' ;                                       
 ------                                                                         
 UPDATE SYSIBM.SYSCOLUMNS SET                                                   
        STATSTIME       = '2021-07-22-07.47.17.664999' ,                        
        HIGH2KEY        = X'8001046A' ,                                         
        LOW2KEY         = X'8001046A' ,                                         
        STATS_FORMAT    = ' ' ,                                                 
        COLCARDF        = 1                                                     
        WHERE TBCREATOR = 'ACT     '

In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.
Update the object names as needed and then press End.
The Confirm SQL panel is displayed.
To migrate statistics to another object on the same Db2 subsystem, perform the following steps:
1. In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.
2. Update the object names as needed and then press End.
  The Confirm SQL panel is displayed.
3. In the Execute SQL field, type Y to execute the SQL displayed on the Confirm SQL panel and then press Enter.
  The SQL Progress Indicator panel is displayed. The panel automatically refreshes to display the status of the SQL that is being executed.
4. Press End.
To migrate statistics to a different Db2 subsystem, perform one of the following procedures:
If you are using (BMC.DB2.SPE2210) or later, perform the following steps:
1. In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.
2. Update the object names as needed and then press End.
  The Confirm SQL panel is displayed.
3. In the Remote Db2 field, perform one of the following steps:
  - Enter a Db2 subsystem ID.
  - To select a Db2 subsystem from a list, type ? and then press Enter.
4. In the Execute SQL field, type Y to execute the SQL displayed on the Confirm SQL panel and then press Enter.
  The SQL Progress Indicator panel is displayed. The panel automatically refreshes to display the status of the SQL that is being executed.
5. Press End.
If you are using pre BMC.DB2.SPE2210, click here for the procedure:
1. In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.
2. Update the object names as needed and then press End.
  The Confirm SQL panel is displayed.
3. In the Save in PDS field, type Y to save the SQL in a member of a partitioned data set (PDS).
4. In the PDS(member) field, type the name of the PDS and member. Then press Enter.
5. Connect Catalog Manager to the receiving Db2 subsystem.
6. Create a list of objects.
7. In the Cmd column of the list, enter DDL next to an object and then press Enter.
  The Confirm SQL panel is displayed.
8. In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.
9. Delete the SQL from the ISPF edit session.
10. Copy the contents of the PDS member, saved in step 8.d, to the ISPF edit session.
11. Press End to return to the Confirm SQL panel.
12. In the Execute SQL field, type Y to execute the SQL displayed on the Confirm SQL panel and then press Enter .
  The SQL Progress Indicator panel is displayed. The panel automatically refreshes to display the status of the SQL that is being executed.
13. Press End.

Migrating statistics

Before you begin

To migrate statistics

Comments