Find orphaned and missing indexes

You can find orphaned and missing indexes using an SQL User Defined Table Function (UDTF) named QDBTS_LISTINXSTS.

An index can be orphaned if a SYSTS_DROP stored procedure is called and the server is stopped at the time the procedure is running.

The QDBTS_LISTINXSTS function combines all the integrated-file-system collections and catalog indexes in the current namespace into one table. The function decides which independent auxiliary storage pool (ASP) or *SYSBASE is set. It then scans the collection directory of each server in the independent ASP or *SYSBASE.

For *SYSBASE, each server directory under /QOpenSys/QIBM/ProdData/TextSearch is checked. For independent ASPs, each server directory under /the ASP number/QOpenSys/QIBM/ProdData/TextSearch is checked. For example, if the independent ASP number is 67, each server directory under /67/QOpenSys/QIBM/ProdData/TextSearch is checked.

For catalog index information, data is obtained from catalog table QSYS2.SYSTEXTINDEXES. If you want to check servers on an independent ASP, issue the Set Auxiliary Storage Pool Group (SETASPGRP) command before this function is called.

If you want to remove possible orphaned indexes from the integrated file system after they are identified, use the SYSPROC.SYSTS_REMOVE or SYSPROC. SYSTS_CLEAR_INDEXES stored procedure or the Advanced administration (adminTool.sh).

Terms

Orphaned index: A collection (an index) exists in the integrated file system directory of the server, but no corresponding index is recorded in catalog QSYS2.SYSTEXTINDEXES.
Missing index: Index records exist in catalog QSYS2.SYSTEXTINDEXES, but the corresponding collection directory does not exist.

Syntax

>>-QDBTS_LISTINXSTS(--null--)---><

Return format

The QDBTS_LISTINXSTS function returns information of detected indexes in a table. See the following SQL command that is used to create the UDTF.

SQL for LISTINXSTS UDTF

CREATE FUNCTION QDBTSLIB.QDBTS_LISTINXSTS()
        RETURNS TABLE(COLLECTIONNAME VARCHAR(255),
                   INDEXID INTEGER,
                   INDEXSCHEMA VARCHAR(128),
                   INDEXNAME VARCHAR(128),
                   SERVERID INTEGER)
   SPECIFIC qdbts_listinxsts
   SCRATCHPAD                                  
   NO FINAL CALL                               
   LANGUAGE C++                                
   PARAMETER STYLE DB2SQL                      
   EXTERNAL NAME 'QDBTSLIB/QDBTSSP(checkIndex)';

Examples

Detect all orphaned indexes:

SELECT COLLECTIONNAME, SERVERID 
       FROM TABLE(QDBTSLIB.QDBTS_LISTINXSTS()) AS T 
       WHERE T.INDEXSCHEMA IS NULL AND T.INDEXNAME IS NULL

Detect all missing indexes:

SELECT INDEXSCHEMA, INDEXNAME 
       FROM TABLE(QDBTSLIB. QDBTS_LISTINXSTS()) AS T  
       WHERE T.COLLECTIONNAME is NULL

Detect orphaned indexes in serverid = 2 on the independent ASP iaspXXX:

CONNECT TO iaspXXX

SQL:

SELECT T.COLLECTIONNAME, S.SERVERPATH 
       FROM TABLE(QDBTSLIB.QDBTS_LISTINXSTS()) 
            AS T LEFT OUTER JOIN QSYS2.SYSTEXTSERVERS S ON (T.SERVERID = S.SERVERID)
       WHERE T.INDEXSCHMEA IS NULL AND T.INDEXNAME IS NULL AND T.SERVERID = 2