Manage virtual indexes

To display the Virtual Index Information panel, specify option 5 in the Create or Drop Index panel and press enter. The list of virtual indexes for the current SQL Id is displayed as shown in the following figure.

Figure 1. Virtual Index Information panel

SQLPA520 -------------- Virtual Index Information ------------- Row 1 to 1 of 1 
                                                                                
 Virtual index table: TS6224D.DSN_VIRTUAL_INDEXES               Db2 system: DC1Q  
                                                                Db2 SQLID : TS6224D
 Primary commands: ENABLE  DISABLE  DELETE                                 
 Line commands. .: V -View U -Update D -Delete E -Enable I -Disable         
       
  Index           Index          Table            Table
  Creator         Name           Creator          Name         En Md Cn Ui Ci                
  ----------> -----------> ---------------> -------------->    -- -- -- -- --         
  _ TS6224        IX10           TS6224         PLAN_TABLE     Y  C   4  U  N
 *********************************Bottom of data************************************

There are following columns in the Virtual Index Information panel:

Index Creator: The schema or authorization ID of the index owner.
Index Name: The index name.
Table Creator: The schema or authorization ID of the table owner.
Table Name: The name of the table on which the index is being created or dropped.
En (ENABLE): Indicates whether the index is enabled.
Md (Mode): Indicates whether the index is being created or dropped.
Cn: The number of columns in the key.
Ui (UNIQUERULE): Indicates whether the index is unique.
Cl (CLUSTERING): Indicates whether the index is clustered.

You can use the following line and primary commands for managing virtual indexes:

Primary commands:

ENABLE: Enable all virtual indexes in the table.
DISABLE: Disable all virtual indexes in the table.
DELETE: Delete all virtual indexes in the table.

Line commands:

V: View details of the selected virtual index.
U: Update selected virtual index.
D: Delete selected virtual index.
E: Enable selected virtual index.
I: Disable selected virtual index.

View Virtual Index

The Virtual Index Details report shows information of the selected virtual index for the current SQL id.

Figure 2. View Virtual Index panel

SQLPA520 ---------------- View Virtual Index ----------------- Row 1 to 1 of 1
                                                                                
 Virtual index table: TS6224.DSN_VIRTUAL_INDEXES                Db2 system: DC1Q
                                                                Db2 SQLID : TS6224
                                   
  Index name: IX39                  > Index Schema: TS6224 
  Table name: PLAN_TABLE            > Table Schema: TS6224

  Enabled . . . . . . . .: Y           Mode. . . . . . . . .  .: C
  Clustering. . . . . . .: N           Index keys padded. . . .: N
  Unique rule . . . . . .: U
  Index Page size . . . .: 4           Sparse index. . . . . . .: N
  Number of key targets .: 0           Extended index type . . .: 

  Number of leaf pages. .: 8           Number of index levels. .: 0
  First key column count.: 2           Number of distinct keys .: -1 
  Clustering ration. . . : -1          Data repeat factor. . . .: -1
  
  Number of columns in key: 5

  Column name                Sq Ord
 --------------------------> -- ---
  QUERYNO                     1  Asc
  QBLOCKNO                    2  Asc
  APPLNAME                    3  Asc
  PROGNAME                    4  Asc
  PLANNO                      5  Asc
 *****************************Bottom of data************************************

There are following fields in this panel:

Index schema: The schema or authorization ID of the index owner.
Index name: Name of the index.
Table schema: The schema or authorization ID of the owner of the table.
Table name: The name of the table on which the index is being created or dropped.
Enabled: Indicates whether the index is enabled.
Mode: Indicates whether the index is being created or dropped.
Clustering: Indicates whether the index is clustered.
Index keys padded: Indicates whether keys within the index are padded for varying-length column data.
Unique rule: Indicates whether the index is unique.
Index page size: The size of the leaf pages in the index in kilobytes.
Sparse index: Indicates whether the index is sparse.
Number of key targets: Shows the number of key targets for an extended index.
Extended index type: The type of extended index.
Number of leaf pages: The number of active leaf pages in the index.
Number of index levels: The number of levels in the index tree.
First key column count: The number of distinct values of the first key column.
Number of distinct keys: The number of distinct values of the key.
Clustering ratio: The percentage of rows that are in clustering order.
Data repeat factor: The number of data pages that are expected to be touched when an index key order is followed.

Update Virtual Index

Using the Update Virtual Index panel you can update the fields of the selected virtual index.

Figure 3. Update Virtual Index panel

SQLPA520 ---------------- Update Virtual Index ------------------ 9:18 
                                                                                
 Virtual index table: TS6224.DSN_VIRTUAL_INDEXES              Db2 system: DC1Q     
                                                              Db2 SQLID : TS6224  
                                   
  Index name: IX39                  > Index Schema: TS6224         >
  Table name: PLAN_TABLE            > Table Schema: TS6224         >

  Enabled . . . . . . . .: Y  (Y/N)    
  Mode. . . . . . . . . .: C
  Clustering. . . . . . .: N  (Y/N)
  Unique rule . . . . . .: U  (D/U)
  Index Page size . . . .: 4  (4, 8, 16, 32)      
  Index keys padded . . .: N  (Y/N)
  Sparse index. . . . . .: N  (N/Y/X)
  Number of leaf pages. .: 8
  Number of index levels.: 0
  First key column count.: 2
  Number of distinct keys: -1
  Clustering ration. . . : -1 (-1 or 0.nnnn)
  Data repeat factor. . .: -1
  
  Number of key targets .: 0            
  Extended index type . . .:

There are following fields in the Update Virtual Index panel:

Index schema : The schema or authorization ID of the index owner.
Index name: The index name.
Table schema: The schema or authorization ID of the table owner.
Table name: The name of the table on which the index is being created or dropped.
Enabled: Indicates whether the index is enabled.
Mode: Indicates whether the index is being created or dropped. Not available for updating.
Clustering: Indicates whether the index is clustered.
Unique rule: Indicates whether the index is unique.
Index page size: The size of the leaf pages in the index in kilobytes.
Index keys padded: Indicates whether keys within the index are padded for varying length column data.
Sparse index: Whether the index is sparse.
Extended index type: The type of extended index.
Number of leaf pages: The number of active leaf pages in the index.
Number of index levels: The number of levels in the index tree.
First key column count: The number of distinct values of the first key column.
Number of distinct keys: The number of distinct values of the key.
Clustering ratio: The percentage of rows that are in clustering order.
Data repeat factor: The number of data pages that are expected to be touched when an index key order is followed.
Number of key targets: The number of key targets for an extended index. Not available for updating.
Extended index type: The type of extended index. Not available for updating

Active Virtual Index

Use the Active Virtual Index panel to disable or drop the virtual indexes.

Figure 4. Active Virtual Index panel

SQLPA520 -------------- Active Virtual Index----------------- 9:18
                                                                                
 Current user TS6224 has enabled virtual indexes.
 It is recommended to disable or delete them before exit 
 such that they do not affect query cost.

 I -Disable   D -Delete   N -No action ==> N (I/D/N)
 ENTER to confirm and exit.

When you close the session in the SQL Performance Analyzer tool, make sure to drop the virtual indexes created as part of the analysis. If a customer enables virtual indexes during the session, it can affect the query cost. Hence the confirmation panel is created in order to notify users to disable or delete active virtual indexes.

Valid values:

I: Disable all virtual indexes.
D: Delete all virtual indexes.
N: Take no actions.