Creating indexes on tables
Use the Create Index (ADB26CX) panel to create indexes on tables.
About this task
In Db2® Admin Tool, you can create an index on a table by using one of the following methods:
- Select option CX on the Create/Drop/Label/Comment On Objects (ADB26) panel. The following procedure explains how to use this method.
- Use the CREX line command on the Tables, Views, and Aliases (ADB21T) panel.
- Use the CRE line command on the Indexes (ADB21X) panel.
- Select option CX on the EXPLAIN (ADB2E) panel.
Each of these methods display the create index panels, beginning with the Create Index (ADB26CX) panel.
Procedure
-
On the DB2 Administration Menu (ADB2) panel, specify option
2, and press Enter.
The Execute SQL Statements (ADB22) panel is displayed, as shown in the following figure:
Figure 1. Execute SQL Statements (ADB22) panel DB2 Admin -------------------- Execute SQL Statements ------------------- 01:36 Option ===> 1 - Edit/run SQL statements DB2 System: DD1A 2 - Run or Explain SQL statements DB2 SQL ID: ADM001 3 - Build SQL SELECT, INSERT, UPDATE or DELETE prototype 4 - Create/drop/label/comment on objects 5 - Grant/revoke privileges on objects - Specify option 4, and press Enter.
-
Specify option CX, and press Enter.
The Create Index (ADB26CX) panel is displayed, as shown in the following figure.
Figure 2. Create Index (ADB26CX) panel ADB26CX n ---------------------- DD1A Create Index ---------------------- 16:17 Command ===> CREATE INDEX Schema . . . . > (default is RIVERAF) Name . . . . . IXFGRNEW > (? to look up) ON Table Schema . > (default is RIVERAF) Table name . . TBFGR > (? to look up) Partitions . . 0 (0 for nonpartitioned INDEX) Like: Index Schema . > (required for Like usage) Index name . . > (? to look up) -
Specify values for the required parameters and for any of the other parameters, and press
Enter.
- Required parameters
-
- Owner
- The name of the index owner for the new index or use the default owner.
- Name
- An index name for the new index, or enter a question mark (?) to look up existing index names on the Indexes (ADB21X) panel.
- Table name
- A table name or enter a question mark (?) to look up existing table names on the Tables, Views, and Aliases (ADB21T) panel.
- Optional parameters
-
- Table owner
- The name of the owner for a table name on which the index is based.
- Partitions
- The number of partitions for a partitioned index.
- Like: Index owner
- The name of an owner on which to model the new owner for the new index.
- Like: Index name
- The name of an index on which to model the new index or enter a question mark (?) to look up existing index names on the Indexes (ADB21X) panel.
When you press Enter, the next Create Index (ADB21XAR) panel is displayed, as shown in the following figure.Figure 3. Create Index (ADB21XAR) panel ADB21XAR --------------- DD1A Create Index ------------------ Row 1 to 2 of 2 Command ===> Scroll ===> CSR Commands: NEXT ORIGINAL EXPRESSION Line commands: nnn A|D - Sequence & order R - Remove the column I - Include A - Ascending D - Descending RA - Random U - Update expression/XML pattern ? - Show all line commands CREATE INDEX RIVERAF . IXFGRNEW > ON RIVERAF.TBFGR Unique . . . . . . . Where Not Null . . . Cluster . . . . . Buffer Pool . . . . Close Rule . . . . . Copy Allowed . . Piece Size . . . . . Define . . . . . . . Defer . . . . . . Partitioned . . . . Padded . . . . . . . Compress . . . . Exclude Null Keys . Select Column Name Col Type Length Scale N ColSeq Ord * * * * * * * ------ ------------------ -------- ----------- ------ - ------ --- AXX INTEGER 4 0 N 1 A BXX CHAR 3 0 Y ******************************* END OF DB2 DATA ******************************* -
On the upper portion of the Create Index (ADB21XAR) panel,
specify the index attributes.
- Specify whether the columns should be in ascending or descending order.
-
Specify the general index attributes in the fields.
Remember: Depending upon the version of Db2 that you are using and your choice of parameters, some attribute fields might be unavailable.Tip: Without negatively impacting query performance, you can improve the insert performance of NULL entries, by excluding NULL rows from an index. Type Yes in the Exclude Null Keys field to exclude NULL rows from a new index. The default is to include NULL keys in a new index.
- On the scrollable table of the panel, use line commands to specify the columns in the index. All columns of the Db2 table are displayed. Index columns are identified in the Colseq and Order columns.
- Issue the NEXT primary command to display the Create Index - Space (ADB21XAS) panel.
- Specify the space allocation and storage parameters for the index or for each partition of the index.If a partitioned index with more than one partition is being created, the word
Default:appears at the beginning of the scrollable portion of the panel. Use this line to enter common values for each partition and to avoid entering the same value for a parameter on all partitions again. - Issue the NEXT primary command to generate the DDL for the index and display an edit session.
- Edit the CREATE statement or exit the session to create the index.
- Follow the instructions on the Statement Execution Prompt (ADB2PSTM) panel (if enabled) to complete and run the SQL statement for creating the new index.