IBM Support

Verifying health of the maximo Text Search indexes in DB2

Technical Blog Post


Abstract

Verifying health of the maximo Text Search indexes in DB2

Body

In order to complete verification please fallow the steps:

Step 1:

 

SELECT INDNAME, TABNAME, COLNAME,UPDATEFREQUENCY, UPDATEMINIMUM,

EVENTVIEWNAME

FROM SYSIBMTS.TSINDEXES where TABNAME ='ASSET' and INDNAME in('AXID5','AXIE6');

 

Expected output:

 

INDNAME TABNAME COLNAME UPDATEFREQUENCY UPDATEMINIMUM EVENTVIEWNAME

---------- ---------- -------------- -------------------------------------------- ---------------- ----------------

AXID5 ASSET DESCRIPTION NONE 1 TSEVENT_523117

AXIE6 ASSET ENDDESCRIPTION D(*)H(*)M(0,5,10,15,20,25,30,35,40,45,50,55) 1 TSEVENT_533117

 

There are Text Search Indexes have defined on ASSET table columns DESCRIPTION and ENDDESCRIPTION.

Lets look into UPDATEFREQUENCY : for the index AXID5 the value is NONE that means that asthmatic update of the

Text Search Index entries is turned off and in order to synchronize the entries manual statement UPDATE INDEX AXID5 FOR TEXT must be run.

 

Different policy is defined for index AXIE6 : value in UPDATEFREQUENCY column is a scheduler when update is going to run

( every 5 min, every hour , every day) IF AT LEEST ONE VALUE HAS BEEN UPDATED in ENDDESCRIPTION column of the asset

table ( UPDATEMINIMUM =1).

 

Step 2:

 

SELECT TIME, MESSAGE FROM SYSIBMTS.TSEVENT_533117

expected result would be :

 

10/9/2015 1:43:20 PM CIE00003 Index update started.

10/9/2015 1:43:23 PM CIE00011 Index build started.

10/9/2015 1:43:23 PM CIE00012 Index build ended.

10/9/2015 1:43:23 PM CIE00004 Index update ended.

10/9/2015 1:43:23 PM CIE00005I DB2 Text Search Index update statistics: "682" number of rows processed. "14" documents inserted, "0" documents updated, "0" documents deleted, "668" empty documents skipped successfully, and "0" documents not indexed due to document errors.

10/16/2015 2:30:01 PM CIE00003 Index update started.

10/16/2015 2:30:01 PM CIE00011 Index build started.

10/16/2015 2:30:01 PM CIE00012 Index build ended.

10/16/2015 2:30:01 PM CIE00004 Index update ended.

 

Output shows the the update run by scheduler and updated Text Search index entry. A result of the insert rows in ASSET table.

[{"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11131441