Handling single document errors

Single document errors occur when one or more text documents in a table cannot be indexed by the text search server.

About this task

In this case, the SYSPROC.SYSTS_UPDATE stored procedure returns SQLCODE +462 (SQLSTATE 01H14) and the message OF00050W: 
DSN00462W EXTERNAL FUNCTION OR PROCEDURE SYSTS_UPDATE (SPECIFIC NAME SYSTS_UPDATE)
HAS RETURNED A WARNING SQLSTATE, WITH DIAGNOSTIC TEXT OF00050W num document(s) 
could not be processed correctly. For details see event table 
'SYSIBMTS'.'EVENTS_index-ID'.

In addition, for each document that cannot be processed, the SYSPROC.SYSTS_UPDATE stored procedure adds an entry to the event table. The text search index does not contain these documents. Therefore, the CONTAINS or SCORE functions will not return these documents.

Usually, a single document error occurs if a document contains ill-formed content. Single document errors typically occur for the following reasons:
  • The document content is damaged or truncated.
  • The document format is different from the format that is specified in the SYSPROC.SYSTS_CREATE stored procedure.
  • The document in a binary column is encoded in a CCSID that is different from the CCSID that is specified in the SYSPROC.SYSTS_CREATE stored procedure.
  • The document size exceeds the maximum size that is supported by the text search server engine.
  • The content is incorrect from the user-defined function, if the user-defined function is specified as a parameter in the SYSPROC.SYSTS_CREATE stored procedure to access the text documents indirectly.

Procedure

To find and correct single document errors and trigger re-indexing:

  1. In the OF00050W message, note the name of the event table and the number num of documents in error.
  2. Find the documents that are in error and their ROWIDs by joining the table that contains that indexed column with the RID column of the event table. You can use the ROWID function as shown in the following example. 
    SELECT HEX(T.rowid-column), T.col-name 
  FROM table-name T,SYSIBMTS.EVENTS_index-ID E
  WHERE T.rowid-column=ROWID(E.RID);
    In this example:
    • table-name is the table that contains the column (col-name) that the external text search index is created on.
    • rowid-column is the name of the required ROWID column.
    • EVENTS_index-ID is the event table name from the OF00050W message.
  3. Correct the documents that are in error in the text table column by using an SQL UPDATE statement. This step is also necessary in cases where a user-defined function returns the document content. In these cases, you must also verify that the user-defined function returns the expected content when applied to the text column. (Make sure to check the CCSID and format.)
  4. Call the SYSPROC.SYSTS_UPDATE stored procedure again. If the stored procedure returns successfully without warning, then all updated documents are included in the text search index.