NetSearch Extender
From Tuxunix
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
