NetSearch Extender

From Tuxunix
Jump to: navigation, search

Créer des indexes NSE

#> db2text "create index db2ext.nomIdx for text on table(champ) LANGUAGE FR_FR UPDATE MINIMUM 1 \
REORGANIZE AUTO COMMITCOUNT FOR UPDATE 200 ADMINISTRATION TABLES IN PINS connect to instance user xxxx using xxx"

En précisant un répertoire

#> db2text "create index db2ext.nomIdx for text on table(champ) LANGUAGE FR_FR UPDATE MINIMUM 1 \
REORGANIZE AUTO COMMITCOUNT FOR UPDATE 200  INDEX DIRECTORY \"/db2index\" WORK DIRECTORY \"/db2index\" \
ADMINISTRATION TABLES IN PINS connect to instance user xxxx using xxx"

En précisant la période de mise à jour

  • Ici tout les 1/4 d'heure
#> db2text "create index db2ext.nomIdx for text on table(champ) LANGUAGE FR_FR UPDATE FREQUENCY d(0,1,2,3,4,5,6) \
h(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23) m(00,15,30,45) UPDATE MINIMUM 1 \
REORGANIZE AUTO COMMITCOUNT FOR UPDATE 200 INDEX DIRECTORY\"/db2index\" WORK DIRECTORY \"/db2index\" \
ADMINISTRATION TABLES IN PINS connect to instance user xxxx using xxx"

Supprimer des indexes NSE

#> db2text "DROP INDEX "dbext"."indexname" for TEXT CONNECT TO nombase"

Fichier update indexes (Scheduler)

Lorsque vous créer un index avec un update frequency, alors le fichier suivant est alimenté :

  • /home/db2inst1/sqllib/db2ext/ctedem.dat

Si vous avez besoin de modifier des paramètres de mise à jour, vous pouvez modifier ce fichier puis, redémarrer db2text pour ca prise en compte.

Suspendre la mise à jour des indexes NetSearch

  • Récuperer et sauvegarder l'état actuel de la périodicité.
$> db2 "select INDNAME, UPDATEFREQUENCY from db2ext.textindexes"

INDNAME            UPDATEFREQUENCY
---------------------------------------------------------------------------------------------------
IDX1               D(0,1,2,3,4,5,6)H(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)M(0,15,30,45)
IDX2               D(0,1,2,3,4,5,6)H(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)M(0,15,30,45)
  • Stop la mise à jour des indexes NSE
$> db2text "ALTER INDEX db2ext.IDX2 FOR TEXT UPDATE FREQUENCY NONE connect to nomBDD user XXXX using XXXXX"
CTE0001 Operation completed successfully.
  • Etat de la périodicité pour l'index 2 :
$> db2 "select INDNAME, UPDATEFREQUENCY from db2ext.textindexes"

INDNAME            UPDATEFREQUENCY
---------------------------------------------------------------------------------------------------
IDX1               D(0,1,2,3,4,5,6)H(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)M(0,15,30,45)
IDX2               NONE


  • Relance la mise à jour des indexes (Périodicité initial)
$> db2text "ALTER INDEX db2ext.IDX2 FOR TEXT UPDATE FREQUENCY D(0,1,2,3,4,5,6)H(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)M(0,15,30,45) connect to nomBDD user XXXX using XXXX"


Récupérer la liste des indexes Netsearch

#> db2 "select * from db2ext.ttextindexes "

Idem avec un focus sur les colonnes importantes

#> db2 "select indexname, eventviewname from db2ext.ttextindexes"

Récupérer la date d’horodatage et le message associé (eventviewname)

#> db2 "select time, message from db2ext.EVENTIXxxxx"

Effacement des événements d'index

#> db2text CLEAR EVENTS FOR INDEX db2ext.IDX1 FOR TEXT connect to nomBDD user XXXX using XXXX

Locks des indexes

Vérifier si un index est verrouillé

#> db2text control list all locks for database nombase INDEX "db2ext"."indexname"

Déverrouiller un index

  • Attention ne pas réaliser si une mise à jour est en cours!!
#> db2text "CONTROL CLEAR ALL LOCKS FOR DATABASE nombase INDEX "db2ext"."indexname"

Configuration requise

Index resource parameters:

{HOME}/sqllib/db2ext/db2extlm.cfg

• Default:

maxIdxPerDb = " 50"

maxLocksPerIdx = "100"

maxIdxPerDb = "200" *Combien d'index avait vous?

maxLocksPerIdx = "600"

{HOME}/sqllib/db2ext/cteixcfg.ini

• Can be tuned depending on your environment for slight performance gain during indexing

and searching. Some cannot be changed while indexes exit.

RespectCase=OFF *If you don’t need case sensitivity.

MaskResolutionLimit= *May need to increase if you do wild carded searches

Erreur connue

Les indexes ne se mette plus a jour suite a un kill du process update

#> db2 "select indexname, indexidentifier from db2ext.ttextindexes"
INDEXNAME INDEXIDENTIFIER
-----------------------------------------------------------------
INDEX1IDX IX580003 
INDEX2IDX IX530103 
INDEX3IDX IX365320 
INDEX4IDX IX572502 

4 record(s) selected.
#> db2 "select * from db2ext.tcommandlocks"
COMMANDIDENTIFIER INDEXIDENTIFIER LOCKSCOPE PROCESSIDENTIFIER THREADIDENTIFIER NODENUMBER LOCKTIME
----------------- ------------------ ----------- ----------------- ---------------- ----------- --------------------------
9 IX530103 0 16140 0 0 2010-10-08-11.30.54.087591

1 record(s) selected.
#> db2 "delete FROM DB2EXT.TCOMMANDLOCKS"
DB20000I The SQL command completed successfully.
#> db2 "select * from db2ext.tcommandlocks"
COMMANDIDENTIFIER INDEXIDENTIFIER LOCKSCOPE PROCESSIDENTIFIER THREADIDENTIFIER NODENUMBER LOCKTIME
----------------- ------------------ ----------- ----------------- ---------------- ----------- --------------------------

0 record(s) selected.

space lock

#> db2text "Select TIME, MESSAGE from DB2EXT.EVENTIX013001 order by time"
----
TIME MESSAGE 2006-02-18-20.30.02.252910 CTE0118 All available lock space for indexes on a \
database is used. Change the configuration.
  • Cause: db2extlm.cfg default of: maxIdxPerDb = "50" was too small.

Need flush

#> db2text select * from dbadmin.LISTINGS where contains(NSEXMLIDX, 'section("/nse/Name") "tools" & "Sears"')=1

SQL0443N Routine "DB2EXT.TEXTSEARCH_1K16" (specific name "CTE21") has returned an error SQLSTATE with diagnostic text "CTE0198 No corresponding text index.". SQLSTATE=38798

Pas d'index, donc on le creer :

db2text "CREATE INDEX dbadmin.LISTINGS_NSEXMLIDX FOR TEXT ON dbadmin.LISTINGS (NSEXMLIDX) …… CONNECT TO MYDB” db2text "UPDATE INDEX dbadmin.LISTINGS_NSEXMLIDX FOR TEXT REORGANIZE CONNECT TO MYDB”

On relance la requete est, on obtient la même erreur ! Car DB2 cache la reponse de l'erreur il faut donc saisir la commande suivante :

Solution :

#> db2 flush package cache dynamic