SYSTS_CLEAR_INDEXES

You can remove orphaned indexes with the SYSPROC. SYSTS_CLEAR_INDEXES SQL stored procedure. Another implicit way is invoking SYSTS_START directly, which tries to clear orphaned indexes automatically.

Authorization

The orphaned indexes can be identified by using the QDBTS_LISTINXSTS User Defined Table Function (UDTF).

The privileges held by the authorization ID of the statement must include at least one of these privileges:

*JOBCTL authority
QIBM_DB_SQLADM security special function usage

Syntax

>>- SYSTS_CLEAR_INDEXES
--(--+-----------+--)-----------------------------><
   +-serverid--+      
   '-aliasname-'

Parameters

serverid or aliasname: Specifies the identifier of the server for clear orphaned indexes. A serverid or server aliasname is a string. If no identifier is provided, the default is to clear orphaned indexes on all servers. The identifier string must either be a valid serverid that exists in the SERVERID column, or a valid server aliasname that exists in the ALIASNAME column of the QSYS2.SYSTEXTSERVERS table. If the identifier can be converted to an integer value, it is interpreted as a serverid. If the identifier cannot be converted to an integer value, it is interpreted as a server aliasname.
The data type of this parameter is VARCHAR(128).

Example

Clear all the orphaned indexes:
```
Call SYSPROC.SYSTS_CLEAR_INDEXES();
```

Clear orphaned indexes on a specific server with ID 50:

Call SYSPROC.SYSTS_CLEAR_INDEXES(50);
Call SYSPROC.SYSTS_CLEAR_INDEXES('50');

Clear orphaned indexes on a specific server with alias name “Local_server”:
```
Call SYSPROC.SYSTS_CLEAR_INDEXES('Local_server');
```

Implicitly clear orphaned indexes:

CALL SYSPROC.SYSTS_START();
CALL SYSPROC.SYSTS_START(50);

Note: When the collection on the text search server is in an independent ASP group, the thread that calls the SYSTS_CLEAR_INDEXES stored procedure must run in the namespace of the independent ASP. Use the Set Auxiliary Storage Pool Group (SETASPGRP) command.

To remove an orphaned index from an independent ASP iaspXXX, you can use the following commands:

   CL: 
   SETASPGRP(isapXXX)
   SQL: 
   CALL SYSPROC.SYSTS_CLEAR_INDEXES( )

Note: If you use System i® Navigator, right-click the database name for the independent ASP, and run your SQL scripts.