Viewing Status of Index Builds through IBM i Access Client Solutions

How To

Summary

This document explains how to find out the status of an Index build or rebuild.

Steps

This documents explains how to find the status of an Index Build or an Index Rebuild using IBM i Access Client Solutions.

There is function in IBM i Access Client Solution that will provide information of the Index Build or an Index Rebuild .
This support is using a status file that is maintained by the system in the QRECOVERY library.

This status file is named QDBIX_LLLLLLLLLL_FFFFFFFFFF_MMMMMMMMMM where LLLLLLLLLL is the library name, FFFFFFFFFF is the file name, and MMMMMMMMMM is the member name of the member being built/rebuilt.
Note that under system naming conventions, these files are named QDBIXxxxxx where xxxxx is a unique numeric identifier.

This will show all the Index Build or an Index Rebuild that have a status file in QRECOVERY.

The Index Build or an Index Rebuild could be active or complete.
The system may delete the status file after the Index Build or an Index Rebuild is complete or the system can choose to keep the status file.

Any status file that is currently not being used can be deleted.

For a particular Index Build or an Index Rebuild , right click and select Display Status.

This will bring up a status window. This example shows that the index rebuild has completed.

Finally, you can also use SQL to query the Index Build or an Index Rebuild status file.
Each record in the status file consists of a numeric field (STEP) and a character field (DATA).
The meaning of some DATA for each of the STEP values is as follows:

0	Build or rebuild started
2	The job name that is performing the build or rebuild
5	Index built/rebuilt synchronously
10	Prepare step
11	Index key build row
13	Index sort phase 1
14	Index sort phase 2
15	Index sort phase 3
16	Index build binary tree phase 1
17	Index build binary tree phase 2 - *MAX1TB
20	Index process change rows
99	Complete

For example, the following query could be run over a QDBIX status file to determine several useful bits of information:

select * 
    from QRECOVERY.QDBIX_MYLIBRARY_MYFILE_MYMEMBER 
   where STEP IN (0,2,5,10,11,13,14,15,17,99) ;

The following query will help match up the short file name to the long name:

SELECT DBXLIB, DBXFIL, DBXLFI 
    FROM QADBXREF
   WHERE DBXLIB = 'QRECOVERY' 
     AND DBXLFI like 'QDBIX%'

The sample results for this query follow:

QRECOVERY QDBIX56457 QDBIXB_KTRISKO_HUGEFILE_SMALLPART
  QRECOVERY QDBIX11916 QDBIXB_MBAILEY_BIGFILE_CUSTCDT

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i6QAAQ","label":"IBM i Db2->Index \/ Access Path \/ Logical file"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

N1011091

Was this topic helpful?

Document Information

Modified date:
18 May 2020

UID

ibm16210415

Tips