Identifying What-If Index candidates
Use this procedure to identify a table that has both a significant amount of getpage activity and a minimal amount of index getpage activity associated with it, by using the Index Component.
Such a table might benefit from the addition of an index.
To identify What-If Index candidates
Use the information in Preparing-to-use-What-If-Index to define options for reporting.Examine the Table Getpage Volume report for an overview of getpage and I/O activity for all tables (and their indexes) on the sysplex, ordered by the volume of getpages that are associated with the tables.
Each table has one line of statistics. The report includes only tables with getpage activity. The Getpage Index % values provide information about the efficiency of the indexes on the tables. A large Getpage Index Number value with a low Getpage Index % value might be a good candidate for What-If Index analysis.
For table RDADMB.DMBTBDYN1, the value in the Getpage Number column shows that this table had getpage requests and that the percentage of total getpages for this table that can be attributed to its indexes is 0.0%.
ASQEQRPW/I View a Report LINE 1 OF 12
Command ====> _______________________________________________ Scroll ===> CSR_
BMCSftwr.IODTGETV -- TABLE GETPAGE VOLUME -- 06/15 16:19:40
Source : DOMS-ACTIVE Intvl : 6/15 16:10 - UNLIMITED
------------------------------------------------------------------------------------------------------------------------ -
Actions for +: B-Table/Index Breakdown T-Table Stats M-Crud Matrix
Q-Qualifications S-Statement List
for *: D-Index Dependencies I-Index Stats Q-Qualifications
+--------- Table ---------+ +---------- Getpage ---------+ +- Sync I/O --+ +-- Async I/O --+ Total Change Change
Creator Name Number Index% /Stmt AvgTime Number AvgTime Number Pages Stmts Stmts Stmt %
-------- ------------------ -------- ------ ------ ------- ------- ------- -------- -------- -------- -------- ------
T RDADMB DMBTBDYN1 168032 0.0% 2.7 0.00000 1 0.00861 0 0 61410 49128 80.0%
+ SYSIBM SYSTABLES 24647 50.0% 2.0 0.00000 0 0.00000 0 0 12299 12288 99.9%
+ SYSIBM SYSTABLESPACE 12292 66.7% 1.0 0.00000 0 0.00000 0 0 12290 12288 100%
+ SYSIBM SYSCOLUMNS 605 11.7% 60.5 0.00000 0 0.00000 0 0 10 4 40.0%
+ BMCDAA52 V52_SQLX_RULES 76 0.0% 38.0 0.00052 2 0.01385 4 29 2 0 0.0%
+ RDADMB PLAN_TABLE 20 0.0% 5.0 0.00284 2 0.01551 2 28 4 2 50.0%
+ RDADMB DSN_DETCOST_TABLE 16 0.0% 4.0 0.00690 2 0.05522 2 60 4 2 50.0%
+ RDADMB DSN_PREDICAT_TABLE 16 0.0% 4.0 0.00269 3 0.01436 2 0 4 2 50.0%
+ RDADMB DSN_STATEMNT_TABLE 12 0.0% 3.0 0.00051 1 0.00606 0 0 4 2 50.0%
+ SYSIBM SYSDATABASE 7 0.0% 7.0 0.00143 1 0.00087 2 59 1 0 0.0%
+ RDADMB STRUCTURE_TABLE 2 0.0% 1.0 0.00000 0 0.00000 0 0 2 2 100%
+ SYSIBM SYSTABLEPART 2 66.7% 1.0 0.00001 0 0.00000 0 0 2 0 0.0%To view table statistics, type T over the plus sign (+) beside the table, and press Enter.The Table Statistics report shows the physical characteristics of the table, along with RUNSTAT statistics that you can use to analyze the index.
ASQEQRPN/I View a Report LINE 1 OF 16
Command ====> _______________________________________________ Scroll ===> CSR_
BMCSftwr.IODTSTAT -- TABLE STATISTICS -- 06/15 16:22:10
Source : DOMS-ACTIVE Intvl : 06/15 16:22 - UNLIMITED More: +
-------------------------------------------------------------------------------
Actions for '*': N-Index List
Actions for '+': D-Detail
Actions for '-': H-Show Hex
Logical DB2 Name: DECX Card . . . . . : -1.000
Table Owner . . : RDADMB Number Pages . : -1
N Table Name . . : DMBTBDYN1 Pctpages . . . : -1.00
Database . . . : DMBDBDYN Colcount . . . : 8
Tablespace . . : DMBTSDYN Record Length . : 44
Last Stats . . : 0001-01-01-00.00.00.000000
Col Number
Column Name No. ColType Length Scale Nulls Colcard Pkno Indexes
------------------ --- -------- ------ ----- ----- -------- ---- -------
+ COL01 1 CHAR 8 0 N -1 0 0
+ COL02 2 INTEGER 4 0 N -1 0 0
+ COL03 3 INTEGER 4 0 N -1 0 0
+ COL04 4 INTEGER 4 0 N -1 0 0
+ COL05 5 INTEGER 4 0 N -1 0 0
+ COL06 6 INTEGER 4 0 N -1 0 0
+ COL07 7 INTEGER 4 0 N -1 0 0To view index information for the table, type N over the asterisk (*) beside the table name, and press Enter.The Defined Indexes for a Table report is displayed. This report helps you to determine whether indexes exist for a table. In this example, the table has no defined indexes.
- Press F3 until you return to the Table Getpage Volume report.
To view a CRUD Matrix for the table, type M over the plus sign (+) beside a table, and press Enter.The Table CRUD Matrix report is displayed. This report shows the columns of the selected table and how they were accessed. In this example, COL01 was used a number of times in an indexable predicate but was never updated. This column might be a good candidate to include in an index.
- Press F3 to return to the Table Getpage Volume report.
To view statement information for the table, type an S over the plus sign (+) beside a table name, and press Enter.The Statement List report is displayed. This report shows all statements that use the specified table.
ASQEQRPW/I View a Report LINE 1 OF 690
Command ====> _______________________________________________ Scroll ===> CSR_
BMCSftwr.IODSTMTS -- STATEMENT LIST -- 11/22 16:28:18
Source : DOMS-ACTIVE Intvl : 06/28 16:10 - UNLIMITED More: +
-----------------------------------------------------------------------------------------------------------------------------
Actions: B-Table/Index Breakdown T-SQL Text
Logical DB2 Name: DECX Table Creator: RDADMB Table Name: DMBTBDYN1
+---------- Getpage ---------+ +- Sync I/O --+ +-- Async I/O --+ SQL Statement
Plan Program Sect. Stmt# Number Index% /Stmt AvgTime Number AvgTime Number Pages Calls Type
-------- -------- ----- ----- -------- ------ ------ ------- ------- ------- -------- -------- -------- -------------------
+ DYNSQL DYNSQL 2 283 356 0.0% 4.0 0.00000 0 0.00000 0 0 89 DELETE
+ DYNSQL DYNSQL 2 283 356 0.0% 4.0 0.00000 0 0.00000 0 0 89 DELETE
T DYNSQL DYNSQL 1 180 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 CURSOR
+ DYNSQL DYNSQL 2 283 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 DELETE
+ DYNSQL DYNSQL 2 229 267 0.0% 3.0 0.00001 0 0.00000 0 0 89 UPDATE
+ DYNSQL DYNSQL 1 180 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 CURSOR
+ DYNSQL DYNSQL 2 229 267 0.0% 3.0 0.00001 0 0.00000 0 0 89 UPDATE
+ DYNSQL DYNSQL 2 283 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 DELETE
+ DYNSQL DYNSQL 2 283 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 DELETE
+ DYNSQL DYNSQL 1 180 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 CURSOR
+ DYNSQL DYNSQL 1 180 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 CURSOR
+ DYNSQL DYNSQL 2 283 267 0.0% 3.0 0.00001 0 0.00000 0 0 89 DELETE
+ DYNSQL DYNSQL 2 229 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 UPDATE
+ DYNSQL DYNSQL 3 242 267 0.0% 3.0 0.00000 0 0.00000 0 0 89 UPDATETo view SQL text for a statement, type T over the plus sign (+) beside a statement, and press Enter.The Statement Text report is displayed.
ASQEQRPW/I View a Report LINE 1 OF 5
Command ====> _______________________________________________ Scroll ===> CSR_
BMCSftwr.IODSTXDH -- STATEMENT TEXT -- 11/22 16:36:45
Source : DOMS-ACTIVE Intvl : 11/22 16:10 - UNLIMITED
----------------------------------------------------------------------------------------
Actions for '+': X-Explain Stmt T-Table/Index Breakdown
Logical DB2 Name: DECX
Plan: DYNSQL Program: DYNSQL Section: 1 Stmt Type: DYNAMIC Call Type: PREPARE
Statement Text
--------------------------------------------------------------------------------------
+ SELECT *
FROM RDADMB.DMBTBDYN1
WHERE COL01 = 'AAAAJAAA'- From this report, you can begin using What-If Index to evaluate the effects of various index changes.For more information, see Using-the-What-If-Index-function.
Related topic