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.

Estimating the space requirements for an index

Use the following procedure to estimate the space requirements for an index:

Related topics

Overview-of-space-estimation

Analyzing-an-alter-type-work-ID-and-generating-a-worklist-in-Change-Manager

Executing-an-alter-type-worklist

Accomplishing-your-data-structure-modification-goals

  1. On the BMC AMI Change Manager for Db2  Main Menu, select WORKID, and press Enter.
  2. On the WORKID Action Menu, type the name of the WORKID or type a wildcard pattern to display a list of work IDs. Then, select Specify Db2 definitions and press Enter.
  3. On the Object Specification panel, specify the name of a partitioned index (or use wildcard characters). Then, press Enter.
  4. On the Mixed List panel, type E in the Act column to edit an index (IX) object. Then, press Enter.

  5. On the Index Detail panel, type S to select Space Estimation. Then, press Enter.The Index Space Estimation panel (partitioned) is displayed.

    Success

    Tip

    Some object names might be too long to be displayed on a panel. To view a long object name, position the cursor on the object name and press the ZOOM (F4) key.

    ALUS9XSE R   --------------------- Index Space Estimation ---------------------
    Command ===>                                                    Scroll. . PAGE
                                                                                  
    IX Name . . . . : J9QTALT.I_T01AICP1                 |------ Estimated -------|
    TB Name . . . . : J9QTALT.T_T01AICP                  | Pages : 12             |
    Table Rows. . . : 0                                  | Space : 192         K  |
    Max Key Length  : 17                                 | Levels: 2              |
    Pagesize  . . . : 8                                  |------------------------|
    Unique  . . . . . D         (U,D,W)                                            
    Compress  . . . . Y         (Y,N)                                              
    Pct Compressed  . 0         (0-99)                                             
                                                               Lines 1 to 4 of 4
    (PA=Propagate Attributes, PE=Propagate Est)                      More:       >
      Logical          Avg Key       Rows                  A      --- Estimated ---
    AC Part   NbrRows  Len Orig      /Key   PriQty  SecQty U       Space     NbrSec
    ************************************ TOP **************************************
          1         0   17 Proj         1     1440     720 K          48 K        0
          2         0   17 Proj         1     1440     720 K          48 K        0
          3         0   17 Proj         1     1440     720 K          48 K        0
          4         0   17 Proj         1     1440     720 K          48 K        0
    *********************************** BOTTOM ************************************
  6. (Optional) Change the uniqueness of the index key.

  7. Modify the following values in the lower portion of the panel for an index:

    • NbrRows—the number of rows in this partition
    • Avg Len—the average length of the key
    • Rows/Key—the number of rows per key value
    • PriQty—the primary quantity for the partition
    • SecQty—the secondary quantity for the partition
    • AU (Aloc Unit)—the allocation unit for the partition
    • Free Page—the page increment for inserting a free page
    • Pct Free—the percentage of free space to leave in each non-leaf page
    • NonLf KeyLn—the average key length for a non-leaf page

    • Devt—the disk device type on which the partition is to be allocated 

      (BMC.DB2.SPE2210)

    Warning

    Important

    You can also type an action code in the Ac column to modify partitioned indexes:

    • To propagate the space estimates to the PriQty field for a specified partition, type PE.
    • To change the values for NbrRows, PriQty, SecQty, AU (Aloc Unit), Rows/Key, and Avg Len fields for all of the partitions in the index, type PA adjacent to the partition that you want to propagate. In the Space Estimation Attribute Propagation panel that is displayed, type over the values that you want to change. Then press END.

    To propagate the space estimates for all of the partitions in the index, type PEALL on the Command line.

    ALUSIXSE R   --------------------- Index Space Estimation ---------------------
    Command ===>                                                    Scroll. . PAGE

    IX  --------------------------------------------------------------------- ----|
    TB  | ALUSPASX     Space Estimation Attribute Propagation               |     |
    Tab | Command ===>                                                      |   K |
    Max |                                                                   |     |
    Uni | Index Name  . . : RDACRJ.IXP7A__C                                 | ----|
        | Device Type . . : 3390                                            | f 7
    (PA |                                                                   |    >
      L |                                                                   | d ---
    AC  | Make any changes necessary or blank out to leave current value;   | brSec
    *** | changes will be applied to all parts where applicable.            | *****
    pa  |                                                                   |     0
        |            AVG   ROWS/                        ALOC                |     0
        | NBRROWS    LEN   KEY        PRIQTY   SECQTY   UNIT                |     0
        | 0          4     1          -1       -1       K                   |     0
        |                                                                   |     0
        |                                                                   |     0
        | Commands: END CANCEL                                              |     0
    *** |                                                                   | *****
        ---------------------------------------------------------------------
    Success

    Tip

    Some object names might be too long to be displayed on a panel. To view a long object name, position the cursor on the object name and press the ZOOM (F4) key.

  8. Press Enter.The new space estimates for the number of pages, the total amount of space, and the number of index levels that are required are displayed.
  9. When you have finished reviewing the space estimates, modify the values for the index in the upper left-hand portion of the panel. The values that you can modify are described in Overview-of-space-estimation. Then, press Enter.

  10. When you have finished updating the fields, press END until the Mixed List panel is displayed.

    ALUSMXL ER   --------------------------- Mixed List ---------------------------
    Command ===>                                                    Scroll. . PAGE

    WORKID  . . . . : RDACRJ.ALTER                                Commands: CANCEL

    Type action next to object and press Enter.
       E=Edit  L=Like  D=Drop  U=Undo
                                                        Objects 112 to 125 of 227
                                                                     More:   - +
    Act     Object-Type    Objects
                TB . . . .     RDACRJ     'tbp1a'
                  FK . . .       RDACRJ     'tbp1a'              P1AX1
        *$        IXC. . .       RDACRJ     IXP7A__C
                  IXP. . .       RDACRJ     IXP7A_2
                    UCP. .         RDACRJ     'tbp1a'              ' TP1A C4 1'
                  AL . . .       RDACRJ     ALP1A__C
              TS . . . . .   DEMOCRJ    ROBBINS
                TB . . . .     RDACRJ     JGSD004_A17_LOWC
                TB . . . .     RDACRJ     JNMP007_BO_HEADER
                TB . . . .     RDACRJ     JNMP029_BO_CMDTY
              TS . . . . .   DEMOCRJ    SF1
              TS . . . . .   DEMOCRJ    SF1X
              TS . . . . .   DEMOCRJ    S1
                TB . . . .     RDACRJ     TBS1A
    Warning

    Important

    If you do not make any changes that would affect Analysis, a space estimation marker is added. The $ marker denotes that an object is put away by Specification solely because of space estimation parameters.

  11. On the Index Detail panel, press END.
  12. Press END until the WORKID Action Menu is displayed.Now that the space requirements for an index have been estimated, you can analyze a work ID and execute a worklist.

 

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

ALTER and BMC AMI Change Manager for Db2 13.1