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.

Migrating statistics

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:

  1. 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.
  2. (Optional) Update extracted statistics.
  3. 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

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

  4. 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:

     AUBDYN                        Confirm SQL                        1 to 37 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     = '2022-08-17-06.36.32.206999' ,                          
            SPACEF        = 288 ,                                                   
            AVGROWLEN     = 165 ,                                                   
            NACTIVEF      = 72                                                      
            WHERE DBNAME  = 'ACTQX19'                                               
              AND NAME    = 'ACTS0219' ;                                            
     ------                                                                         
     COMMIT ;                                                                       
     ------                                                                         
     UPDATE SYSIBM.SYSTABLES SET                                                    
            STATSTIME     = '2022-08-17-06.36.32.206999' ,                          
            AVGROWLEN     = 165 ,                                                   
            CARDF         = 1 ,                                                     
            NPAGES        = 1 ,                                                     
            NPAGESF       = 1 ,                                                     
            PCTPAGES      = 12 ,                                                    
            PCTROWCOMP    = 0                                                       
            WHERE CREATOR = 'ACT'                                                   
              AND NAME    = 'ACTT01_D19S02' ;                                       
     ------                                                                         
     UPDATE SYSIBM.SYSCOLUMNS SET                                                   
            STATSTIME       = '2022-08-17-06.36.32.206999' ,                        
            HIGH2KEY        = X'8001046A' ,                                         
            LOW2KEY         = X'8001046A' ,                                         
            STATS_FORMAT    = ' ' ,                                                 
            COLCARDF        = 1                                                     
            WHERE TBCREATOR = 'ACT'                                                 
              AND TBNAME    = 'ACTT01_D19S02'                                       
              AND NAME      = 'COLUMN_1' ;                                          
     ------                                                                         
     DELETE FROM SYSIBM.SYSCOLDIST                                                  
            WHERE TBOWNER   = 'ACT'                                                 
              AND TBNAME    = 'ACTT01_D19S02'                                       
              AND NAME      = 'COLUMN_1' ;                                          
     ------                                                                         
     INSERT INTO SYSIBM.SYSCOLDIST ( FREQUENCY, STATSTIME, IBMREQD,                 
  5. In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL and then press Enter.

  6. Update the object names as needed and then press End.

    The Confirm SQL panel is displayed.

  7. 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 Ex ecute  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.

  8. 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 Ex ecute 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 Ex ecute 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.

 

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

BMC AMI Catalog Manager for Db2 13.1