QDBTSLIB.QDBTS_ROWS_STATUS

Users can check which documents were not indexed successfully using the QDBTSLIB.QDBTS_ROWS_STATUS SQL stored procedure after calling SYSTS_UPDATE.

With this stored procedure, users can get a result set which presents those documents which were not indexed successful. Or users can choose generating a new table to store related info of those documents which were not indexed successful.

Syntax

>>-QDBTSLIB.QDBTS_ROWS_STATUS
(IndexSchema, IndexName, ResultSetIndicator, TableSchema, TableName)
-><

The schema qualifier is QDBTSLIB.

Parameter

Required Parameter Group

IndexSchema: Identifies the schema of the text search index. You must specify a value that is not null for this parameter.
The data type of this parameter is VARCHAR(128).

IndexName: Identifies the name of the text search index. The name of the text search index with the index schema uniquely identifies the full-text index in the DB2® subsystem. You must specify a value that is not null for this parameter.
The data type for this parameter is VARCHAR(128).

Optional Parameter Group

ResultSetIndicator: Identifies whether return the result set to user directly or not.; If the ResultSetIndicator is not passed or it’s set to zero, the result set is returned to the caller.; If the ResultSetIndicator is specified and is NOT set to zero, no result set is returned and the caller can query the table specified by TableSchema and TableName.
The data type for this parameter is INTEGER.

TableSchema: Identifies the table schema of the table. If ResultSetIndicator is specified and is NOT zero, then this schema must exist.

TableName: Identifies the table name generated by this stored procedure. If ResultSetIndicator is specified and is NOT zero, this stored procedure will create a new table with this table name.

Result Set or Table Structure

TIME	TIMESTAMP
STATUS	INTEGER
TEXT_STATUS	VARCHAR(50)
MESSAGE	VARCHAR(1024)
KEYCOLUMNNAMES	Depends on the key columns defined in base table

TIME: This is the time when error/warning was thrown out. ..

STATUS, TEXT_STATUS

30	WARNING	this record was indexed but there is warning about it
40	ERROR	this record was not indexed successful for some errors
50	FATAL ERROR	this record returns a fatal error and breaks up the indexing

MESSAGE: This shows the error code and error message. According to this column, users can know why the record was not indexed successful.

KEYCOLUMNNAMES: There might be more than one key columns specified. If so, all the key columns will be returned. Each key column name will have a prefix ‘PK_’.

Examples

To check if the index has document not indexed successful:
```
CALL QDBTSLIB.QDBTS_ROWS_STATUS('indexSchema1','indexName1')
```
The result set will be returned to caller directly.
To generate a new table to stored the result:
```
CALL QDBTSLIB.QDBTS_ROWS_STATUS('indexSchema1','indexName1',1,'TableSchema','ResultTable')
```
Then users can query from the result table to get more info.
```
SELECT * FROM TABLESCHEMA.RESULTTABLE;
```
Assume there are 2 columns (K1, K2) of the base table to be indexed.

To get the rows not indexed of base table, users can use following SQL statement.
```
SELECT b.*,r.MESSAGE FROM TABLESCHEMA.RESULTTABLE r LFET JOIN BASETABLESCHEMA.BASETABLE b on r.PK_K1=b.K1 and r.PK_K2=b.K2; 
```
Then users can update that row based on the message returned, then invoke SYSTS_UPDATE again to index the new changed row.