INDEX—Define Indexes to Process

The INDEX keyword specifies which indexes to process.
cafrdb220
The INDEX keyword specifies which indexes to process:
  • For a RECOVER statement, INDEX specifies the indexes to recover. You can recover one index or all indexes that are associated with the specified tablespace. RECOVER INDEX can be used only on indexes that are created with the COPY YES attribute.
  • For a REBUILD statement, INDEX specifies the indexes to rebuild from the tablespace they reference. You can rebuild one index, a list of indexes, or all indexes that are associated with a tablespace. Use REBUILD INDEX for indexes that are not created with the COPY YES attribute.
RECOVER INDEX requires that you specify SORTLOG YES.
You can specify multiple RECOVER INDEX or REBUILD INDEX statements in your job.
The following sample JCL is provided for your use:
  • To recover an index only, see member
    hlq
    .CDBAJCL(RECOVER2).
  • To recover a tablespace and its associated indexes, see member
    hlq
    .CDBAJCL(RECOVER1).
  • To recover multiple tablespaces and their associated indexes, see member
    hlq
    .CDBAJCL(RECOVER4).
The RECOVER INDEX and REBUILD INDEX control statements have the following syntax:
RECOVER {INDEX {(ALL) TABLESPACE {[
dbname
.]
tsname
}} | {([
creator.
]
ixname
) [TABLESPACE {[
dbname
.]
tsname
}] [DSNUM
int
]} [
Index Options
]} REBUILD {INDEX {(ALL) DATABASE {
dbname
}}
| {(ALL) TABLESPACE {[
dbname
.]
tsname
} [DSNUM
int
]} | {([
creator
.]
ixname
[DSNUM
int
]) [TABLESPACE {[
dbname
.]
tsname
}]} | {([
creator
.]
ixname
) [TABLESPACE {[
dbname
.]
tsname
}] [DSNUM
int
]} | {([
creator
.]
ixname1
[DSNUM
int
],...,
ixnameN
[DSNUM
int
]) [TABLESPACE {[
dbname
.]
tsname
}]} | {([
creator
.]
ixname1
,...,
ixnameN
) [TABLESPACE {[
dbname
.]
tsname
}] [DSNUM
int
]}} | {INDEX LIST {
listdef-name
}} {INDEX OBJ-LIST [
creator-id
.]
obj-list-name
} [
Index Options
]
Enclose the index specifications in parentheses. If you list more than one index name for REBUILD INDEX, separate the names with commas. To apply the DSNUM keyword to all indexes in parentheses for REBUILD INDEX, specify this keyword outside of the parentheses. If you specify at least one index in the parentheses with DSNUM, do not specify this keyword at the end of the statement.
  • (ALL)
    Processes all indexes that are associated with the specified tablespace. This option facilitates planning and executing a disaster recover scenario by removing the need to identify all the objects in the database.
    You can use INDEX (ALL) with the following object keywords:
    • DATABASE for REBUILD
    • TABLESPACE for REBUILD and RECOVER
    REBUILD INDEX (ALL) DATABASE cannot be used to process Db2 catalogs or the Db2 directory. Use REBUILD INDEX (ALL) TABLESPACE instead.
  • creator
    (Optional) Specifies the user ID of the index creator. If you do not specify the creator, it defaults to the user ID for the job.
  • ixname
    Specifies the index name. For REBUILD, you can specify multiple indexes by separating them with commas. Enclose the index or series of indexes in parentheses. All indexes that are included in one REBUILD statement must belong to the same tablespace.
  • DSNUM
    int
    | PART
    int
    Specifies the index partition to process.
Example: Use Wildcards to Define Multiple Indexes
The following example uses wildcards to specify indexes in RECOVER or REBUILD statements:
INDEX (ALL) TABLESPACE [DB01.]TS% INDEX ([USER%.]I%01) TABLESPACE [DB01.]TS01 INDEX ([USER%.]I%01,I%02,I%03) TABLESPACE [DB01.]TS01
Example: Recover an Index Only
The following example recovers a three-partition index and allocates the sort work data sets dynamically:
//SYSIN DD * RECOVER INDEX(
owner
.
ixname
) TABLESPACE
dbname.tsname
SORTLOG YES SORTDEVT SYSDA SORTSIZE 4M SPACE-DEFN YES
Example: Recover a Tablespace and Associated Indexes
The following example recovers a nonpartitioned tablespace (NEWTS) with its associated clustering and nonclustering index:
//SYSIN DD * RECOVER TABLESPACE DB01.NEWTS SORTLOG YES ALLMSGS STOP-LIMIT 3 RECOVER INDEX (ALL) TABLESPACE DB01.NEWTS
Example: Recover Multiple Tablespaces and Associated Indexes
The following example recovers tablespaces NEWTS1 and NEWTS2 and their associated indexes concurrently:
//SYSIN DD * RECOVER TABLESPACE
dbname
.NEWTS1 TABLESPACE
dbname
.NEWTS2 SORTLOG YES MAXTASKS 2 STOP-LIMIT 3 ALLMSGS SPACE-DEFN YES STARTUP-ACCESS RW REBUILD INDEX (ALL) TABLESPACE
dbname
.NEWTS1 SORTDEVT SYSDA SORTNUM 3 MAXTASKS 2 ESTIMATED-KEYS 100000 STOP-LIMIT 3 STARTUP-ACCESS RW REBUILD INDEX (ALL) TABLESPACE
dbname
.NEWTS2 SORTDEVT SYSDA SORTNUM 3 MAXTASKS 2 ESTIMATED-KEYS 500000 STOP-LIMIT 3 STARTUP-ACCESS RW