Altering indexes
You can rename an index, change index attributes, and change the columns that are indexed. Depending on the change that you want to make, use either the AL or ALT line command.
About this task
Use the AL line command to make changes that are supported by the ALTER INDEX statement. Also, use AL if you want to regenerate an expression-based index for changes other than APPLCOMPAT. (If you are changing the APPLCOMPAT value for expression-based indexes, you can use AL or ALT.)
Use the ALT line command to alter an index when the changes are more complex and intrusive and cannot be accomplished with an ALTER INDEX statement. An intrusive alter is one in which the objects have to be dropped and re-created, such as inserting a column in the middle of a table, dropping a column, renaming a column, or changing the attributes of a column. When you specify your index redefinition parameters, you can choose to save your request to a work statement list.
Altering an index by using the AL line command
Procedure
- On the DB2 Administration Menu (ADB2) panel, select option 1, and press Enter.
- On the System Catalog (ADB21) panel, select option X, and press Enter.
-
On the Indexes (ADB21X) panel, enter the
AL line command against the index that you want
to alter.
Figure 1. Indexes (ADB21X) panel ADB21X in --------------------- DD1A Indexes ----------------- Row 1 to 1 of 1 Command ===> Scroll ===> CSR Commands: DIS STA STO ALL XSPACE Line commands: T - Tables D - Database G - Storage group P - Plans C - Columns DIS - Display index space STA - Start index space STO - Stop index space ? - Show all line commands Index Table C C C C Select Index Name Schema Table Name Schema U Cols G D L M * * * * * * * * * * ------ ------------------ -------- ------------------ -------- - ------ - - - - AL IXFGR RIVERAF TBFGR RIVERAF U 1 N N Y N ******************************* END OF DB2 DATA ******************************* -
On the Alter Index (ADB21XA) panel, make
changes to any index attributes, and press Enter.
Figure 2. Alter Index (ADB21XA) panel ADB21XA n ------------------ DD1A Alter Index ---------------- Row 1 to 1 of 1 Command ===> Scroll ===> CSR Commands: ADDCOL ALTER INDEX RIVERAF.IXFGR (Nonpartitioned ) Buffer Pool . . . . BP1 Close Rule . . . . . YES Copy Allowed . . NO Piece Size . . . . . 2097152 Cluster . . . . . . NO Padded . . . . . Compress . . . . . . NO Regenerate . . . . ApplCompat . V13R1M503 Sel Part Pqty Sqty FreePg %Free Erase ST VCAT Stogroup GBPCache --- ------ ----------- ------- ------ ----- ----- -- -------- -------> -------- 0 -1 -1 0 10 NO I DD1A SYSDEFLT CHANGED ******************************* END OF DB2 DATA *******************************
Note: The Regenerate and ApplCompat fields are displayed only for expression-based indexes.For a partitioning index, a detail line is displayed for each partition. You can alter any partition by updating the available attribute, such as
%Free. To apply the same change to all partitions of the index, provide a value in the All Parts row.After you press Enter, Db2® Admin Tool runs the SQL ALTER INDEX statement. Depending on your prompt options, you might need to first confirm that you want to execute the ALTER statement.
To change certain parameters, you must stop and restart the associated object. In these cases, Db2 Admin Tool issues a STOP table space or STOP index (or partition) command and checks that the object is in a fully-stopped state. If stopped, it issues an ALTER INDEX statement, followed by a START command. If the object is not in a fully-stopped state, the STOP Check - Action panel panel prompts you to perform one of the following actions:- Check again
- Issue the ALTER
- Cancel the operation
Altering an index by using the ALT line command
About this task
Procedure
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, specify option X and optionally any filtering criteria at the bottom of the panel, and press Enter.
-
On the Indexes (ADB21X) panel, issue the
ALT line command next to the index that you want to redefine, and press
Enter:
Figure 3. Indexes (ADB21X) panel ADB21X in --------------------- DD1A Indexes --------------- Row 1 to 25 of 25 Command ===> Scroll ===> CSR Commands: DIS STA STO ALL XSPACE Line commands: T - Tables D - Database G - Storage group P - Plans C - Columns DIS - Display index space STA - Start index space STO - Stop index space ? - Show all line commands Index Table C C C C Select Index Name Schema Table Name Schema U Cols G D L M * * * * * * * * * * ------ ------------------ -------- ------------------ -------- - ------ - - - - IXFGRB RIVERAF TBFGRB RIVERAF U 3 N N Y N IXFGR RIVERAF TBFGR RIVERAF U 1 N N Y N IXFGR_PBR RIVERAF TBFGR_PBR RIVERAF U 1 N N Y N ALT IXFGR2 RIVERAF TBFGR2 RIVERAF U 1 N N Y N IXFGR2_PBR RIVERAF TBFGR2_PBR RIVERAF U 1 N N Y N IXFGRA RIVERAF TBFGRA RIVERAF U 1 N N Y N IXFGRI RIVERAF TBFGRI RIVERAF U 1 N N Y N IXFGRID RIVERAF TBFGRID RIVERAF U 1 N N Y N IXFGRID2 RIVERAF TBFGRID2 RIVERAF P 1 N N Y N IXFGRIX1 RIVERAF TBFGRIX1 RIVERAF D 2 Y Y Y N IXFGRMAS RIVERAF TBFGRMAS RIVERAF P 1 N N N N IXFGRMAT RIVERAF TBFGRMAT RIVERAF P 1 N N N N IXFGRMQ1 RIVERAF TBFGRMQ1 RIVERAF D 1 N N Y N IXFGRMQ2S1 RIVERAF TBFGRMQ2S1 RIVERAF D 1 N N Y N IXFGRMQ2S2 RIVERAF TBFGRMQ2S2 RIVERAF D 1 N N Y N IXFGRMQ2U RIVERAF TBFGRMQ2U RIVERAF D 1 N N Y N IXFGRTB2 RIVERAF TBFGRTB2 RIVERAF P 1 Y Y N N IXFGRTB4 RIVERAF TBFGRTB4 RIVERAF P 1 Y Y N N IXFGRXM5 RIVERAF TBFGRXM5 RIVERAF D 1 N N Y N IXFGRXM6 RIVERAF TBFGRXM6 RIVERAF D 1 N N Y N IXFGRXMP RIVERAF TBFGRXMP RIVERAF D 1 N N Y N IXFGRV RIVERAF TBFGRV RIVERAF U 1 N N Y N IXFGRV_PBR RIVERAF TBFGRV_PBR RIVERAF U 1 N N Y N IXFGRC RIVERAF TBFGRC RIVERAF P 1 N N Y N IXFGRG RIVERAF TBFGRG RIVERAF P 1 N N Y N ******************************* END OF DB2 DATA ******************************* -
On the Redefine Index (ADB21XAR) panel, alter
any index attributes and press Enter:
Figure 4. Redefine Index (ADB21XAR) panel ADB21XAR --------------- DD1A Redefine Index ---------------- Row 1 to 2 of 2 Command ===> Scroll ===> CSR Commands: NEXT ORIGINAL 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 . IXFGR2 > ON RIVERAF.TBFGR2 Owner . . . . . . RIVERAG > Owner type . . . . . _ (U/R) Unique . . . . . . . YES Where Not Null . . . Cluster . . . . . NO Buffer Pool . . . . BP1 Close Rule . . . . . YES Copy Allowed . . NO Piece Size . . . . . 2097152 Define . . . . . . . YES Defer . . . . . . Partitioned . . . . Padded . . . . . . . Compress . . . . NO Exclude Null Keys . NO Select Column Name Col Type Length Scale N ColSeq Ord OldSeq Ord * * * * * * * * * ------ ------------------ -------- ----------- ------ - ------ --- ------ --- A INTEGER 4 0 N 1 A 1 A B CHAR 3 0 Y ******************************* END OF DB2 DATA ******************************* - Issue the primary command NEXT, and press Enter.
-
On the Redefine Index - Space (ADB21XAS ) panel, issue
NEXT again, and press Enter:
Figure 5. Redefine Index - Space (ADB21XAS ) panel ADB21XAS --------------- DD1A Redefine Index - Space -------- Row 1 to 1 of 1 Command ===> NEXT Scroll ===> CSR Commands: NEXT ORIGINAL Line commands: O - Original data C - Clear data ? - Show all line commands CREATE INDEX RIVERAF.IXFGRREN ON RIVERAF.TBFGR S Part Pqty Sqty FreePg PF Erase ST VCAT Stogroup GBPCache DSSIZE * * * * * * * * * * * - ------ ------ ------ ------ -- ----- -- -------- -------> -------- ------ 0 -1 -1 0 10 NO I DD1A SYSDEFLT CHANGED ******************************* END OF DB2 DATA *******************************
- Generate an ALT job.