Manage index rebuilds

Manage index rebuilds

You can manage the rebuild of your indexes using iSeries™ Navigator. You can view a list of access paths that are rebuilding and either hold the access path rebuild or change the priority of a rebuild.
To display Access paths to rebuild, follow these steps:

In the iSeries Navigator window, expand the system that you want to use.

Expand Databases.

Right-click the database that you want to work with and select Manage index rebuilds.

The Access paths to rebuild dialog includes the following columns:
Table 1. Columns used in Manage index rebuilds window
Column name Description

Name of Index to Rebuild Long name of access path being rebuilt
Schema Schema name where the index is located
Type The type of index displayed.
Possible values are:
Keyed Physical File

Keyed Logical File

Primary Key

Unique Key

Foreign Key

Index

Status Displays the status of the rebuild.
Possible values are:
1-99 – Rebuild Priority

Running – Rebuilding

Held – Held from be rebuilt

Rebuild Priority Displays the priority in which the rebuild for this access path is run. Also referred to as sequence number.
Possible values are:
1-99: Order to rebuild

Held

Open

Rebuild Reason Displays the reason why this access path needs to be rebuilt.
Possible values are:
Create or build index

IPL

Runtime error

Change file or index sharing

Other

Not needed

Change End of Data

Restore

Alter table

Change table

Change file

Reorganize

Enable a constraint

Alter table recovery

Change file recovery

Index shared

Runtime error

Verify constraint

Convert member

Restore recovery

Rebuild Reason Subtype Displays the subtype reason why this access path needs to be rebuilt.
Possible values are:
Unexpected error

Index in use during failure

Unexpected error during update, delete, or insert

Delayed maintenance overflow or catch-up error

Other

No event

Change End of Data

Delayed maintenance mismatch

Logical page size mismatch

Partial index restore

Index conversion

Index not saved and restored

Partitioning mismatch

Partitioning change

Index or key attributes change

Original index invalid

Index attributes change

Force rebuild of index

Index not restored

Asynchronous rebuilds requested

Job ended abnormally

Alter table

Change constraint

Index invalid or attributes change

Invalid unique index found

Invalid constraint index found

Index conversion required
Note that if there is no subtype, this field will display a 0.
Invalidation Reason Displays the reason why this access path was invalidated.
Possible values are:
User requested (See Invalidation Reason type for more information)

Build Index

Load (See Invalidation Reason type for more information)

Initial Program Load (IPL)

Runtime error

Modify

Journal failed to build the index

Marked index as mendable during runtime

Marked index as mendable during IPL

Change end of data

Invalidation Reason Type Displays the reason type for why this access path was invalidation.
Possible reason types for User requested:
Invalid because of REORG

It is a copy

Alter file

Converting new member

Change to *FRCRBDAP

Change to *UNIQUE

Change to *REBLD

Possible reason types for LOAD
The index was marked for invalidation but the system crashed before the invalidation could actually occur

The index was associated with the overlaid data space header during a load, therefore it was invalidated

Index was in IMPI format. The header was converted and now it is invalidated to be rebuilt in RISC format

The RISC index was converted to V5R1 format

Index invalidated due to partial load

Index invalidated due to a delayed maintenance mismatch

Index invalidated due to a pad key mismatch

Index invalidated due to a significant fields bitmap fix

Index invalidated due to a logical page size mismatch

Index was not restored. File may have been saved with ACCPTH(*NO) or index did not exist when file was saved.

Index was not restored. File may have been saved with ACCPTH(*NO) or index did not exist when file was saved.

Index was rebuilt because file was saved in an inconsistent state with SAVACT(*SYSDFN).
Note that for other invalidation codes, this field will display a 0.
Estimated Rebuild Time Amount of time that it is estimated that the rebuild of the access path will take.
Rebuild Start Time Time when the rebuild was started.
Elapsed Rebuild Time Amount of time that has elapsed since the start of the rebuild of the access path
Unique Indicates whether the rows in the access path are unique. Possible values are:
Yes

No

Last Query Use Timestamp when the access path was last used
Last Query Statistics Use Timestamp when the access path was last used for statistics
Query Use Count Number of times the access path has been used for a query
Query Statistics Use Count Number of times the access path has been used for statistics
Partition Partition detail for the index.
Possible values:

<blank>, which means For all partitions

For Each Partition

specific name of the partition

Owner User ID of the owner of this access path.
Parallel Degree Number of processors to be used to rebuild the index.
Short Name The system name of the file that owns the index to be rebuilt.
Text Text description of the file owning the index.

You can also use the Edit Rebuild of Access Paths (EDTRBDAP) command to manage rebuilding of access paths.

Parent topic:
Indexes and the optimizer

Related information

Rebuild access paths
Edit Rebuild of Access Paths (EDTRBDAP) command

Column name	Description
Name of Index to Rebuild	Long name of access path being rebuilt
Schema	Schema name where the index is located
Type	The type of index displayed. Possible values are: Keyed Physical File Keyed Logical File Primary Key Unique Key Foreign Key Index
Status	Displays the status of the rebuild. Possible values are: 1-99 – Rebuild Priority Running – Rebuilding Held – Held from be rebuilt
Rebuild Priority	Displays the priority in which the rebuild for this access path is run. Also referred to as sequence number. Possible values are: 1-99: Order to rebuild Held Open
Rebuild Reason	Displays the reason why this access path needs to be rebuilt. Possible values are: Create or build index IPL Runtime error Change file or index sharing Other Not needed Change End of Data Restore Alter table Change table Change file Reorganize Enable a constraint Alter table recovery Change file recovery Index shared Runtime error Verify constraint Convert member Restore recovery
Rebuild Reason Subtype	Displays the subtype reason why this access path needs to be rebuilt. Possible values are: Unexpected error Index in use during failure Unexpected error during update, delete, or insert Delayed maintenance overflow or catch-up error Other No event Change End of Data Delayed maintenance mismatch Logical page size mismatch Partial index restore Index conversion Index not saved and restored Partitioning mismatch Partitioning change Index or key attributes change Original index invalid Index attributes change Force rebuild of index Index not restored Asynchronous rebuilds requested Job ended abnormally Alter table Change constraint Index invalid or attributes change Invalid unique index found Invalid constraint index found Index conversion required Note that if there is no subtype, this field will display a 0.
Invalidation Reason	Displays the reason why this access path was invalidated. Possible values are: User requested (See Invalidation Reason type for more information) Build Index Load (See Invalidation Reason type for more information) Initial Program Load (IPL) Runtime error Modify Journal failed to build the index Marked index as mendable during runtime Marked index as mendable during IPL Change end of data
Invalidation Reason Type	Displays the reason type for why this access path was invalidation. Possible reason types for User requested: Invalid because of REORG It is a copy Alter file Converting new member Change to FRCRBDAP Change to UNIQUE Change to REBLD Possible reason types for LOAD The index was marked for invalidation but the system crashed before the invalidation could actually occur The index was associated with the overlaid data space header during a load, therefore it was invalidated Index was in IMPI format. The header was converted and now it is invalidated to be rebuilt in RISC format The RISC index was converted to V5R1 format Index invalidated due to partial load Index invalidated due to a delayed maintenance mismatch Index invalidated due to a pad key mismatch Index invalidated due to a significant fields bitmap fix Index invalidated due to a logical page size mismatch Index was not restored. File may have been saved with ACCPTH(NO) or index did not exist when file was saved. Index was not restored. File may have been saved with ACCPTH(NO) or index did not exist when file was saved. Index was rebuilt because file was saved in an inconsistent state with SAVACT(SYSDFN). Note that for other invalidation codes, this field will display a 0.
Estimated Rebuild Time	Amount of time that it is estimated that the rebuild of the access path will take.
Rebuild Start Time	Time when the rebuild was started.
Elapsed Rebuild Time	Amount of time that has elapsed since the start of the rebuild of the access path
Unique	Indicates whether the rows in the access path are unique. Possible values are: Yes No
Last Query Use	Timestamp when the access path was last used
Last Query Statistics Use	Timestamp when the access path was last used for statistics
Query Use Count	Number of times the access path has been used for a query
Query Statistics Use Count	Number of times the access path has been used for statistics
Partition	Partition detail for the index. Possible values: <blank>, which means For all partitions For Each Partition specific name of the partition
Owner	User ID of the owner of this access path.
Parallel Degree	Number of processors to be used to rebuild the index.
Short Name	The system name of the file that owns the index to be rebuilt.
Text	Text description of the file owning the index.