Updating the Db2 catalog by using BMCUPRS

Use this procedure to create a BMCUPRS action that updates the Db2 catalog with the BMC Software statistics from the 

DASD MANAGER PLUS

 database.

If you have current BMCSTATS statistics, you might want to use BMCUPRS instead of BMCSTATS. BMCUPRS enables you to update the Db2 catalog during low activity times.

Important

DASD MANAGER PLUS does not collect statistics for the following objects:

  • HASH table spaces
  • Indexes that contain keys with random ordering
  • Objects with names that do not convert to EBCDIC

To update the Db2 catalog by using BMCUPRS

  1. Create an action as described in Creating-a-Service-Action.A user-defined Service Action is a group of services (utilities) within a DASD MANAGER PLUS job. This Service Action creates a worklist to run the BMCUPRS utility. For more information about actions, see Maintaining-and-generating-Service-Actions.

    After you specify the action and press Enter, the Action List panel appears.

  2. On the Action List panel, select the action by typing E (or S) in the Act field beside the action and pressing Enter.
  3. On the Edit Action Services panel, add the BMCUPRS service to the Service Action, as follows:
    1. Type I in the Act field next to the services that should precede the BMCUPRS service and press Enter to add a blank line.
    2. In the blank line, type the following information:
      • Type E (or S) in the Act field.
      • In the Service field, type BMCUPRS.

      • In the Object Name field, type the fully qualified object name.

        You can use wildcards. For example, QZUD40.% specifies all table spaces in the QZUD40 database.

      • In the Type field, type the two-letter abbreviation for the object type. Valid entries are:

        • TS (table space)
        • IX (index)
        • IS (index space)
        • TT (table space set)

        Alternatively, specify the object name and object type by using an object set.

      • Press Enter to display the Service Syntax List panel.

  4. Type E in the Act field next to the syntax and press Enter to display the BMCUPRS parameters panel for a table space (below). Optionally, you can add new syntax by typing I. 

    DEMO               BMCUPRS TABLESPACE XXXXXX.XXXXXX         Row 1 to 21 of 21
    Command ===>                                                Scroll ===> CSR  

    Service Syntax: BMCUPRS

    Type Service Syntax options. Then press End.                        More:      

    -------------------------- What to Update -------------------------------------
    Tablespace Options:                                                           
      TABLE  . . . . . * N      (Y/N/S Y=ALL Tables, N=No Tables, S=Select Tables)
      INDEX  . . . . . Y        (Y/N Update column stats on all indexes)           

    -------------------------- DB2 Catalog Update Options -------------------------
    UPDATEDB2  . . . . A        (A/P/S A=All, P=Accesspath, S=Space)               
    HISTORY  . . . . . N        (N/A/P/S N=None, A=All, P=Accesspath, S=Space)     
      DELETEHISTAGE    32767    (0-32767 Days for deleting history table entries)  
    OMITCARD0  . . . . N        (Y/N - Bypass catalog update if cardinality is 0)  
    RESETRTS . . . . . N        (Y/N - Reset realtime stats in the catalog)        
    INVALIDATECACHE             (Y/N Invalidate Dynamic Statement Cache)          

    -------------------------- BMC Statistics Report Options ----------------------
    REPORT . . . . . . Y        (Y/N - Print statistics report)                    


    -------------------------- Stats Processing Options ---------------------------
    BADOBJECTRC  . . . 4        (0-8 Return code when object is bypassed)          
    MSGLEVEL . . . . . 0        (0/1 0-Normal msgs, 1-Additional msgs)             
    ******************************* Bottom of data ********************************
  5. Specify the BMCUPRS utility parameters, as follows:
    1. In the TABLE field, type one of the following actions:
      • Type Y to update column statistics on all tables in the table space.
      • Type S to display a selection list of tables to update. The options for S are similar to the options for using BMCSTATS.
      • Type N if you don't want to update column statistics on all tables in the table space. The default is N.
    2. In the INDEX field, indicate whether to update column statistics for all of the indexes in the table space and for the table space itself. The default is Y.
    3. In the UPDATEDB2 field, indicate whether to update the Db2 catalog with All, Accesspath, or Space statistics that you gather.The default is A (for all). BMCSTATS updates the same columns for ACCESSPATH and SPACE statistics as does IBM's RUNSTATS utility. For more information, see the chapter about managing statistics in the catalog in the Administration Guide for DB2 for OS/390 and z/OS, volume 2.
    4. In the History field, indicate whether to update the Db2 tables.The default is N.
    5. In the Delete History Age field, indicate how long to keep the history table entries before deleting them.The default is 32767, which means that the entries will not be deleted.
    6. In the OMITCARD0 field, indicate whether to bypass updating the Db2 catalog for objects in which BMCSTATS finds a zero cardinality.The default is N, which means that the Db2 catalog will be updated.
    7. In the RESETRTS field, indicate whether to reset the statistics in the Db2 real-time statistics tables.The default is N.

    8. In the INVALIDATECACHE field, indicate whether to invalidate the dynamic statement cache for an object. 

      The default is Y, which means that the dynamic statement cache is invalidated.


    1. In the REPORT field, indicate whether to print a report into the job output of the statistics that you copied or updated.The default is Y.
    2. In BADOBJECTRC and MSGLEVEL fields, accept the defaults.
    3. When you finish editing the BMCUPRS parameters, press END to save your changes and press END again until you return to the Edit Action Services panel.
    4. Enter S on the Service Syntax List panel to select it.
  2. Access the Action Job Generation panel by typing G beside an action on the Action List panel and pressing Enter.The Action Job Generation panel appears.
  3. Complete the fields on the Action Job Generation panel, as follows:
    1. In the Build Worklist, Build JCL, and Submit Job fields type S.
    2. (optional) In the Edit Worklist and Edit JCL fields, type S to review the built worklist and built JCL.
    3. Press Enter.
  4. Depending on the options that you specify on the Action Job Generation panel, complete the following tasks:
    • To override the Product Options File (POF) settings, select Override POF Values.
    • To build the worklist, select Build Worklist.

    • To review the built worklist, select Edit Worklist.

      For detailed information about the worklist commands, see Worklist-commands.

    • To build the JCL, select Build JCL.

    • To review the built JCL, select Edit JCL.

      When you review the built JCL, you can submit it for execution by typing SUB in the Command line.

    • To run the job automatically after building the worklist and JCL, select Submit JCL.

      As an alternate method (if you selected Edit JCL) type SUB on the ISPF Edit panel.

      Asterisks (*) in the Build Worklist and Build JCL fields indicate that the product has generated them.

  5. Press Enter to confirm that you want to submit the BMCUPRS job. If you do not want to submit the job now, press END to return to the Edit Action Services panel.

    Tip

    Alternately, when the JCL appears for you to review it, you can enter SUB on the ISPF Edit panel to submit the JCL.



 

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