Index Create Option

22
carcudb2
The Create Index option lets you create an index (and implicitly, indexspace). Indexes can be created for existing tables or for tables that have not been created. When the Index Create screen appears, an index name and creator must be specified. The name and creator of the table for which the index is to be created must then be specified. If you have specified a name for your index, and a valid table name and creator are entered on the Index Create screen, the Column selection panel for that table appears. If search criteria are entered, a table selection list appears.
2
2
Once a table is selected, a list of columns is presented for that table. Select the columns and their order in the index. If creating an index for a table that has not been created, columns cannot be selected from a list; it must be entered manually. Once the information is complete, review the DDL and decide whether to process the create, edit the DDL, or cancel the create.
If a table has been created in the current session (batch mode), but the DDL has not been executed, an index can be created based on any of these tables. The table information is retained, enabling tables and index creation in the same batch job. Tables created in batch mode in the current session are listed at the top of the Table selection panel and CREATE appears in the Database column.
The Item Name and Creator fields of the header pertain to the index, not to its base table.
Enter information for tables on which indexes should be defined in the Table Name and Creator fields of the Index Create screen. If a valid table name and creator ID are entered, the column selection panel for that table appears. If valid selection criteria is entered in the Table Name and Creator fields, an Index Table selection panel appears.
An index can be partitioned or non-partitioned. If the index refers to a table stored on a partitioned tablespace, the index must also be partitioned. Non-partitioned indexes can be clustered.
Create a Non-Partitioned DB2 Index
You can create a non-partitioned DB2 index.
Follow these steps:
  1. Access the Index Create screen.
  2. Enter data in the following fields to define some basic information:
    • Index Name (and its corresponding Creator field)
    • Table Name (and its corresponding Creator field)
    Other fields contain default specifications. Set defaults through the DEF command for all fields except Index Name, Table Name, and Partitioned.Press Enter.
  3. The Index Column Selection & Key Maintenance screen appears.
  4. Select the columns that compose the index key.
  5. Enter storage related information about the indexspace in the Partition Information fields.
    Because this is a non-partitioned index, only enter information on the first line. A VCAT or storage group must be entered to override the storage group default (SYSDEFT).
    These fields are optional. Because the indexspace is not partitioned, the only valid line commands are V (VSAM Define), S (Space Calculation), and U (Undo any changes, set back to old definition).
    You can set defaults through the DEF command for all fields except Erase.
    Press F3 (End) to process the creation.
Create a Partitioned DB2 Index
A partitioned index is used whenever the table to which it refers is stored on a partitioned tablespace. A partitioned index must also be a clustering index. You can use this procedure to create a partitioned DB2 index.
Follow these steps:
  1. Access the Index Create screen.
  2. Enter data in the following fields to define some basic information:
    • Index Name (and its corresponding Creator field)
    • Table Name (and its corresponding Creator field)
    Other fields contain default specifications. Set defaults through the DEF command for all fields except Index Name and Table Name.Press Enter.The Index Column Selection & Key Maintenance screen appears.
  3. Select the columns that compose the index key, then press Enter.
  4. Enter storage related information about each partition in the Partition Information fields.
    You must enter a VCAT or storage group if you do not want to accept the storage group default (SYSDEFT). Set defaults through the DEF command for all fields except Part and Erase.Press F3 (End).The Index Partitions Limit Key Values screen appears.
  5. Enter a limit key value for the first partition, then press Enter.
    The information is processed, and the next partition is presented.
  6. Press F3 (End) (after information has been entered for all partitions).
    The creation is processed.
Maintain Comments in an Index
Whenever you create, alter, or template an index, you can also create and maintain comments for the index.
Follow these steps:
Perform one of the following:
  • Create a comment for an index by entering
    Y
    in the Comment field.
    The Index Comment panel appears.
  • View or update an index's existing comment by entering
    U
    in the Comment field.
    The Index Comment panel appears.
  • Delete an index's comment by entering
    N
    in the Comment field.
    Any existing comments are removed.
Comments can be up to 254 characters long. Adding a comment inserts the COMMENT ON INDEX statement into the generated DDL.
Index Create Screen
The Index Create screen permits entry of information about the index and the (implicitly created) indexspace. For information about the fields, press F1 (Help).
If you specify partition information and the indexspace is changed to non-partitioned, all lines except the first are deleted. If the indexspace is returned to a partitioned indexspace, the old partition values are returned.
Manipulating Partition Information
You can use standard ISPF line commands to create a number of partitions quickly (copy, delete, move, and insert). After copying a partition, change the information necessary to complete its definition.
To manipulate the partition information on the Index Create, Index Alter, or Index Template, enter a standard ISPF line command or a line command pertaining to a partition (for partitioned indexspaces) or indexspace (for non-partitioned indexspaces).
The following commands are supported for partitioned and non-partitioned indexspaces except as noted:
  • A
    Places the partition to be copied or moved
    after
    this line. This command applies to partitioned indexspaces only.
  • B
    Places the partition to be copied or moved
    before
    this line. This command applies to partitioned indexspaces only.
  • C, C
    nn
    , or CC
    Copies this partition, number of partitions, or block of partitions to a location marked by A or B. These commands apply to partitioned indexspaces only.
  • D, D
    nn
    , or DD
    Deletes this partition, number of partitions, or block of partitions. These commands apply to partitioned indexspaces only.
  • G
    Allows you to view or enter Group Bufferpool Cache information for this partition. When specified, the Group Bufferpool Cache Setting screen appears.
  • I or I
    nn
    Inserts a partition or number of partitions after this partition. These commands apply to partitioned indexspaces only.
  • L
    Edits the limit key values for this partition. When specified, the Index Partitions Limit Keys Values screen appears, which displays all columns for the partition horizontally across the screen. To enter limit key values for a specific partition, enter LIMITS on the command line, and press Enter.
    Only the partition on which you entered the command is unprotected. All other partitions are automatically protected. To unprotect all other columns on all other partitions, enter the UNPROTECT * command on the primary command line.
  • LC
    Generates and browse IDCAMS LISTCAT for VSAM data set. For more information, see the IBM IDCAMS documentation.
  • M, M
    nn
    , or MM
    Moves this partition, number of partitions, or block of partitions to a location marked by A or B. These commands apply to partitioned indexspaces only.
  • R, R
    nn
    , or RR
    Repeats this partition, number of partitions, or block of partitions after this partition. These commands apply to partitioned indexspaces only.
  • RES
    Resets the partition to its old definition.
  • S
    Performs space calculations for this partition. When specified, the Space Calculation screen appears.
  • SO
    Performs space calculations for this partition. When specified, the Options screen appears, which lets you change the space calculator options before going to the space calculator.
  • SRC
    Sets all values for this partition to its source definition values. On a partitioned index, the limit values for all columns in the partition are also reset. This command is valid for compares only.
  • TRG
    Sets all values for this partition to its target definition values. On a partitioned index, the limit values for all columns in the partition are also reset. This command is valid for compares only.
  • U, U
    nn
    , or UU
    Undoes this partition, number of partitions, or block of partitions and return them back to their old definitions. This command performs the same function as the RES command.
  • V
    Edits the VSAM cluster definition for this partition.
Group Bufferpool Cache Setting for Indexspace Partitions
When you issue the G line command next to an indexspace partition on an index create, alter, or template panel, the Group Bufferpool Cache Setting panel appears.
To specify what pages of the indexspace are written to the group buffer pool in a data-sharing environment, enter one of the following values in the GBP Cache for partition field:
  • A
    Caches both changed and unchanged pages in the group buffer pool.
  • C
    Caches only changed pages in the group buffer pool. This is the default.
  • N
    Does not cache any pages in the group buffer pool. DB2 uses the group buffer pool only for cross-invalidation. If you specify N, the tablespace or partition must not be in a rebuild pending status and must be in the dropped state.
In a non-data sharing environment, you can specify this information, but it is ignored.
Changing Partition Information for an Indexspace
If you specify partition information and the indexspace is changed to non-partitioned, all lines except the first are deleted. If the indexspace is returned to a partitioned indexspace, the old partition values are returned.
Manipulating Partition Information
Index Table Selection Panel
The Index Table selection panel appears when creating a new index, or when selection criteria are entered in the Table Name field on the Index Create, Index Alter or Index Template screen. This screen lets you select the tables on which indexes should be created.
Tables created as part of the strategy appear at the top of the screen. For newly created tables, *CREATE* appears in the Database column. To view referential integrity relationships in the Status field, use the M (Max) scroll command (enter M in the command line and press F20 to scroll all the way right. This field indicates whether the table has a primary index already created.
Select the tables to work with by entering
S
next to the names. If you are creating an index, the Index Column Selection & Key Maintenance screen appears; otherwise the Index Template or Index Alter screen appears.
Index Column Selection and Key Maintenance
You can select, delete, move, and modify key columns and index keys quickly and easily from the Index Column Selection & Key Maintenance screen.
This screen appears when you:
  • Enter
    K
    ,
    KA
    , or
    KB
    line commands next to a key column on the Index Create or Index Alter screen. The A (after) or B (before) command is inserted automatically for you on this screen on the corresponding line from the Index Create or Index Alter screen. Select the columns that you want to insert after this key column from the Column Name Selection List.
  • Enter the
    KEY
    primary command on the Index Create or Index Alter screen.
  • Alter, Create, or template on the Index Create or Index Alter screens, and after you press Enter it is determined that key columns are defined for the index.
By default, the Column Name Selection list only displays the columns that are eligible to be selected as key-columns for your index. When you select a column, it is removed from this list and inserted into the Index Key list at the bottom of the screen.
To display ineligible columns (although not selectable) as well as columns that are already part of the key, enter the
L
primary command to toggle the Column Selection List display mode.
Select Columns
You can select a column to work with on the Index Column Selection and Key Maintenance screen.
Follow these steps:
  1. Enter one of the following in the
    CMD
    column under the Column Name Selection list:
    • S
      nn
      Selects
      nn
      column names (including this one) and moves them to the location marked by A or B in the Index Key list. If
      nn
      is omitted, 1 is assumed.
    • SS
      Indicates to use block select. Use paired SS commands to delimit a block of column names to move to the location marked by A or B in the Index Key list.
      When using these commands, only eligible columns will be included in the selection. Columns that are displayed but are not selectable (marked by three dashes) are not included in the selection or the count specified by S
      nn
      .
  2. Enter
    A
    (after) or
    B
    (before) in the CMD column under the Index Key list next to the Index Key where you want to place the column. All entries are put in the index in ascending order by default.
    If S and numbers are used in the same column selection, the columns selected with S will come first in the index key, in the order of their column number. The numbered keys will follow the S keys, in the order of the entered numbers.
    To insert blank key columns, specify
    I
    (insert) in the CMD column under the Index Key list. For other valid line commands, see the online help.Press Enter.
Processing Considerations for Index Creation
The CREATE INDEX statement cannot be executed while a DB2 utility has control of the tablespace that contains the identified table. If the named table contains data, CREATE INDEX creates the index entries for it. If the table does not yet contain data, CREATE INDEX creates a description of the index; the index entries are created when the table is loaded.
Confirm the Index Creation
When all information has been entered, you can confirm the index creation.
Follow these steps:
  1. Press F3 (End) to process the creation.
    A confirmation screen appears.
  2. Accept, edit, or reject the DDL that will be used to create the index.