You can create, template, alter, and drop the following types of supported indexes:
You can create, template, alter, and drop the following types of supported indexes:
  • Regular (simple indexes, including DPSI and partitioning indexes)
  • Expression (index on scalar expression)
  • Auxiliary (an index on an auxiliary table)
An index is primarily used to provide more efficient access to a Db2 table. You can use a unique index to ensure unique values and implement referential rules.
The index option includes many useful features to help you facilitate the creation and overall management of your Db2 index objects. For example:
For more information about these features, see the online help.
  • Db2 Object Selection Help
    When defining an index, the object names of the index's table and database must be specified. Help is provided for entering Db2 object names. Instead of entering a specific object name, selection criteria can be entered. Fields that support such selection criteria include the Table Name for your index, STOGROUP (storage group), and VCAT.
  • Auxiliary Indexes
    You can create, alter, and template indexes on LOB columns for auxiliary tables.
    Toggles the type of index between one that is defined on scalar expressions and one taht is defined on simple table columns, while automatically setting certain screen options as appropriate for the index.
  • Table Column Name Selection
    You can insert the @ sign symbol to indicate where you want column names substituted inside expressions when PARSE is ON (auto-parsing) or for on-demand parsing with the P line command. A selection list displays automatically when the symbol is detected.
    You can create indexes on application temporal and bi-temporal tables with the BUSINESS_TIME WITHOUT OVERLAPS clause included. When the table for the index is a temporal table that is defined with a BUSINESS_TIME period, set the BTWOO option to YES to automatically insert the BUSINESS_TIME WITHOUT OVERLAPS clause as the last item in the index key.
  • Limit Key Help
    When creating a partitioned index, the limit must be specified for at least the first column in the key for each partition. This lets you specify the limit key value on a separate screen.
  • Help for conversion from index-controlled partitioning to table-controlled partitioning
    You may have DB2 convert a table object that is using index-controlled partitioning to use table-controlled partitioning. To do so, you can alter the table object's clustering index to be NOT CLUSTER or create a partitioned index or data-partitioned secondary index (DPSI) using ALTER or CREATE statements.
  • Storage Group and VCAT Help
    Help is also provided for entering VCAT and storage group names. Instead of entering a specific name, selection criteria can be entered to receive a list of objects meeting the criteria.
  • Space Locator
    The Space Locator facility helps locate a volume or STOGROUP with adequate free space for indexspaces.
  • VSAM Definition
    To specify VSAM data sets rather than use a storage group, the data set name and type must be specified and any required passwords supplied. A VSAM Define screen is provided to assist in defining VSAM data sets.
  • Header Option
    Use the HEADER command to provide a larger work area for managing your index expressions.
  • Column Selection Help
    Specify the columns for the index by selecting them from a list. All columns from the selected table are displayed. Specify the columns and their order within the index on a single screen.
  • Limit Key Values for ICP
    A facility is provided for entering and maintaining limit key values for index-controlled partitioning (ICP).
  • Limit Key Values for TCP
    You can create a non-clustering partitioning index with limit key values so that Db2 will implicitly convert the table object to use table-controlled partitioning using the key and limit values you choose.
  • Implicit Conversion from ICP to TCP
    You can have Db2 implicitly convert a table object that is using index-controlled partitioning (ICP) to use table-controlled partitioning (TCP). Simply alter the clustering index for the table object to be NOT CLUSTER, or create a partitioning index or data-partitioning secondary index (DPSI) that will generate only ALTER or CREATE statements.
  • Space Calculation Assistance
    A sophisticated space calculation facility is available to assist in calculating space allocations for the indexspace. If the indexspace is partitioned, space calculations can be made on each partition. Once complete, the space information is passed back to the indexspace screen.
  • Alteration Support
    Db2 permits only certain changes to indexspaces. If the requested changes are not supported by Db2, RC/Alter is invoked to drop and re-create the indexspace. All dependents, data, and authorizations are automatically restored and all changes are automatically propagated to any dependent object types. This extremely powerful feature is transparent to the end user. The user makes the request for the change, and the change is made using the appropriate methods.
  • Auxiliary Index Support
    You can create, template, and alter indexes on auxiliary tables for LOB columns by entering the name of an auxiliary table for the index.
  • XML Index Support
    You can create, alter, and template XML indexes on a column of XML data in a DB2 table to improve performance during queries on XML documents. Use the XML command on the index panels to toggle between the XML index type and a simple index. You can also change an index to XML by entering 2V for Index Type. Note that Partitioned must first be set to NO. An XML index cannot be partitioned. Detailed information about XML indexes is provided in the online help.