CURRENTwww.qqq147.com

IBMie-businessandWebservingOmniFindTextSearchServerforDB2fori7.
1IBMIBMie-businessandWebservingOmniFindTextSearchServerforDB2fori7.
1IBMNoteBeforeusingthisinformationandtheproductitsupports,readtheinformationin"Notices,"onpage129.
Thiseditionappliestoversion1,release2,modification0ofOmniFindTextSearchServerforDB2fori(productnumber5733-OMF)andtoallsubsequentreleasesandmodificationsuntilotherwiseindicatedinneweditions.
Thisversiondoesnotrunonallreducedinstructionsetcomputer(RISC)modelsnordoesitrunonCISCmodels.
CopyrightIBMCorporation2002,2010.
USGovernmentUsersRestrictedRights–Use,duplicationordisclosurerestrictedbyGSAADPScheduleContractwithIBMCorp.
ContentsOmniFindTextSearchServerforDB2fori1What'snewforIBMi7.
11PDFfileforOmniFindTextSearchServerforDB2fori2IntroductiontotheOmniFindTextSearchServerforDB2fori2OverviewoftheOmniFindTextSearchServerforDB2fori2SystemrequirementsforinstallingtheOmniFindTextSearchServerforDB2fori4Keyconcepts4Createandupdateatextsearchindex4Asynchronousindexingandtriggers5Supporteddocumentformats6Supporteddatatypes7Textscoreandsynonymsupport7Linguisticprocessing8Serveraliasname10Installandconfiguretextsearchfunctions.
.
.
.
11InstallOmniFindTextSearchServerforDB2fori11Starttextsearchfunctions12Createatextsearchindex13Updateatextsearchindex13Searchatextsearchindex14Documenttruncation14Administrationstoredproceduresfortextsearch.
.
14SYSPROC.
SYSTS_START14SYSPROC.
SYSTS_STOP16SYSPROC.
SYSTS_CREATE17SYSPROC.
SYSTS_ALTER26SYSPROC.
SYSTS_DROP31SYSPROC.
SYSTS_UPDATE32Searchwithatextsearchindex35CONTAINS35SCORE37Searchargumentsyntax39XMLsearch44AdministeranOmniFind(r)TextSearchServerforDB2(r)fori59StarttheOmniFindTextSearchServerforDB2fori59StoptheOmniFindTextSearchServerforDB2fori60Saveandrestoretextsearchindexes61Problemdetermination63Viewandsaveserverlogs64Administrationtools65ServerInstancetool75HealthChecker76IndependentASPconsiderationsforOmniFindTextSearchServerforDB2fori77HighAvailability78Performanceanalysis79Transactionconsiderations82UsingIBMNavigatorfori83UsingSystemiNavigator89Textsearchadministrationtables96QSYS2.
SYSTEXTDEFAULTSadministrationtable96QSYS2.
SYSTEXTINDEXESadministrationtable97QSYS2.
SYSTEXTCOLUMNSadministrationtable99QSYS2.
SYSTEXTSERVERSadministrationtable99QSYS2.
SYSTEXTCONFIGURATIONadministrationtable100QSYS2.
SYSTEXTSERVERHISTORYadministrationtable101TextSearchIndexview101ExtensionstoIndexandSearchNon-DB2Data.
.
102ExtensionsOverview102CreatingaTextSearchCollection103AddinganObjectSetforSpoolFileData.
.
.
108AddinganObjectSetforStreamFileData.
.
.
112RemovinganObjectSet114UpdatetheCollection115RepopulatetheTextSearchCollection.
.
.
.
116SearchtheCollection117QueryObjectSetInformation119RetrieveStatusofIndexesObjects120GetObjectsNotIndexed121RetrieveStatusofCollection122DroppingaTextSearchCollection123IndependentASPConsiderationsforTextSearchCollections124BackupandRestoreConsiderationsforTextSearchCollections125Messagesandcodes125OmniFindmessages125Appendix.
Notices129Programminginterfaceinformation131Trademarks131Termsandconditions.
131CopyrightIBMCorp.
2002,2010iii|||||||||||||||||||||||||||||||||||||ivIBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriOmniFindTextSearchServerforDB2foriOmniFindTextSearchServerforDB2foriallowsyoutoissueSQLstatementsthatsatisfyfamiliartextsearchqueriesondocumentsthatarestoredinaDB2database.
Note:Byusingthecodeexamples,youagreetothetermsofthe"Codelicenseanddisclaimerinformation"onpage128.
What'snewforIBMi7.
1ReadaboutneworsignificantlychangedinformationfortheOmniFindTextSearchServerforDB2foritopiccollection.
Themajornewfeaturesinclude:vEnhanced"XMLsearch"onpage44–"Usingnamespacesinsearch"onpage51–"Numericcomparisons"onpage47–"DateandDateTimecomparisons"onpage47vTextsearchindexforDB2XMLcolumns:"XMLsearchexample"onpage55v"Saveandrestoreatextsearchindexwithoutdata"onpage61v"Serveraliasname"onpage10vTwoGUIinterfaces:–"UsingIBMNavigatorfori"onpage83–"UsingSystemiNavigator"onpage89v"HighAvailability"onpage78v"Performanceanalysis"onpage79v"Transactionconsiderations"onpage82What'snewasof3October2012:vUsingIBMNavigatorforitosupport"ExtensionstoIndexandSearchNon-DB2Data"onpage102What'snewasof14October2011:v"ExtensionstoIndexandSearchNon-DB2Data"onpage102v"HealthChecker"onpage76vAnexplanationofotherOmniFindTextSearchServerforDB2forienhancementscanbefoundat:OmniFindTextSearchServerforDB2forienhancements.
Howtoseewhat'sneworchangedTohelpyouseewheretechnicalchangeshavebeenmade,theinformationcenteruses:vTheimagetomarkwhereneworchangedinformationbegins.
vTheimagetomarkwhereneworchangedinformationends.
InPDFfiles,youmightseerevisionbars(|)intheleftmarginofnewandchangedinformation.
Tofindotherinformationaboutwhat'sneworchangedthisrelease,seetheMemotousers.
CopyrightIBMCorp.
2002,20101||||||||||||||||||||||||||||||PDFfileforOmniFindTextSearchServerforDB2foriUsethispagetoviewandprintaPDFofthisinformation.
ToviewordownloadthePDFfileforthisdocument,selectOmniFindTextSearchServerforDB2fori(about1192KB).
OtherinformationYoucanalsovieworprintanyofthesePDFfiles:vPreparingforandTuningtheSQLQueryEngineonDB2fori5/OSvSQLPerformanceDiagnosisonIBMDB2UniversalDatabaseforiSeries.
SavingPDFfilesTosaveaPDFonyourworkstationforviewingorprinting:1.
Right-clickthePDFinyourbrowser(right-clicktheprecedinglink).
2.
ClicktheoptionthatsavesthePDFlocally.
3.
GotothedirectoryinwhichyouwanttosavethePDF.
4.
ClickSave.
DownloadingAdobeReaderYouneedAdobeReaderinstalledonyoursystemtovieworprintthesePDFfiles.
YoucandownloadafreecopyfromAdobe(http://get.
adobe.
com/reader/).
IntroductiontotheOmniFindTextSearchServerforDB2foriDB2foriusestheOmniFind(r)TextSearchServerasanindexingandsearchenginefordocumentsthatarestoredinaDB2database.
OMNIFINDisatextsearchproductthatallowsIBMiuserstosearchunstructuredtextstoredinacolumnofaDB2foritable.
Thetextstoredinthecolumncanbeeithersimplecharactertext,anXMLdocument,oranyofseveraldifferenttypesofrichtextdocuments,suchasaPDForDOCfile.
TheproductallowsuserstoindexunstructureddatawithouthavingtoparseitintoastructuredformsuchasanSQLtable.
OmniFindTextSearchServerisacontext-basedsearchengine.
Itsupportsfuzzysearchcapability.
Forexample,asearchfor'mice'discoversdocumentswith'mice'or'mouse'inthem.
Thesearchenginealsosupportslanguagecontext.
Forexample,itunderstandsthefuzzysearchequivalentsof'mice'and'mouse'inbothEnglishandSpanish.
Manyapplicationscantakeadvantageofthiscapability.
AgoodexampleisaHumanResourcesdatabase.
Candidateresumescanbestoredinthedatabaseinwhateverformtheyaresubmitted.
SubsequentsearchesusingOmniFindcanbeusedtosearchforpotentialcandidateswithcertainkeyskills.
OverviewoftheOmniFindTextSearchServerforDB2foriOmniFindTextSearchServerforDB2foriprovidesasetofadministrativestoredproceduresandtwobuilt-infunctions:CONTAINSandSCORE.
Thesefunctionsareusedtosearchtextindexescreatedfrom2IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||documentsstoredinaDB2table.
Theadministrativestoredproceduresareusedtoenableanddisabletextsearchingandtocreate,update,anddroptextindexes.
Atextindexcanbecreatedoveranycolumnofthefollowingdatatypes:vCHARvVARCHARvCLOBvBLOBvDBCLOBvGRAPHICvVARGRAPHICvBINARYvVARBINARYvXMLThedatacancontainplaintext,HTML,XML,ormanyrichdocumenttypes,suchasPDFfiles.
ThedataisreadfromthetextcolumnandisconvertedtoUnicode(CCSID1208)beforeitisindexed.
TextindexesarenottypicalDB2indexes.
Theyarenotmaintainedautomatically,cannotbejournaled,andcannotbebackedupusingthetypicalbackupandrestoremethods.
Textindexesarecreatedandstoredonatextsearchserver.
Bydefault,thetextsearchserveriscreatedonthesamesystemasthedatastoredintheDB2database.
However,atextsearchservercanbecreatedonanotherserverrunningIBMi,Linux,UNIX,AIX,orWindows.
Thetextsearchservercontainsacollectionofsignificanttermsextractedfromeachrowofthecolumn.
ATCP/IPconnectionisusedtocommunicatewiththetextsearchserver.
TheCONTAINSandSCOREfunctionsarebuilt-infunctionswhichareintegratedintoDB2fori.
DB2foriusestheOmniFind(r)TextSearchServerasanindexingandsearchenginefordocumentsthatarestoredinaDB2database.
OMNIFINDsupportsmultiplecollections.
Acollectioncontainsonetextsearchindexandtheindex-specificoptionsforparsing,indexing,andsearching.
OMNIFINDhasagraphicaluserinterfaceforadministrationofserversandtextindexes.
ThetextsearchserveralsoprovidesSQLstoredproceduresandcommand-linetoolsthatyoucanuseforcommontasks.
Thesecommontasksincludeconfiguringandadministeringthetextsearchserver,creatingasynonymdictionaryforacollection,anddiagnosingproblems.
Relatedconcepts:"Administrationstoredproceduresfortextsearch"onpage14Youcanstartandstoptextsearchfunctionsandcreate,drop,andupdatetextsearchindexesbyusingasetofadministrationSQLstoredprocedures.
TheseprocedurescanbecalledfromanySQLinterfaces.
YoucannotcalltheseproceduresfromanIBMicommandlinebyusingCLcommands.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
OmniFindTextSearchServer3|||||||||||||||||||||||||||||||||||||||||"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
SystemrequirementsforinstallingtheOmniFindTextSearchServerforDB2foriBeforeyouinstallanOmniFindTextSearchServerforDB2fori,makesurethatyoursystemmeetsallthehardware,software,andoperatingsystemrequirements.
WhenyouinstallOmniFindTextSearchServerforDB2fori,theinstallationprogramcreatesonetextsearchserverforIBMi.
YoucaninstalltextsearchserversonremoteserversrunningLinuxorWindows.
TheseserversarepartofDB2AccessoriesSuiteforz/OS(5655-R14)(http://www-01.
ibm.
com/software/data/db2imstools/db2tools/accessories-suite/).
Thelinkhasinformationaboutdownloadingthesuite.
SoftwarerequirementsMakesurethatyoursystemmeetsthefollowingminimumsoftwarerequirements:v5761JV1IBMDeveloperKitforJava–Oneofthefollowing:-5761JV1Option11JavaSE632bit-5761JV1Option12JavaSE664bitvDB2UniversalJavaDriverinstalledandconfiguredonthetextsearchservervForIBMi,thefollowingprogramsmustbeinstalled:–5770SS1Option30Qshell–5770SS1Option33IBMiPortableApplicationSolutionsEnvironment(IBMiPASE)–5770SS1Option39InternationalComponentsforUnicode–ThelatestGroupPTFforIBMDB2foriisappliedonthesystem.
KeyconceptsUnderstandingthekeyconceptsabouttextsearchfunctionshelpsyoutousethebenefitsofOmniFindTextSearchforDB2fori.
Keyconceptsincludethedocumenttypesandlanguagesthataresupported.
CreateandupdateatextsearchindexYoucancreateatextsearchindexbydefininganddeclaringthepropertiesoftheindex.
YoucanupdateatextsearchindexbyaddingnewdatafromaDB2tabletotheindex.
Youcanalsoupdateatextsearchindexbychangingtheexistingdataintheindex.
Foreachtextsearchindexthatyoucreate,anewcollectioniscreatedontheOmniFindTextSearchServerforDB2fori.
Afterinitialcreation,thetextsearchindexcontainsnodata.
YouadddatatothetextsearchindexbycallingtheSYSPROC.
SYSTS_UPDATEstoredprocedure.
Thefirstupdateprocessaddsallthetextdocumentsfromthetextcolumntothetextsearchindex.
Thisprocessisknownastheinitialupdate.
Thesubsequentupdatesareincremental.
Whenatextsearchindexiscreated,thefollowingobjectsarecreatedorupdated:vThestagingtableiscreatedintheQSYS2library.
vTheINSERT,DELETE,andUPDATEtriggersareaddedtothebasetable.
4IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||vAnSQLviewwiththenameofthetextsearchindexiscreatedintheschemaofthetextsearchindex.
Thisviewcontainsinformationaboutthetextindex.
Forexample,theviewcanbeusedtoobtainthebasetablenameandthestagingtablename.
Theviewalsoshowsthenumberofpendingchangestothebasetablethatarenotyetreflectedinthetextsearchindex.
vThetextsearchindexcatalogs(SYSTEXTINDEXESandSYSTEXTCOLUMNS)intheQSYS2libraryareupdatedwithanewentryaddedforthenewtextsearchindex.
Stagingtableconsiderations:vDonotperformanyDBoperationonthestagingtableexceptsavingandrestoringthefile,orchangingauthorities.
vIfyouarechangingtheauthoritiesonthebasetable,changetheauthoritiesonthestagingtablealso.
Basetableconsiderations:vDonotremovetheDELETE,UPDATE,andINSERTtriggersthatareaddedwhenatextsearchindexiscreated.
vDroppingthetextsearchindexremovesthetriggers.
vDonotalterorremovetheROWID,primarykey,oruniquecolumnthatwasusedasthekeyinthetextsearchindex.
vAlteringthedatacolumnofthebasetablethatresultsindatatruncationmightresultinfalsepositivematchesinthetextsearchindex.
Relatedreference:"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"QSYS2.
SYSTEXTINDEXESadministrationtable"onpage97YoucanseeinformationabouteachtextsearchindexintheQSYS2.
SYSTEXTINDEXESadministrationtable.
Eachtextsearchindexhasaname,schemaname,andanassociatedcollectionnameonthetextsearchserver.
"QSYS2.
SYSTEXTCOLUMNSadministrationtable"onpage99YoucanseeinformationaboutthetextcolumnsforatextsearchindexintheQSYS2.
SYSTEXTCOLUMNSadministrationtable.
EachtextsearchindexhasanindexID,textcolumnnames,andtheschemanameofthebasetable.
AsynchronousindexingandtriggersYoucanupdatethetextsearchindexontheOmniFindTextSearchServerforDB2forimanuallyorscheduleittorunautomatically.
ThetextsearchindexmaintainedontheOmniFindTextSearchServerforDB2foriisnotupdatedsynchronouslywhentheDB2tableisupdated.
Updatingatextsearchindexisanextensiveoperation.
Instead,changestotheDB2tablecolumnarecapturedbytriggerstoalocallogtable.
Thislogtableisalsocalledastagingtable.
Thesetriggersautomaticallystoreinformationaboutnew,changed,anddeleteddocumentsinalogtable.
Eachlogtableisassociatedwithonetextsearchindex.
Applyingthecontentsofthelogtabletoitscorrespondingtextsearchindexiscalledanincrementalupdate.
Youmustperiodicallyupdatethetextsearchindexinorderforchangestobereflectedinqueries.
YoucanupdatethetextsearchindexmanuallybycallingtheSYSPROC.
SYSTS_UPDATEstoredprocedure.
UpdatescanalsobescheduledtooccurautomaticallybyusingtheUPDATEFREQUENCYclauseontheSYSPROC.
SYSTS_CREATEprocedurewhenthetextindexiscreated.
OmniFindTextSearchServer5||||||||||||||||Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
SupporteddocumentformatsThetextcolumndatacanbeplaintext,anHTMLdocument,anXMLdocument,oranydocumentthatisrecognizedbythesearchengine.
OmniFindTextSearchServerforDB2foriparsesdocumentstoextracttherelevantpartsandmakethosepartssearchable.
Forexample,tagsandmetadatainanHTMLdocumentarenotindexed.
Parsingofthefollowingdocumentformatsissupported:vTEXT:FlattextvHTML:HypertextMarkupLanguagevXML:ExtensibleMarkupLanguagevINSO:TheOmniFindTextSearchServerforDB2foriusesfilterstodetecttheformatoftextdocuments.
ThefollowingINSOdocumentformatsaresupported:–XML–HTML–JustSystemsIchitaro–Lotus123–LotusFreelance–LotusWordPro–MicrosoftExcel–MicrosoftPowerPoint–MicrosoftRichTextFormat–MicrosoftVisio–MicrosoftWord–MicrosoftWrite–PortableDocumentFormat(PDF)–QuattroPro–RichTextRTF–StarOfficeCalcandOpenOfficeCalcAllthedocumentsinanindexedtextcolumnmustbeofthesameformat(TEXT,HTML,XML,orINSO).
XMLdataXMLstructureintheXMLdataisindexedintheOmniFindTextSearchServerforDB2foriafterparsingthedatathroughanXMLparser.
ThenyoucanusethesupportedXMLSearchquerysyntaxtoretrievetheresults.
Relatedconcepts:6IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori"XMLsearch"onpage44YoucanindexandsearchXMLdocuments.
TheXMLsearchgrammarusesasubsetoftheW3XPathlanguagewithextensionsfortextsearch.
Theextensionssupportrangesearchesofnumeric,Date,andDateTimevaluesthatareassociatedwithanXMLattributeorelement.
Structuralelementscanbeusedseparately,orcombinedwithfreetextinqueries.
SupporteddatatypesThedatainthetextcolumnsthatyouwanttoindexandsearchcanbeeitherbinarydataorcharacterdata.
Thefollowingdatatypesarebinarydata:vBINARYvVARBINARYvBLOBInaddition,OMNIFINDhandlesthefollowingdatatypessimilarlytobinarydata:vCHARFORBITDATAvVARCHARFORBITDATAThefollowingdatatypesarecharacterdata:vCHARFORSBCSDATAorFORMIXEDDATAvVARCHARFORSBCSDATAorFORMIXEDDATAvCLOBvDBCLOBvGRAPHICvVARGRAPHICvXMLIfthedataisbinarydata,youcanspecifythecodedcharactersetidentifier(CCSID)usedtobuildthetextsearchindex.
Forcharacterdata,theDB2databaseknowstheencoding;therefore,ifyouexplicitlyspecifyaCCSID,thatspecificationisignored.
TextscoreandsynonymsupportYoucanusesynonymstoimprovetheresultsforaquery.
Youcanuseatextscoretofindouthowcloselyaresultmatchesthequery.
TextscoreAtextscoreiscalculatedaspartofthesearch,andcanbeincludedinthequeryresults.
Atextscoreisavalue0-1,uptothreedecimalpoints;forexample,0.
000to1.
000.
Atextscoredenoteshowcloselyaresultmatchesthequeryrelativetoalltheotherdocumentsinthetextsearchindex.
OMNIFINDcomposesthetextscorefromvariousfactors,suchasthegeneralimportanceofthesearchtermsandtheproximityofoccurrencesofthesearchterms.
Thegeneralimportanceisbasedonthefrequencyofthetermsineachdocumentandoffsetbythefrequencyofthetermsacrossalldocuments.
SynonymsupportTheOmniFindTextSearchServerforDB2forisupportstheuseofsynonymstomodifytheresultsofaquery.
UsingsynonymscanincreasethenumberofqueryresultsbycausingmoredocumentstomatchaOmniFindTextSearchServer7|||||||query.
However,usingsynonymsmightalsodecreasetheprecisionofaqueryandmakeitmoredifficulttofindfewdocumentsthatmatchtheexactsearchcriteria.
Bydefault,synonymsarenotusedforaquery.
Tousesynonymsforaquery,createasynonymdictionary,andaddthesynonymdictionarytoacollectionbyusingthesynonymtool.
Formoreinformationaboutsynonyms,see"Synonymdictionaries"onpage71.
Relatedreference:"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
LinguisticprocessingTheOmniFindTextSearchServerforDB2foriprovidesdictionarypackstosupportthelinguisticprocessingofdocumentsandqueriesthatarenotinEnglish.
Asanalternativetodictionary-basedwordsegmentation,theOmniFindTextSearchServerforDB2foriusesn-gramsegmentationsupportforlanguagessuchasChinese,Japanese,andKorean.
n-gramsegmentationisamethodofanalysisthatconsidersoverlappingsequencesofagivennumberofcharactersasasingleword.
Alternatively,Unicode-basedwhite-spacesegmentationusesblankspacetodelimitwords.
Ifatextdocumentisinoneofthesupportedlanguages,linguisticprocessingiscarriedoutwhenthetextisparsedintotokens.
Forunsupportedlanguages,anerrorcodeisreturned.
Whenyousearchatextsearchindex,amatchisindicatedthatcontainslinguisticvariationsofthequeryterms.
Thevariationsofaworddependonthelanguageofthequery.
SupportedlanguagesYoucanspecifythattextdocumentsbeprocessedusingaspecificlanguage.
YoucanspecifythelanguagefortheindexedtextdataintheSYSPROC.
SYSTS_CREATEadministrationstoredprocedure.
IfyousetthevaluetoAUTO,theOmniFindTextSearchServerforDB2foritriestodeterminethelanguage.
Forshortdocuments,automaticdetectionmightbenotaccurateandisnotrecommended.
ThedefaultlanguageforlinguisticprocessingisEnglish(en_US).
Thefollowingtableshowsthefive-characterlanguagecodesforthesupportedlanguages.
Table1.
Thefive-characterlanguagecodesforthesupportedlanguagesLanguagecodeLanguagear_AAArabiccs_CZCzechda_DKDanishde_CHGerman(Switzerland)de_DEGerman(Germany)el_GRGreeken_AUEnglish(Australia)en_GBEnglish(UnitedKingdom)en_USEnglish(UnitedStates)es_ESSpanish(Spain)fi_FIFinnish8IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||Table1.
Thefive-characterlanguagecodesforthesupportedlanguages(continued)LanguagecodeLanguagefr_CAFrench(Canada)fr_FRFrench(France)it_ITItalianja_JPJapaneseko_KRKoreannb_NONorwegianBokmalnl_NLDutchnn_NONorwegianNynorskpl_PLPolishpt_BRBrazilianPortuguesept_PTPortuguese(Portugal)ru_RURussiansv_SESwedishzh_CNSimplifiedChinesezh_TWTraditionalChineseRelatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
LinguisticprocessingforChinese,Japanese,andKoreandocumentsYoucanprocessdocumentsthatareinChinese,Japanese,orKoreanbyusingdictionary-basedsegmentationorbyusingn-gramsegmentation.
Forasearchengine,gettinggoodsearchresultsdependsinlargepartonthetechniquesthatareusedtoprocesstext.
Afterthetextisextractedfromthedocument,thefirststepintextprocessingistoidentifytheindividualwordsinthetext.
Identifyingtheindividualwordsinthetextisreferredtoassegmentation.
Formanylanguages,whitespace(blanks,theendofaline,andcertainpunctuation)canbeusedtorecognizewordboundaries.
However,Chinese,Japanese,andKoreandonotusewhitespacebetweencharacterstoseparatewords,soothertechniquesmustbeused.
TheOmniFindTextSearchServerforDB2foriprovidesthefollowingtwomethodstosupportthelinguisticprocessingofChinese,Japanese,andKorean:vDictionary-basedwordsegmentation(alsocalledmorphologicalanalysis)vN-gramsegmentationDictionary-basedwordsegmentationDictionary-basedwordsegmentationusesalanguage-specificdictionarytoidentifywordsinthesequenceofcharactersinthedocument.
Thistechniqueprovidesprecisesearchresults,becausethedictionariesareusedtoidentifywordboundaries.
However,dictionary-basedwordsegmentationcanmissspecificmatchingresults.
OmniFindTextSearchServer9N-gramsegmentationN-gramsegmentationavoidstheproblemofidentifyingwordboundaries,andinsteadindexesoverlappingpairsofcharacters.
BecausetheOmniFindTextSearchServerforDB2foriusestwocharacters,thistechniqueisalsocalledbi-gramsegmentation.
N-gramsegmentationalwaysreturnsallmatchingdocumentsthatcontainthesearchterms;however,thistechniquemightsometimesreturndocumentsthatdonotmatchthequery.
Bydefault,theOmniFindTextSearchServerforDB2foricomeswithapre-configuredindexthatusesn-gramsegmentationforChinese,Japanese,andKorean.
Toseehowbothtypesoflinguisticprocessingwork,examinethefollowingtextinadocument:electionforgovernorofKanagawaprefecture.
InJapanese,thistextcontainseightcharacters.
Forthisexample,theeightcharactersarerepresentedasABCDEFGH.
Asamplequerythatusersmightentercouldbeelectionforgovernor,whichisfourcharactersandarerepresentedasEFGH.
(Thedocumenttextandthesamplequerysharesimilarcharacters.
)Ifyouusen-gramsegmentationprocessing:Afterthedocumentisindexed,thesearchenginesegmentsthetextelectionforgovernorofKanagawaprefectureintothefollowingsetsofcharacters:ABBCCDDEEFFGGHThesamplequeryelectionforgovernorissegmentedintothefollowingsetsofcharacters:DEEFFGGH.
Ifyousearchwiththesamplequeryelectionforgovernor,thedocumentisfound.
Thereasonisthatthetokensforboththedocumenttextandthequeryappearinthesameorder.
Whenyouenablen-gramsegmentation,youmightseemoreresultsbutpossiblylesspreciseresults.
Forexample,inJapanese,ifyousearchwiththequeryKyotoandadocumentinyourindexcontainsthetextCityofTokyo,thedocumentisfound.
ThereasonisthatCityofTokyoandKyotosharetwoofthesameJapanesecharacters.
Ifyoudonotusen-gramsegmentationprocessing:Afterthedocumentisindexed,thesearchenginesegmentsthetextelectionforgovernorofKanagawaprefectureintothefollowingsetsofcharacters:ABCDEFGH.
Thesamplequeryelectionforgovernorissegmentedintothefollowingsetsofcharacters:EFGH.
ThecharactersEFdonotappearinthetokensofthedocumenttext.
(EventhoughthedocumentdoesnothaveEF,itdoeshaveDEF).
ThedocumenttextcontainsDEF,butthequerycontainsonlyEF.
Therefore,thedocumentislesslikelytobefoundbyusingthesamplequery.
Whenyoudonotenablen-gramsegmentation,youprobablyreceivemorepreciseresultsbutpossiblyfewerresults.
ServeraliasnameYoucanuseaserveraliasnametoassignameaningfulnametoaserver.
EachtextsearchserverisuniquelyidentifiedbycolumnSERVERIDincatalogQSYS2.
SYSTEXTSERVERS.
TheSERVERIDcolumnisanincrementallygeneratedintegerbydatabase.
TheALIASNAMEcolumninQSYS2.
SYSTEXTSERVERSisprovidedtoallowameaningfulaliasnametobeassignedtoeachserver.
ServeraliasnamescanbeusedtorefertoserversinSYSTS_START,SYSTS_STOPandSYSTS_CREATEprocedures.
Note:ServeraliasnamecanbechangeddirectlyintheQSYS2.
SYSTEXTSERVERScatalogtablebyupdatingtheALIASNAMEvalue.
10IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||Assignservernumber1analiasnameof"PRIMARY_LOCAL_SERVER".
UPDATEQSYS2.
SYSTEXTSERVERSSETALIASNAME='PRIMARY_LOCAL_SERVER'WHERESERVERID=1Toremoveanaliasnamefromaserver,setthecolumntoNULL.
Removeanaliasnamefromservernumber1.
UPDATEQSYS2.
SYSTEXTSERVERSSETALIASNAME=NULLWHERESERVERID=1Relatedreference:"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"SYSPROC.
SYSTS_STOP"onpage16YoucancalltheSYSPROC.
SYSTS_STOPstoredproceduretostopDB2textsearchfunctions.
ThisstoredproceduresetstheSERVERSTATUSvalueinthecatalogQSYS2.
SYSTEXTSERVERSto1(stopped).
"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
InstallandconfiguretextsearchfunctionsYoucaninstallandconfigureOmniFindTextSearchServerforDB2fori.
Youcanalsocreateandupdateatextsearchindexsothatyoucanstartusingtextsearchfunctionsagainstacolumninatable.
InstallOmniFindTextSearchServerforDB2foriInstallOmniFindTextSearchServerforDB2foriusingthestandardinstallationproceduresforalicensedprogramonIBMi.
Adefaulttextserveriscreated,andtheQSYS2.
SYSTEXTSERVERStableispopulatedwithdefaultserverinformation.
Optionally,youcanaddadditionaltextsearchserversaftertheinstall.
PopulatetheQSYS2.
SYSTEXTSERVERStableOmniFindTextSearchServerforDB2foriisthelicensedprogram5733-OMFfromIBM.
SeeInstallingadditionallicensedprogramsfordetailsabouthowtoinstallalicensedprogram.
Tofindthisproduct,enterGOLICPGMfromthecommandlineandselectoption10(Displayinstalledlicensedprograms).
Itisdisplayedunderthelistoflicensedprograms.
TheQSYS2.
SYSTEXTSERVERStablecontainsinformationabouttheIBMOmniFindTextSearchServersinstalledandavailableforDB2fori.
WhentheOmniFindTextSearchServerforDB2foriproductisfirstinstalled,adefaulttextserveriscreatedontheIBMisystem.
TheQSYS.
SYSTEXTSERVERStableisalsopopulatedwithdefaultserverinformation.
CreateadditionaltextsearchserversIfyouareusingtextsearchserversonaremoteIBMsystem,orifyouareusingnon-IBMserverssuchasaWindowsorLinuxserver,explicitlypopulatethistablebyissuinganSQLINSERTstatement.
OmniFindTextSearchServer11|||||||||||||||||||||||||||||||||||||||||IfyouwanttopopulatetheQSYS2.
SYSTEXTSERVERStablewithadditionalservers,followthesestepsontheSQLINSERT:1.
SpecifytheserverportnumberandservernameforeachtextsearchserverontheSERVERPORTcolumnandSERVERNAMEcolumnsoftheQSYS2.
SYSTEXTSERVERStablebyissuinganSQLINSERTstatement.
2.
SpecifytheauthenticationtokenfromeachtextsearchserverontheSERVERAUTHTOKENcolumnoftheQSYS2.
SYSTEXTSERVERStableontheSQLINSERTstatement.
WhentheDB2databasecommunicateswithatextsearchserver,anauthenticationtokenisrequired.
Thistokenisgeneratedonthetextsearchserverduringtheinstallation.
3.
SpecifytheserverkeyforeachtextsearchserverontheSERVERMASTERKEYcolumnofQSYS2.
SYSTEXTSERVERStableSQLINSERTstatement.
4.
OPTIONAL:SpecifyanALIASNAMEfortheserverontheALIASNAMEcolumn.
ThisALIASNAMEcanbeusedtorefertotheserveronsubsequentoperations.
ExampleThefollowingexampleofanSQLINSERTstatementcopiestherequiredinformationforatextsearchservertothecolumnsintheQSYS2.
SYSTEXTSERVERStable:INSERTINTOQSYS2.
SYSTEXTSERVERS(SERVERNAME,SERVERADRINFO,SERVERPORT,SERVERTYPE,SERVERAUTHTOKEN,SERVERMASTERKEY,SERVERPATH,ALIASNAME)VALUES('127.
0.
0.
1',VARBINARY(X'0000'),49200,0,'AH2X4w==','b1YhcR9O858ArwxLJeIY/Q==','/QOpenSys/QIBM/ProdData/TextSearch/server1/bin/','LOCALSERVER2');Theexamplevaluesmustbereplacedwiththeactualvaluesfortheserver.
Relatedconcepts:"Serveraliasname"onpage10Youcanuseaserveraliasnametoassignameaningfulnametoaserver.
Relatedreference:"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
StarttextsearchfunctionsBeforeyoustartusingthetextsearchfunctions,calltheSYSPROC.
SYSTS_STARTstoredprocedure.
Bycallingthisprocedure,youcanstartalltheproductionserversthatyouhavedefinedtobelocaltothesystem.
TextsearchsupportincludesSQLstatementsthatusetheCONTAINSfunction,theSCOREfunction,andthefollowingadministrationstoredprocedures:vSYSPROC.
SYSTS_CREATEvSYSPROC.
SYSTS_UPDATEvSYSPROC.
SYSTS_DROPRelatedreference:12IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"SYSPROC.
SYSTS_DROP"onpage31YoucancalltheSYSPROC.
SYSTS_DROPstoredproceduretodropatextsearchindexthatwasdefinedbyusingtheSYSPROC.
SYSTS_CREATEstoredprocedure.
"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
CreateatextsearchindexYoucancreateatextsearchindexbycallingtheSYSPROC.
SYSTS_CREATEstoredprocedure.
TheDB2basetablemustcontainaROWIDcolumn,uniquekey,orprimarykey.
TocreateatextsearchindexonanexistingDB2tablewithacolumnthatcontainstext:CalltheSYSPROC.
SYSTS_CREATEstoredprocedure.
Thetextsearchindexisemptyuntilthefirsttimethatyouupdatetheindex.
Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
UpdateatextsearchindexYoucanupdateatextsearchindexbycallingtheSYSPROC.
SYSTS_UPDATEstoredprocedure.
TheSYSTS_UPDATEcallisusedtoinitiallypopulatethetextsearchindex.
ItisalsousedanytimethecontentsoftheDB2tablechangesandyouwanttosynchronizethetextsearchindextothosechanges.
Afteratextsearchindexupdateoccurs,youcanperformsearchqueriesonthetextsearchindex.
Thebasetabletextsearchcolumncanbechangedaftertheupdate.
Inthiscase,thesearchqueryresultsdonotreflectthosechangesuntilthenexttextsearchindexupdateisrun.
Relatedreference:"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
OmniFindTextSearchServer13||||||||||||SearchatextsearchindexYoucansearchatextsearchindexbyusinganSQLstatementwithaCONTAINSorSCOREfunction.
Thesearchargumentcriteriaisspecifiedonthefunction.
TheuserwhoisperformingthetextqueriesonaDB2tablemusthavethestandardprivilegesetthatisrequiredforanyformofquery,asspecifiedintheDB2SQLReference.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
DocumenttruncationTheOmniFindTextSearchServerforDB2forilimitsthenumberofcharactersthatcanbeindexedforeachtextdocument.
Sometimesthischaracterlimitresultsinthetruncationoflargetextdocumentsinthetextsearchindex.
Documentsthatcontainmorethan10millionUnicodecharactersmightbetruncatedbythetextsearchserver.
Forarichtextdocument,thislimitisappliedafterthedocumentistransformedtoplaintext.
Ifatextdocumentistruncatedduringtheparsingstage,youreceiveawarningthatsomedocumentswerenotprocessedcompletely.
Thewarningappearsinthejoblog.
Thedocumentispartiallyindexed.
Textthatisinthedocumentafterthelimitisreachedisnotindexedandisnotconsideredduringsearches.
Youmightwanttoremovethedocumentthathasbeentruncatedfromthetextsearchindextoavoidunexpectedbehaviorduringsearchprocessing.
YoucanremovethedocumentbyremovingthecorrespondingrecordfromtheDB2table,orbychangingthevalueforthedocumenttoemptyornull.
AdministrationstoredproceduresfortextsearchYoucanstartandstoptextsearchfunctionsandcreate,drop,andupdatetextsearchindexesbyusingasetofadministrationSQLstoredprocedures.
TheseprocedurescanbecalledfromanySQLinterfaces.
YoucannotcalltheseproceduresfromanIBMicommandlinebyusingCLcommands.
WhenlookingatthesystemcatalogsusingSTRSQL,youcanseethecontentofthecolumnsbysettingyourjobtoaCCSIDotherthan65535.
SYSPROC.
SYSTS_STARTYoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
TextsearchfunctionsincludesupportforSQLqueriesthatusetheCONTAINSfunction,theSCOREfunction,andtheadministrationstoredproceduresthatareusedtomaintaintextsearchindexes.
RuntheSYSPROC.
SYSTS_STARTstoredprocedureeachtimewhenaserverisaddedorchangedinQSYS2.
SYSTEXTSERVERStable.
14IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||Iftextsearchfunctionsarenotstarted,thedatabasereturnsSQLCODE-20424withreasoncode4fortheCONTAINSandSCOREfunctions.
TheSYSPROC.
SYSTS_CREATEandSYSPROC.
SYSTS_UPDATEadministrativeproceduresalsofailwithSQLCODE-20424iftheserverhasnotbeenstarted.
ForthetextsearchserversthatarecontainedintheQSYS2.
SYSTEXTSERVERStable,TCP/IPnamesareresolved.
MultiplecallstotheSYSPROC.
SYSTS_STARTstoredprocedurearenotconsideredanerror.
ThisprocessallowsyoutoverifytheaddressresolutionintheQSYS2.
SYSTEXTSERVERStable.
Iftheserverisalocalserver,thenthisstoredprocedurecallstartstheserverifitisnotalreadystarted.
Iftheserverisaremoteserver,theprocedurecallverifiesthattheserverisactive,butdoesnotactuallystarttheserver.
PrerequisitesBeforeyoucalltheSYSPROC.
SYSTS_STARTstoredprocedure,verifythattheQSYS2.
SYSTEXTSERVERStablecontainsatleastoneentry.
AuthorizationTheuserIDunderwhichthisstoredprocedureisinvokedmusthavethefollowingprivileges:v*EXECUTEauthorityontheprocedurevSELECTandUPDATEprivilegeontheSYSTXTSRVRStable.
v*EXECUTEauthorityontheQSYS2libraryoftheSYSTXTSRVRSfile.
v*JOBCTLauthorityorQIBM_DB_SQLADMsecurityspecialfunctionusage.
ForinformationaboutthesystemauthoritiescorrespondingtoSQLprivileges,seeGRANT(TableorViewPrivileges).
SyntaxSYSPROC.
SYSTS_START()serveridaliasnameTheschemaqualifierisSYSPROC.
ParameterserveridoraliasnameSpecifiestheidentifieroftheservertobestarted.
Aserveridorserveraliasnameisastring.
Ifnoidentifierisprovided,thedefaultistostartallservers.
TheidentifierstringmusteitherbeavalidserveridthatexistsintheSERVERIDcolumn,oravalidserveraliasnamethatexistsintheALIASNAMEcolumnoftheQSYS2.
SYSTEXTSERVERStable.
Iftheidentifiercanbeconvertedtoanintegervalue,itisinterpretedasaserverid.
Iftheidentifiercannotbeconvertedtoanintegervalue,itisinterpretedasaserveraliasname.
ThedatatypeofthisparameterisVARCHAR(128).
Note:Onlytheserversthatareidentifiedasproductionserversarestartedifnovalueisspecifiedforserveridoraliasname.
ProductionserversareidentifiedbytheparameterSERVERCLASS=0intheQSYS2.
SYSTEXTSERVERStable.
Anytestserversmustbestartedbyspecifyingtheserveridoraliasnamethatisassociatedwiththetestserver.
Tostartallproductionservers:CALLSYSPROC.
SYSTS_START().
OmniFindTextSearchServer15|||||||||||||||||||||||||||||||||||||||||||||||||||||||||TostartaserverwithanIDof1:CALLSYSPROC.
SYSTS_START(1)Tostartaserverwithanaliasnameof"LOCAL_SERVER":CALLSYSPROC.
SYSTS_START('LOCAL_SERVER')Tostartaserverwithanaliasnameof"local_server":CALLSYSPROC.
SYSTS_START('"local_server"')Relatedconcepts:"Serveraliasname"onpage10Youcanuseaserveraliasnametoassignameaningfulnametoaserver.
Relatedtasks:"StarttheOmniFindTextSearchServerforDB2fori"onpage59YoucanstarttheOmniFindTextSearchServerforDB2foribycallingSYSPROC.
SYSTS_START.
Relatedreference:"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
SYSPROC.
SYSTS_STOPYoucancalltheSYSPROC.
SYSTS_STOPstoredproceduretostopDB2textsearchfunctions.
ThisstoredproceduresetstheSERVERSTATUSvalueinthecatalogQSYS2.
SYSTEXTSERVERSto1(stopped).
Afterthisstoredprocedurehascompleted,SQLqueriesthatusetheCONTAINSorSCOREfunctionsoradministrationstoredproceduresusedforindexmaintenancereturnafailurewithouttryingtocontactatextsearchserver.
Note:AdministrativeproceduresandSQLqueriesusingtheCONTAINSorSCOREbuilt-infunctionsthatwererunningwhenSYSTS_STOPwasinvokedareallowedtocomplete.
Changestothebased-ontableoftheindexcontinuetobelogged,evenwhentheserverisstopped.
However,scheduledupdatesoftheindexdonotoccuruntilSYSPROC.
SYSTS_STARThasbeeninvoked.
AuthorizationTheuserIDunderwhichthisstoredprocedureisinvokedmusthavethefollowingprivileges:v*EXECUTEauthorityontheprocedurevSELECTandUPDATEprivilegesontheSYSTEXTSERVERStable.
v*EXECUTEauthorityontheQSYS2libraryoftheSYSTEXTSERVERSfile.
v*JOBCTLauthorityorQIBM_DB_SQLADMsecurityspecialfunctionusage.
ForinformationaboutthesystemauthoritiescorrespondingtoSQLprivileges,seeGRANT(TableorViewPrivileges).
16IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||SyntaxSYSTS_STOP()serveridaliasnameTheschemaqualifierisSYSPROC.
ParameterserveridoraliasnameSpecifiestheidentifieroftheservertobestopped.
Aserveridorserveraliasnameisastring.
Ifnoidentifierisprovided,thedefaultistostopallservers.
TheidentifierstringmusteitherbeavalidserveridthatexistsintheSERVERIDcolumn,oravalidserveraliasnamethatexistsintheALIASNAMEcolumnoftheQSYS2.
SYSTEXTSERVERStable.
Iftheidentifiercanbeconvertedtoanintegervalue,itisinterpretedasaserverid.
Iftheidentifiercannotbeconvertedtoanintegervalue,itisinterpretedasaserveraliasname.
ThedatatypeofthisparameterisVARCHAR(128).
Tostopallproductionservers:CALLSYSPROC.
SYSTS_STOP().
TostopaserverwithanIDof1:CALLSYSPROC.
SYSTS_STOP(1)Tostopaserverwithanaliasnameof"LOCAL_SERVER":CALLSYSPROC.
SYSTS_STOP('LOCAL_SERVER')Tostopaserverwithanaliasnameof"local_server":CALLSYSPROC.
SYSTS_STOP('"local_server"')Relatedconcepts:"Serveraliasname"onpage10Youcanuseaserveraliasnametoassignameaningfulnametoaserver.
Relatedreference:"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
SYSPROC.
SYSTS_CREATEYoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
OmniFindTextSearchServer17||||||||||||||||||||||||||||||||||||||||||||||||||||||||||ThetextsearchindexiscreatedononeofthetextsearchserversthatislistedintheQSYS2.
SYSTEXTSERVERStable.
ThetextsearchindexisnotupdatedsynchronouslywhentheDB2tableisupdated.
Instead,alogofchangestotheDB2tablecolumniscapturedbytriggersandplacedinastagingtable.
Note:Thisstoredprocedureonlydefinesthetextsearchindex.
ThetextsearchindexdoesnotcontainanydatauntilafterthefirstinvocationoftheSYSPROC.
SYSTS_UPDATEstoredprocedureforthenewtextsearchindex.
Youcreatethetextsearchindexafterthetableisinitiallypopulated.
Bycreatingthetextsearchindexafterthetableisinitiallypopulated,youavoidthefiringofchangetriggersbeforeaninitialindexupdate.
PrerequisitesBeforetheSYSPROC.
SYSTS_CREATEstoredprocedurecall,verifythefollowingprerequisites:vDB2textsearchfunctionswerestartedbyinvokingtheSYSPROC.
SYSTS_STARTstoredprocedureandatleastonetextsearchserverisrunning.
vThetableincludesacolumnthatisdefinedasprimarykey,uniqueindex,orROWID.
vTheQSYS2.
SYSTEXTSERVERStablecontainsatleastoneentry.
AuthorizationTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vTheprivilegetocreateintheschema.
Formoreinformation,seeAuthorization,privilegesandobjectownership.
vAdministrativeauthorityTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vThefollowingsystemauthorities:–*USEtotheCreateLogicalFile(CRTLF)command–*CHANGEtothedatadictionaryifthelibraryintowhichthetextindexiscreatedisanSQLschemawithadatadictionaryvAdministrativeauthorityTheprivilegesheldbytheauthorizationIDofthestatementmustalsoincludeatleastoneofthefollowingprivileges:vForthereferencedtable:–TheINDEXprivilegeonthetable–Thesystemauthority*EXECUTEonthelibrarycontainingthetablevAdministrativeauthorityvIfSQLnamesarespecified,andauserprofileexiststhathasthesamenameasthelibraryintowhichthetextindexiscreated,andthatnameisdifferentfromtheauthorizationIDofthestatement,thentheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:–Thesystemauthority*ADDtotheuserprofilewiththatname–AdministrativeauthorityIfadistincttypeisreferenced,theprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vForeachdistincttypeidentifiedinthestatement:18IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||–TheUSAGEprivilegeonthedistincttype,and–Thesystemauthority*EXECUTEonthelibrarycontainingthedistincttypevAdministrativeauthorityForinformationaboutthesystemauthoritiescorrespondingtoSQLprivileges,seeGRANT(TableorViewPrivileges).
SyntaxSYSTS_CREATE(indexSchemanull,indexName,textSource,options)TheschemaqualifierisSYSPROC.
ParametersindexSchemaIdentifiestheschemaofthetextsearchindex.
Ifthisparameterisnull,thevalueoftheCURRENTSCHEMAspecialregisterfortheinvokerisused.
ThisvaluemustbeavalidSQLname.
Note:EnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywordsthatcanbeused.
ThedatatypeofthisparameterisVARCHAR(128).
indexNameIdentifiesthenameofthetextsearchindex.
ThenameofthetextsearchindexwiththeindexschemauniquelyidentifiesthetextsearchindexintheDB2subsystem.
Youmustspecifyanon-nullvalueforthisparameter.
ThisvaluemustbeavalidSQLname.
Note:EnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywordsthatcanbeused.
ThedatatypeforthisparameterisVARCHAR(128).
textSourceIdentifiesthetableandcolumnspecificationforthedocumenttextsource.
Thisparametercanincludeuser-definedfunctions.
Youmustspecifyanon-nullvalueforthisparameter.
ThedatatypeforthisparameterisVARCHAR(1024).
textSource:tableSchema.
tableName(text-column-name)function-name(text-column-name)function-schema.
tableSchemaIdentifiestheschemaofthetableonwhichthetextsearchindexiscreated.
Note:EnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywordsthatcanbeused.
tableNameIdentifiesthenameofthetexttablethatcontainsthecolumnthattheexternaltextsearchindexiscreatedon.
Notes:OmniFindTextSearchServer19||||||||||vViewsandlogicalfilesarenotsupported.
vAnaliasmustpointtoatableorasinglememberofaphysicalfile.
vEnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywords.
text-column-nameIdentifiesthenameofthecolumnthatcontainsthetextthatisusedforcreatingthetextsearchindex.
ThiscolumnmustbeoftypeCHAR,CHARFORBITDATA,BINARY,VARCHAR,VARCHARFORBITDATA,VARBINARY,CLOB,DBCLOB,BLOB,XML,GRAPHIC,orVARGRAPHIC.
Ifthedatatypeisnotoneofthesedatatypes,youcanspecifyanexternalfunctionthatreturnsasupporteddatatype.
Notes:vOnlyonetextsearchindexisallowedforacolumn.
Ifatextsearchindexexistsforthecolumn,SQLCODE-20427isreturned.
vEnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywords.
function-schema.
function-nameIdentifiestheschemaandthenameofabuilt-inoruser-definedfunction.
Thefunctioncanbeusedtomodifyatextdocumentstoredinthecolumn.
Thefunctioncanalsobeusedtoaccesstextdocumentsinacolumnthatisnotofasupporteddatatype.
Orthefunctioncanbeusedtoaccessadocumentthatisstoredelsewhere.
Thefunctionhasoneinputparameterforthetextcolumndatatype.
Forexample,anintegerthatservesasaforeignkeytothedocumentcontentinanothertable.
ThefunctionreturnsavalueofoneoftheOmniFindTextSearchforDB2forisupporteddatatypes.
Thefunctiontransformsthetextcolumncontenttotheindexeddocumentcontent.
Notes:vCastfunctionsandfunctionswithmorethanoneargumentarenotallowed.
vEnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywordsthatcanbeused.
optionsAcharacterstringthatspecifiesthevariousoptionsthatareavailableforthisstoredprocedure.
ThedatatypeforthisparameterisVARCHAR(32000).
options:text-default-informationupdate-characteristicsindex-configuration-optionstext-default-information:CCSIDccsidLANGUAGElanguageFORMATformattext-default-informationSpecifiesthecodedcharactersetidentifierusedwhenindexingbinarytextdocuments.
Alsospecifiesthelanguagethatisusedwhenprocessingdocuments,andtheformatoftextdocumentsinthecolumn.
CCSIDccsidSpecifiesthecodedcharactersetidentifierthatisusedforatextsearchindexinacolumnwithabinarydatatype.
Thedefaultvalueis1208(UTF-8)andistakenfromthe20IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||QSYS2.
SYSTEXTDEFAULTStable.
AlltheCCSIDsthataresupportedforconversiontoUTF-8byIBMiconversionservicesareallowedforthisparameter.
Thisparameterisignoredforatextsearchindexinacolumnwithanon-binarydatatype.
TextcolumnsinherittheCCSIDfromthetablespecification.
TheccsidvalueisignoredwhentheformatvalueissettoINSO.
LANGUAGElanguageSpecifiesthelanguagethatOmniFindTextSearchServerforDB2foriusesforthelinguisticprocessingoftextdocuments.
Thedefaultvalueisen_US(English).
IfyouspecifythevalueforthisparameterasAUTO,OmniFindTextSearchServerforDB2foritriestodeterminethelanguage.
Important:IfthelanguageofthedocumentsisnotEnglish,donotusethedefaultvalueofen_US.
Changethevaluetothelanguageofthedocuments;otherwise,linguisticprocessingdoesnotworkasexpected.
FORMATformatIdentifiestheformatoftextdocumentsinthecolumn,suchasHTML.
TheOmniFindTextSearchServerforDB2forineedstoknowtheformat,orcontenttype,ofthetextdocumentsthatyouintendtoindexandsearch.
Ifyoudonotspecifytheformatparameter,thedefaultvalueistakenfromtheFORMATcolumnintheQSYS2.
SYSTEXTDEFAULTStable.
ThesupportedformatvaluesareTEXT,HTML,XML,andINSO.
TheformatvalueINSOallowsOmniFindTextSearchServerforDB2foritodeterminetheformat.
Inthiscase,theccsidvalueisignored.
IftheOmniFindTextSearchServerforDB2foricannotdeterminethedocumentformat,thenadocumenterrorisnotedinthejoblogduringprocessingbytheSYSPROC.
SYSTS_UPDATEstoredprocedure.
Note:IfyoudonotspecifytheformatparameterwhilecreatingtheindexoveranXMLdatatypecolumn,thedefaultvalueisXML.
IfyouspecifytheformatparameterasTEXTorINSO,theXMLsearchcapabilityisnotavailableoverthisindex.
Inaddition,awarningmessageappearsinthejoblog.
update-characteristics:NONEUPDATEFREQUENCYUPDATEMINIMUMminchangesupdate-characteristicsSpecifiesthefrequencyofautomaticupdatestothetextsearchindex.
Alsospecifiestheminimumnumberofchangestotextdocumentsbeforethetextsearchindexisupdatedincrementallyatthespecifiedtime.
UPDATEFREQUENCYupdate-frequencySpecifieswhentomakeautomaticupdatestothetextsearchindex.
ThedefaultvalueisNONE.
Thisoptionmightbeusefulforatextcolumninwhichtherearenofurtherchanges.
Theformatoftheupdate-frequencyoptionsupportstwodifferentformats.
update-frequency(Format1):NONED(*)H(*)M(*),,,0.
.
60.
.
230.
.
590.
.
60.
.
230.
.
59OmniFindTextSearchServer21||||NONEIfNONEisspecified,thennofurtherindexupdatesaremade.
Theupdatemustbestartedmanually.
Thisoptionmightbeusefulforatextcolumninwhichnofurtherchangesareplanned.
DSpecifiesthedayordaysoftheweekwhentheindexisupdated.
Anasterisk(*)specifiesalldays.
0specifiesSunday.
HSpecifiesthehourorhourswhentheindexisupdated.
Anasterisk(*)specifiesallhours.
MSpecifiestheminuteorminuteswhentheindexisupdated.
Anasterisk(*)cannotbespecified.
Theminimumupdatefrequencyis5minutes.
Example:Thisexamplespecifiesthattheindexupdateistorunevery30minutes.
UPDATEFREQUENCYD(*)H(*)M(0,30)update-frequency(Format2,chronological):Theformatoftheupdate-frequency(chronological)optionisalistofthefivevaluesseparatedbyablankspace.
Thefivevaluesrepresenttheminutes,hours,daysofthemonth,monthsoftheyear,anddaysoftheweekbeginningwithSunday.
Ifyouspecifyanintervalofvaluesoranasterisk(*),youcanspecifyastepvaluebyusingaforwardslash(/)attheendofthedefinedinterval.
Example:Thisexamplespecifiesthattheindexupdateistoruneveryquarterhour(0,15,30,45)ontheevenhoursbetween8a.
m.
and6:45p.
m.
(8-18/2isequivalentto8,10,12,14,16,18),fromMondaytoFridayeverymonthoftheyear(**1-5).
0,15,30,458-18/2**1-5minuteSpecifiestheminutesofthehourwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foranintervalofevery5minutes,oryoucanspecifyaninteger0-59.
Youcannotrepeatvalues.
Theminimumupdatefrequencyis5minutes.
Avalueof1,4,8isnotvalid.
update-frequency(minute):*/0.
.
.
59,0.
.
.
59-0.
.
.
59/0.
.
.
590.
.
.
59hourSpecifiesthehoursofthedaywhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryhour,oryoucanspecifyaninteger0-23.
Youcannotrepeatvalues.
22IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||update-frequency(hour):*/0.
.
.
23,0.
.
.
23-0.
.
.
23/0.
.
.
230.
.
.
23dayOfMonthSpecifiesthedaysofthemonthwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryday,oryoucanspecifyaninteger1-31.
Youcannotrepeatvalues.
update-frequency(dayOfMonth):*/1.
.
.
31,1.
.
.
31-1.
.
.
31/1.
.
.
311.
.
.
31monthOfYearSpecifiesthemonthsoftheyearwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreverymonth,oryoucanspecifyaninteger1-12.
Youcannotrepeatvalues.
update-frequency(monthOfYear):*/1.
.
.
12,1.
.
.
12-1.
.
.
12/1.
.
.
121.
.
.
12dayOfWeekSpecifiesthedaysoftheweekwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryday,oryoucanspecifyaninteger0-7.
Both0and7arevalidvaluesforSunday.
Youcannotrepeatvalues.
update-frequency(dayOfWeek):*/0.
.
.
7,0.
.
.
7-0.
.
.
7/0.
.
.
70.
.
.
7UPDATEMINIMUMminchangesSpecifiestheminimumnumberofrecordchangesmadetotheunderlyingtablebeforethetextsearchindexisupdatedincrementallyatthetimespecifiedintheupdate-frequencyoption.
OmniFindTextSearchServer23|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||Thevaluemustbeaninteger1-2147483647.
ThedefaultvalueistakenfromtheUPDATEMINIMUMcolumnintheQSYS2.
SYSTEXTDEFAULTStable.
Thisoptionisignoredwhenyouupdatethetextsearchindex,unlessyouspecifytheUSINGUPDATEMINIMUMoptionintheSYSPROC.
SYSTS_UPDATEstoredprocedure.
index-configuration-options:INDEXCONFIGURATION(,optionvalue)index-configuration-optionsSpecifiesadditionalindex-specificvaluesasoptionvaluepairs.
Youmustenclosestringvaluesinsinglequotationmarks.
Asinglequotationmarkwithinastringvaluemustberepresentedbytwoconsecutivesinglequotationmarks.
CJKSEGMENTATIONSpecifiesthesegmentationmethodtousewhenyouindexdocumentsforCJK(Chinese,Japanese,Korean)languages.
ThesupportedvaluesareMORPHOLOGICALandNGRAM.
IftheCJKSEGMENTATIONvalueisnotspecified,thedefaultvalueisused.
ThedefaultvalueisspecifiedbytheDEFAULTNAMEvalueintheQSYS2.
SYSTEXTDEFAULTStable.
COMMENTSpecifiesacommentthatisstoredintheREMARKScolumnoftheQSYS2.
SYSTEXTINDEXESadministrationtableandasthedescriptionoftheOmniFindTextSearchServerforDB2foricollection.
Thevalueforthisoptionisastringvaluethatislessthanorequalto512bytes.
IGNOREEMPTYDOCSSpecifieswhethertorepresentemptydocumentsinthetextsearchindex.
Emptydocumentsarethosedocumentswithanemptystringoranullvalue.
Thesupportedvaluesforthisoptionare0(zero)and1.
Thedefaultvalueis1.
Ifthisoptionissetto1,emptydocumentsarenotrepresentedinthetextsearchindex.
Ifyouusethisoptionandchangethedocumentcontenttoempty,thenextincrementalupdatedeletesthedocumentsfromthetextsearchindex.
KEYCOLUMNSpecifiesthenameofauniquecolumntobeusedasthekeycolumninthetextindex.
Thekeycolumnisusedtoassociatedatainthetextindextoadocumentorrowinthebasetable.
Thespecifiedcolumnmusthaveaprimarykeyconstraintoruniqueindex.
IfKEYCOLUMNisnotspecified,theROWIDcolumnfromthetableisused,ifoneexists.
Otherwise,theprimarykeydefinedonthetableisused.
SERVERSpecifiestheIDoraliasnameoftheservertobeusedtostorethetextsearchindex.
IfanIDisused,thevalueisanintegerthatmustexistintheSERVERIDcolumnoftheQSYS2.
SYSTEXTSERVERScatalog.
Ifanaliasnameisused,thevalueisastringthatmustexistintheALIASNAMEcolumnoftheQSYS2.
SYSTEXTSERVERScatalog.
IfSERVERisnotspecified,thedefaultistoselecttheserverwiththefewesttextsearchindexesfromtheserversintheQSYS2.
SYSTEXTSERVERStablewhereparameterSERVERSTATUSissetto0(zero),whichmeansthattheserverisavailable.
UPDATEAUTOCOMMITSpecifieshowoftenacommitoperationisperformedwhenfetchingdocumentsduringanindexupdate.
Avalueof0(zero)meansthatacommitoperationoccursonlyattheendofprocessing.
Thevaluemustbeanintegerbetween0(zero)and2147483647.
Thedefaultvalueis100.
24IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||Performancetip:ThevalueofUPDATEAUTOCOMMITcanhaveasubstantialimpactontheperformanceofindexupdates.
Thecommitoperationthattakesplaceatthespecifiedintervalensuresaconsistentcheckpointfromwhichtorestarttheindexupdate,ifitisinterrupted.
However,thecommitalsotemporarilysuspendstheupdateprocess.
IncreasingtheUPDATEAUTOCOMMITvalue(orsettingitto0)cansubstantiallyimprovetheupdateperformance,especiallytheinitialupdate.
Thevalueyouspecifymustbalancetheneedforperformancewiththeneedforrecoverability,basedonthefrequencyoftheindexupdates.
DefaultvaluesfortheoptionsparameterWhenyouinstallOmniFindTextSearchforDB2fori,theQSYS2.
SYSTEXTDEFAULTStableiscreatedandpopulatedwithdefaultvaluesfortheoptionsparameteroftheSYSPROC.
SYSTS_CREATEstoredprocedure.
Thefollowingtableliststheoptions,defaultvalues,anddescriptionsoftheoptions.
Table2.
DefaultvaluesfortheoptionsparameterOptionDefaultvalueDescriptionCCSID1208Specifiesthecodedcharactersetidentifierthatisusedwhenbinarytextdocumentsareindexed.
CJKSEGMENTATIONNGRAMSpecifiesthesegmentationmethodtousewhenyouindexdocumentsforCJK(Chinese,Japanese,Korean)languages.
LANGUAGEen_USSpecifiesthelanguageusedtoprocesstextdocuments.
FORMATTEXTIdentifiestheformatoftextdocumentsinthecolumn.
ThedefaultformatisplaintextunlessthedatatypeisXML.
UPDATEFREQUENCYNONEIndicatesthatnoautomaticupdatesarescheduled.
UPDATEMINIMUM1Ifatleastonedocumentchangedsincethelastindexupdate,theSYSPROC.
SYSTS_UPDATEstoredprocedurestartsprocessing.
IGNOREEMPTYDOCS1Specifiesthatemptydocuments(documentswithanemptystringoranullvalue)arenotrepresentedinthetextsearchindex.
Themetadatafieldsforthesedocumentsarenotavailableforsearch.
UPDATEAUTOCOMMIT100Specifieshowoftenacommitoperationisperformedwhendocumentsarefetchedduringanindexupdate.
MINIMUMUPDATEINTERVAL5SpecifiestheintervalsfortheUPDATEFREQUENCYoption.
Intervalscannotbeshorterthan5minutes.
USEREXITTHREADS0ReservedRelatedconcepts:"Supporteddocumentformats"onpage6Thetextcolumndatacanbeplaintext,anHTMLdocument,anXMLdocument,oranydocumentthatisrecognizedbythesearchengine.
"Supporteddatatypes"onpage7Thedatainthetextcolumnsthatyouwanttoindexandsearchcanbeeitherbinarydataorcharacterdata.
Relatedreference:"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
OmniFindTextSearchServer25|||||||||||||||||||||||||||||||||||||||||||||||||||||||"QSYS2.
SYSTEXTINDEXESadministrationtable"onpage97YoucanseeinformationabouteachtextsearchindexintheQSYS2.
SYSTEXTINDEXESadministrationtable.
Eachtextsearchindexhasaname,schemaname,andanassociatedcollectionnameonthetextsearchserver.
"QSYS2.
SYSTEXTDEFAULTSadministrationtable"onpage96YoucanseethedefaultparametersandvaluesintheQSYS2.
SYSTEXTDEFAULTSadministrationtable.
ThistableiscreatedwhenyouinstallOmniFindTextSearchforDB2fori.
"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"Supportedlanguages"onpage8Youcanspecifythattextdocumentsbeprocessedusingaspecificlanguage.
SYSPROC.
SYSTS_ALTERYoucancalltheSYSPROC.
SYSTS_ALTERstoredproceduretomodifyattributesofanindexthatwascreatedbySYSPROC.
SYSTS_CREATE.
Onlyattributesexplicitlyspecifiedonthisprocedurearechanged.
Allotherattributesoftheindexremainunchanged.
Thisisusefulifyouneedtochangetheattributesoftheindex,suchastheupdatefrequency,aftertheindexhasalreadybeencreated.
PrerequisitesBeforeyoucalltheSYSPROC.
SYSTS_ALTERstoredprocedure,verifythefollowingprerequisite:vThetextsearchindexwascreated(byinvocationoftheSYSPROC.
SYSTS_CREATEstoredprocedure).
AuthorizationTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vTheprivilegetocreateintheschema.
Formoreinformation,seeAuthorization,privilegesandobjectownership.
vAdministrativeauthorityTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vThefollowingsystemauthorities:–*USEtotheCreateLogicalFile(CRTLF)command–*CHANGEtothedatadictionaryifthelibraryintowhichthetextindexiscreatedisanSQLschemawithadatadictionaryvAdministrativeauthorityTheprivilegesheldbytheauthorizationIDofthestatementmustalsoincludeatleastoneofthefollowingprivileges:vForthereferencedtable:–TheINDEXprivilegeonthetable–Thesystemauthority*EXECUTEonthelibrarycontainingthetablevAdministrativeauthority26IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||vIfSQLnamesarespecified,andauserprofileexiststhathasthesamenameasthelibraryintowhichthetextindexiscreated,andthatnameisdifferentfromtheauthorizationIDofthestatement,thentheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingauthorities:–Systemauthority*ADDtotheuserprofilewiththatname–AdministrativeauthorityIfadistincttypeisreferenced,theprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vForeachdistincttypeidentifiedinthestatement:–TheUSAGEprivilegeonthedistincttype,and–Thesystemauthority*EXECUTEonthelibrarycontainingthedistincttypevAdministrativeauthorityForinformationaboutthesystemauthoritiescorrespondingtoSQLprivileges,seeGRANT(TableorViewPrivileges).
SyntaxSYSTS_ALTER(indexSchema,indexName,options)TheschemaqualifierisSYSPROC.
ParametersindexSchemaIdentifiestheschemaofthetextsearchindex.
Ifthisparameterisnull,thevalueoftheCURRENTSCHEMAspecialregisterfortheinvokerisused.
ThedatatypeofthisparameterisVARCHAR(128).
indexNameIdentifiesthenameofthetextsearchindex.
ThenameofthetextsearchindextogetherwiththeindexschemauniquelyidentifiesthetextsearchindexintheDB2subsystem.
Youmustspecifyanon-nullvalueforthisparameter.
ThedatatypeofthisparameterisVARCHAR(128).
optionsAcharacterstringthatspecifiesthevariousoptionsthatareavailableforthisstoredprocedure.
ThedatatypeofthisparameterisVARCHAR(32000).
TheparametercannotbeNULL.
options:rename-function-informationupdate-characteristicsindex-configuration-optionsrename-function-information:RENAMEFUNCTIONfunction-namefunction-schema.
Specifiestheuser-definedfunctiontoberenamed.
OmniFindTextSearchServer27|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||function-schema.
function-nameSpecifiestheschemaandnameofauser-definedfunction.
Thisoptionisusedtochangeafunctionthatwasspecifiedwhilecreatinganindex.
Ifthefunctionischanged,SYSTS_UPDATEusesthenewfunctiontoindexthetextcolumn.
Ifthefunctionwaschanged,SYSTS_UPDATEdoesnotchangetheexistingdataoftheindex.
Onlythenewchangeddataafterthelastupdateisprocessed.
update-characteristics:NONEUPDATEFREQUENCYupdate-frequencyUPDATEMINIMUMminchangesupdate-characteristicsSpecifiesthefrequencyofautomaticupdatestothetextsearchindex.
Alsospecifiestheminimumnumberofchangestotextdocumentsbeforethetextsearchindexisupdatedincrementallyatthespecifiedtime.
UPDATEFREQUENCYupdate-frequencySpecifieswhentomakeautomaticupdatestothetextsearchindex.
ThedefaultvalueisNONE.
Thisoptionmightbeusefulforatextcolumninwhichtherearenofurtherchanges.
Theformatoftheupdate-frequencyoptionsupportstwodifferentformats.
update-frequency(Format1):NONED(*)H(*)M(*),,,0.
.
60.
.
230.
.
590.
.
60.
.
230.
.
59NONEIfNONEisspecified,thennofurtherindexupdatesaremade.
Theupdatemustbestartedmanually.
Thisoptionmightbeusefulforatextcolumninwhichnofurtherchangesareplanned.
DSpecifiesthedayordaysoftheweekwhentheindexisupdated.
Anasterisk(*)specifiesalldays.
0specifiesSunday.
HSpecifiesthehourorhourswhentheindexisupdated.
Anasterisk(*)specifiesallhours.
MSpecifiestheminuteorminuteswhentheindexisupdated.
Anasterisk(*)cannotbespecified.
Theminimumupdatefrequencyis5minutes.
Example:Thisexamplespecifiesthattheindexupdateistorunevery30minutes.
UPDATEFREQUENCYD(*)H(*)M(0,30)update-frequency(Format2,chronological):Theformatoftheupdate-frequency(chronological)optionisalistofthefivevaluesseparatedbyablankspace.
Thefivevaluesrepresenttheminutes,hours,daysofthemonth,monthsoftheyear,anddaysoftheweekbeginningwithSunday.
28IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||Ifyouspecifyanintervalofvaluesoranasterisk(*),youcanspecifyastepvaluebyusingaforwardslash(/)attheendofthedefinedinterval.
Example:Thisexamplespecifiesthattheindexupdateistoruneveryquarterhour(0,15,30,45)ontheevenhoursbetween8a.
m.
and6:45p.
m.
(8-18/2isequivalentto8,10,12,14,16,18),fromMondaytoFridayeverymonthoftheyear(**1-5).
0,15,30,458-18/2**1-5minuteSpecifiestheminutesofthehourwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foranintervalofevery5minutes,oryoucanspecifyaninteger0-59.
Youcannotrepeatvalues.
Theminimumupdatefrequencyis5minutes.
Avalueof1,4,or8isnotallowed.
update-frequency(minute):*/0.
.
.
59,0.
.
.
59-0.
.
.
59/0.
.
.
590.
.
.
59hourSpecifiesthehoursofthedaywhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryhour,oryoucanspecifyaninteger0-23.
Youcannotrepeatvalues.
update-frequency(hour):*/0.
.
.
23,0.
.
.
23-0.
.
.
23/0.
.
.
230.
.
.
23dayOfMonthSpecifiesthedaysofthemonthwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryday,oryoucanspecifyaninteger1-31.
Youcannotrepeatvalues.
update-frequency(dayOfMonth):*/1.
.
.
31,1.
.
.
31-1.
.
.
31/1.
.
.
311.
.
.
31monthOfYearSpecifiesthemonthsoftheyearwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreverymonth,oryoucanspecifyaninteger1-12.
Youcannotrepeatvalues.
OmniFindTextSearchServer29||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||update-frequency(monthOfYear):*/1.
.
.
12,1.
.
.
12-1.
.
.
12/1.
.
.
121.
.
.
12dayOfWeekSpecifiesthedaysoftheweekwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryday,oryoucanspecifyaninteger0-7.
Both0and7arevalidvaluesforSunday.
Youcannotrepeatvalues.
update-frequency(dayOfWeek):*/0.
.
.
7,0.
.
.
7-0.
.
.
7/0.
.
.
70.
.
.
7UPDATEMINIMUMminchangesSpecifiestheminimumnumberofrecordchangesmadetotheunderlyingtablebeforethetextsearchindexisupdatedincrementallyatthetimespecifiedintheupdate-frequencyoption.
Thevaluemustbeaninteger1-2147483647.
index-configuration-options:INDEXCONFIGURATION(,optionvalue)COMMENTSpecifiesacommentthatisstoredintheREMARKScolumnoftheQSYS2.
SYSTEXTINDEXESadministrationtableandasthedescriptionoftheOmniFindTextSearchServerforDB2foricollection.
Thevalueforthisoptionisastringvaluethatislessthanorequalto512bytes.
UPDATEAUTOCOMMITSpecifieshowoftenacommitoperationisperformedwhenfetchingdocumentsduringanindexupdate.
Avalueof0(zero)meansthatacommitoperationoccursonlyattheendofprocessing.
Thevaluemustbeanintegerbetween0(zero)and2147483647.
Performancetip:ThevalueofUPDATEAUTOCOMMITcanhaveasubstantialimpactontheperformanceofindexupdates.
Thecommitoperationthattakesplaceatthespecifiedintervalensuresaconsistentcheckpointfromwhichtorestarttheindexupdate,ifitisinterrupted.
However,thecommitalsotemporarilysuspendstheupdateprocess.
IncreasingtheUPDATEAUTOCOMMITvalue(orsettingitto0)cansubstantiallyimprovetheupdateperformance,especiallytheinitialupdate.
Thevalueyouspecifymustbalancetheneedforperformancewiththeneedforrecoverability,basedonthefrequencyoftheindexupdates.
Relatedreference:30IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"QSYS2.
SYSTEXTINDEXESadministrationtable"onpage97YoucanseeinformationabouteachtextsearchindexintheQSYS2.
SYSTEXTINDEXESadministrationtable.
Eachtextsearchindexhasaname,schemaname,andanassociatedcollectionnameonthetextsearchserver.
SYSPROC.
SYSTS_DROPYoucancalltheSYSPROC.
SYSTS_DROPstoredproceduretodropatextsearchindexthatwasdefinedbyusingtheSYSPROC.
SYSTS_CREATEstoredprocedure.
ItisrecommendedthatyoudropatextsearchindexbyusingtheSYSPROC.
SYSTS_DROPstoredprocedurebeforedroppingthetable.
Droppingtheviewrepresentingthetextsearchindex,evenastheresultofaDROPTABLECASCADEstatement,attemptstodropthetextsearchindex.
However,becausethetextsearchindexcannotbedroppedundercommitmentcontrol,theSQLviewcannotbedroppedundercommitmentcontrol.
Ifthetextsearchservercannotbereached,thecollectionontheservermightbecomeorphaned.
Ifthathappens,thecollectionneedstobedeletedmanually.
Whentheserverisavailableagain,usetheOmniFindTextSearchServerforDB2foriadministrationtooltodeletethecollectionontheserver.
In"Administrationtools"onpage65,youcanfindinformationaboutthetoolstoidentifyorphanedindexesandthestoredprocedureSTSPROC.
SYSTS_REMOVEtodeleteorphanedindexes.
PrerequisitesBeforeyoucalltheSYSPROC.
SYSTS_DROPstoredprocedure,verifythefollowingprerequisites:vDB2textsearchfunctionswerestartedbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
vThetextsearchindexwascreated(byinvocationoftheSYSPROC.
SYSTS_CREATEstoredprocedure).
vEnsurethatthefollowingstoredproceduresarenotrunningforthetextsearchindexthatyouwanttodrop:SYSPROC.
SYSTS_CREATE,SYSPROC.
SYSTS_UPDATE,andSYSPROC.
SYSTS_DROP.
AuthorizationTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vThefollowingsystemauthorities:–Thesystemauthoritiesof*OBJOPRand*OBJEXISTonthetextindextobedropped–Thesystemauthority*EXECUTEonthelibrarythatcontainsthetextindextobedroppedvAdministrativeauthorityForinformationaboutthesystemauthoritiescorrespondingtoSQLprivileges,seeGRANT(TableorViewPrivileges).
OmniFindTextSearchServer31|||||||||||SyntaxSYSTS_DROP(indexSchemanull,indexName)TheschemaqualifierisSYSPROC.
ParametersindexSchemaIdentifiestheschemaofthetextsearchindex.
Ifthisparameterisnull,thevalueoftheCURRENTSCHEMAspecialregisterfortheinvokerisused.
ThedatatypeofthisparameterisVARCHAR(128).
indexNameIdentifiesthenameofthetextsearchindex.
ThenameofthetextsearchindextogetherwiththeindexschemauniquelyidentifiesthetextsearchindexintheDB2subsystem.
Youmustspecifyanon-nullvalueforthisparameter.
ThedatatypeofthisparameterisVARCHAR(128).
Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"SYSPROC.
SYSTS_UPDATE"YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"SYSPROC.
SYSTS_REMOVE"onpage67YoucanremoveorphanedindexeswiththeSYSPROC.
SYSTS_REMOVESQLstoredprocedure.
SYSPROC.
SYSTS_UPDATEYoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
Becauseupdatingatextsearchindexisanextensiveoperation,thetextsearchindexisnotupdatedsynchronouslywhentheDB2tableisupdated.
Instead,changestotheDB2tablecolumnarecapturedbyatriggerandwrittentoastagingtable.
ThetextsearchindexisupdatedthenexttimetheSYSPROC.
SYSTS_UPDATEstoredprocedureisinvokedorwhentheUPDATEFREQUENCYoptionindicatesitisupdated.
Therefore,somesearchrequestsmightnotreflectrecentupdatestothetable.
ThisstoredprocedurereturnsonlyafteralltheupdateprocessingforthetextsearchindexontheOmniFindTextSearchServerforDB2foriiscompleted.
ThedurationdependsonthenumberofentriesinthestagingtableatthetimetheSYSTS_UPDATEwascalled.
Duringtheupdateprocess,thetextsearchindexremainssearchable.
Ifanissueoccurredwhileindexingadocumentfromthebasetable,thestagingtablecolumnwiththeTOBEDELETEDhasavaluesettoeitherE(error)orW(warning).
Youcanretrievetheserecordswithastoredprocedurethatincludesthefollowingquery:32IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||SELECTs.
TOBEDELETED,based_on_columnsFROMbased_on_tabletINNERJOINQSYS2.
stagingtablesON(QQQ_TEXTSEARCH_KEY(t.
k1,t.
k2,t.
k3,s.
KEYID)WHEREs.
TOBEDELETEDIN('E','W')Inthiscase,based_on_columnsisthecolumnlistthatyouneedtoseefromthebased_on_table.
based_on_tableisthetablebeingindexed.
stagingtableisthestagingtablelistedinthecatalogsforthetextsearchindex.
k1,k2,k3,.
.
.
isthelistofkeycolumnsintheprimarykey,rowID,oruniquekeythatisusedtobuildthetextsearchindex.
Onceyoucorrecttheerrorsforthosedocuments,runtheupdateagain.
Ifanindexupdateisrequestedatthesametimeanupdateisalreadyrunningforthatindex,anerrorisreturned.
Onlyoneupdateisallowedtorunatatimeforagivenindex.
PrerequisitesBeforecallingtheSYSPROC.
SYSTS_UPDATEstoredprocedure,verifythefollowingprerequisites:vThetextsearchindexwascreated(byinvocationoftheSYSPROC.
SYSTS_CREATEstoredprocedure).
vThefollowingstoredproceduresarenotrunningforthetextsearchindexthatyouwanttoupdate:SYSPROC.
SYSTS_CREATE,SYSPROC.
SYSTS_UPDATE,andSYSPROC.
SYSTS_DROP.
vThetextsearchserverthattheindexresidesonmusthavebeenstartedusingtheSYSTS_STARTstoredprocedure.
TheSERVERSTATUScolumninQSYS2.
SYSTEXTSERVERSmusthaveavalueof'0'(started).
AuthorizationTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vTheprivilegetocreateintheschema.
Formoreinformation,seeAuthorization,privilegesandobjectownership.
vAdministrativeauthorityTheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vThefollowingsystemauthorities:–*USEtotheCreateLogicalFile(CRTLF)command–*CHANGEtothedatadictionaryifthelibraryintowhichthetextindexiscreatedisanSQLschemawithadatadictionaryvAdministrativeauthorityTheprivilegesheldbytheauthorizationIDofthestatementmustalsoincludeatleastoneofthefollowingprivileges:vForthereferencedtable:–TheINDEXprivilegeonthetable–Thesystemauthority*EXECUTEonthelibrarycontainingthetablevAdministrativeauthorityvIfSQLnamesarespecified,andauserprofileexiststhathasthesamenameasthelibraryintowhichthetextindexiscreated,andthatnameisdifferentfromtheauthorizationIDofthestatement,thentheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:–Thesystemauthority*ADDtotheuserprofilewiththatname–AdministrativeauthorityOmniFindTextSearchServer33|||||||||||||||||||||||||||||||||||||||||||Ifadistincttypeisreferenced,theprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneofthefollowingprivileges:vForeachdistincttypeidentifiedinthestatement:–TheUSAGEprivilegeonthedistincttype,and–Thesystemauthority*EXECUTEonthelibrarycontainingthedistincttypevAdministrativeauthorityForinformationaboutthesystemauthoritiescorrespondingtoSQLprivileges,seeGRANT(TableorViewPrivileges).
SyntaxSYSTS_UPDATE(indexSchemanull,indexName,options)TheschemaqualifierisSYSPROC.
ParametersindexSchemaIdentifiestheschemaofthetextsearchindex.
Ifthisparameterisnull,thevalueoftheCURRENTSCHEMAspecialregisterfortheinvokerisused.
ThedatatypeofthisparameterisVARCHAR(128).
indexNameIdentifiesthenameofthetextsearchindex.
Thenameofthetextsearchindextogetherwiththeindexschemauniquelyidentifiesthefull-textindexintheDB2subsystem.
Youmustspecifyanon-nullvalueforthisparameter.
ThedatatypeforthisparameterisVARCHAR(128).
optionsAcharacterstringthatspecifiestheoptionthatisavailableforthisstoredprocedure.
TheavailableoptionisUSINGUPDATEMINIMUM.
ThisoptionusestheUSINGUPDATEMINIMUMsettingsthatyouspecifiedfortheSYSPROC.
SYSTS_CREATEstoredprocedure.
Itstartsanincrementalupdateonlyifthespecifiednumberofchangeswasreached.
Thedefaultistounconditionallystarttheupdateprocess.
USINGUPDATEMINIMUM:USINGUPDATEMINIMUMRelatedconcepts:"Documenttruncation"onpage14TheOmniFindTextSearchServerforDB2forilimitsthenumberofcharactersthatcanbeindexedforeachtextdocument.
Sometimesthischaracterlimitresultsinthetruncationoflargetextdocumentsinthetextsearchindex.
Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
34IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"SYSPROC.
SYSTS_DROP"onpage31YoucancalltheSYSPROC.
SYSTS_DROPstoredproceduretodropatextsearchindexthatwasdefinedbyusingtheSYSPROC.
SYSTS_CREATEstoredprocedure.
SearchwithatextsearchindexYoucanusetheCONTAINSfunctionandtheSCOREfunctioninanSQLstatementwithOMNIFIND.
CONTAINSandSCOREareusedtosearchatextsearchindexusingthesearchargumentcriteriathatyouspecify.
YoucanalsoindexandsearchXMLdocuments.
CONTAINSYoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
CONTAINS(column-name,search-argument,string-constant(1))Notes:1string-constantmustconformtotherulesforthesearch-argumentoptions.
search-argument-options:(1)QUERYLANGUAGE=valueRESULTLIMIT=valueOFFSYNONYM=ONNotes:1Thesameclausemustnotbespecifiedmorethanonce.
TheschemaisQSYS2.
column-nameSpecifiesaqualifiedorunqualifiednameofacolumnthathasatextsearchindexthatistobesearched.
ThecolumnmustexistinthetableorviewthatisidentifiedintheFROMclauseofthestatement.
Thecolumnofthetable,orthecolumnoftheunderlyingbasetableoftheview,musthaveanassociatedtextsearchindex(SQLSTATE38H12).
Theunderlyingexpressionofthecolumnofaviewmustbeasimplecolumnreferencetothecolumnofanunderlyingtable,directlyorthroughanothernestedview.
search-argumentSpecifiesanexpressionthatreturnsastringvaluecontainingthetermsusedinthesearch.
Theexpressionmustnotbeallblanksortheemptystring(SQLSTATE38H14).
Theactuallengthofthestringmustnotexceed32704bytes.
Thislengthmightbefurtherlimitedbywhatissupportedbythetextsearchserver(SQLSTATE38H10).
ThevalueisconvertedtoUnicodebeforeitisusedtosearchthetextsearchindex.
string-constantIdentifiesastringconstantthatspecifiesthesearchargumentoptionsthatareineffectforthefunction.
OmniFindTextSearchServer35|||||||||||Theoptionsthatcanbespecifiedaspartofthesearch-argument-optionsareasfollows:QUERYLANGUAGE=valueSpecifiesthequerylanguage.
Thevaluecanbeanyofthesupportedlanguagecodes.
IftheQUERYLANGUAGEoptionisnotspecified,thedefaultisthelanguagevalueofthetextsearchindexthatisusedwhenthisfunctionisinvoked.
IfthelanguagevalueofthetextsearchindexisAUTO,thedefaultvalueforQUERYLANGUAGEisen_US.
RESULTLIMIT=valueSpecifiesthemaximumnumberofresultstobereturnedfromtheunderlyingsearchengine.
Thevaluecanbeanintegervalue1-2147483647.
IftheRESULTLIMIToptionisnotspecified,noresultlimitisineffectforthequery.
Thisscalarfunctionmightnotbecalledforeachrowoftheresulttable,dependingontheplanthattheoptimizerchooses.
Thisfunctioncanbecalledoncefortheentirequerytotheunderlyingsearchengine.
Aresultsetofalltheprimarykeysthatmatcharereturned.
Thisresultsetisthenjoinedtothetablecontainingthecolumntoidentifytheresultrows.
Inthiscase,theRESULTLIMITvalueactslikeFETCHFIRSTROWSfromtheunderlyingtextsearchengine,andcanbeusedasanoptimization.
Ifthesearchengineiscalledforeachrowoftheresultbecausethatisthebestplan,thentheRESULTLIMIToptionisignored.
SYNONYM=OFForSYNONYM=ONSpecifieswhethertouseasynonymdictionarythatisassociatedwiththetextsearchindex.
Youcanaddasynonymdictionarytoacollectionbyusingthesynonymtool.
OFFOFFisthedefaultvalue.
ONUsethesynonymdictionarythatisassociatedwiththetextsearchindex.
Theresultofthefunctionisalargeinteger.
Ifthesecondargumentcanbenull,theresultcanbenull.
Ifthesecondargumentisnull,theresultisthenullvalue.
Theresultis1ifthedocumentcontainsamatchforthesearchcriteriathatarespecifiedinthesearchargument.
Otherwise,theresultis0.
Theresultisalso0ifthecolumnisnull.
IfthesearchargumentisNull,thentheresultisthenullvalue.
CONTAINSisanondeterministicfunction.
Example1ThefollowingstatementfindsalltheemployeeswhohaveCOBOLintheirresume.
SELECTEMPNOFROMEMP_RESUMEWHERERESUME_FORMAT='ascii'ANDCONTAINS(RESUME,'COBOL')=1Example2Thesearchargumentdoesnotneedtobeastringconstant.
ThesearchargumentcanbeanySQLstringexpression,includingastringcontainedinahostvariable.
Thefollowingstatementsearchesfortheexactterm"ate"intheCOMMENTcolumn.
Note:Theterm"ate"mustbedelimitedindoublequotessothatonlytheexacttermissearchedforandlinguisticvariationsarenotconsidered.
charsearch_arg[100];/*inputhostvariable*/.
.
.
EXECSQLDECLAREC3CURSORFORSELECTCUSTKEYFROMK55ADMIN.
CUSTOMERSWHERECONTAINS(COMMENT,:search_arg)=136IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||ORDERBYCUSTKEY;strcpy(search_arg,"\"ate\""');EXECSQLOPENC3;.
.
.
Example3Thefollowingstatementfinds10studentsatrandomwhowroteonlineessaysthatcontainthephrase"fossilfuel"inSpanish,whichis"combustiblefósil.
"Thesestudentsareforaradiointerview.
Usethesynonymdictionarythatwascreatedfortheassociatedtextsearchindex.
Becauseonly10studentsareneeded,optimizethequerybyusingtheRESULTLIMIToptiontolimitthenumberofresultsfromtheunderlyingtextsearchserver.
SELECTFIRSTNME,LASTNAMEFROMSTUDENT_ESSAYSWHERECONTAINS(TERM_PAPER,'combustiblefósil','QUERYLANGUAGE=es_ESRESULTLIMIT=10SYNONYM=ON')=1Relatedtasks:"Searchatextsearchindex"onpage14YoucansearchatextsearchindexbyusinganSQLstatementwithaCONTAINSorSCOREfunction.
Thesearchargumentcriteriaisspecifiedonthefunction.
Relatedreference:"SCORE"YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
"Searchargumentsyntax"onpage39Youcanspecifyasearchargumentastheconditionofasearchfortermsintextdocuments.
Itconsistsofsearchparametersandoneormoresearchterms.
TheSQLscalartextsearchfunctionsthatusesearchargumentsareCONTAINSandSCORE.
SCOREYoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
(1)SCORE(column-name,search-argument,string-constant)Notes:1string-constantmustconformtotherulesforthesearch-argumentoptions.
search-argument-options:(1)QUERYLANGUAGE=valueRESULTLIMIT=valueOFFSYNONYM=ONNotes:1Thesameclausemustnotbespecifiedmorethanonce.
TheschemaisQSYS2.
OmniFindTextSearchServer37column-nameSpecifiesaqualifiedorunqualifiednameofacolumnthathasatextsearchindexthatistobesearched.
ThecolumnmustexistinthetableorviewthatisidentifiedintheFROMclauseofthestatement.
Thecolumnofthetable,orthecolumnoftheunderlyingbasetableoftheview,musthaveanassociatedtextsearchindex(SQLSTATE38H12).
Theunderlyingexpressionofthecolumnofaviewmustbeasimplecolumnreferencetothecolumnofanunderlyingtable,eitherdirectlyorthroughanothernestedview.
search-argumentSpecifiesanexpressionthatreturnsastringvaluecontainingthetermsusedinthesearch.
Theexpressionmustnotbeallblanksortheemptystring(SQLSTATE38H14).
Theactuallengthofthestringmustnotexceed32704bytes.
Thislengthmightbefurtherlimitedbywhatissupportedbythetextsearchserver(SQLSTATE38H10).
ThevalueisconvertedtoUnicodebeforeitisusedtosearchthetextsearchindex.
Ifthesearch-argumentisnull,theresultisthenullvalue.
string-constantIdentifiesastringconstantthatspecifiesthesearchargumentoptionsthatareineffectforthefunction.
Theoptionsthatcanbespecifiedaspartofthesearch-argument-optionsareasfollows:QUERYLANGUAGE=valueSpecifiesthequerylanguage.
Thevaluecanbeanyofthesupportedlanguagecodes.
IftheQUERYLANGUAGEoptionisnotspecified,thedefaultisthelanguagevalueofthetextsearchindexthatisusedwhenthisfunctionisinvoked.
IfthelanguagevalueofthetextsearchindexisAUTO,thedefaultvalueforQUERYLANGUAGEisen_US.
RESULTLIMIT=valueSpecifiesthemaximumnumberofresultsthataretobereturnedfromtheunderlyingsearchengine.
Thevaluecanbeanintegervalue1-2147483647.
IftheRESULTLIMIToptionisnotspecified,noresultlimitisineffectforthequery.
Thisscalarfunctionmightnotbecalledforeachrowoftheresulttable,dependingontheplanthattheoptimizerchooses.
Thisfunctioncanbecalledoncefortheentirequerytotheunderlyingsearchengine.
Aresultsetofalltheprimarykeysthatmatcharereturned.
Thisresultsetisthenjoinedtothetablecontainingthecolumntoidentifytheresultrows.
Inthiscase,theRESULTLIMITvalueactslikeFETCHFIRSTROWSfromtheunderlyingtextsearchengineandcanbeusedasanoptimization.
Ifthesearchengineiscalledforeachrowoftheresultbecausethatisthebestplan,thentheRESULTLIMIToptionisignored.
SYNONYM=OFForSYNONYM=ONSpecifieswhethertouseasynonymdictionarythatisassociatedwiththetextsearchindex.
Youcanaddasynonymdictionarytoacollectionbyusingthesynonymtool.
OFFOFFisthedefaultvalue.
ONUsethesynonymdictionarythatisassociatedwiththetextsearchindex.
Theresultofthefunctionisadouble-precisionfloating-pointnumber.
Ifthesecondargumentcanbenull,theresultcanbenull.
Ifthesecondargumentisnull,theresultisthenullvalue.
Theresultisgreaterthan0butlessthan1ifthecolumncontainsamatchforthesearchcriteriathatthesearchargumentspecifies.
Themorefrequentlyamatchisfound,thelargertheresultvalue.
Ifthecolumndoesnotcontainamatch,theresultis0.
Thescoreisalso0ifthecolumnisnull.
SCOREisanondeterministicfunction.
38IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||ExampleThefollowingstatementgeneratesalistofemployeesorderedbyhowwelltheirresumesmatchprogrammerAND(javaORcobol).
Inaddition,arelevancevaluethatisnormalizedbetween0(zero)and100isreturned.
SELECTEMPNO,INTEGER(SCORE(RESUME,'programmerAND(javaORcobol)')*100)ASRELEVANCEFROMEMP_RESUMEWHERERESUME_FORMAT='ascii'ORDERBYRELEVANCEDESCRelatedtasks:"Searchatextsearchindex"onpage14YoucansearchatextsearchindexbyusinganSQLstatementwithaCONTAINSorSCOREfunction.
Thesearchargumentcriteriaisspecifiedonthefunction.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"Searchargumentsyntax"Youcanspecifyasearchargumentastheconditionofasearchfortermsintextdocuments.
Itconsistsofsearchparametersandoneormoresearchterms.
TheSQLscalartextsearchfunctionsthatusesearchargumentsareCONTAINSandSCORE.
SearchargumentsyntaxYoucanspecifyasearchargumentastheconditionofasearchfortermsintextdocuments.
Itconsistsofsearchparametersandoneormoresearchterms.
TheSQLscalartextsearchfunctionsthatusesearchargumentsareCONTAINSandSCORE.
Foranylanguage-specificprocessingduringasearch,youcanspecifyavaluefortheQUERYLANGUAGEparameterasasearchargumentoption.
Thevaluecanbeanyofthesupportedlanguagecodes.
IftheQUERYLANGUAGEparameterisnotspecified,thedefaultvalueisthelanguagevalueofthetextsearchindexusedwhenthisfunctioniscalled.
IfthelanguagevalueofthetextsearchindexisAUTO,thedefaultvalueforQUERYLANGUAGEisen_US.
LimitationsYoucannotusetheCONTAINSandSCOREfunctionsinanSQLconstraintorindexdefinition.
YoucanusetheminSQLquerystatementsandviewdefinitionsunderthefollowingrestrictions:vIfaview,nestedtableexpression,orcommontableexpressionprovidesatextsearchcolumnforaCONTAINSorSCOREscalarfunction,andiftheapplicableview,nestedtableexpression,orcommontableexpressionhasaDISTINCTclauseontheoutermostSELECTstatement,thentheSELECTlistmustcontainallthecorrespondingkeyfieldsofthetextsearchindex.
Otherwise,SQLmessage38H12isreturned.
vIfaview,nestedtableexpression,orcommontableexpressionprovidesatextsearchcolumnforaCONTAINSorSCOREscalarfunction,thentheapplicableview,nestedtableexpression,orcommonOmniFindTextSearchServer39||||||||||||tableexpressioncannothaveaUNION,anEXCEPT,oranINTERSECTstatementattheoutermostSELECTlevel.
Otherwise,SQLmessage38H12isreturned.
vIfacommontableexpressionprovidesatextsearchcolumnforaCONTAINSorSCOREscalarfunction,thecommontableexpressioncanbereferencedagainintheentirequeryonlywhenthereferencedoesnotprovideatextsearchcolumnforaCONTAINSorSCOREscalarfunction.
Otherwise,SQLmessage38H12isreturned.
vAfunctioncannotbecreatedsourcedontheCONTAINSorSCOREscalarfunctions.
Otherwise,SQLmessageSQL0457isreturned.
vThequerycanrunthroughtheSQLQueryEngine(SQE).
SimplesearchTodoasimplekeywordsearch,enteroneormorekeywordsinthequery.
Thesearchenginereturnsdocumentsthatcontainallthosekeywords,orvariationsofthekeywords.
Forexample,ifyouenterking,thesearchenginereturnsalldocumentsthatcontainthewordkingorkings.
Ifyouenterthequerykinglear,thesearchenginereturnsdocumentsthatcontainthetermskingandlear.
Toseemorepreciseresults,usemorespecifickeywords.
Forexample,useFrenchroastcoffeeratherthancoffee,oruseKauaihikingtoursratherthanHawaiianvacations.
Ifasimplekeywordsearchreturnstoomanydocumentsthatarenotwhatyouarelookingfor,youcanuseoperatorstorefineyoursearch.
ExclusionoftermsinasearchUsetheminussign(-)toexcludeterms.
Forexample,ifyouwanttofinddocumentswiththetermlear,andnotedward,enterthequerylear-edward.
Theminussign(-)alsoappliestoatermanditsvariants.
Forexample,thequery-edwardexcludesdocumentsthatcontainthewordedward's.
PhrasesearchIfyouwanttoensurethattermsaredisplayedexactlyinthesequencethatyoutypedthemin,youcanusedoublequotationmarks.
Forexample,ifyouwanttoseedocumentswiththetermkinglearexactly,andnotrelatedphrasessuchaskinglylearorkingandqueenlear,enter"kinglear".
Thesearchisnotcase-sensitive,buttermvariantsarenotconsideredmatches.
LanguageprocessingOmniFindTextSearchperformslanguage-specificprocessingontermsbyusingthelanguagethatisspecifiedbythequery.
Whenthelanguageisnotspecified,thedefaultlanguageisused.
Whenyousearchforaword,thebaseformofthewordisalsosearched.
Forexample,searchingfortestsortestingalsofindsthewordtest.
Duringlanguageprocessing,predefinedsynonymsareaddedtothequery.
Languageprocessingisnotperformedonphrasesandontermsinallcapitalletters,forexample,DOG.
40IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriWildcardcharacterinasearchThewildcardcharacter(*)helpsyoufinddocumentswhenyoudonotknowthefullspelling,orifyouwanttofindvariationsoftheterm.
Forexample,thequeryczech*returnsdocumentswiththetermsczech,czechoslovakia,czechoslovakian,czechrepublic,andotherpossibleresults.
Youcanalsousethewildcardcharacterinaphrasesearch.
Forexample,thequery"John*Kennedy"returnsdocumentswiththetermsJohnFitzgeraldKennedyandJohnFKennedybutnotJohnKennedy.
ThequeryMi*lGorbachevreturnsMikhailGorbachev.
Addingawildcardcharactertothebeginningofaquery(forexample,*zech)mightcausethesearchenginetotakelongertoreturnresults.
SearchesforatleastoneofthetermsThelogicaloperatorORspecifiesthatatleastoneofthetermsinaquerymustbedisplayedinthereturneddocument.
Forexample,thequery(othelloORotello)returnsdocumentsthatcontainthetermothellorotello.
YoucanalsousethelogicaloperatorsAND,OR,andNOTincombinationsbyusingparentheses.
Forexample,thequerycougarOR(jaguarANDNOTcar)returnsdocumentswiththetermscougarorjaguarbutnotcar.
YoumustenterthelogicaloperatorsAND,OR,andNOTinalluppercase.
Useparenthesesforgrouping.
Relatedconcepts:"XMLsearch"onpage44YoucanindexandsearchXMLdocuments.
TheXMLsearchgrammarusesasubsetoftheW3XPathlanguagewithextensionsfortextsearch.
Theextensionssupportrangesearchesofnumeric,Date,andDateTimevaluesthatareassociatedwithanXMLattributeorelement.
Structuralelementscanbeusedseparately,orcombinedwithfreetextinqueries.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
SimplequeryexamplesSimplequeriesfortheCONTAINSandSCOREfunctionssearchforasinglewordormultiplewordsinatextsearchindex.
Thesearchengineignoreswhitespacebetweencharacters.
Thesearchstringmustnotbeemptyorcontainallblanks(SQLSTATE38H14).
Thefollowingtableshowssomeexamplesofsimplesearchqueries.
Table3.
SimplequeryexamplesSearchwordtypesExamplesQueryresultsSinglewordkingReturnsalldocumentsthatcontainthewordkingorkings.
Thisquerymatchesdifferentsurfaceformsandisnotcasesensitive.
OmniFindTextSearchServer41||Table3.
Simplequeryexamples(continued)SearchwordtypesExamplesQueryresultsMultiplewordskinglearReturnsalldocumentsthatcontainkingandlear.
ThedefaultoperatoristhelogicaloperatorAND.
TheoperatorsANDand+areimplicitineveryquery.
Forexample,thequeryKingLearreturnsthesameresultsasKingANDLearorKing+Lear.
YoumustenterthelogicaloperatorsNOT,AND,andORinalluppercase.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
AdvancedsearchoperatorsYoucanuseadvancedsearchoperatorstorefinethesearchresultsfortheCONTAINSfunctionandtheSCOREfunction.
Inthefollowingtable,thefirstcolumndescribestheoperatorthatyoucanuseinasearchquery.
YoumustenterthelogicaloperatorsNOT,AND,andORinalluppercaseletters.
Thesecondcolumnshowsasamplequerythatyoumightenter.
Thethirdcolumndescribesthetypesofresultsthatyoumightseefromtheexamplequery.
Table4.
AdvancedsearchoperatorsandcomplexqueryexamplesOperatorsExamplesQueryresultsAND"KingLear"AND"Othello""KingLear""Othello"EitherqueryreturnsdocumentsthatcontainbothtermsKingLearandOthello.
TheANDoperatoristhedefaultconjunctionoperator.
Ifnologicaloperatorisbetweenthetwoterms,theANDoperatorisused.
Forexample,thequeryKingLearisthesameasthequeryKingANDLear.
OR"KingLear"ORLearReturnsdocumentsthatcontaineitherKingLearorjustLear.
TheORoperatorlinksthetwotermsandfindsamatchingdocumentifeitherofthetermsexistinadocument.
NOT"KingLear"NOT"NormanLear"ReturnsdocumentsthatcontainKingLearbutnotNormanLear.
""(Exactmatch)Firstquery:"KingLear"Secondquery:"king"ThefirstqueryreturnstheexactphraseKingLear.
Thesecondqueryreturnsonlythewordkingandnootherforms,suchaskingsorkingly.
*(Wildcardcharacter)test*te*tReturnsdocumentsthatcanmatchpossiblecombinations,suchastest,tests,andtester,ortestandtext.
42IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriTable4.
Advancedsearchoperatorsandcomplexqueryexamples(continued)OperatorsExamplesQueryresults^(Scoreboostfactor)somewordorphrase^numberFirstquery:"KingLear"^4"RichardIII"Secondquery:title:(softwaredownload)^5pdfviewer-shippingThefirstqueryforcesdocumentswiththephraseKingLeartobedisplayedhigherinthelistofsearchresults.
Thesecondqueryforcesadocumenttitledsoftwaredownloadtobedisplayedhigherinthelistofresults.
Althoughaboostfactormustbepositive,theboostfactorcanbelessthan1.
Forexample,0.
2.
Theboostfactornumberhasnolimit.
+(Includes)+LearKingReturnsalldocumentsthatcontainLearandKing,whichisthesameasthequeryLearANDKing.
-(Excludes)"KingLear"-"LearJet"ReturnsdocumentsthatcontainKingLearbutnotLearJet.
()(KingORLear)ANDplaysReturnsdocumentsthatcontaineitherKingorLearandplays.
TheparenthesesensurethatplaysisfoundandeithertermKingorLearispresent.
\(Escapecharacter)\(1\+1\)\:2Returnsdocumentsthatcontain(1+1):2.
Usethe\asanescapecharactersothatyoucanusespecialcharactersthatarenormallypartofthequerysyntax.
Ifaspecialcharacterisprecededbytheescapecharacter,thespecialcharacterisanalyzedaspartofthequery.
Specialcharactersare:and\.
Ifaspecialcharacteriscleared,thespecialcharacterisanalyzedaspartofthequery.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
ExampleusingCONTAINSandSCOREfunctionsYoucanusetheCONTAINSandSCOREfunctionsinthesamequery.
Thequerysearchesatextsearchindexandreturnifandhowfrequentlythetextdocumentmatchesthesearchargumentcriteria.
TheexampleinthefollowingtableusesdatafromthebasetableBOOKSwiththecolumnsISBN(VARCHAR(20)),ABSTRACT(VARCHAR(10000)),andPRICE(INTEGER).
OmniFindTextSearchServer43||Table5.
ThebasetableBOOKSISBNABSTRACTPRICEi1"abc"7i2"abd"10i3"aea"8Yourunthefollowingquery:SELECTISBN,SCORE(ABSTRACT,'"b"')FROMBOOKSWHERECONTAINS(ABSTRACT,'"b"')=1Thisqueryreturnsthefollowingtworows:i1,0.
3i3,0.
4Thescorevaluesmightdifferdependingonthecontentofthetextcolumn.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
XMLsearchYoucanindexandsearchXMLdocuments.
TheXMLsearchgrammarusesasubsetoftheW3XPathlanguagewithextensionsfortextsearch.
Theextensionssupportrangesearchesofnumeric,Date,andDateTimevaluesthatareassociatedwithanXMLattributeorelement.
Structuralelementscanbeusedseparately,orcombinedwithfreetextinqueries.
DocumentsmustbeindexedtoincludetheXMLmarkupbeforetheindexcanbesearchedusingthexmlxpquerysyntax.
Documentindexingisdonebyusingthe"FORMATXML"optionatindexcreationtime.
Indexescreatedonapreviousreleasecanbeusedtoperformsearches.
However,documentsindexedonapreviousreleasedonothavetheinformationnecessarytousealltheXMLsearchcapabilitiesavailableinanewerrelease.
Documentsaddedorupdatedinthetextsearchindexaftertheupgradetothenewreleaseincludetheadditionalinformation.
Anupgrademightresultindocumentsindexedonthepriorreleasenotbeingincludedinsomesearchresults.
TheSYSPROC.
SYSTS_REPRIMEINDEXstoredprocedurecanbeusedtorebuildtheindexandresolvethisproblem.
TousetheOMNIFINDCONTAINSandSCOREbuilt-infunctionstosearchXMLdata,thequerystringmuststartwiththe@xmlxp:queryprefix.
TheprefixisfollowedbyavalidXMLSearchqueryexpression.
The@xmlxp'opaque'termprefixindicatesthatasearchisperformedusingthequerypathexpression.
Forexample:CONTAINS(columnname,'@xmlxp:''query_expression''').
Thesinglequotes''surroundingthequery_expressionmustbedoubledbecausetheyarecontainedwithinanSQLstring,ineffect,astringwithinastring.
44IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||The@xpath:opaquetermprefixthatwasusedinpreviousreleasesofOmniFindTextSearchServerforDB2foriissupportedforcompatibilitywithearlierversions.
However,ithasbeendeprecatedandisnotrecommended.
ThefollowinglisthighlightsthekeyfeaturesofXMLsearch:XMLstructuralsearchByincludingspecialopaqueXMLtermsinqueries,youcansearchXMLdocumentsforstructuralelementsandtextthatisscopedbythoseelements.
Structuralelementsaretagnames,attributenames,andattributevalues.
Elementandtagnamesarecasesensitive.
XMLquerytokenizationTokenizationistheprocessofparsinginputintotokens.
FreetextinXMLquerytermsistokenizedthesamewaythattextinnon-XMLquerytermsistokenized.
Anexceptionisthatnestedopaquetermsarenotsupported.
Freetextsearchisnotcasesensitive.
XMLSchemaandDTDAnyXMLschemaassociatedwiththeXMLdocumentisnotdownloaded,anddefaultvaluesarenotindexed.
NumericvaluesPredicatesthatcompareattributeorelementvaluestonumbersaresupported.
ElementvaluesPredicatesthatcompareelementvaluestonumbersordatesaresupported.
TheelementcontainingthedateornumbermustbeanXMLelementthatcontainsonlythenumberordate.
Leadingandtrailingwhitespaceareignored.
StringvaluesUseofthe=operatorforastringargumentinapredicaterequiresacompletematchofallkeywordsinthestringwithtokensintheidentifiedtextspan.
Theorderofthetokensisnotsignificantwhenmatchingisperformed.
DateTimevaluesPredicatesthatcompareDateorDateTimeattributesorelementsaresupported.
Pathexpressions:Table6.
Pathexpressions@xmlxpExpressionDescriptionTagNameSelectsatagnamedTagName,andallchildrenofthattag.
@AttributeNameSelectsanattributenamed@AttributeName.
/Selectsfromrootnode.
//Selectsmatchingtagsandattributesthataredescendantsofthecurrentpositionandmatchtheexpression.
.
Self:thecurrenttagorelementnode.
OmniFindTextSearchServer45||||||||||||||||||||||||||||||||||||||||||||Table7.
Pathexpressionexamples:@xmlxpExpressionResult/DocumentReturnsalldocumentswithatop-leveltagDocument.
//DocumentReturnsalldocumentswithatagDocumentatanylevel.
/Document/Child1Returnsalldocumentswithatop-leveltagDocumentthathasadirectchildtagChild1.
/Document//Child1Returnsalldocumentswithatop-leveltagDocumentthathasadescendanttagChild1atanylevel.
/Root/@attr1Returnsalldocumentwithatop-leveltagRootwithanattributeattr1.
/Root//@attr1Returnsalldocumentswithatop-leveltagRootwithanattributeattr1onthatroottagoranydescendanttag.
//@attr1Returnsalldocumentsthathaveanattribute@attr1atanylevel.
Note:TheXMLSearchexpressionmusthaveanactualtagorattributenameintherelativepathexpression.
/and//bythemselvesarenotvalidsearchqueries.
Pathexpressionsareonlyallowedintheforwarddirection,andonlyonasingleaxis.
Itisrecommendedthatapathexpressionstartwithaleading/or//.
Thisindicatesthattheexpression'sinitialcontextisthedocument'srootnode.
Whentheleading/or//isomitted,theexpressionismatchedatanylevel.
Inotherwords,'Sentences'istreatedas'//Sentences'.
Thebehaviorisdefinedthiswaytobecompatiblewithpriorreleases,anddoesnotfollowtheW3orSQL/XMLstandard.
PathexpressionwildcardsupportInthepathexpression,thespecialwild-cardcharacter*canbeusedtoindicateexactlyonetag,withanyname.
Trailingpathexpressionwildcardsareignored.
Thefollowingusesofpathexpressionwildcardsarenotsupportedandresultinanerror:vAnexpressionthatreferencesonlywildcardsandnospecificelementsorattributes.
vAwildcardattributeatanylevel:/Tag/@*.
vAwildcardthatimmediatelyprecedesapredicateexpression:/Root/*[//anytag].
vAwildcardthatisusedinapredicatecomparison:/Root[*>5].
vAwildcardasanXMLnamespaceprefix://*:tagname.
vAwildcardprefixedwithanXMLnamespaceprefix://ns:*.
vAwildcardcharacterusedaspartofatagname:/start*.
Table8.
Pathexpressionwildcardexamples:@xmlxpExpressionResult/Root/*/T1Alldocumentshavingatop-leveltagRootthathasadescendanttagT1withoneintermediatelevel.
/Root/*//T1Alldocumentshavingatop-leveltagRootthathasadescendanttagT1withoneormoreintermediatelevels.
46IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||||PredicatesPredicatesareusedtospecifyavalueorconditionthatanelementorattributenodemustsatisfy.
Predicatesarealwaysenclosedinsquarebrackets:[].
Table9.
Predicateexamples:@xmlxpExpressionResult/Book[Sentences]Top-leveltagisBookandmusthaveadirectchildSentences.
/Book[.
//Sentencesand.
//Author]Top-leveltagisBookandmusthavebothSentencesandAuthordescendants.
EachdescendantcanbeatanylevelbelowBook.
Becausepathexpressionsarealwaysintheforwarddirection,andlimitedtoasingleaccess,pathexpressionsinpredicatesmustberelativetothecurrentnode.
/Book[/Root]and/Book[//Root]arenotvalid,becauseinbothcasesthepredicatepathexpressionbeginswiththetop-leveltag'Root'insteadofthecurrentnode.
NumericcomparisonsOMNIFINDsupportstheand!
=operatorsforcomparisonsofelementsandattributestointegersandfloatingpointvalues.
Elementshaveonlytheirnumericvaluesindexediftheyaresimpleelements.
Theymustnotcontainadditionalcharacters(otherthanwhitespace)andmustnothaveanydescendantelements.
Complexelementsareindexedastextonly.
Table10.
Numericcomparisonexamples:@xmlxpExpressionResult/Book[@id_num=12345]Top-leveltagisBookandmusthaveanattributeid_numwithavalueof12345.
/Book[Costxs:date("2000-01-01")]Top-leveltagisBook.
BookhasanattributepublishDatethatisgreaterthanthedateof2000-01-01.
/Book[purchaseTime>xs:dateTime("2009-05-20T13:00:00")]Top-leveltagisBook.
BookhasadirectchildpurchaseTimethatisaDateTimeexpressiongreaterthan2009-05-20T13:00:00.
000000.
ContainsandexcludesinXMLmarkupThecontainsandexcludesfunctionsareusedtoperformfulltextsearcheswithintheXMLmarkup.
Containsreturnstrueifthequeryiscontainedwithinthetargetnode;excludesreturnstrueifthequeryisNOTcontainedwithinthetargetnode.
Forexample,findalldocumentswithatop-leveltagcalledemail,andadirectdescendantcalledbodythatcontainsvariationsofthephrase"Departmentbudget".
@xnkxo:''/email[bodycontains("departmentbudget")]''Thefreetextpassedtothecontainsorexcludesfunctionishandledinthesamewayasanyotherfreetextsearch.
Thesearchisnotcase-sensitive,andlinguisticvariationsareconsidered.
Theearlierquerymatches"departmentsbudgets"andalso"budgetforthedepartment".
Thesearchcanberestrictedtoanexactmatchbyusingthetraditionalquotationmarks,forexample,@xmlxp:''/email[bodycontains("""departmentbudget"Thequotesindicatinganexactmatchmustbedoubledsothattheyarenotinterpretedastheendofthecontainsfreetextstring.
Table12.
Containsandexcludesexamples:@xmlxpExpressionResult/Book[abstractcontains("catANDdog")]Top-leveltagBookthathasachildtagabstractwhichcontainslinguisticvariationsofthetermscatanddog.
/Book[abstractcontains("catANDdog")]/Book/@title[.
contains("catORdog")]Top-leveltagBookhasanattributetitlethatcontainslinguisticvariationsofeithercatordog.
/Book/Title[.
contains("""Allgooddogsgotoheaven""")]Top-leveltagBookwithadirectchildTitlethatcontainsallgooddogsgotoheaveninorder,andwithoutlinguisticvariationsbeingconsidered.
/Book[abstractexcludes("catANDdog")]Top-leveltagBookthathasachildtagabstractwhichdoesnotcontainlinguisticvariationsofthetermscatanddog.
CompletestringmatchoperatorThe=operatorwithastringargumentinapredicatecallsforacompletematchofalltokensinthestringwithalltokensintheidentifiedtextspan.
Linguisticequivalentsarenotconsidered.
Theorderofthetermssearchedforisnotsignificant.
Itisnotrequiredthattheelementorattributecontainonlythetextthatwassearchedfor.
48IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||||||Table13.
Completestringmatchoperatorexamples:@xmlxpExpressionResult/Book[@author="NicholasLawrence"]Top-leveltagBookthathasanattributeauthor.
authormustcontainthetermsNicholasLawrence.
Linguisticvariationsonthosetermsarenotconsideredmatches.
/Book[author="""NicholasLawrence"""]Top-leveltagBookthathasadirectdescendantauthor.
authormustcontainthetermsNicholasLawrenceinorder.
Linguisticvariationsonthosetermsarenotconsideredmatches.
LogicalOperatorsThelogicaloperatorsandandorcanbeusedinpredicates.
Table14.
Logicaloperatorexamples:@xmlxpExpressionResult/Book[@author="""NicholasLawrence"""]/Price[.
=.
vLogicaloperatorsareandandor.
Youcanuseparenthesestoensuretheprecedencethatyouwant.
Relatedreference:"Searchargumentsyntax"onpage39Youcanspecifyasearchargumentastheconditionofasearchfortermsintextdocuments.
Itconsistsofsearchparametersandoneormoresearchterms.
TheSQLscalartextsearchfunctionsthatusesearchargumentsareCONTAINSandSCORE.
"SYSPROC.
SYSTS_REPRIMEINDEX"onpage69YoucanreprimetheindexandstartaninitialupdateusingtheSYSPROC.
SYSTS_REPRIMEINDEXstoredprocedure.
Usethisstoredprocedurewhenyouwanttorestoredatafromthebasetable.
"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
OmniFindTextSearchServer49||||||||||||||||||||||||||||||||||||||||||||||||||||XMLSearchNamespaceSupportYoucanuseanamespacetoscopeelementsandattributesinadocument.
Namespacesareusefulinrestrictingthequerysearchtothemeaningfulelementswithinthedocument.
OverviewInXML,elementandattributenamesarechosenbythedeveloper.
ThesenamescancreateconflictswhenXMLdocumentsfromdifferentapplicationsaremixed.
Itisthereforeusefultorestrictthequerysearchtothemeaningfulelementswithinthedocument,especiallywhenmultipledifferentdocumenttypesmightbeindexed.
Restrictingthesearchcanbeaccomplishedbyusingnamespaces.
Namespacesprovidescopingoftheelementsandattributesofthedocumenttoensurecorrectinterpretationofthevalues.
Namespacesaredescribedwithlongname(URI)andoptionallyashortnamecalledtheQname(qualifiedname).
http://example.
com/ns/abcisthelongnamespacenameandxistheQnameprefix.
AQnameprefixisusefulasashorthandforthenamespaceofeachelementreference.
Elementpisqualifiedbynamespacehttp://example.
com/ns/abc.
ThedefaultnamespaceAdefaultnamespacecanbespecifiedforXMLelements.
Thedefaultnamespaceappliestothecurrenttagandanydescendenttags.
Anyunqualifiedtaginthenamespaceinheritsthedefaultnamespace.
Inthiscasebothdocandpelementsareinthehttp://example.
com/ns/abcnamespace.
AttributenamespacesAnattributemighthaveadifferentnamespacethanitsassociatedelement.
Elementandattribute,qualified:MuttThereisadifferenceinhowelementsandattributesinheritnamespacewhenitisnotexplicitlyspecified.
Unqualifiedelementspickupthedefaultnamespaceofthescopewithinwhichtheylie.
Unqualifiedattributesdonothaveanynamespace.
Elementandattribute,non-qualified:MuttInthisexample,elementbreedhasanamespaceofhttp://example.
org/animals.
However,attributesizehasnonamespaceassociatedwithit.
50IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||||FormoreinformationaboutXMLnamespaces,consulttheW3CRecommendationforNamespacesinXMLwhichcanbefoundattheWorldWideWebConsortium(W3C)(http://www.
w3.
org).
ReservedQnameprefixesThefollowingQnameprefixesarereservedandmustnotbeusedtoqualifyuser-definedelementsorattributes:xml,xs,xsi,fn,local.
UsingnamespacesinsearchQNameprefixesanddefaultelementnamespacesmustbedefinedinthe@xmlxpqueryprologofthesearchterm.
Anexampleprologthatmapsnamespacens1toURI"http://mycompany.
com"declarenamespacens1="http://mycompany.
com";AnexampleprologthatspecifiesthatallunqualifiedelementsarequalifiedbyURI"http://mycompany.
com":declaredefaultelementnamespace"http://mycompany.
com"IfaquerydoesnotdeclareanynamespaceQNameprefixordefaultelementnamespace,thennamespacesarenotconsideredinthequery.
Anelementorattributenameisconsideredamatchifitexistsinanynamespace.
IfanyQNameprefixordefaultnamespaceisdeclared,elementorattributenamesareamatchonlyiftheyexistinthenamespacespecified.
Thesyntaxdeclaredefaultelementnamespace"";couldbeusedtoindicatethatunqualifiedtagsarenotinanynamespace.
QNameprefixesusedintheXMLsearchstringareNOTrequiredtomatchtheQNameprefixusedintheXMLdocument.
MatchesarebasedsolelyonthelongnameURI.
Examples:Restrictsearchtoattributeattrofelementtestwhereelementtestismappedtonamespace"http://posample.
org",andattrisnotinanynamespace.
Usedefaultnamespacetosimplifysyntax.
CONTAINS(myxmlcol,'@xmlxp:''declaredefaultelementnamespace"http://myexample.
org";/test[@attr>xs:date("2005-01-01")]''')Restrictsearchtoattributeattrofelementtestwhereelementtesthasanamespaceof"http://myexample.
org".
UseexplicitnamespacesyntaxbyusingtheQNameprefixabc.
CONTAINS(myxmlcol,'@xmlxp:''declarenamespaceabc="http://myexample.
org";/abc:test[@attr'||'OmniFindTextSearchServerforDB2'||'1>'||''||'1'||'Introduction'||'ThischapterwillintroducethereadertothecapabilitiesofOmniFindforDB2forIBMi'||''||''||'2'||'CreatingaTextSearchIndex'||'Thischapterwillexplainhowtocreateatextsearchindex'||''||'');INSERTINTOXMLTEST.
XML_DOCUMENTS(ID,XML_DATA)VALUES(2,''||'UsingtheXMLdatatypeforDB2forIBMi'||'2>'||''||'1'||'Introduction'||'ThischapterwillintroducethereadertotheDB2XMLdatatype'||''||''||'2'||'InsertingXMLdataintoaDB2table'||'ThischapterwillexplainhowtoinsertXMLdataintoaDB2table'||''||''||'3'||'SearchingXMLdata'||'ThischapterwillexplainhowtoquerydatainXMLcolumnsusingtheCONTAINSandSCOREUDFS'||''||'');INSERTINTOXMLTEST.
XML_DOCUMENTS(ID,XML_DATA)VALUES(3,''||'UsingNamespaceswithOmniFindTextSearchServerforDB2forIBMi'||'2'||''||'1'||'Introduction'||'ThischapterwillintroducethereadertoXMLnamespaces'||''||''||52IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||'2'||'Usingdefaultnamespaces'||'ThischapterwillexplainhowtouseanamespaceinanXMLsearch'||''||'');vUpdatetheindex:CALLSYSPROC.
SYSTS_UPDATE('XMLTEST','XML_INDEX','');ExamplequeriesExample1:FindalldocumentsthathavearootelementBOOKwithadirectdescendantTITLEthatcontainsDB2.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''/BOOK/TITLE[.
contains("DB2"1;Becauseanamespaceprologisnotspecifiedinthesearchterm,nonamespaceisconsideredinthesearch.
Table15.
ResultID123Example2:FindalldocumentsthathavearootelementBOOKwithadirectdescendantTITLEthatcontainsDB2.
UseadefaultelementnamespacetoindicatethatBOOKandTITLEmustbeinthe"http://www.
ibm.
com/digital_media_library"namespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declaredefaultelementnamespace"http://www.
ibm.
com/digital_media_library";/BOOK/TITLE[.
contains("DB2"1;Table16.
ResultID3Example3:FindalldocumentsthathavearootelementBOOKthathasanattributepublication_dateafter"2010-01-01"andhasachildelementTITLEthatcontainsDB2.
RestrictthesearchsothattagsBOOKandTITLEmustnotexistinanynamespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declaredefaultelementnamespace"";/BOOK[@publication_date>xs:date("2010-01-01")]/TITLE[.
contains("DB2"1;Table17.
ResultID2OmniFindTextSearchServer53||||||||||||||||||||||||||||||||||||||||||||||||Example4:FindalldocumentswitharootelementBOOK(notinanynamespace)thathaveadirectdescendantCHAPTER(alsonotinanamespace)thatcontainsinformationaboutinsertingdataintoanXMLtable.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declaredefaultelementnamespace"";/BOOK/CHAPTER[.
contains("insertingXMLdataintoatable"1;Note:vThetextcontainedwithinCHAPTERincludesthetextcontainedwithintheABSTRACTandTITLEelementsthatarethedescendantsofCHAPTER.
vThesearchstringisnotcase-sensitive,andlinguisticvariationsofthesearchwordsareconsidered.
Table18.
ResultID2Example5:FindalldocumentswitharootelementBOOK(innamespace"http://www.
ibm.
com/digital_media_library")thathaveadirectdescendantCHAPTER(alsoinnamespace"http://www.
ibm.
com/digital_media_library").
CHAPTERmusthaveadirectdescendantNUMBER(innamespace"http://www.
ibm.
com/digital_media_library")withavalueof1,andalsocontaintextinformationaboutsearchinganXMLnamespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declarenamespacens1="http://www.
ibm.
com/digital_media_library";/ns1:BOOK/ns1:CHAPTER[.
contains("searchXMLusinganamespace")andNUMBER=1]1;Document#3istheonlydocumentwithtagsinthecorrectnamespace,butithaskeywordmatchesonlyinachapterwithanumbervalueof2(not1).
Norowsarereturned.
Table19.
ResultIDExample6:FindalldocumentswitharootelementBOOK(innamespace"http://www.
ibm.
com/digital_media_library")thathaveadirectdescendantCHAPTER(innamespace"http://www.
ibm.
com/digital_media_library").
CHAPTERmusthaveadirectdescendantNUMBER(innamespace"http://www.
ibm.
com/digital_media_library")withavalueof1.
BOOKmusthaveadescendantCHAPTER(notnecessarilywithaNUMBERdescendant)thatcontainstextinformationaboutsearchinganXMLnamespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declarenamespacens1="http://www.
ibm.
com/digital_media_library";/ns1:BOOK[ns1:CHAPTERcontains("searchXMLusinganamespace")]/ns1:CHAPTER[ns1:NUMBER=1]1;54IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||||||||Document3doeshaveaCHAPTERelementthatmatchestheCONTAINScriteria,andalsohasaCHAPTERelementwithadescendantNUMBERthathasavalueof1.
Therefore,document3isamatchforthisquery.
Table20.
ResultID3Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
XMLsearchexampleThisexampleincludesatableofXMLdocuments,atextsearchindexoveranXMLcolumninthetable,andsixSQLtextsearchesusingCONTAINS.
Note:Byusingthecodeexamples,youagreetothetermsofthe"Codelicenseanddisclaimerinformation"onpage128.
vCreateatableXML_DOCUMENTSinschemaXMLTESTtostoretheXMLdocuments:CREATETABLEXMLTEST.
XML_DOCUMENTS(IDINT,XML_DATAXML,PRIMARYKEY(ID));vCreateatextsearchindexcalledXML_INDEXovertheXMLcolumn:callSYSPROC.
SYSTS_CREATE('XMLTEST','XML_INDEX','XMLTEST.
XML_DOCUMENTS(XML_DATA)','');vInsertsomeXMLdocuments:INSERTINTOXMLTEST.
XML_DOCUMENTS(ID,XML_DATA)VALUES(1,''||'OmniFindTextSearchServerforDB2'||'1>'||''||'1'||'Introduction'||'ThischapterwillintroducethereadertothecapabilitiesofOmniFindforDB2forIBMi'||''||''||'2'||'CreatingaTextSearchIndex'||'Thischapterwillexplainhowtocreateatextsearchindex'||''||'');INSERTINTOXMLTEST.
XML_DOCUMENTS(ID,XML_DATA)VALUES(2,''||'UsingtheXMLdatatypeforDB2forIBMi'||'2>'||''||'1'||'Introduction'||'ThischapterwillintroducethereadertotheDB2XMLdatatype'||''||''||OmniFindTextSearchServer55||||||||||||||||||||||||||||||||||||||||||||||||||||||||'2'||'InsertingXMLdataintoaDB2table'||'ThischapterwillexplainhowtoinsertXMLdataintoaDB2table'||''||''||'3'||'SearchingXMLdata'||'ThischapterwillexplainhowtoquerydatainXMLcolumnsusingtheCONTAINSandSCOREUDFS'||''||'');INSERTINTOXMLTEST.
XML_DOCUMENTS(ID,XML_DATA)VALUES(3,''||'UsingNamespaceswithOmniFindTextSearchServerforDB2forIBMi'||'2'||''||'1'||'Introduction'||'ThischapterwillintroducethereadertoXMLnamespaces'||''||''||'2'||'Usingdefaultnamespaces'||'ThischapterwillexplainhowtouseanamespaceinanXMLsearch'||''||'');vUpdatetheindex:CALLSYSPROC.
SYSTS_UPDATE('XMLTEST','XML_INDEX','');ExamplequeriesSearch1:FindalldocumentsthathavearootelementBOOKwithadirectdescendantTITLEthatcontainsDB2.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''/BOOK/TITLE[.
contains("DB2"1;Becauseanamespaceprologisnotspecifiedinthesearchterm,nonamespaceisconsideredinthesearch.
Table21.
ResultID123Search2:FindalldocumentsthathavearootelementBOOKwithadirectdescendantTITLEthatcontainsDB2.
UseadefaultelementnamespacetoindicatethatBOOKandTITLEmustbeinthe"http://www.
ibm.
com/digital_media_library"namespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declaredefaultelementnamespace"http://www.
ibm.
com/digital_media_library";/BOOK/TITLE[.
contains("DB2"1;56IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||Table22.
ResultID3Search3:FindalldocumentsthathavearootelementBOOKthathasanattributepublication_dateafter"2010-01-01"andhasachildelementTITLEthatcontainsDB2.
RestrictthesearchsothattagsBOOKandTITLEmustnotexistinanynamespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declaredefaultelementnamespace"";/BOOK[@publication_date>xs:date("2010-01-01")]/TITLE[.
contains("DB2"1;Table23.
ResultID2Search4:FindalldocumentswitharootelementBOOK(notinanynamespace)thathaveadirectdescendantCHAPTER(alsonotinanamespace)thatcontainsinformationaboutinsertingdataintoanXMLtable.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declaredefaultelementnamespace"";/BOOK/CHAPTER[.
contains("insertingXMLdataintoatable"1;Note:vThetextcontainedwithinCHAPTERincludesthetextcontainedwithintheABSTRACTandTITLEelementsthatarethedescendantsofCHAPTER.
vThesearchstringisnotcase-sensitive,andlinguisticvariationsofthesearchwordsareconsidered.
Table24.
ResultID2Search5:FindalldocumentswitharootelementBOOK(innamespace"http://www.
ibm.
com/digital_media_library")thathaveadirectdescendantCHAPTER(alsoinnamespace"http://www.
ibm.
com/digital_media_library").
CHAPTERmusthaveadirectdescendantNUMBER(innamespace"http://www.
ibm.
com/digital_media_library")withavalueof1,andalsocontaintextinformationaboutsearchinganXMLnamespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declarenamespacens1="http://www.
ibm.
com/digital_media_library";/ns1:BOOK/ns1:CHAPTER[.
contains("searchXMLusinganamespace")andNUMBER=1]1;Document#3istheonlydocumentwithtagsinthecorrectnamespace,butithaskeywordmatchesonlyinachapterwithanumbervalueof2(not1).
Norowsarereturned.
OmniFindTextSearchServer57||||||||||||||||||||||||||||||||||||||||||||||||Table25.
ResultIDSearch6:FindalldocumentswitharootelementBOOK(innamespace"http://www.
ibm.
com/digital_media_library")thathaveadirectdescendantCHAPTER(innamespace"http://www.
ibm.
com/digital_media_library").
CHAPTERmusthaveadirectdescendantNUMBER(innamespace"http://www.
ibm.
com/digital_media_library")withavalueof1.
BOOKmusthaveadescendantCHAPTER(notnecessarilywithaNUMBERdescendant)thatcontainstextinformationaboutsearchinganXMLnamespace.
SELECTIDFROMXMLTEST.
XML_DOCUMENTSWHERECONTAINS(XML_DATA,'@xmlxp:''declarenamespacens1="http://www.
ibm.
com/digital_media_library";/ns1:BOOK[ns1:CHAPTERcontains("searchXMLusinganamespace")]/ns1:CHAPTER[ns1:NUMBER=1]1;Document3doeshaveaCHAPTERelementthatmatchestheCONTAINScriteria,andalsohasaCHAPTERelementwithadescendantNUMBERthathasavalueof1.
Therefore,document3isamatchforthisquery.
Table26.
ResultID3XMLsearchquerygrammarThegrammarforXMLSearchisbasedonasubsetoftheXPathlanguage,whichisdefinedbyExtendedBackus-NaurForm(EBNF)grammar.
Queriesthatdonotconformtothesupportedgrammararerejectedbythequeryparser.
TheEBNFgrammarhasbeensimplifiedinthefollowingwaysby:vDisallowingabsolutepathnamesinpredicateexpressions.
vRecognizingonlyoneaxis(tag)andonlyintheforwarddirection.
vApplyingadditionalsemanticrestrictionstotheuseoftheWildcardcharacter(seeprevioussectionon"PathExpressionWildcardSupport"in"XMLsearch"onpage44.
vRequiringthatthenamespacedeclarationisspecifiedinthesearchstringbeforeanyusage,impliedorexplicit,ofthenamespace.
Ifthenamespacedeclarationisnotincluded,namespacesarenotconsideredinthesearch.
vRelativepathexpressionsmusthaveatagorattributenameincludedintheexpression.
Thequery'/'toselecttherootnode,and'//'toselectallnodesarenotvalidexpressions.
ThefollowingtableshowsthesupportedgrammarinEBNFnotation.
Table27.
SupportedquerygrammarinEBNFnotationSymbolProductionXMLQuery::=QueryPrefixNameSpaceDeclarationQueryString|QueryPrefixQueryStringQueryPrefix::=@xmlxp:QueryString::="'"PathExpr"'"PathExpr::=RelativePathExpr|"/"RelativePathExpr|"//"RelativePathExpr58IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||Table27.
SupportedquerygrammarinEBNFnotation(continued)RelativePathExpr::=StepExprStepExpr)*StepExprAbbrevForwardStep)PredicateAbbrevForwardStepQName|"*")Predicate::="["PredicateExpr"]"PredicateExpr::=Expr|PredicateExpr("and"|"or")|"("PredicateExpr")"Expr::=ComparisonExpr|ContainmentExprComparisonExpr::=PathExprComparisonOpLiteralComparisonOpLiteral::=StringLiteral|NumericLiteral|DateLiteralContainmentExpr::=PathExpr"contains""("StringLiteral")"|PathExpr"excludes""("StringLiteral")"StringLiteralDateLiteral::="xs:date(\""xmlDate"\")"|"xs:dateTime(\""xmlDateTime"\")"xmlDate::=yyyy"-"mm"-"ddxmlDateTime::=yyyy"-"mm"-"dd[T]hh":"mm":"ss".
"uuuuuuNameSpaceDeclaration::=defaultNameSpace(NameSpacePrefixDeclaration)*defaultNameSpace::="declaredefaultelementnamespace"StringLiteral";"NameSpacePrefixDeclaration::="declarenamespace"NameSpacePrefix"="StringLiteral";"NameSpacePrefixAdministeranOmniFind(r)TextSearchServerforDB2(r)foriYoucanadministertheOmniFindTextSearchServerforDB2foriusingthefollowingtechniquesandtools.
StarttheOmniFindTextSearchServerforDB2foriYoucanstarttheOmniFindTextSearchServerforDB2foribycallingSYSPROC.
SYSTS_START.
OMNIFINDstartsthetextsearchserverautomaticallyasneeded,aslongastheSERVERSTATUSinQSYS2.
SYSTEXTSERVERSis0.
Thispolicyallowsthetextsearchservertostartautomaticallywhenthehostsystemstarts.
However,youcanstarttheservermanuallyifnecessary.
Tostarttheserver:CALLSYSPROC.
SYSTS_START(serverid)Ifsuccessful,theSERVERSTATUSinQSYS2.
SYSTEXTSERVERSissetto0afteryoucalltheprocedure.
Whentheserverislocal,thefollowingjobsareactiveinthebackground:vQJVAEXECuserxBCI0.
0JVM-com.
ibm.
esvQJVAEXECuserxBCI0.
0PGM-textExtracvQJVAEXECuserxBCI0.
0PGM-textExtracOmniFindTextSearchServer59|||||||||||||||||||||||||||||||||||||||||||||||||vQJVAEXECuserxBCI0.
0PGM-textExtracvQJVAEXECuserxBCI0.
0PGM-textExtracwhereuserxistheuserIDoftheadministratorthatcalledthestoredprocedure.
Itmighttakeafewminutesbeforeallthesejobsareactiveandthetextservercanbeused.
Relatedreference:"SYSPROC.
SYSTS_START"onpage14YoucanenableDB2textsearchfunctionsbycallingtheSYSPROC.
SYSTS_STARTstoredprocedure.
"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
StoptheOmniFindTextSearchServerforDB2foriYoucanstoptheOmniFindTextSearchServerforDB2forimanuallybyusingtheshutdownscriptthatisprovided.
IfyouinstalledtheOmniFindTextSearchServerforDB2foriasaservice,thetextsearchserverstopsautomaticallyeachtimethatthehostsystemisshutdown.
However,youcanstoptheservermanuallyevenifyouinstalledtheOmniFindTextSearchServerforDB2foriasaservice.
TostoptheOmniFindTextSearchServerforDB2fori:1.
IndicateintheSYSTEXTSERVERcatalogthattheserverisstoppedbycallingSYSPROC.
SYSTS_STOP.
vTostopallservers:CALLSYSPROC.
SYSTS_STOP().
vTostopaspecificserver:a.
Querytheservercatalogtogettheserveridthatyouwanttostop:SELECTSERVERID,SERVERPORT,SERVERSTATUS,SERVERPATHFROMQSYS2.
SYSTEXTSERVERSNote:SERVERPATHidentifiestheserver.
SERVERSTATUSindicateswhethertheserveriscurrentlyactive(0)orinactive(1).
b.
CallSYSPROC.
SYSTS_STOP,specifyingthenumericserveridorthealiasnameoftheserveryouwanttostop:CALLSYSPROC.
SYSTS_STOP(serverid).
2.
(Optional)Stoptheserveritselfbycallingtheshutdownscript.
Stoppingtheserverendsallthetextsearchserverjobsonthehostsystem.
StoptheserverintheQshellenvironment.
Toshutdownthelocalserver,enterthefollowingcommandfromthecommandline:QSHCMD('cd/QOpenSys/QIBM/ProdData/TextSearch/server1/bin;shutdown.
sh').
Iftheservertobeshutdownisnotthedefaultlocalservercreatedbytheinstallprocess,youneedtoobtainthecorrectSERVERPATHvaluefromQSYS2.
SYSTEXTSERVERS.
UsethatSERVERPATHinsteadof/QOpenSys/QIBM/ProdData/TextSearch/server1/bin.
Ifyoustoptheserverbyusingtheshutdownscript,theSERVERSTATUScatalogisnotchangedtotheinactive(1)status.
WhentheSYSTS_CREATE,SYSTS_UPDATE,andSYSTS_DROPstoredproceduresarecalledthenexttime,orwhenaCONTAINSorSCOREbuilt-infunctionisinvokedaspartofanSQLquery,theserverwillstartautomatically.
Relatedreference:"SYSPROC.
SYSTS_STOP"onpage16YoucancalltheSYSPROC.
SYSTS_STOPstoredproceduretostopDB2textsearchfunctions.
ThisstoredproceduresetstheSERVERSTATUSvalueinthecatalogQSYS2.
SYSTEXTSERVERSto1(stopped).
"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministration60IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||table.
SaveandrestoretextsearchindexesYoucansaveandrestoreyourtextsearchindexeswithorwithoutdata.
SaveandrestoreatextsearchindexwithoutdataYoucansaveandrestoreatextsearchindexstructurewithouttheindexdata.
ThesaveandrestoreprocesscanbeaccomplishedusingtheSAVOBJandSAVLIBCLcommands.
WhenyoucreateatextsearchindexusingSYSTS_CREATE,aDB2viewiscreatedusingtheindexschemaandnameastheviewname.
Theviewservesasamechanismforsavingandrestoringthestructureoftheindex.
Theusercansavetheviewusingthesamemethodsforsavingdatabasetablesandviews.
(SeeSAVOBJorSAVLIBCLcommands.
)Savingtheviewautomaticallysavesadditionalinformationneededtorecreatetheindexduringrestore.
TheviewcanberestoredusingtheRSTOBJorRSTLIBCLcommand.
DB2forirecognizesthattheviewrepresentsatextsearchindexandrecreatestheindex.
Aftertheindexstructurehasbeenrecreated,anupdatewillbesubmittedtoabackgroundjobtorepopulatetheindexdata.
Additionalconsiderationsneedtobemadeduringtherestoreprocess:1.
Ifthetextsearchservercannotbestarted,orarequiredproductisnotinstalledonthesystem,therestorefails.
SeetheSoftwarerequirementsforalistofrequiredproducts.
2.
Ifthetextsearchindexexistsonthesystem,thefollowingactionsaretaken.
a.
Iftheexistingindexinformationexactlymatchestheindexbeingrestored,therestoresucceeds.
Theindexisnotrebuilt.
b.
Iftheexistingindexinformationdoesnotmatchtheindexbeingrestored,andcannotbemodifiedtomatchwithoutrecreatingtheindex,therestorefails.
c.
Iftheexistingindexinformationdoesnotmatchtheindexbeingrestored,butcanbemodifiedtomatchusingSYSTS_ALTER,thentheexistingindexisalteredtomatchtheindexthatwassaved.
Theindexisnotrebuilt.
3.
Theindexisrestoredtousethesametextsearchserverthatwasinuseatthetimeofthesave.
Iftheserverthatwasusedatthetimeofthesaveisnotdefined,acurrentlyavailableserverisselected.
Ifthesavedserverisdefinedbutnotavailable,therestorefails.
4.
Ifthetextsearchindexcannotbecreatedforanyotherreason,suchasanincompatiblecolumninthebased-ontable,therestorefails.
5.
ThestagingtablenameinQSYS2,triggernamesthatareaddedtothebased-ontable,andthecollectionnameonthetextsearchservercanchange,sincetheyaregeneratedbythesystem.
6.
Synonymsthathavebeenaddedtothetextsearchindexsynonymdictionaryarenotpreserved.
7.
IftheindexexistsintheSystemcatalogsatrestoretime,andtheviewdoesnotcurrentlyexistonthesystem,onlytheviewisrestored.
Thestagingtable,textsearchservercollection,andtriggersonthebased-ontablearenotcreated.
Inthiscase,thetextsearchindexisassumedtobepartofalargerrestorewheretheindividualpiecesoftheindexweresavedexplicitlybytheuser,andarenowallbeingrestored(suchasrestoringtheentiresystem).
Alltherequiredpiecesoftheindexmustberestoredbeforetheindexworks.
Itistheusersresponsibilitytoensurethatallpiecesoftheindexaresynchronized.
OmniFindTextSearchServer61||||||||||||||||||||||||||||||||||||||||TextsearchindexesaresupportedbytheRestoreDeferredObjects(RSTDFROBJ)command.
TheuseoftheDFRIDparameterontheRSTOBJandRSTLIBCLcommandsisrecommended.
ThisparameterallowsTextSearchindexestoberestoredusingtheRSTDFROBJcommandaftercorrectingcommonconditionsthatpreventcreationoftheindex.
Examplesofconditionsthatcausetheindextobedeferredare:vArequiredproductwasnotinstalled.
vAtextsearchserverwasnotavailableordefined.
vThebasedontabledidnotexist.
Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
"SYSPROC.
SYSTS_ALTER"onpage26YoucancalltheSYSPROC.
SYSTS_ALTERstoredproceduretomodifyattributesofanindexthatwascreatedbySYSPROC.
SYSTS_CREATE.
Onlyattributesexplicitlyspecifiedonthisprocedurearechanged.
Allotherattributesoftheindexremainunchanged.
Relatedinformation:SaveObject(SAVOBJ)SaveLibrary(SAVLIB)RestoreObject(RSTOBJ)RestoreLibrary(RSTLIB)RestoreDeferredObjects(RSTDFROBJ)SaveandrestoreatextsearchindexwithdataSavingandrestoringatextsearchindexwithdataisamorecompletedoperationthanwithoutdata.
Youmustsavethefollowingobjects:vThetextsearchindex(storedintheintegratedfilesystem).
vThestagingtableusedasalogfilethattracksrecordchangesinthebasetable(overwhichtheindexisbuilt).
ThestagingtableisinlibraryQSYS2.
ItsnamebeginswithQDBTS,forexample,QDBTS00001.
vTheview,whichisthedatabaseobjectrepresentingthetextindex.
Theviewhasthesamenameasthetextindex.
vThebasetableoverwhichtheindexisbuilt.
vTheSQLcatalogsthatstoretheinformationtotracktheindex.
Completethefollowingstepstosavethetextsearchindexes:1.
Recommended:bringtheindexesuptodatebyfirstperformingupdateoperations(SYSTS_UPDATE)forthetextsearchindexes.
2.
SavethebasetableandviewusingstandardsavetechniquessuchastheSAVOBJcommand.
3.
SavethestagingtablesthatareinQSYS2usingstandardsavetechniques.
Forexample,SAVOBJLIB(QSYS2)OBJ(QDBTS*).
4.
SavethetextsearchindexcatalogsinQSYS2:ThecatalognamesallbeginwithSYSTXT,forexample,SYSTXTSRVR.
LiketheotherSQLcatalogsinQSYS2,itisyourresponsibilitytoensurethatabackupcopyissavedandavailable.
Thisbackupcopycanbeaccomplishedinoneoftwoways:a.
TheentirelibrarycanbesavedaspartoftheSAVLIBcommand,specifyingparameterLIBaseither*ALLUSRor*IBM.
62IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||b.
ThespecifictextsearchcatalogscanbesavedusingtheSAVOBJcommand,specifyingLIB(QSYS2)andOBJ((SYSTXT*)).
5.
Savethetextsearchindexinformationintheintegratedfilesystem.
Thisinformationincludestheentirecontentsoftheconfigdirectoryunderthetextserverpath.
ThetextserverpathcanbedeterminedbyqueryingtheSERVERPATHcolumnoftheSYSTXTSRVRcatalogfortheserverofinterest.
Theserverpathhasthedirectorybinappendedtoit,whichyoureplacewiththeconfigdirectory.
AcommonsavetechniqueistousetheSAVcommand,thoughanytypeofsavecompressionworks.
Note:ThissaveinformationisonlyapplicabletotextserversrunningonIBMi.
Example:SupposeyouwanttosaveallthetextindexesassociatedwiththedefaulttextservercreatedbyOMNIFIND.
YouhaveatableQGPL/MYDOCSwithtextindexQGPL/MYDOCIXbuiltoverit.
Inthisexample,thesavemediaaresavefiles.
Completethefollowingsteps:1.
SaveallthestagingtablesandtheOMNIFINDcatalogsfromQSYS2:SAVOBJOBJ(QDBTS*SYSTXT*)LIB(QSYS2)DEV(*SAVF)SAVF(QGPL/SAVFQSYS2)2.
Savethebasetableandview:SAVOBJOBJ(MYDOCSMYDOCIX)LIB(QGPL)DEV(*SAVF)SAVF(QGPL/SAVFMYFILE)3.
UsingSQL,getthepathnameofthetextserver.
Inthisexample,serverid=2:SELECTSERVERPATHFROMsystxtsrvrWHEREserverid=2TheSERVERPATHvaluereturnedis/QOpenSys/QIBM/ProdData/TextSearch/server1/bin/.
Note:Verifythatyouarequeryingforthecorrectserver.
4.
Substituteconfigforbin/andsavethetextindexes:SAVDEV('/QSYS.
LIB/QGPL.
LIB/SAVIFS.
FILE')OBJ(('/QOpenSys/QIBM/ProdData/TextSearch/server1/config'))ThetextindexesarenowsavedinsavefilesQGPL/SAVFMYFILE,QGPL/SAVFQSYS2,andQGPL/SAVIFS.
Thetextindexrestoremustbedoneinthesameorderasthesave.
TheQSYS2catalogsMUSTberestoredfirst.
Relatedreference:"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
Relatedinformation:SaveObject(SAVOBJ)SaveLibrary(SAVLIB)ProblemdeterminationYoucanusethesystemandtracemessagesloggedtodeterminethesourceofproblemsthatmightoccur.
TheOmniFindTextSearchServerforDB2foriserverlogsarelocatedinthe/logdirectory.
Thedefaultserverlogcreatedatinstallationislocatedinthe/QOpenSys/QIBM/ProdData/TextSearch/server1/logdirectory.
OmniFindTextSearchServer63||||||||||||||||||||||||||||||||||||Bydefault,thetracelogisturnedoff,andthesystemloglevelissettoinformational.
Youcanusetheconfigurationtooltochangethetraceandlogleveloptions.
Theserverlogsarerotatedbysize.
Thefivemostrecentcopiesofserverlogsthatarenomorethan8MBarestored.
Youcanviewandsavetheserverlogsbyusingthescriptthatisprovided.
OnIBMioraLinuxserver,thescriptislogformatter.
sh.
OnaWindowsserver,thescriptislogformatter.
bat.
OptionsThescripthasthefollowingoptions.
-flogfileSpecifiestheserverlogfilethatyouwanttoformat.
-llocaleSpecifiesthelocaletousewhenwritingthereformattedmessages.
Forexample,specifyen_USforEnglish,orja_JPforJapanese.
Thisvalueisoptional.
Thedefaultvalueisen_US.
-ooutputfileSpecifiestheoutputfilewherethereformattedlogmessagesaretobewrittenbyusingUTF-8encoding.
Thisvalueisoptional.
Ifyoudonotspecifythisoption,astandardoutputfileisused.
-Printsthehelpmessage.
Thisvalueisoptional.
-vSpecifiesthemodefordisplayingdebuggingmessages.
Thisvalueisoptional.
ViewandsaveserverlogsYoucanusetheprovidedscriptstoviewandsavetheserverlogs.
Theselogscanhelpyoudeterminethesourceofproblems.
Toviewandsaveaserverlog:1.
Toviewtheserverlog,runoneofthefollowingcommands:OptionDescriptionOnIBMi(withintheQSHenvironment)bin/logformatter.
sh-flog/System.
0.
logOnaLinuxserverbin/logformatter.
sh-flog/System.
0.
logOnaWindowsserverbin/logformatter.
bat-flog/System.
0.
log2.
Tosavetheserverlogtoafilesothatyoucanreadtheloginafileeditor,runoneofthefollowingcommands:OptionDescriptionOnIBMi(withintheQSHenvironment)bin/logformatter.
sh-flog/System.
0.
log–oOnaLinuxserverbin/logformatter.
sh-flog/System.
0.
log–oOnaWindowsserverbin/logformatter.
bat-flog/System.
0.
log–o64IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriAdministrationtoolsOMNIFINDprovidestoolsthatyoucanuseforcommontasks.
Thesetasksincludeconfiguringandadministeringanadditionaltextsearchserver,andaddingasynonymdictionarytoacollection.
ThesetoolsareshellscriptsratherthanCLcommands.
TheycanbecalledwithinthescriptenvironmentthatisstartedthrougheithertheStartQSH(STRQSH)orQSH(QSH)CLcommands.
ThesetoolsdonotauthenticateuserIDs.
However,thesetoolscanberunonlybyauserwithvalidaccesstothetextsearchserver.
Relatedinformation:StartQSH(STRQSH)StartQSH(QSH)ConfigurationtoolUsetheconfigurationtooltocustomizeconfigurationsettingsafteryouinstallOmniFindTextSearchServerforDB2fori.
Tocustomizemostoftheconfigurationsettings,youmuststopthetextsearchserverbeforerunningtheconfigurationtool.
However,whentheserverisrunning,youcandisplaythefollowingoptions:vthecurrentauthenticationtokenvtheserverportvthecurrentpropertiesofthesystemTheconfigServerAndDB2toolTheconfigServerAndDB2(configServerAndDB2.
sh)toolislocatedinintegrated-file-systemdirectory/QOpenSys/QIBM/ProdData/TextSearch.
ThistoolcanbeusedtocreateormodifyentriesintheDB2catalogfileSYSTEXTSERVERS.
Itcanalsobeusedtoconfiguretheauthenticationtokenortheportnumberassociatedwiththespecificserver.
ThetoolmodifiesorsetsthevaluesforSERVERAUTHTOKENandSERVERPORTintheDB2catalogfileSYSTEXTSERVERS.
Ifyouwanttocreateanadditionalserverthatrunslocallytoyoursystem,usethe"ServerInstancetool"onpage75instead.
TheconfigServerAndDB2(configServerAndDB2.
sh)tooliscalledwithfiveparameters:1.
ThefirstparameteriseithergenerateTokenorconfigureHTTPListener.
2.
Thesecondparameteris-serverPath.
3.
Thethirdparameteristhepathtotherootnodeintheintegratedfilesystemwheretheinformationrelatedtotheserverisstored.
Example:/QOpenSys/QIBM/ProdData/TextSearch/server2.
4.
Thefourthandfifthparametersvarydependingonthevalueofthefirstparameter.
vIfthefirstparameterisgenerateToken,thenthefourthparameteris-seedfollowedbyaninteger(forexample,1)asthefifthparametervIfthefirstparameterisconfigureHTTPListener,thenthefourthparameteris-adminHTTPPort.
Thefifthparameterisanintegervaluethatisusedasthesocketportfortheserver.
Herearetwoexamples:OmniFindTextSearchServer65||||||||vSTRQSHcd/QOpenSys/QIBM/ProdData/TextSearchconfigServerAndDB2.
shgenerateToken-serverPath/QOpenSys/QIBM/ProdData/TextSearch/server2-seed1vSTRQSHcd/QOpenSys/QIBM/ProdData/TextSearchconfigServerAndDB2.
shconfigureHTTPListener-serverPath/QOpenSys/QIBM/ProdData/TextSearch/server2-adminHTTPPort9997TheconfigToolscriptTheconfigTool.
shscriptisavailableforeachlocalserver.
Itisnotrecommendedtouseitinmodifyingserverentries.
Youcanuseittoprinttheserverinformation(suchasprintAllandprintToken).
Table28.
CommandstoruntheconfigurationtoolOnIBMi:configTool.
shOnaLinuxserverconfigTool.
shOnaWindowsserverconfigTool.
batCommandoptionsTheconfigurationtoolsupportsthefollowingcommandoptions:configureParamsSpecifiesthesystemparametersthatyoucanconfigure.
Youcanconfigurethefollowingparameters:-configPathSpecifiestheabsolutepathtotheconfigurationfolderthatcontainstheconfig.
xmlfile.
-adminHTTPPortSpecifiestheadministrationHTTPportnumber.
Ifanerroroccurs,anerrorcodeof-3isreturned.
-logPathSpecifiestheabsolutepathtothelogdirectory.
-temDirPathSpecifiestheabsolutepathtothetemporarydirectory.
-numberOfIndexersSpecifiesthenumberofconcurrenttextsearchindexingsubsystems.
-numberOfTokenizersSpecifiesthenumberofconcurrentsubsystemsthatareusedforparsinginputintotokens.
66IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori-maxDocumentSizeSpecifiesthemaximumnumberofcharactersthataretobeindexedforadocument.
Ifanerroroccurs,anerrorcodeof-3isreturned.
-logLevelSpecifiestheloglevelforsystemmessagesinthelogfile.
Thedefaultlevelisinformational.
Additionaloptionsarewarningandsevere.
-maxHeapSizeStartsandendstheheapsizeinaformatthatisacceptedbytheJavaVirtualMachine.
Ifanerroroccurs,anerrorcodeof-5isreturned.
printTokenPrintsthecurrentauthenticationtokenandencryptionkey.
printAllPrintsallthecurrentvaluesfortheoptionsthatyoucanconfigurewiththistool.
printAdminHTTPPortPrintsthecurrentvaluefortheadministrationHTTPport.
generateTokenGeneratestheauthenticationtoken.
Globalarguments-configPathSpecifiestheabsolutepathtotheconfigurationfolderthatcontainstheconfig.
xmlfile.
Thisglobalargumentismandatory.
-localeSpecifiesthefive-characterlocalesettingforwritingmessagestothetracefile.
Ifyoudonotspecifythissetting,thedefaultvalue,-en_US,isused.
ExampleOnaLinuxserver,usethefollowingcommandtoprintthecurrentauthenticationtoken:configTool.
shprintToken-configPathRelatedinformation:StartQSH(QSH)SYSPROC.
SYSTS_REMOVEYoucanremoveorphanedindexeswiththeSYSPROC.
SYSTS_REMOVESQLstoredprocedure.
AuthorizationThecollection-nameofthepossibleorphanedindexescanbeidentifiedbyusingtheQDBTS_LISTINXSTSUserDefinedTableFunction(UDTF).
TheprivilegesheldbytheauthorizationIDofthestatementmustincludeatleastoneoftheseprivileges:v*JOBCTLauthorityvQIBM_DB_SQLADMsecurityspecialfunctionusageSyntax>>-SYSPROC.
SYSTS_REMOVE(collection-name)->>-SYSPROC.
SYSTS_REPRIMEINDEX(indexSchema,indexName,options)->>-SYSPROC.
SYSTS_VALIDITYCHECK(indexSchema,indexName,autoFix)->PaixoamorflorlindaworldwidepatenttrackingsystemwptsAddasynonymdictionarytoacollection:Specifyingthesynonymgroupsinasynonymdictionaryimprovesthequalityoftextsearchresults.
TheOMNIFINDadministratorhasthecorrectauthorityandprivilegestorunthesynonymtoolandIBMNavigatorfori.
vToaddasynonymdictionarytoacollectionwiththesynonymtool,followthesesteps:1.
CreateasynonymXMLfilebyspecifyingthesynonymgroups,asshowninthefollowingexample:Paixoamorflorlindaworldwidepatenttrackingsystemwpts2.
CopythesynonymXMLfiletoanydirectoryonthetextsearchserver.
3.
Usethesynonymtooltoaddthesynonymdictionarytoacollection.
Youcanaddasynonymdictionaryinappendmodeorreplacemode.
Ifyouaddasynonymdictionaryinappendmode,thenewsynonymsareaddedtotheexistingsynonymdictionary.
Ifyouaddasynonymdictionaryinreplacemode,theexistingsynonymsarereplacedbythenewsynonymsthatyoudefinedforthetextsearchindex.
OptionDescriptionOnIBMi,enterthefollowingcommand(withintheQSHinterface):synonymTool.
shimportSynonym-synonymFile-collectionName-replace-configPathOmniFindTextSearchServer71OptionDescriptionOnaLinuxserver,enterthefollowingcommand:synonymTool.
shimportSynonym-synonymFile-collectionName-replace-configPathOnaWindowsserver,enterthefollowingcommand:synonymTool.
batimportSynonym-synonymFile-collectionName-replace-configPathIftheformatoftheXMLfileisnotvalid,oriftheXMLfileisempty,anerrorcodeisreturned.
vToimportsynonymdictionarytoacollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectImportSynonymDictionary.
Removeasynonymdictionaryfromacollection:Usethescriptthatisprovidedtoremoveasynonymdictionaryfromacollection.
TheOMNIFINDadministratorneedstoretrievethenameofthecollectionfromwhichyouwantthesynonymdictionarytoberemoved.
Runthescripttoremovethesynonymdictionaryfromacollection.
OptionDescriptionOnIBMi,enterthefollowingcommand(withintheQSHinterface):removeSynonym.
sh-collectionName-configPathOnaLinuxserver,enterthefollowingcommand:removeSynonym.
sh-collectionName-configPathOnaWindowsserver,enterthefollowingcommand:removeSynonym.
bat-collectionName-configPathIfadatabasehasseveraltextsearchindexes,youmustcompletethistaskforeachofthecorrespondingcollections.
FindorphanedandmissingindexesYoucanfindorphanedandmissingindexesusinganSQLUserDefinedTableFunction(UDTF)namedQDBTS_LISTINXSTS.
AnindexcanbeorphanedifaSYSTS_DROPstoredprocedureiscalledandtheserverisstoppedatthetimetheprocedureisrunning.
72IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||TheQDBTS_LISTINXSTSfunctioncombinesalltheintegrated-file-systemcollectionsandcatalogindexesinthecurrentnamespaceintoonetable.
Thefunctiondecideswhichindependentauxiliarystoragepool(ASP)or*SYSBASEisset.
ItthenscansthecollectiondirectoryofeachserverintheindependentASPor*SYSBASE.
For*SYSBASE,eachserverdirectoryunder/QOpenSys/QIBM/ProdData/TextSearchischecked.
ForindependentASPs,eachserverdirectoryunder/theASPnumber/QOpenSys/QIBM/ProdData/TextSearchischecked.
Forexample,iftheindependentASPnumberis67,eachserverdirectoryunder/67/QOpenSys/QIBM/ProdData/TextSearchischecked.
Forcatalogindexinformation,dataisobtainedfromcatalogtableQSYS2.
SYSTEXTINDEXES.
IfyouwanttocheckserversonanindependentASP,issuetheSetAuxiliaryStoragePoolGroup(SETASPGRP)commandbeforethisfunctioniscalled.
Ifyouwanttoremovepossibleorphanedindexesfromtheintegratedfilesystemaftertheyareidentified,usetheSYSPROC.
SYSTS_REMOVEstoredprocedureorthe"Advancedadministration"onpage74(adminTool.
sh).
TermsOrphanedindexAcollection(anindex)existsintheintegratedfilesystemdirectoryoftheserver,butnocorrespondingindexisrecordedincatalogQSYS2.
SYSTEXTINDEXES.
MissingindexIndexrecordsexistincatalogQSYS2.
SYSTEXTINDEXES,butthecorrespondingcollectiondirectorydoesnotexist.
Syntax>>-QDBTS_LISTINXSTS(--null--)--->-configPathadminTool.
sh-[delete|status]–collectionName-configPathadminTool.
bat-[delete|status]–collectionName-configPathTable30.
CommandstodisplaytheversionoftheserverandtoreportallthecollectionsOnIBMi(withintheQSHinterface)OnaLinuxserverOnaWindowsserveradminTool.
sh-[version]-configPathadminTool.
sh-[version|reportAll]-configPathadminTool.
bat-[version|reportAll]-configPath74IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriOptionsstatusChecksthestatusofthecollection.
deleteSpecifiesthatyouwanttodeletetheorphanedcollection.
versionDisplaystheversionoftheserver.
reportAllReportsallthecollectionsthatareonthetextsearchserver.
ExampleTofindouttheversionoftheserver,enterthefollowingcommandonaLinuxserver:adminTool.
sh–version-sWhenyouuseaWindowsserver,acorresponding.
batscriptisprovided.
ServerInstancetoolYoucanusetheServerInstancetooltocreateordeleteserverson*SYSBASEoranindependentauxiliarystoragepool(ASP).
YoucanalsousetheServerInstancetooltolinkfilesfromaservertotheserverwhereOmniFindTextSearchServerforDB2foriisinstalled.
Bydefault,OmniFindTextSearchServerforDB2foriisinstalledunderdirectory/QOpenSys/QIBM/ProdData/TextSearch/server1.
YoucanusetheServerInstancetooltocompletethefollowingtasksbeforeyouuseittostopserver1on*SYSBASE:vCreateaserveron*SYSBASEorindependentASPsvDeleteaserveron*SYSBASEorindependentASPsvLinkfilesfromaservertoserver1SyntaxServerInstance.
sh–[create|delete|relink]–servernum(-port)(-device)CommandoptionscreateCreatesaserver.
deleteDeletesaserver.
relinkLinksfilesfromaservertoserver1.
Note:Youdonotneedthisoptionafteryouhaveprogramtemporaryfix(PTF)SI31548installedonyoursystem.
ThesystemautomaticallyprocessesthelinkingoperationifyouhavethisPTFinstalled.
OmniFindTextSearchServer75ParametersservernumSpecifiestheservernumber.
Forexample,whenaserverwithservernumber3iscreated,thedirectoryoftheserveris/QOpenSys/QIBM/ProdData/TextSearch/server3.
portSpecifiestheportoftheserver.
Thisparameterisneededonlywhenyoucreateaserver.
deviceSpecifiesthenameoftheindependentASP.
ThisparameterisneededonlywhentheoperationiscompletedontheindependentASP.
ExamplesvTocreateaserverwithservernumber2andportnumber50000on*SYSBASE:ServerInstance.
sh-create-servernum2-port50000vTocreateaserverwithservernumber3andportnumber50001onindependentASPiasp1:ServerInstance.
sh-create-servernum3-port50001-deviceiasp1vTodeleteaserverwithservernumber2on*SYSBASE:ServerInstance.
sh-delete-servernum2vTodeleteaserverwithservernumber3onindependentASPiasp1:ServerInstance.
sh-delete-servernum3-deviceiasp1vTolinkfilesfromaservertoservernumber2on*SYSBASE:ServerInstance.
sh-relink-servernum2vTolinkfilesfromaservertoservernumber3onindependentASPiasp1:ServerInstance.
sh-relink-servernum3-deviceiasp1HealthCheckerHealthcheckerisanenvironmentverificationtoolthatcanbeusedtodiagnoseanyOmniFindTextSearchServerforDB2foriconfigurationproblems.
ItcanbeusedtoverifythattheOmniFindTextSearchServerforDB2foritextserversandindexesarecorrectlyfunctioning,andwillgenerateareportwithwarningsorerrorsforanypotentialissuesfound.
PrerequisitesTheOmniFindTextSearchServerforDB2forihealthcheckerisbasedonARE(ApplicationRuntimeExpertfori,product5770-ARE).
Therefore,AREmustbeinstalledforthistooltorun.
HealthCheckerProceduresHealthcheckerisinvokedthroughaseriesofDB2SQLstoredprocedures.
TheprocedurescanbeinvokedthroughanySQLinterface,includingfromahighlevellanguagesuchasRPGandCOBOL.
Allinformation,includingwarningsorerrors,isreturnedthroughacorrespondingresultset.
SYSPROC.
SYSTS_HC_GENERAL()ThisstoredprocedureisusedtocheckthegeneralhealthoftheOmniFindTextSearchServerforDB2foriproduct.
Theprocedurecheckstheconfigurationoftheproduct.
ThisisanexampletoreturnallwarninganderrormessagesrelatedtogeneralOmniFindTextSearchServerforDB2foriconfigurationinformation:>CALLSYSPROC.
SYSTS_HC_GENERAL();76IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriSYSPROC.
SYSTS_HC_USR_AUTH()ThisstoredprocedureisusedtoidentifyanyauthorityissuesfortheinvokinguserthatwouldpreventthemfromusingtheOmniFindTextSearchServerforDB2foriforsearches.
Thisisanexampletoreturnanywarningsorerrorsregardingtheinvokinguser'sauthorities:>CALLSYSPROC.
SYSTS_HC_USR_AUTH();SYSPROC.
SYSTS_HC_IDX()ThisstoredprocedureisusedtocheckallOmniFindTextSearchServerforDB2foritextindexestoensuretheyareinavalidstate.
Note:IfusinganIASP(IndependentAuxiliaryStoragePool)group,thisonlyappliestotheIASPgroupcurrentlyactiveinthejob.
SYSSTS.
HC_SVR()ThisstoredprocedureisusedtocheckthehealthofalllocalOmniFindTextSearchServerforDB2foritextserversthathavebeendefined.
AdditionalInformationMoreinformationabouttheOmniFindTextSearchServerforDB2forihealthchecker,includingnumerousadditionalstoredproceduresoptionsaswellasaQShellinterface,isavailableondeveloperWorksOmniFindTextSearchServerforDB2foriunderthearticle'HealthChecker'.
IndependentASPconsiderationsforOmniFindTextSearchServerforDB2foriYoucanadministeratextsearchindexonanindependentauxiliarystoragepool(ASP).
TheASPcanbeswitchedbetweenmultiplesystems,sothereareadditionalconsiderations.
AlocaltextsearchserveriscreatedduringtheinstallationofOMNIFIND.
ForindependentASPs,alocaltextserveriscreatedbyanadministratorusingtheServerInstancetool(ServerInstance.
sh)aftertheindependentASPgroupiscreated.
AfteryoucreatealocaltextsearchserverontheindependentASP,theindexdataexistsontheindependentASPfilesystem.
ThedataisavailableiftheindependentASPisswitchedtoadifferentsystem.
TheadministratorneedstocreatealocaltextsearchserveronlyonceforeachindependentASPgroup.
TextsearchindexesthatareontheindependentASPmustbecontainedintextsearchserversthathavebeendefinedintheindependentASP.
YoucannotviewatextsearchserverdefinedinadifferentindependentASPgrouporinthesystemASPwhenthejobisconnectedtotheindependentASP.
TocreateatextsearchserveronanindependentASPnamedmyiasp,followthesesteps:1.
VaryontheindependentASPwiththeWorkwithConfigurationStatus(WRKCFGSTS)CLcommandorbyusingSystemiNavigator.
2.
ConnecttothenamespaceoftheindependentASPgroupbyusingtheSetAuxiliaryPoolGroup(SETASPGRP)CLcommand.
3.
UsetheServerInstance.
shscripttocreateatextsearchserver.
HereisanexampleQSHcommandtouse:/QOpenSys/QIBM/ProdData/TextSearch/ServerInstance.
sh-create-servernum2-portnnnnn-devicemyiaspOmniFindTextSearchServer77|||||Inthecommand,nnnnnisanavailableportnumberfortheservertouse.
ThisportnumbermustbeavailableforuseonallsystemsthattheindependentASPgroupcanbeswitchedto.
AfteratextsearchserverisdefinedfortheindependentASPgroup,theadministrativestoredprocedurescanbeusedtostartandstopthetextsearchserver.
Thestoredprocedurescanalsobeusedtocreate,drop,andupdatetextsearchindexes.
Note:JobschedulerentriesareaddedwhentheindependentASPisvariedonforanyindexeswithscheduledupdatesthatexistintheindependentASP.
Thejobschedulerentriesallowscheduledupdatestocontinue,evenwhentheindependentASPisswitchedbetweensystems.
RestrictionsofusingtextsearchindexesandindependentASPsvAllsystemsthattheindependentASPcanbeswitchedbetweenmusthaveOmniFindTextSearchServerforDB2foriinstalled,andmustbeatthesameprogramtemporaryfix(PTF)levels.
vDonotcreatetextsearchindexesonanASPotherthantheonethatthetableindexisbuiltover.
vThesystemcatalogsSYSTEXTSERVERS,SYSTEXTINDEXES,SYSTEXTDEFAULTS,SYSTEXTCOLUMNS,andSYSTEXTCONFIGURATIONdonotcontainrecordsforindexesandserversthataredefinedinadifferentASPgroup,includingthesystemASP.
ThecatalogscontainrowsonlyforindexesandserversthataredefinedfortheindependentASPgroupthatthejobisconnectedto.
vTheadministrativestoredprocedurescanbeusedtoperformfunctionsonlyontextsearchserversandindexesthataredefinedintheindependentASPgroupthatthejobisconnectedto.
Note:YoucanusetheCONTAINSandSCORESQLstatementswhenajobisconnectedtoanindependentASPgroup,evenifthecolumnisbasedonatablethatexistsonthesystemASP.
HighAvailabilityYoucanimplementahighavailabilitysolutionthatincludestextsearchindexesusingexistingAPIsandcommands.
DB2forIBMinowrecognizestextsearchindexesandtakesspecialactionsduringDB2operationsthataffecttheseindexes.
Specialconsiderationsfortextsearchindexes,highavailability,anddatabaseadministrationvAdeletefile(DLTFCLcommand)orDROPVIEWSQLstatementagainsttheviewrepresentingthetextsearchindexresultsinadropofthetextsearchindex.
Thedropfailsifthedropisexecutedundercommitmentcontrol.
vRestoringatableorphysicalfilethatwassavedwithatextsearchindexoveracolumndoesnotenablethetriggersthatwerecreatedforthetextsearchindexbeforethesave.
Ifthetextsearchindexislaterrestoredorcreated,triggersareaddedaspartoftheindexcreation.
Thismethodenablesapplicationstowork,evenifthetextsearchindexoriginallybuiltoverthetableisnotrestoredorcreated.
ReplayingjournalentriesforOmniFindAdministrativestoredproceduresvAreplayofthejournalentry(QDBRPLAYAPI)forthecreationoftheviewthatrepresentsthetextsearchindexresultsincreatingthetextsearchindex.
vTheadditionandremovalofthetriggersonthebased-ontableduringSYSTS_CREATEorSYSTS_DROPprocedurecallsdoesnotcausejournalentriestobelogged.
vAcalltotheSYSTS_UPDATEorSYSTS_REPRIMEINDEXstoredprocedureresultsinajournalentrybeingloggedagainsttheviewofthetextsearchindex.
AreplayofthisjournalentryusingtheQDBRPLAYAPIresultsinthecorrectprocedurecallbeingreplayed.
vAutomaticallyscheduledupdatesdoNOTresultinjournalentriesfortheindexandcannotbereplayed.
vAcalltoSYSTS_ALTERresultsinachangejournalentryagainsttheview.
AreplayofthejournalentryusingtheQDBRPLAYAPIcausestheSYSTS_ALTERcalltobereplayed.
78IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||HighAvailabilityrecommendationsIfyouwanttoimplementahighavailabilitysolution,considerthefollowingrecommendations:vAfterCreatingthetextsearchindexontheprimarysystem,theQDBRPLAYAPIcanbeusedtoreplaythecreateonthebackupsystem.
vThebackupsystemnowcontainsaduplicateindex,withscheduledupdatesthatoccuratthesamefrequencyastheprimarysystem.
vAsrecordchangesarereplayedintothebackupsystemsbasedontable,theyareloggedinthestagingtableofthebackupsystem.
vCallstoSYSTS_UPDATE,SYSTS_REPRIMEINDEX,andSYSTS_ALTERontheprimarysystemresultsinjournalentriesthatcanbereplayedonthebackupsystem.
vItisNOTnecessaryforcustomerstojournalandreplicatetheIFSfilesfortheTextSearchindexes.
vItisnecessarytoensurethatthetriggersthathavebeenaddedaspartofSYSTS_CREATEareenabledtorecordchangestothebasedontable.
Thesetriggerscanbeidentifiedbyusingthespecial"QDBTS"prefixonthenameofthetrigger.
ApplyJournalChangesCLcommand(APYJRNCHG)TheApplyJournalChanges(APYJRNCHG)CLcommandcanbeusedtoreplayOmniFindevents.
Userswantingtousethiscommandmustcarefullyconsidertheorderinwhichjournalentriesarereplayed.
ThestagingtableinQSYS2mustbeexactlysynchronizedwiththebased-ontableatthetimeaSYSTS_UPDATEisreplayedorinvoked.
Recordchangesthatareappliedtothebased-ontableareNOTloggedinthestagingtableduringtheAPYJRNCHGprocess.
Insomecases,itmightbenecessarytocalltheSYSTS_REPRIMEINDEXstoredproceduretorebuildtheindexafterusingthiscommand.
Relatedreference:"SYSPROC.
SYSTS_REPRIMEINDEX"onpage69YoucanreprimetheindexandstartaninitialupdateusingtheSYSPROC.
SYSTS_REPRIMEINDEXstoredprocedure.
Usethisstoredprocedurewhenyouwanttorestoredatafromthebasetable.
PerformanceanalysisOMNIFINDperformanceanalysisincludeschoosingthecorrectindexdefinition,handlingdocumentsefficiently,andspecifyingaselectivesearch.
BackgroundOmniFindprocessingisacombinationofworkonafrontend'client'jobandworkonabackend'server'job,withcommunicationoccurringbetweenthesejobs.
Communicationisperformedusingstandardsocketconnections.
Theclientjobreadsrecordsfromtheappropriatedatabasetableduringindexbuildsandmaintenance.
Itprocessesthelogoftablechanges,sendsdocumentsforingestion,andhandlesanytextsearchrequestsusingtheCONTAINSorSCORESQLfunctions.
Theclientjobistheoneinwhichtheprocedure,forexample,SYSTS_CREATEorSYSTS_UPDATE,orthequerywithCONTAINSorSCORE,isrun.
Clientjobperformanceisdependentonprocessingdatabaseactionsquickly,andretrievingandtransportingtextdocumentsefficientlytotheserverjob.
Theserverjobparsesdocumentssentfromtheclientjob,maintainstheassociatedtextindexwithinsertsanddeletions,andhandlessearchrequestswithintheindex.
Theperformanceoftheserverjobisdependentonitsabilitytocommunicateefficientlywiththeclientjobsandtohandledocumentsefficiently.
Thereisnormallyasingleserverjobservingmultipleclientjobs.
Consequently,theserverjobisamulti-threadedjobsothatitcanhandlemultipleclients.
OmniFindTextSearchServer79||||||||||||||||||||||||||||||||||||||||||||Atextindexisnotupdatedimmediatelywithanychangestotheunderlyingdatabasetable.
Instead,anyrecordchangestothetableareloggedusingacombinationofadatabasetriggerandastagingtable.
Thestagingtablerecordstheupdatetype(insert,update,ordelete)alongwithanindicationofwhichrecordinthebasetablewaschanged.
Thechangedtextisnotcapturedinthelog;onlyinformationtoidentifytherecordthatwaschanged.
Tablerecordchangesarenotreflectedinthetextindex,andconsequentlynotinsearcheswithCONTAINSorSCORE,untilthenextsuccessfulSYSTS_UPDATE.
Subsequentupdatestoatextindexaftertheinitialupdatearecalledincrementalupdates.
Theseupdatesaddordeletedocumentstothetextindexbasedonanyrecordchangesmadetotheunderlyingtablesincethelastupdate.
Anychangesmadetothebasetableareregisteredinastagingtable.
Onanincrementalupdate,thestagingtableisusedtodeterminewhichrecordsinthebasetablehavechanged.
Thoserecordsarelaterreadfromthebasetableandtheirupdatedcontentsarereflectedintheindex.
Theinitialupdateisamoreefficientprocess,perrow,thananincrementalupdate.
Thebasetableisprocessedwithoutneedingtoalsoprocessthestagingtable.
Therefore,agoodperformancetechniqueistodotheinitialupdateonthetextindexaftertheunderlyingtablehasbeeninitiallypopulated.
Thistechniqueminimizesthetimetopopulatethedocumentsintotheindex.
Animportantperformance-relatedconfigurationoptionisUPDATEAUTOCOMMIT.
Thisvaluedefineshowfrequentlythedatabaseclientjobinterruptsdocumentprocessing.
Theclientwaitsfortheserverjobtoconfirmthatithasprocessedalldocumentscurrentlysenttoit.
UPDATEAUTOCOMMITisusedasacheckpointmethodtoallowthedatabasetosetboundariesofcompletedwork.
Iftheindexupdateisinterruptedandcontinuedlater,theprocessrestartsatthecheckpointboundary.
Aswithanyinterruption,acheckpointboundaryforcestheflowofdocumentstobesuspendedandthepipelinebetweentheclientandservertobeclearedofdocuments,orinotherwords,emptiedofdocuments.
Thisstartandstopprocesscanhaveconsiderablenegativeeffectonperformance.
ThedefaultvalueforUPDATEAUTOCOMMITis100,whichprovidesfrequentcheckpoints.
SettingthevaluehigherusuallyresultsinbetterresponsetimeperformanceforSYSTS_UPDATEcalls.
However,thehighervaluedoesmeanalongerrecoverytimeiftheupdateiscanceledandrestarted.
SettingUPDATEAUTOCOMMITtoalargenumber(orzero,whichmeansnocheckpointoccurs)providesthebestresponsetimeperformance.
However,iftheinitialupdateiscanceled,OmniFindmuststartoverfromthebeginningoftheindexbuildbecausetherewasnocheckpoint.
Forsomecustomers,settingthevalue5000-20000appearstoprovideareasonablebalancebetweenperformanceandcheckpointrecoverability.
ChoosethecorrectindexdefinitionAtextindexcanbespecifiedwithoneoffourFORMATconfigurationtypes:TEXT,HTML,XML,andINSO.
1.
TEXTisusuallythemostefficientformat.
Thetextisreadfromthedatabaserecordandsenttothetextserverandtheserverprocessesitdirectly.
2.
HTMLisusedwhenthedocumentsareknowntobeintheformofHyperTextMarkupLanguage.
Thetextisprocessedwithconsiderationofignoringmarkupcontrolvalueswithinthedocument.
3.
XMLisusedwhenthedocumentsareknowntobeintheformofeXtensibleMarkupLanguage.
Specialconsiderationofthestructuralnatureofthedocumentcontentsisdone,withtrackingof80IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||||elements,attributes,andthehierarchywithinthedocument.
MarkinganindexasXMLprovidestheabilitytodoXMLsearchesusingthexmlxp(xpath)searchlanguageontheCONTAINSandSCOREfunctions.
4.
INSOisusedwhenthedocumentneedstobeprocessedINSideOut.
Inthisform,thecontentsofeachdocumentisassumedtobemorethansimpletext.
Eachdocumentispre-analyzedtodeterminewhichtypeofdocumentitis,thenconvertedtoplaintext.
INSOdocumentsareusuallyrichtextword-processingdocumentsgeneratedbyword-processingprograms.
Fromaperformanceperspective,theworktoindexadocumentincreasesasyoumovefromformatTEXTtoformatINSO.
WhileformatINSOhandlessimpletext,itismoreoverheadthanformatTEXT,asinterpretationtakesplace.
UseTEXTwhenthedocumentissimpletextinthedatabasecolumn.
TheLANGUAGEconfigurationoptionisnotstrictlyrequired,asOmniFinddeterminesthelanguageofthedocumentbasedonexamination.
However,ifthelanguageofthedocumentsisknown,specifyingitontheLANGUAGEoptioncanspeedupperformance.
Itlimitstheamountofinterpretationthatneedstobedone.
HandledocumentsefficientlyTheclientandserverjobscommunicatethedocumentinformationusingUTF(UnicodeTransformationFormat).
Togetthedocumentintothisformat,theclientreadsthedocumentfromthedatabase,convertingtheresultingdataintoUTF-8,alsoknownasCCSID1208.
AlltextisconvertedtoUTF-8beforebeingsenttotheserver.
Toimproveperformance,setthetextcolumnbeingindexedtoCCSID1208toavoidthisconversion,improvingtheefficiencyofthedocumenthandlingprocess.
UseSYSTS_REPRIMEINDEXinsteadofSYSTS_UPDATEforapossibleperformanceimprovementSYSTS_REPRIMEINDEXandSYSTS_UPDATEareusedtoupdatethetextsearchindex.
SYSTS_REPRIMEINDEXisusedtorecreatetheindex.
SYSTS_UPDATEisusedtoupdatetheindexwiththeincrementalchangesusedafterlastsuccessfulupdate.
Insomeinstances,SYSTS_REPRIMEINDEXmayperformbetterthantheSYSTS_UPDATE.
TodetermineiftheSYSTS_REPRIMEINDEXwillperformbetterthantheSYSTS_UPDATE,checkthecolumn,PENDINGCOUNTofTextSearchIndexview.
ThePENDINGCOUNTcolumnwillindicatehowmanyrowswillneedtobechangedforthenextupdateprocess.
Iftheuserupdatedthesamerecordinbasetable10times,thePENDINGCOUNTcolumnwillbeincreasedby10.
Therefore,iftheuserisupdatingthebasetablefrequently,thePENDINGCOUNTcolumnwillbeincreased.
Inmostcases,ifthePENDINGCOUNTcolumnislargerthanthetotalcountofthebasetable,thentheSYSTS_REPRIMEINDEXprocedurewillbethebetterchoicetouseratherthantheSYSTS_UPDATE.
SpecifyaselectivesearchTextsearchesaredoneusingtheCONTAINSorSCOREfunctionswithinanSQLquerystatement.
Thesesearchescomparethefunctionsearchcriteriatothedocumentsassociatedwiththecolumnbeingsearched.
Matchingrecordsareidentifiedandselected.
Theclientjobsendsthesearchrequesttotheserverandreceivestheansweronwhetheramatchisfound.
Aswithallsearchcriteria,themoreselectivethesearch,themoreefficientthesearchis.
Lookingforcommonphrases,suchas'the'intheEnglishlanguage,resultsinnumerousmatchesandcannegativelyaffectperformance.
Practicallyspeaking,suchasearchisunlikelytoprovidemeaningfulinformation.
Specifyingmoreselectivesearchphrasesresultsinfewer,moremeaningfulmatches.
OmniFindTextSearchServer81|||||||||||||||||||||||||||||||||||||||||||WhenusingCONTAINSintheWHEREclauseofanSQLstatement,itusuallyperformsbesttohaveitANDedtoothercriteria.
Forexample:SELECTbn,pubdate,descriptionFROMmyBooksWHERECONTAINS(description,'Alladin')=1ANDPubdate>'2004-01-01'UsingCONTAINSandSCORETheoptimizercanimprovetheperformanceoftheCONTAINSandSCOREfunctionsbyinternallycombiningandreplacingthesebuilt-infunctionswithauser-definedtablefunction(UDTF).
TheUDTFreturnsalistofmatchingdocumentsinoneresult.
ThisUDTFprocessinginmanycasesperformsbetterthanthealternativeprocessofinvokingthebult-infunctionforeachrecordtodetermineamatch.
ToenabletheoptimizertoperformtheUDTFrewrite,theCONTAINSfunctionmust:vresideintheWHEREclauseoftheSQLstatementvbeconnectedby"AND"toeveryadditionalpredicateintheWHEREclausevbeacomparisonwiththevalue1.
Forexample,theclause:WHERECONTAINS(MyDocuments,'javaperformance')=1couldberewrittenbytheoptimizerintheUDTFform.
However,theclause:WHERECONTAINS(MyDocuments,'javaperformance')=1ORprice>100couldnotberewrittenduetotheCONTAINSbeingORedtootherpredicates.
Note:whenaUDTFrewriteisenabled,theoptimizerstillusescostcomparisontochoosetheoptimalplan.
Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
"SYSPROC.
SYSTS_UPDATE"onpage32YoucancalltheSYSPROC.
SYSTS_UPDATEstoredproceduretoupdatethetextsearchindextoreflectthecurrentcontentsofthetextcolumn.
"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
TransactionconsiderationsConsideryourenvironmentwhendecidinghowoftentoupdateyourtextindexesfromtheunderlyingdatainthedatabasetables.
Alargeconsiderationfortraditionaldatabaseusersistheconceptoftransactionboundariesandtransactionprocessing.
Aclassicexampleisabanktransactionwheremoneyistransferredfromoneaccounttoanother.
Thetransferisconsideredtobeasingletransaction;eitherthetransferoccursoritdoesnot.
Thecustomerwouldnotappreciatehavingthemoneyremovedfromoneaccountbutnotshow82IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||||||||||||||||||||||upintheotheraccountuntilsometimelater.
Conversely,thebankwouldnotwantthemoneytoshowupinbothaccountsforsomeamountoftimeuntilthetransactionwasfinallycompleted.
Theideaisthatifachangeismade,itisreflectedimmediately.
Therearemanyexamplesininformationmanagementwheredelaysaremoreacceptableandevenexpected.
Inatraditionaldatawarehousedesign,thecontentsofthedatawarehouseoftenlagtheoperationaldatabyhoursormore.
Thisacceptanceofdelayisbasedonacombinationofrealisticexpectationsofadatawarehouseandadesireforpredictable,coherentdata.
Unstructuredtextsearchesliesomewherebetweenthesetwoboundaries.
Practicallyspeaking,indexingtextdocumentsisanintensiveprocesstoanalyzeandbreakdowntheunderlyingmeaningofthewordsinthedocument.
Textsearchesallowasearchfor'mice'tofinddocumentswith'mouse'.
Thissearchresulthappensduetotheabilityoftheindexingtechniquetobreakdownwordsintotheirunderlyingmeanings.
Thisanalysisisdoneatthetimewhenadocumentisindexedinordertomakesubsequentsearchesasfastaspossible.
Usersofatraditionaldatabaseindexexpecttheindextoreflectthestateofthedatainthedatabasetable.
Thissameexpectationdoesnotholdforatextindex.
Thecontentsofthetextindexreflectthestateofthetablebasedonthetimewhenthelastupdate(SYSTS_UPDATE)wasperformed.
Inahighlychangingenvironment,itisunlikelythatthetextindexwouldreflectthecurrentstateofthetableatanygiventime.
However,inamorepredictableenvironmentwherethedatabasetableisupdatedlessfrequentlyorinbatchmode,thetextindexupdatescanbetimedtoperformafterthetableupdate,accuratelyreflectingthestateofthetable.
Itisimportanttohavetheappropriateexpectationforatextindex.
UsetheUPDATEFREQUENCYoptiononthetextindex"SYSPROC.
SYSTS_CREATE"onpage17or"SYSPROC.
SYSTS_UPDATE"onpage32,orexplicitlycalltheSYSTS_UPDATEproceduretoupdatethecontentsofthetextindexappropriately.
Formorestaticenvironmentssuchasbulkdataloads,itmakessensetotimethetextindexupdatetotakeplaceafterthebulkloadiscompleted.
Formoretransaction-orientedenvironments,theUPDATEFREQUENCYvaluecanbesettoashortdurationortheSYSTS_UPDATEprocedurecanbeinvokedfrequently.
Itisnormallytruethatthemorefrequenttheupdate,themoreworkloadisplacedonthemachine.
Relatedreference:"QSYS2.
SYSTEXTINDEXESadministrationtable"onpage97YoucanseeinformationabouteachtextsearchindexintheQSYS2.
SYSTEXTINDEXESadministrationtable.
Eachtextsearchindexhasaname,schemaname,andanassociatedcollectionnameonthetextsearchserver.
UsingIBMNavigatorforiYoucanadministeryourOmniFindtextsearchserversandtextsearchindexesusingIBMNavigatorfori.
1.
IntheIBMNavigatorforiwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
SelectOmniFindTextSearch.
OmniFindTextSearchServer83||||||||||||||||||||||||||||||||||||||||WorkwithtextsearchserversYoucanstartandstopyourOmniFindtextsearchserversandcreateatextsearchindexusingIBMSystemsDirectorNavigatorfori.
ViewingthestatusofyourOmniFindTextServers:1.
SelecttheTextServersfolderintherightpane.
2.
Viewthestatusofthecurrentlyconfiguredtextsearchserversonthesystemintherightpane.
3.
SelectRefreshtorefreshthelistofservers.
84IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||StartingandstoppingyourOmniFindTextServers:1.
SelecttheboxinfrontofyourselectedServerIDintherightpane.
2.
SelectfromtheActionsmenu:vStartservervStopservervCreateatextsearchindexCreatingatextsearchindex:OmniFindTextSearchServer85|||||||||WorkwithtextsearchindexesYoucanperformoperationsonanytextsearchindexonasystemusingIBMSystemsDirectorNavigatorfori.
ViewingtheOmniFindtextindexesforatextserver:1.
IntheIBMSystemsDirectorNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
SelectOmniFindTextSearch.
5.
SelectTextServers.
6.
Selectthetextserverthatyouwanttoworkwith.
Theindexesforthatserveraredisplayedintherightpane.
86IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||ViewingtheOmniFindtextindexesforthissystem:1.
SelectOmniFindTextSearch.
2.
SelectTextIndexes.
Theseindexesareallthetextsearchindexesforthissysteminthecurrentpartition.
Performoperationsonatextindex:OmniFindTextSearchServer87|||||||||1.
Selecttheboxfortheindexintherightpanethatyouwanttoworkwith.
2.
SelectActionfromtheboxatthetopofthepanel.
vDefinitionvUpdatevReprimevDeletevDescriptionViewingtheOmniFindtextindexesforaschema:1.
IntheIBMSystemsDirectorNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
ExpandSchemas.
5.
Expandtheschemathatyouwanttoworkwith.
6.
SelectOmniFindTextIndexes.
Theseindexesareallthetextsearchindexesforthisschema.
Viewtextsearchindexbuilds:YoucanviewtextindexesthatarebeingbuiltbythedatabaseusingIBMSystemsDirectorNavigator.
Thisviewishelpfulindeterminingwhentextsearchindexesbecomeavailabletoyourapplications.
Todisplaytextsearchindexesthatarebeingbuilt,followthesesteps:1.
IntheIBMSystemsDirectorNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
88IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||4.
SelectDatabaseMaintenance.
5.
SelectTextIndexBuilds.
Thispanelshowsonlytextsearchindexupdatesinprogress.
Itisemptyiftherearenoupdatesorreprimingcurrentlyrunningonthesystem.
UsingSystemiNavigatorYoucanadministeryourOmniFindtextsearchserversandtextsearchindexesusingSystemiNavigator.
1.
IntheSystemiNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
SelectOmniFindTextSearch.
OmniFindTextSearchServer89||||||||||||WorkwithtextsearchserversYoucanstartandstopyourOmniFindtextsearchserversandcreateatextsearchindexusingSystemiNavigator.
ViewingthestatusofyourOmniFindTextServers:1.
SelecttheTextServersfolderintherightpane.
2.
Viewthestatusofthecurrentlyconfiguredtextsearchserversonthesystemintherightpane.
3.
SelectF5torefreshthelistofservers.
90IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||StartingandstoppingyourOmniFindTextServers:1.
SelectyourselectedServerIDintherightpane.
2.
Right-clicktoviewoptions:vStartservervStopservervCreateatextsearchindexCreatingatextsearchindex:OmniFindTextSearchServer91|||||||||WorkwithtextsearchindexesYoucanperformoperationsonanytextsearchindexonasystemusingSystemiNavigator.
ViewingtheOmniFindtextindexesforatextserver:1.
IntheSystemiNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
SelectOmniFindTextSearch.
5.
SelectTextServers.
6.
Selectthetextserverthatyouwanttoworkwith.
Theindexesforthatserveraredisplayedintherightpane.
92IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||ViewingtheOmniFindtextindexesforthissystem:1.
SelectOmniFindTextSearch.
2.
SelectTextIndexes.
Theindexesshownareallthetextsearchindexesforthissysteminthecurrentpartition.
OmniFindTextSearchServer93||||||Performoperationsonatextindex:1.
Selectanindexintherightpanethatyouwanttoworkwith.
2.
Right-clickontheindex.
3.
Selectanoperation:vupdatevreprimevdeletevviewdescriptionvviewdefinitionvalterdefinitionViewingtheOmniFindtextindexesforaschema:1.
IntheSystemiNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
ExpandSchemas.
5.
Expandtheschemathatyouwanttoworkwith.
6.
SelectOmniFindTextIndexes.
Theseindexesareallthetextsearchindexesforthisschema.
94IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||Viewtextsearchindexbuilds:YoucanviewtextindexesthatarebeingbuiltbythedatabasewithSystemiNavigator.
Thisviewishelpfulindeterminingwhentextsearchindexesbecomeavailabletoyourapplications.
Todisplaytextsearchindexesthatarebeingbuilt,followthesesteps:1.
IntheSystemiNavigatorwindow,expandthesystemyouwanttouse.
2.
ExpandDatabases.
3.
Expandthedatabasethatyouwanttoworkwith.
4.
ExpandDatabaseMaintenance.
5.
SelectTextIndexBuilds.
Thispanelshowsonlytextsearchindexupdatesinprogress.
Itisemptyiftherearenoupdatesorreprimingcurrentlyrunningonthesystem.
OmniFindTextSearchServer95|||||||||||||TextsearchadministrationtablesYoucansupportyourtextsearchserversandindexesusingtheadministrationtablesinQSYS2.
QSYS2.
SYSTEXTDEFAULTSadministrationtableYoucanseethedefaultparametersandvaluesintheQSYS2.
SYSTEXTDEFAULTSadministrationtable.
ThistableiscreatedwhenyouinstallOmniFindTextSearchforDB2fori.
ThefollowingtableshowsthecontentsoftheQSYS2.
SYSTEXTDEFAULTSadministrationtable.
Table31.
ContentsoftheQSYS2.
SYSTEXTDEFAULTSadministrationtableColumnnameDatatypeNullableDescriptionNAMEVARCHAR(30)NoNameofadefaultparameterforthedatabasefortextsearch.
VALUEVARCHAR(512)NoValueforthedefaultparameterfortextsearch.
TYPEINTEGERNoReserved.
96IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||QSYS2.
SYSTEXTINDEXESadministrationtableYoucanseeinformationabouteachtextsearchindexintheQSYS2.
SYSTEXTINDEXESadministrationtable.
Eachtextsearchindexhasaname,schemaname,andanassociatedcollectionnameonthetextsearchserver.
ThefollowingtableshowsthecontentsoftheQSYS2.
SYSTEXTINDEXESadministrationtable.
TheuniquekeyforthistableistheINDEXSCHEMAcolumnwiththeINDEXNAMEcolumn.
TheprimarykeyistheINDEXIDcolumn.
Table32.
ContentsoftheQSYS2.
SYSTEXTINDEXESadministrationtableColumnnameDatatypeNullableDescriptionINDEXIDINTEGERNoUniquelygeneratedindexIDforthetextsearchindex.
INDEXSCHEMAVARCHAR(128)NoSchemanameforthetextsearchindex.
INDEXNAMEVARCHAR(128)NoUnqualifiednameofthetextsearchindex.
TABLESCHEMAVARCHAR(128)NoSchemanameofthebasetable.
TABLENAMEVARCHAR(128)NoUnqualifiednameofthebasetable.
TABLEIASPSMALLINTNoIndependentASPofthebasetable.
COLLECTIONNAMEVARCHAR(255)NoNameoftheassociatedcollectiononthetextsearchserver.
SERVERIDINTEGERNoTheserverIDforthetextsearchindex.
TAKEOVERSERVERIDINTEGERYesReservedforfutureuse.
TAKEOVERSERVERPULSETIMESTAMPYesReservedforfutureuse.
SEARCHARGSVARBINARY(1024)YesReservedforfutureuse.
ALIASSCHEMAVARCHAR(128)NoThealiasfortheschemaofthebasetablethatwasusedintheSYSPROC.
SYSTS_CREATEstoredprocedure.
Ifnoaliasisused,thisvalueisidenticaltoTABLESCHEMA.
ALIASNAMEVARCHAR(128)NoThealiasforthenameofthebasetablethatwasusedintheSYSPROC.
SYSTS_CREATEstoredprocedure.
Ifnoaliasisused,thisvalueisidenticaltoTABLENAME.
STAGINGTABLENAMEVARCHAR(128)YesThenameofthelogtableforthetextsearchindex.
EVENTTABLENAMEVARCHAR(128)NoThenameoftheeventtableforthetextsearchindex.
OFINDEXTABLENAMEVARCHAR(128)NoThenameofthetableforthetextsearchindexontheOmniFindTextSearchServerforDB2fori.
UPDATEMINIMUMINTEGERNoMinimumnumberofentriesinthelogtablebeforeanincrementalupdateofthetextsearchindexisperformed.
UPDATEFREQUENCYVARCHAR(512)NoTheupdatefrequencyforthetextsearchindexasspecifiedbytheSYSPROC.
SYSTS_CREATEstoredprocedure.
OmniFindTextSearchServer97|||Table32.
ContentsoftheQSYS2.
SYSTEXTINDEXESadministrationtable(continued)ColumnnameDatatypeNullableDescriptionUPDATEMODEINTEGERNoIndicatestheupdatemodeofthetextsearchindex.
Theinteger0(zero)indicatestheinitialupdateofthetextsearchindex.
Avalueof1indicatessubsequent,incrementalupdates.
REORGANIZATIONMODEINTEGERNoIndicatesthereorganizationmodeofthetextsearchindex.
CREATETIMETIMESTAMPNoThetimethatthetextsearchindexwascreated.
LASTUPDATETIMETIMESTAMPYesThetimethatthetextsearchindexwaslastupdated.
LASTUPDATESTATUSCHARYesIndicatestheinternalstatusforoptimizingthecleanupprocessafteraninitialorincrementalupdateofthetextsearchindex.
Typicalvaluesinclude:v(Null)indicatesthattheindexhasneverbeenupdated.
v'C'indicatesthataninitialupdatewasinitiated.
Iftheupdatemodeisnotincremental,thentheinitialupdateiseitherstillinprogressordidnotcomplete.
v'N'indicatesthatanincrementalupdatehassuccessfullycompleted.
Othercodesareusedinternallyduringupdateprocessing.
Theupdateprocessusesthesecodestodeterminespecificrecoveryactionsthatcanbetakeniftheupdatefailstocomplete.
SCHEDULERTASKIDINTEGERYesReservedforfutureuse.
EXPRESSIONLISTSCLOB(32K)YesReservedforfutureuse.
EXPRESSIONNUMBERSVARBINARY(32)YesReservedforfutureuse.
USEREXITFUNCTIONVARCHAR(18)YesReservedforfutureuse.
REMARKSVARCHAR(2000)YesRemarksmadeintheCOMMENTSoptionoftheindex-configuration-optionsparameteroftheSYSPROC.
SYSTS_CREATEstoredprocedure.
TABLEMBRVARCHAR(10)YesThetablememberoverwhichthetextindexisbuilt.
Usedtotrackthespecificmemberbeingindexedifthefileisamulti-memberfile.
Ifthevalueisnull,thememberisthefirstandonlymemberofthetable.
Relatedconcepts:"Serveraliasname"onpage10Youcanuseaserveraliasnametoassignameaningfulnametoaserver.
98IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||QSYS2.
SYSTEXTCOLUMNSadministrationtableYoucanseeinformationaboutthetextcolumnsforatextsearchindexintheQSYS2.
SYSTEXTCOLUMNSadministrationtable.
EachtextsearchindexhasanindexID,textcolumnnames,andtheschemanameofthebasetable.
ThefollowingtableshowsthecontentsoftheQSYS2.
SYSTEXTCOLUMNSadministrationtable.
TheprimarykeyforthistableistheINDEXIDcolumnwiththeCOLUMNNAMEcolumn.
TheforeignkeyistheINDEXIDcolumn.
Table33.
ContentsoftheQSYS2.
SYSTEXTCOLUMNSadministrationtableColumnnameDatatypeNullableDescriptionINDEXIDINTEGERNoUniquelygeneratedindexIDforthetextsearchindex.
COLUMNNAMEVARCHAR(128)NoUnqualifiednameofthetextcolumn.
TABLESCHEMAVARCHAR(128)NoSchemanameofthebasetable.
TABLENAMEVARCHAR(128)NoUnqualifiednameofthebasetable.
LANGUAGEVARCHAR(5)NoThelanguagethatthetextsearchserverusesforthelinguisticprocessingoftextdocuments.
Thedefaultvalueisen_US(English).
FUNCTIONSCHEMAVARCHAR(128)YesTheschemaofauser-definedfunctionusedbyOMNIFINDtoaccesstextdocumentsthatareinacolumnthatisnotofasupporteddatatype,orthatarestoredelsewhere.
FUNCTIONNAMEVARCHAR(18)YesThenameofauser-definedfunctionusedbyOMNIFINDtoaccesstextdocumentsthatareinacolumnthatisnotofasupporteddatatype,orthatarestoredelsewhere.
CCSIDINTEGERNoThecodedcharactersetidentifierthatisusedforatextsearchindexonacolumnwithabinarydatatype.
FORMATVARCHAR(30)NoTheformatoftextdocumentsinthecolumn.
ThesupportedformatvaluesareTEXT,HTML,XML,andINSO.
KEYCOLUMNCOUNTINTEGERNoThecountofkeycolumnsforthetextsearchindex.
KEYCOLUMNNAMESVARCHAR(1200)NoThekeycolumnnamesforthetextsearchindex.
QSYS2.
SYSTEXTSERVERSadministrationtableYoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
ThefollowingtableshowsthecontentsoftheQSYS2.
SYSTEXTSERVERSadministrationtable.
TheuniquekeyforthistableistheSERVERNAMEcolumnwiththeSERVERPORTcolumn.
TheprimarykeyistheSERVERIDcolumn.
OmniFindTextSearchServer99||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||Table34.
ContentsoftheQSYS2.
SYSTEXTSERVERSadministrationtableColumnnameDatatypeNullableDescriptionSERVERIDINTEGERNoUniquelygeneratedIDforthetextsearchserver.
SERVERNAMEVARCHAR(128)NoThehostnameorIPaddressofthetextsearchserver.
SERVERADRINFOVARBINARY(3000)YesTheinternalrepresentationoftheSERVERNAMEandSERVERPORTasdeterminedbytheSYSPROC.
SYSTS_STARTstoredprocedure.
SERVERPORTINTEGERNoTheportnumberforthetextsearchserver.
SERVERPATHVARCHAR(512)NoTheserverpathforthetextsearchserver.
SERVERTYPEINTEGERNoTheservertypeforthetextsearchserver.
Thevalue0(zero)indicatesanIBMitextsearchserver.
Thevalue1indicatesaLinuxtextsearchserver.
Thevalue2indicatesaWindowstextsearchserver.
SERVERAUTHTOKENVARCHAR(256)NoTheauthenticationtokenforthetextsearchserver.
SERVERMASTERKEYVARCHAR(36)NoTheserverkeyforthetextsearchserver.
SERVERCLASSINTEGERNoTheserverclassforthetextsearchserver.
Thevalue0(zero)indicatesaproductionserver,availableforautomaticselection.
thevalue9indicatesatestserver,neverallocatedautomatically.
SERVERSTATUSINTEGERNoIndicateswhethertheservercanbeusedasatextsearchservertocreatenewtextsearchindexes.
Thedefaultvalueis0(zero),whichmeansthattheservercanbeused.
ALIASNAMEVARCHAR(128)YesAliasnameisuniquewhennotnullandiscasesensitive.
Relatedconcepts:"Serveraliasname"onpage10Youcanuseaserveraliasnametoassignameaningfulnametoaserver.
QSYS2.
SYSTEXTCONFIGURATIONadministrationtableYoucanseetheconfigurationparametersforthetextsearchindex,aspassedbytheSYSPROC.
SYSTS_CREATEstoredprocedure,intheQSYS2.
SYSTEXTCONFIGURATIONadministrationtable.
ThefollowingtableshowsthecontentsoftheQSYS2.
SYSTEXTCONFIGURATIONadministrationtable.
TheprimarykeyistheINDEXIDcolumnwiththePARAMETERcolumn.
TheforeignkeyistheINDEXIDcolumn.
Table35.
ContentsoftheQSYS2.
SYSTEXTCONFIGURATIONadministrationtableColumnnameDatatypeNullableDescriptionINDEXIDINTEGERNoUniquelygeneratedindexIDforthetextsearchindex.
100IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||Table35.
ContentsoftheQSYS2.
SYSTEXTCONFIGURATIONadministrationtable(continued)ColumnnameDatatypeNullableDescriptionPARAMETERVARCHAR(30)NoParametersthatarespecifiedforthetextsearchindexintheSYSPROC.
SYSTS_CREATEstoredprocedure.
VALUEVARCHAR(512)NoValuesforthespecifiedparameters.
Relatedreference:"SYSPROC.
SYSTS_CREATE"onpage17YoucancalltheSYSPROC.
SYSTS_CREATEstoredproceduretocreateatextsearchindex.
Thisstoredprocedureenablesatextcolumnfortextsearchindexing.
ThetextsearchindexcanthenbeusedinSQLqueriesthatcontaintheCONTAINSorSCOREfunctions.
QSYS2.
SYSTEXTSERVERHISTORYadministrationtableYoucanseethehistoryofserversusedfortheSYSPROC.
SYSTS_DROPstoredprocedurebyviewingtheauxiliarytableQSYS2.
SYSTEXTSERVERHISTORY.
ThefollowingtableshowsthecontentsoftheQSYS2.
SYSTEXTSERVERHISTORYadministrationtable.
TheuniquekeyforthistableistheINDEXIDcolumnwiththeSERVERIDcolumn.
TheforeignkeyistheINDEXIDcolumn.
Table36.
ContentsoftheQSYS2.
SYSTEXTSERVERHISTORYadministrationtableColumnnameDatatypeNullableDescriptionINDEXIDINTEGERNoTheindexIDforacreatedtextsearchindex.
SERVERIDINTEGERNoTheserverIDwhereatextsearchindexneedstobedroppedonSYSPROC.
SYSTS_DROP.
Relatedreference:"SYSPROC.
SYSTS_DROP"onpage31YoucancalltheSYSPROC.
SYSTS_DROPstoredproceduretodropatextsearchindexthatwasdefinedbyusingtheSYSPROC.
SYSTS_CREATEstoredprocedure.
TextSearchIndexviewWhenatextsearchindexiscreatedwithSYSTS_CREATE,aviewrepresentingtheindexwillbecreated.
Queryingthetextsearchindex'sviewcanhelptheuserobtainthestatusoftheindex.
Thetextsearchindexview'snameissamenameasthetextsearchindex'snamethatwasspecifiedwhilecreatingtheindexwithSYSTS_CREATE.
Thefollowingtableshowsthecontentoftextsearchindex'sview.
Table37.
ContentsoftheviewcreatedbySYSTS_CREATEColumnnameDatatypeCCSIDNullableDescriptionTABLESCHEMAVARCHAR(128)1208NoSchemanameofthebasetable.
TABLENAMEVARCHAR(128)1208NoUnqualifiednameofthebasetable.
COLUMNNAMEVARCHAR(128)1208NoUnqualifiednameofthetextcolumnfromthebasetable.
OmniFindTextSearchServer101||||||||||||||||||||||||||||||Table37.
ContentsoftheviewcreatedbySYSTS_CREATE(continued)ColumnnameDatatypeCCSIDNullableDescriptionSERVERIDINTEGERNoUniqueserverIDfromSYSTEXTSERVERS.
SERVERNAMEVARCHAR(128)1208NoUnqualifiednameofthetextsearchserver.
SERVERSTATUSVARCHAR(32)1208Nov'STARTED'–Serverisrunning.
v'STOPPED'–Serverisstopped.
STAGINGTABLENAMEVARCHAR(128)1208NoUnqualifiednameofthelogtableforthetextsearchindex.
LASTUPDATETIMETIMESTAMPYesThetimethatthetextsearchindexwaslastupdated.
LASTUPDATESTATUSVARCHAR(30)1208Yesv'NEVERUPDATED'–Theindexwasnotupdatedbefore.
v'UPTODATE'–Lastupdatesucceedandthereisnopendingchangestodo.
v'CHANGESPENDING'–Lastupdatesucceed,buttherearestillnewchangestobeupdated.
v'FAILED'–Lastupdatefailed.
UPDATEMINIMUMINTEGERNoMinimumnumberofentriesinthelogtablebeforeanincrementalupdateofthetextsearchindexisperformed.
UPDATEFREQUENCYVARCHAR(512)1208NoTheupdatefrequencyforthetextsearchindexasspecifiedbytheSYSPROC.
SYSTS_CREATEstoredprocedure.
PENDINGCOUNTINTEGERNoIndicatehowmanyrowstobeindexedinnextupdateprocess.
ExtensionstoIndexandSearchNon-DB2DataTheOmniFindTextSearchforDB2foriprovidesanadditionalsetofstoredprocedurestocreate,administer,andsearchtextsearchcollections.
ExtensionsOverviewOmniFindTextSearchServerforDB2foriprovidesanadditionalsetofstoredprocedurestocreate,administer,andsearchtextsearchcollections.
Atextsearchcollectionisusedtoindexdataassociatedwithsystemobjectssuchasspoolfilesinanoutputqueue,orstreamfiledataintheintegratedfilesystem.
102IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||Atextsearchcollectiondescribesoneormoresetsofsystemobjectsthatwillhavetheirassociatedtextdataindexedandsearched.
Forexample,acollectionmaycontainanobjectsetofallspoolfilesinoutputqueueQUSRSYS/QEZJOBLOG,and/oranobjectsetforallstreamfilesindirectory'/home/alice/text_data'.
ThetextsearchcollectionreferredtointhisdocumentationshouldnotbeconfusedwithaDB2schema(sometimesalsoreferredtoasacollection),oraLucenecollection(PartoftheinternalstructureofaDB2textsearchindex).
Whenatextsearchcollectioniscreated,severalDB2objectsarecreatedonthesysteminanSQLschema.
Thefollowingobjectswillbecreatedintheschema:vCatalogsfortrackingthecollection'sconfiguration.
vCatalogsfortrackingtheobjectsthathavebeenindexed.
vSQLStoredprocedurestoadministerandsearchthecollection.
vADB2textsearchindexforindexingtheassociatedtext.
Administrationofthecollectionisprovidedwithstoredprocedures,mostofwhicharecreatedintheschema.
AnexplanationofotherOmniFindTextSearchServerforDB2forienhancementscanbefoundat:OmniFindTextSearchServerforDB2forienhancements.
CreatingaTextSearchCollectionThisprocedurecreatesanemptysearchcollection.
AnSQLschemawillbecreatedonthesystemtocontaininformationaboutthecollection.
TheschemawillcontainDB2objectsnecessarytotrackandindexobjects.
SYSPROC.
SYSTS_CRTCOLandSYSPROC.
SYSTS_CREATE_COLLECTIONAuthorizationSYSTS_CRTCOLandSYSTS_CREATE_COLLECTIONwillbeshippedwith*EXECUTEauthoritygrantedtopublic.
Theseprocedureswillnotadoptanyadditionalauthorityandwillrunundertheinvokingprofile.
Inordertocreateatextsearchcollectiontheinvokermusthave:vAuthoritytocreateaDB2schemavAuthority/abilitytocreateatextsearchindexTheDB2Objectscreatedaspartofthecollection,includingtheadministrativestoredproceduresarecreatedwithpublicauthority*EXCLUDETheuserprofilecreatingthecollectionownsallobjectsinthecollection.
Ausermaygrantauthoritytospecificproceduretootherusersinordertoallowanotherusertoadministerandsearchthetextsearchcollection.
SyntaxSYSTS_CRTCOL(collection_name,options,asp_device_name)OmniFindTextSearchServer103SyntaxSYSTS_CREATE_COLLECTION(collection_name,options,asp_device_name)TheschemaqualifierisSYSPROC.
Parameterscollection_nameNameofCollection.
Thisnameuniquelyidentifiesthecollectionandmustbeanonnullstring.
AschemaofthenamespecifiedforcollectionnamewillbecreatedtoholdtheassociatedDB2objects.
Note:EnclosenamesindoublequotationmarksifthenamesconflictwithSQLkeywordsorOmniFindkeywordsthatcanbeused.
ThecollectionnameparameterwillfollowtheSQLrulesforschemanames.
Thecollectionnamemustnotmatchthenameofanexistinguserprofile.
ThedatatypeofthisparameterisVARCHAR(128).
optionsAcharacterstringthatspecifiesthevariousoptionsthatareavailableforthisstoredprocedure.
ThedatatypeforthisparameterisVARCHAR(32000).
options:text-default-informationupdate-characteristicsindex-configuration-optionstext-default-information:LANGUAGElanguageFORMATformattext-default-informationSpecifiesthelanguagethatisusedwhenprocessingdocuments,andtheformatoftextdocumentsinthecolumn.
LANGUAGElanguageSpecifiesthelanguagethatOmniFindTextSearchServerforDB2foriusesforthelinguisticprocessingoftextdocuments.
Thedefaultvalueisen_US(English).
IfyouspecifythevalueforthisparameterasAUTO,OmniFindTextSearchServerforDB2foritriestodeterminethelanguage.
Important:IfthelanguageofthedocumentsisnotEnglish,donotusethedefaultvalueofen_US.
Changethevaluetothelanguageofthedocuments;otherwise,linguisticprocessingdoesnotworkasexpected.
FORMATformatIdentifiestheformatoftextdocumentstobeindexes,suchasTEXTorINSO.
TheOmniFindTextSearchServerforDB2forineedstoknowtheformat,orcontenttype,ofthetextdocumentsthatyouintendtoindexandsearch.
Ifyoudonotspecifytheformatparameter,thedefaultisTEXT.
104IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriTheformatvalueINSOallowsOmniFindTextSearchServerforDB2foritodeterminetheformat.
IftheOmniFindTextSearchServerforDB2foricannotdeterminethedocumentformat,thenadocumenterrorisloggedinthejoblogduringprocessingbytheUPDATEstoredprocedure.
update-characteristics:NONEUPDATEFREQUENCYupdate-characteristicsSpecifiesthefrequencyofautomaticupdatestothetextsearchcollection.
Theupdateprocessforatextsearchcollectioninvolvesbothindexingthetextdata,andcrawlingsystemobjectstodetectneworchangeddata.
UPDATEFREQUENCYupdate-frequencySpecifieswhentomakeautomaticupdatestothetextsearchcollection.
ThedefaultvalueisNONE.
update-frequency(Format1):NONED(*)H(*)M(*),,,0.
.
60.
.
230.
.
590.
.
60.
.
230.
.
59NONEIfNONEisspecified,thennofurtherindexupdatesaremade.
Theupdatemustbestartedmanually.
DSpecifiesthedayordaysoftheweekwhentheindexisupdated.
Anasterisk(*)specifiesalldays.
0specifiesSunday.
HSpecifiesthehourorhourswhentheindexisupdated.
Anasterisk(*)specifiesallhours.
MSpecifiestheminuteorminuteswhentheindexisupdated.
Anasterisk(*)cannotbespecified.
Theminimumupdatefrequencyis5minutes.
Example:Thisexamplespecifiesthattheindexupdateistorunevery30minutes.
UPDATEFREQUENCYD(*)H(*)M(0,30)update-frequency(Format2,chronological):Theformatoftheupdate-frequency(chronological)optionisalistofthefivevaluesseparatedbyablankspace.
Thefivevaluesrepresenttheminutes,hours,daysofthemonth,monthsoftheyear,anddaysoftheweekbeginningwithSunday.
Ifyouspecifyanintervalofvaluesoranasterisk(*),youcanspecifyastepvaluebyusingaforwardslash(/)attheendofthedefinedinterval.
Example:Thisexamplespecifiesthattheindexupdateistoruneveryquarterhour(0,15,30,45)ontheevenhoursbetween8a.
m.
and6:45p.
m.
(8-18/2isequivalentto8,10,12,14,16,18),fromMondaytoFridayeverymonthoftheyear(**1-5).
0,15,30,458-18/2**1-5OmniFindTextSearchServer105||minuteSpecifiestheminutesofthehourwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foranintervalofevery5minutes,oryoucanspecifyaninteger0-59.
Youcannotrepeatvalues.
Theminimumupdatefrequencyis5minutes.
Avalueof1,4,8isnotvalid.
update-frequency(minute):*/0.
.
.
59,0.
.
.
59-0.
.
.
59/0.
.
.
590.
.
.
59hourSpecifiesthehoursofthedaywhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryhour,oryoucanspecifyaninteger0-23.
Youcannotrepeatvalues.
update-frequency(hour):*/0.
.
.
23,0.
.
.
23-0.
.
.
23/0.
.
.
230.
.
.
23dayOfMonthSpecifiesthedaysofthemonthwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryday,oryoucanspecifyaninteger1-31.
Youcannotrepeatvalues.
update-frequency(dayOfMonth):*/1.
.
.
31,1.
.
.
31-1.
.
.
31/1.
.
.
311.
.
.
31monthOfYearSpecifiesthemonthsoftheyearwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreverymonth,oryoucanspecifyaninteger1-12.
Youcannotrepeatvalues.
106IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||update-frequency(monthOfYear):*/1.
.
.
12,1.
.
.
12-1.
.
.
12/1.
.
.
121.
.
.
12dayOfWeekSpecifiesthedaysoftheweekwhenthetextsearchindexistobeupdated.
Youcanspecifyanasterisk(*)foreveryday,oryoucanspecifyaninteger0-7.
Both0and7arevalidvaluesforSunday.
Youcannotrepeatvalues.
update-frequency(dayOfWeek):*/0.
.
.
7,0.
.
.
7-0.
.
.
7/0.
.
.
70.
.
.
7index-configuration-options:INDEXCONFIGURATION(,optionvalue)index-configuration-optionsSpecifiesadditionalindex-specificvaluesasoptionvaluepairs.
Youmustenclosestringvaluesinsinglequotationmarks.
Asinglequotationmarkwithinastringvaluemustberepresentedbytwoconsecutivesinglequotationmarks.
CJKSEGMENTATIONSpecifiesthesegmentationmethodtousewhenyouindexdocumentsforCJK(Chinese,Japanese,Korean)languages.
ThesupportedvaluesareMORPHOLOGICALandNGRAM.
IftheCJKSEGMENTATIONvalueisnotspecified,thedefaultvalueisused.
ThedefaultvalueisspecifiedbytheDEFAULTNAMEvalueintheQSYS2.
SYSTEXTDEFAULTStable.
SERVERSpecifiestheIDoraliasnameoftheservertobeusedtostorethetextsearchindex.
IfanIDisused,thevalueisanintegerthatmustexistintheSERVERIDcolumnoftheQSYS2.
SYSTEXTSERVERScatalog.
Ifanaliasnameisused,thevalueisastringthatmustexistintheALIASNAMEcolumnoftheQSYS2.
SYSTEXTSERVERScatalog.
IfSERVERisnotspecified,thedefaultistoselecttheserverwiththefewesttextsearchindexesfromtheserversintheQSYS2.
SYSTEXTSERVERStablewhereparameterSERVERSTATUSissetto0(zero),whichmeansthattheserverisavailable.
UPDATEAUTOCOMMITSpecifieshowoftenacommitoperationisperformedwhenfetchingdocumentsduringanindexupdate.
Avalueof0(zero)meansthatacommitoperationoccursonlyattheendofprocessing.
Thevaluemustbeanintegerbetween0(zero)and2147483647.
Thedefaultvalueis100.
OmniFindTextSearchServer107|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||Performancetip:ThevalueofUPDATEAUTOCOMMITcanhaveasubstantialimpactontheperformanceofindexupdates.
Thecommitoperationthattakesplaceatthespecifiedintervalensuresaconsistentcheckpointfromwhichtorestarttheindexupdate,ifitisinterrupted.
However,thecommitalsotemporarilysuspendstheupdateprocess.
IncreasingtheUPDATEAUTOCOMMITvalue(orsettingitto0)cansubstantiallyimprovetheupdateperformance,especiallytheinitialupdate.
Thevalueyouspecifymustbalancetheneedforperformancewiththeneedforrecoverability,basedonthefrequencyoftheindexupdates.
asp_device_nameThisparameter,ifspecifiedandnotnull,determineswhichAuxiliarystoragepoolthecollectioniscreatedinto.
ThisparameterisoptionallyavailabletomatchtheCREATESCHEMAcapabilitytocreatetheDB2objectsonaspecificASPdevice.
Ifavalueissuppliedforthisparameter,thevaluemustnameadiskpoolintheprimaryaspgroupofthecurrentnamespace,orabasicASPunitifthenamespaceisthesystemASPonly.
ThedatatypeforthisparameterisVARCHAR(10).
Examples1.
CALLSYSTS_CRTCOL('mycollection');AcollectionMYCOLLECTIONiscreated.
2.
CALLSYSTS_CRTCOL('"mycollection"',"UPDATEFREQUENCYD(*)H(*)M(0)")Acollectionmycollection(lowercasenotincludingthedelimiters)iscreated.
Thetextsearchcollectionwillhaveanupdatefrequencyofeveryday,atthetopofeveryhour.
3.
CALLSYSTS_CRTCOL('ur_collection','UPDATEFREQUENCYNONE'||'LANGUAGEzh_CNINDEXCONFIGURATION(CJKSEGMENTATIONMORPHOLOGICAL)','23')AcollectionUR_COLLECTIONiscreated.
Thecollectionhasnoupdatefrequency.
Thecollection'slanguageissimplifiedChinese,usingdictionarylinguisticanalysis(morphological).
TheSQLschemaiscreatedinbasicASP#23.
Whenthetextsearchcollectioniscreated,proceduresarecreatedintheDB2schematoadministerthecollection.
TocreateacollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>Omnifind>CreateCollection.
AddinganObjectSetforSpoolFileDataThestoredprocedureisintheDB2schematoaddanobjectsetforspoolfiledata.
Note:onlySNACharacterString(SCS)dataissupported.
Spoolfilesthatcontainothertypesofdatacannotbeindexedandwillresultinadocumentlevelerrorwhenencountered.
Theerrorwillbeloggedinthejoblog,andindexingwillcontinuewiththenextspoolfile.
TheGET_OBJECTS_NOT_INDEXEDprocedurecanbeusedtodeterminewhichspoolfileswerenotincludedintheindexingprocess.
ADD_SPLF_OBJECT_SETAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
willbeshippedwith*EXECUTEauthoritygrantedtopublic.
108IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriTheprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxAddaSpoolFileObjectSet:ADD_SPLF_OBJECT_SET(output_queue_libnull,output_queue_namenull,user_namenull,qual_job_namenull,qual_job_usernull,qual_job_numbernull,user_datanull,begin_timestampnull,ending_timestampnull,output_set_id)Thefollowingsimplifiedversionsoftheaddspoolfileobjectsetproceduremaybeusedforconvenience.
Selectspoolfilesbyoutputqueueonly:ADD_SPLF_OBJECT_SET(output_queue_libnull,output_queue_namenull)Selectspoolfilesbyoutputqueue,andusernameonly:ADD_SPLF_OBJECT_SET(output_queue_libnull,output_queue_namenull,user_namenull)Theschemaqualifieristhenameofthetextsearchcollection.
Parametersoutput_queue_libAsystemnamefortheoutputqueuelibrary.
ANULLvalueoremptystringindicatesthatoutputqueuesinalllibrarieswillbeincludedintheindex.
ThedatatypeforthisparameterisVARCHAR(10)[Seesystemnameconventions][Seeadditionalrestrictions]output_queue_nameAsystemnamefortheoutputqueuename.
ANULLvalueoremptystringindicatesthatanyoutputqueuewillbeincludedintheindex.
ThedatatypeforthisparameterisVARCHAR(10)[Seesystemnameconventions][Seeadditionalrestrictions]user_nameAsystemnamefortheuserthatownsthespoolfile.
ANULLvalueoremptystringindicatesthatnofilteringontheusernamewillbeperformed.
ThedatatypeforthisparameterisVARCHAR(10)OmniFindTextSearchServer109[Seesystemnameconventions][Seeadditionalrestrictions]qual_job_nameAsystemnamefortheoutputqueuelibrary.
ANULLvalueoremptystringindicatesthatnofilteringonthejobnamewilloccur.
ThedatatypeforthisparameterisVARCHAR(10)[Seesystemnameconventions]qual_job_userSystemnamefortheuserprofilenameofthejobassociatedwiththespoolfile.
ANULLvalueoremptystringwillindicatethatnofilteringonthejobuserwilloccur.
ThedatatypeforthisparameterisVARCHAR(10)Anon-null,non-emptyvalueforthisparameterisrequiredifQUALIFIED_JOB_NAMEisspecified.
IfQUALIFIED_JOB_NAMEisemptystringorNULL,thisparametermustalsobeNULLoremptystring.
[Seesystemnameconventions][Seeadditionalrestrictions]qual_job_numberAsixcharacterstringrepresentingthejobnumber.
(Mustbedigits0-9)ANULLvalueoremptystringwillindicatethatnofilteringonthejobnumberwilloccur.
ThedatatypeforthisparameterisVARCHAR(6)Anon-null,non-emptyvalueforthisparameterisrequiredifQUALIFIED_JOB_NAMEisspecified.
IfQUALIFIED_JOB_NAMEisemptystringorNULL,thisparametermustalsobeNULLoremptystring.
[Seesystemnameconventions][Seeadditionalrestrictions]user_dataAtencharacterstringthattheuserassociateswithaspoolfile.
Thisstringisnotconvertedtouppercase,andmustexactlymatchtheuserdataassociatedwithaspoolfileinordertobeconsideredamatch.
Note:Avalueof'abc'isdifferentthan'ABC'ANULLvalue,oremptystringwillindicatethatnofilteringontheuserdatawilloccur.
ThedatatypeforthisparameterisVARCHAR(10)begin_timestampTimestampvalueindicatingtheearliestcreationtimethatwillbeincludedinthecollection.
Spoolfilescreatedearlierthanthistimestampwillnotbeindexed.
AvalueofNULLcanbeprovidedtoindicatethatanyspoolfilecreatedbeforetheendingcreationtimestampvalueshouldbeindexed.
IfEND_TIMESTAMPisalsoNULL,thennofilteringonthecreationtimestampwilloccur.
ThedatatypeforthisparameterisTIMESTAMP110IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriend_timestampTimestampvalueindicatingthelatestcreationtimethatwillbeincludedinthecollection.
Spoolfilescreatedafterthistimestampwillnotbeindexed.
AvalueofNULLcanbeprovidedtoindicatethatanyspoolfilecreatedaftertheBEGIN_TIMESTAMPwillbeindexed.
IfBEGIN_TIMESTAMPisalsoNULL,thennofilteringonthecreationtimestampwilloccur.
ThedatatypeforthisparameterisTIMESTAMPoutput_set_idOutputIntegervaluethatreturnsthesetidfortheobjectsetthatwasadded.
Thisvaluecanbeusedtoremovetheobjectsetatalatertime.
Thisparameterisoptional.
ThedatatypeforthisparameterisINTEGERSystemNamingConventionsParametersthatrequiresystemnamesasinputmustbevalidsystemnamesoranerrorwilloccur.
ThisparameterswillbeprocessedthesamewaythecommandanalyzerprocessesnamesforCLcommands.
SeeObjectnamingrulesformoreinformationonsystemnamescallnick12345.
add_splf_object_set('ntl','justtext','ntl'NULL,NULL);ThefilterinformationpassedtotheprocedurewillbeanoutputqueueNTL/JUSTTEXTforuserNTL(convertedtouppercase)UnlikeSQLnames,forasystemnamethedelimiterswillremainonthename,butonlyifnecessary.
callnick12345.
add_splf_object_set('"ntl"','"justtext"','"NTL"NULL,NULL);Thefilterinformationpassedtotheapiwillbeoutputqueue"ntl"/"justtext"foruserNTL(noquotesarounduserNTL)Note:Thestoredprocedurehasatencharacterlimitontheinterfaceanddoesnotsupportunnecessarydoublequotesthatcausethislimittobeexceeded.
Invalidsystemnameswillcauseanerror.
AdditionalRestrictionsvGenericnamesarenotsupported.
InotherwordsitisnotpossibletoindexalloutputqueuesthatstartwithMYOUTbyaddinganobjectsetforMYOUT*vTheoutputqueuelibrarynameandoutputqueuenamemusteitherbebothnull(oremptystring).
.
.
orbothhavevalidsystemnames.
Itisnotpossiblefilteronalloutputqueuesinlibraryxyz,ortofilteronalloutputqueuesnamed'abc'inanylibrary.
vIfaspecificoutputqueuenameandlibraryarespecified,thatoutputqueuemustexistatthetimetheobjectsetisadded.
Thereisnologictopreventthedeletionoftheoutputqueueatsomelatertime,buttheobjectsetwilleffectivelybecome'empty'.
vIfbothUSER_NAMEandQUALIFIED_JOB_USERarenon-null,non-emptyvalues,thentheymustbeequal.
Aspoolfileownerwillalwaysmatchthequalifiedjobuser,andthereforethesevaluescanneverbedifferentwhenbothareusedasafilter.
AuthoritiestoIndexedObjectsWhenaddingaspoolfileobjectset,considertheauthorityrequirementsneededtoretrievethetextfromthespoolfilescarefully.
TheseauthoritieswillbeafactorwhentheUPDATEstoredprocedureisinvoked.
OmniFindTextSearchServer111Seetheupdatestoredproceduredocumentationforinformationontheauthorityrequirementstoindexedobjects.
ExamplesAddanobjectsettocollectionnick789forallspoolfilesinoutputqueueNTL/MYOUTQ.
>callnick789.
add_splf_object_set('NTL','MYOUTQ');Addanobjectsettocollectionnick123toindexallspoolfilesownedbyuserNTL.
>callnick123.
add_splf_object_set(NTL');Addanobjectsettocollectiondefault_search_coltoindexallspoolfilescreatedin2010>calldefault_search_col.
add_splf_object_set(2010-01-01T00:00:00','2011-01-01T00:00:00');Addanobjectsettocollectiondefault_search_coltoindexallspoolfilescreatedin2010withuserdata'MYAPP':>calldefault_search_col.
add_splf_object_set(MYAPP','2010-01-01T00:00:00','2011-01-01T00:00:00'ToaddspoolfileobjectsettoacollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectProperties.
OntheObjecttab,pressAddOutputQueuesorAddSpooledFilesbutton.
AddinganObjectSetforStreamFileDataThestoredprocedureisintheDB2schematoaddanobjectsetforstreamfiledata.
ADD_IFS_STMF_OBJECT_SETAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxThisprocedureallowsausertoaddanobjectsetofstreamfiles(STMF)intheIntegratedFileSystem(IFS).
Addanobjectsetforstreamfiledata(streamfilesinIFS):ADD_IFS_STMF_OBJECT_SET(stmf_expression_string,output_set_id)Theschemaqualifieristhenameofthetextsearchcollection.
Parametersstmf_expression_stringThisparametercontainsanabsolutepathtoadirectorycontainingthefilesthatwillbeindexed.
112IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriThismustbeavaliddirectory(type*DIR)onafilesystemthatisaccessible.
Streamfileobjects(type*STMF)withinthisdirectorywillbeindexed.
Thepathnameshouldbeabsoluteandshouldnotcontainanyregularexpressions.
ThedatatypeforthisparameterisVARCHAR(32000)Streamfilescontainedwithinthespecifieddirectoryareindexed.
vSymboliclinksareNOTfollowedvSub-directoriesareNOTprocessedvPathnamesmustnotbedelimited,characterssuchasetcdonothaveanyspecialmeaningandshouldnotbeescaped.
vPathnamesmayormaynotbecasesensitive,dependingontheattributeofthefilesystem.
Acheckwillbeperformedwhenaddingtheobjectsettoverifythataduplicatesetdoesnotalreadyexistinthetextsearchcollection.
Thischeckdoesnotconsiderequivalentpathstobeduplicate.
Inotherwordsthefollowingpathscouldallrepresentthesamedirectory,butwillbeconsidereduniqueobjectsets,furthermore,theobjectsinthosesetswillbeindexedmultipletimesasuniqueobjects.
/dir1/DIR2/dir1//DIR2///DIR1/DIR2/(iffilesystemiscaseinsensitive)/dir1/DIR2/.
.
/DIR2etc.
output_set_idOutputIntegervaluethatreturnsthesetidfortheobjectsetthatwasadded.
Thisvaluecanbeusedtoremovetheobjectsetatalatertime.
Thisparameterisoptional.
ThedatatypeforthisparameterisINTEGER.
SpecialConsiderationsforUpdateProcessingNon-existentfilesystems:Ifadirectorycannotbelocatedduringanupdateoperation,thefilesassociatedwiththatdirectorywillnotberemovedfromtheindex.
Thisavoidsunnecessaryre-indexingofdocumentswhenafilessystemisunmountedandlaterremounted.
Ifthesefilesneedtoberemovedfromtheindex,severaloptionsexist:vIssuetheremoveobjectsetstoredprocedureagainsttheIFSStreamfileobjectset.
Thiswillremoveanydocumentsassociatedwiththeobjectset.
vIssuetheREPRIMEstoredprocedureagainstthecollection.
Alldatawillberemovedfromtheindex,andonlyfilesthatcanbelocatedwillbereindexed.
vCreatethedirectoryasanemptydirectoryandissuetheupdateCCSIDConversionIfthecollection'sFORMATisTEXT:vTheCCSIDattributeofthefileisusedtoconvertthefile'sextracteddatatoUTF-8forindexing.
TheCCSIDattributeofthefilemustbecorrectinorderforthefiletobecorrectlyindexed.
Ifthecollection'sFORMATisINSO:vThedatafromthefilewillbeextractedfromthefileandsenttothetextsearchserverforprocessing.
Nocharactersetconversionwilloccur,andtheCCSIDattributeofthefilewillbeignored.
Thetextsearchserverwilluseitsrichtextprocessingtodeterminetheformatandencodingofthedocument.
OmniFindTextSearchServer113Thiscanbeusedtoindexrichtext(suchasPDF)files,orordinarytextfiles.
Forsomeplaintextdocuments,itmaynotbepossibleforthetextsearchservertodetermineencodingofthedocumentwithenoughconfidencetoindexthedata.
Thisismorelikelyforverysmalldocuments,butcanoccurforlargerdocumentsthatuseawiderangeofcharactersaswell.
Iftheformatandencodingofthefilecannotbedetermined,thefilewillnotbeindexedandadocumenterrorwillbelogged.
AuthoritiestoIndexedObjectsWhenaddinganIFSstreamfileobjectset,considertheauthorityrequirementstoreadthestreamfilescarefully.
Adoptedauthoritiesarenothonoredwhenaccessingthestreamfile'sdata.
Inaddition,scheduledupdatesrunundertheuserprofilethatownstheindex.
Seetheupdatestoredproceduredocumentationforinformationontheauthorityrequirementstoindexedobjects.
ExampleAddanobjectsettoMYCOLLECTIONtoindexallstreamfilesinanIFSdirectory'/home/ntl/stmf':>CALLMYCOLLECTION.
ADD_IFS_STMF_OBJECT_SET('/home/ntl/stmf');ToaddIFSpathtoacollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectProperties.
OntheObjecttab,pressAddIFSPathbutton.
RemovinganObjectSetThisstoredprocedurewillremoveanobjectsetfromatextsearchcollection.
REMOVE_OBJECT_SETAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxREMOVE_OBJECT_SET(setid)Theschemaqualifieristhenameofthetextsearchcollection.
ParameterssetidThesetidwhichwasobtainedwhenaddingobjectset.
TheobjectsetIDcanalsobeobtainedusingtheQUERY_OBJECT_SETstoredprocedureaftertheobjectsethasbeenadded.
ThedatatypeforthisparameterisINTEGER.
114IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriExamplesRemoveobjectset#1fromcollectionMYCOLLECTION.
>CALLMYCOLLECTION.
remove_object_set(1)ToremoveanobjectsetfromacollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectProperties.
OntheObjecttab,pressRemovebutton.
UpdatetheCollectionThisstoredprocedureupdatesthecollection.
Whencalledinitially,allobjectsincludedintheobjectsetsforthecollectionareindexed.
Whenthisstoredprocedureiscalledafterasuccessfulinitialupdatehascompleted,allchangedobjectsareupdatedintheindex.
Theprocedurewillnotreturncontroltothecalleruntilaftertheupdatehascompleted.
UPDATEAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxUPDATETheschemaqualifieristhenameofthetextsearchcollection.
ParametersNoneAuthorityRequirementsonIndexedObjectsScheduledupdatesrunundertheprofilethatownsthetextsearchcollection.
CallstotheUPDATEstoredproceduresrunundertheprofileinvokingtheprocedure.
Itisrecommendedthatboththeowneroftheindex,andtheprofileadministeringtheindexhaveauthoritytoreadthetextdatafromallobjectsindexedinthecollection.
Failuretohavesufficientauthoritycancauseunpredictableresults.
Adoptedauthoritywillnotnecessarilybehonoredforcrawlingobjectsandextractingtextfromthoseobjects.
Authorityproblemsduringtheupdateprocessmaycausetheupdatetofail,orindividualdocumentsnottobeindexed.
OmniFindTextSearchServer115ExamplesCALLMYCOLLECTION.
UPDATE;ToupdatethecollectionindexfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectUpdate.
RepopulatetheTextSearchCollectionTheREPRIMEstoredprocedureclearsthecollection,andthenperformsaninitialupdate.
Theprocedurewillnotreturncontroltothecalleruntilaftertheupdatehascompleted.
REPRIMEAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxREPRIMETheschemaqualifieristhenameofthetextsearchcollection.
ParametersNoneReprimeAuthorityConsiderationstoIndexedObjectsScheduledupdatesrunundertheprofilethatownsthetextsearchcollection.
CallstotheREPRIMEstoredproceduresrunundertheprofileinvokingtheprocedure.
Itisrecommendedthatboththeowneroftheindex,andtheprofileadministeringtheindexhaveauthoritytoreadthetextdatafromallobjectsindexedinthecollection.
Failuretohavesufficientauthoritycancauseunpredictableresults.
Adoptedauthoritywillnotnecessarilybehonoredforcrawlingobjectsandextractingtextfromthoseobjects.
Authorityproblemsduringtheupdateprocessmaycausetheupdatetofail,orindividualdocumentsnottobeindexed.
ExamplesCALLMYCOLLECTION.
REPRIME;TorepopulatethetextsearchcollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
116IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectReprime.
SearchtheCollectionThisprocedureallowsausertosearchatextsearchcollectionforobjectsthatmatchasearch.
SEARCHAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxSEARCH(search_string,search_options,number_of_results)Theschemaqualifieristhenameofthetextsearchcollection.
Parameterssearch_stringAstringparameterthatcontainsthesearchexpression.
Note:Thisexpressionmustnotbeallblanksoremptystring.
SeetheSearchArgumentSyntaxoftheOmniFindReferencefordetails.
ThedatatypeofthisparameterisVARCHAR(32700).
search_optionsIdentifiesastringconstantthatspecifiesthesearchargumentoptionsthatareineffectforthefunction.
ThedatatypeofthisparameterisVARCHAR(32700).
search_options:,QUERYLANGUAGE=value,RESULTLIMIT=value,SYNONYM=OFFONQUERYLANGUAGESpecifiesthequerylanguage.
Thevaluecanbeanyofthesupportedlanguagecodes.
IftheQUERYLANGUAGEoptionisnotspecified,thedefaultisthelanguagevalueofthetextsearchindexthatisusedwhenthisfunctionisinvoked.
IfthelanguagevalueofthetextsearchindexisAUTO,thedefaultvalueforQUERYLANGUAGEisen_US.
RESULTLIMITThisprovidesacluetotheDB2optimizerforhowmanyrowsoftheresultsetareexpectedtobeused.
TheoptimizermaychooseadifferentplantoreturnfewerrowsfromtheSEARCHprocedure.
Theoptimizermayalsoignorethisoptionifaperformancebenefitduringthesearchwillnotbeobtained.
OmniFindTextSearchServer117SYNONYMSpecifieswhethertouseasynonymdictionarythatisassociatedwiththetextsearchindex.
Youcanaddasynonymdictionarytoacollectionbyusingthesynonymtool.
OFFisthedefaultvalue.
number_of_resultsOutputIntegervaluethatreturnsthenumberofdocumentsforthesearchresult.
ThedatatypeofthisparameterisVARCHAR(32700).
RESULTSETRETURNEDThesearchprocedurereturnsaresultsetwithmatchesforthesearchexpression.
Theresultsetcontainsfollowingcolumns:OBJTYPECHAR(10)CCSID1208OBJATTRCHAR(10)CCSID1208CONTAINING_OBJECT_LIBCHAR(10)CCSID1208CONTAINING_OBJECT_NAMECHAR(10)CCSID1208OBJECTINFORXMLMODIFY_TIMETIMESTAMPSCOREDOUBLEOBJTYPE-Thetypeofsystemobjectforthisresult(*STMF,*OUTQ,etc).
OBJATR-Theattributeofthesystemobjectthatmatchedthesearchexpression(*SPLF,*DATA,etc).
CONTAINING_OBJECT_LIB-Thelibraryforthematchedsystemobject.
CONTAINING_OBJECT_NAME-Thenameofthematchingsystemobject.
OBJECTINFOR-AnXMLvaluethatdescribesthelocationinformationoftheindexeddatathatmatchedthesearch_stringexpression.
Anexamplespoolfilelocationwilllooklike:QPADEV000Cjob_name>QPADEV000C>USERAjob_user_name>USERA>DSXSVRALSspool_file_name>DSXSVRALS>1spool_file_number>1>ZD21BP1job_system_name>ZD21BP1>1081027create_date>1081027>035554create_time>035554>AnexampleIFSstreamfilelocationmightlooklike:/home/usera/a.
xmlfile_path>/home/usera/a.
xml>DB2providesanumberofwaystoconvertanXMLvalueintootherformatssothatapplicationscanaccessthedata.
Onepossibilityistocreateanxsl-stylesheetandusetheXSLTRANSFORMfunction.
AnotherpossibilityistocreateanannotatedschemafortheXMLvaluesandusetheXDBDECOMPXMLproceduretopopulaterelationaltableswiththevalues.
TheSQLReferenceintheinfocentercontainsdetailsonbothofthesefunctions.
MODIFY_TIME-Atimestampindicatingthelastmodificationtimeoftheobjectthatisinthecollection.
Thistimestampwillneverbemorerecentthanthelastupdateprocess.
118IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriSCORE-Theresultisgreaterthan0butlessthan1iftheindexedtextdatacontainsamatchforthesearchcriteriathatthesearchargumentspecifies.
Themorefrequentlyamatchisfound,thelargertheresultvalue.
Ifthecolumndoesnotcontainamatch,theresultis0.
Theresultsetisorderedbyscoredescending.
Examples1.
callMYCOLLECTION.
search('bigbadwolf');2.
callMYCOLLECTION.
search('bigbadwolf','QUERYLANGUAGE=en_US');TosearchthecollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>Search.
2.
SelectSearch.
QueryObjectSetInformationThisprocedureallowsausertoseethelistofobjectsetsthatarecontainedwithinthecollection.
QUERY_OBJECT_SETAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxQUERY_OBJECT_SETTheschemaqualifieristhenameofthetextsearchcollection.
ParametersNoneRESULTSETRETURNEDTheprocedurereturnsaresultsetthathasonerowforeachobjectset:SETIDINTEGERSETSTATECHAR(10)CCSID1208LASTREFRESHTIMETIMESTAMPADDOBJSETSQLVARCHAR(2000)CCSID1208SETID-Auniqueidentifierassignedtoeachobjectset.
Thisidentifiermaybeusedontheremove_object_setstoredproceduretoremovetheobjectsetfromthecollection.
SETSTATE-Thestateoftheobjectset.
Thisisreservedforfutureexpansionandisalways'ACTIVE'.
LASTREFRESHTIME-Thelasttimetheobjectsethasbeenrefreshedtoreflectobjectsactuallyonthesystem.
ADDOBJSETSQL-TheSQLstoredprocedurecallthatwasusedtoaddthisobjectset.
OmniFindTextSearchServer119ExamplesCallMYCOLLECTION.
QUERY_OBJECT_SET;ToqueryobjectsetinformationfromacollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectProperties.
ClicktheObjecttab.
RetrieveStatusofIndexesObjectsThisprocedurereturnsthestatusofallobjectsinthetextsearchcollection.
GET_OBJECT_STATUSAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxGET_OBJECT_STATUSTheschemaqualifieristhenameofthetextsearchcollection.
ParametersNoneRESULTSETRETURNEDTheprocedurereturnsaresultsetthathasonerowforeachobjectset:OBJECTXMLSTATUS_TIMESTAMPTIMESTAMPSTATUS_CODEINTEGERTEXT_STATUSVARCHAR(100)OBJECT-Thelocationinformationfortheobjectintheindex,thismatchestheformatreturnedbysearchfortheOBJECTINFORcolumn.
STATUS_TIMESTAMP-IftheobjectisintheINDEXEDstate,thenthisisthemodificationtimestampoftheobjectintheindex.
Inotherwordschangesmadetotheobjectpriortothistimearereflectedinthetextsearchindex.
Forallotherstatevalues,thistimestampisthetimethestatewasrecordedintheindex.
Forexample:Iftheobject'sstateisERROR,thisisthetimethattheerrorwasrecorded.
STATUS_CODE-Anumericvaluerepresentingthestateofthisobject:0=Theobjecthasbeenindexedandnochangesweredetectedtotheobject.
10=Theobjectiscurrentlyintheprocessofbeingindexed.
20=Changestotheobjecthavebeendetected.
30=Theobjectwasindexed,butawarningoccurredduringtheindexingprocess.
40=Anerrorpreventedtheobjectfrombeingindexed.
120IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriThesevaluesallowmorecomplexselectioncriteria.
i.
e.
retrieveallobjectsnotcurrentintheindexcouldbeexpressedasSTATUS_CODE>0.
Note:thestateofobjectsthatareintheprocessofbeingupdated,orwereintheprocessofbeingupdatedwhenanupdatewascanceled,coulddifferfromreality.
Itispossibleforanobjecttoreporttherearechangespending,wheninfactthetexttobeindexedhasalreadybeensenttotheserver.
Itisalsopossibletoadocument'sstatustobe'INDEXED',buttheobjectmaynotbesearch-ableuntilafuturecommitpointintheupdateprocess.
TheSTATUS_CODEwillreflectrealitywhentheupdatestoredprocedurehascompleted.
Theresultsetissorted(descending)bystatuscode.
TEXT_STATUS-TheTextVersionofthestatuscode:0=INDEXED10=INDEXING20=CHANGESPENDING30=WARNING40=ERRORExamplesCallMYCOLLECTION.
GET_OBJECT_STATUS;GetObjectsNotIndexedThisprocedurereturnstheobjectswhicharenotindexedinthetextsearchcollection.
GET_OBJECTS_NOT_INDEXEDAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxGET_OBJECTS_NOT_INDEXEDTheschemaqualifieristhenameofthetextsearchcollection.
ParametersNoneRESULTSETRETURNEDTheprocedurereturnsaresultsetthathasonerowforeachobjectset:OBJECTXMLSTATUS_TIMESTAMPTIMESTAMPSTATUS_CODEINTEGERTEXT_STATUSVARCHAR(100)OBJECT-Thelocationinformationfortheobjectintheindex,thismatchestheformatreturnedbysearchfortheOBJECTINFORcolumn.
OmniFindTextSearchServer121STATUS_TIMESTAMP-IftheobjectisintheINDEXEDstate,thenthisisthemodificationtimestampoftheobjectintheindex.
Inotherwordschangesmadetotheobjectpriortothistimearereflectedinthetextsearchindex.
Forallotherstatevalues,thistimestampisthetimethestatewasrecordedintheindex.
Forexample:Iftheobject'sstateisERROR,thisisthetimethattheerrorwasrecorded.
STATUS_CODE-Anumericvaluerepresentingthestateofthisobject:0=Theobjecthasbeenindexedandnochangesweredetectedtotheobject.
10=Theobjectiscurrentlyintheprocessofbeingindexed.
20=Changestotheobjecthavebeendetected.
30=Theobjectwasindexed,butawarningoccurredduringtheindexingprocess.
40=Anerrorpreventedtheobjectfrombeingindexed.
Thesevaluesallowmorecomplexselectioncriteria.
i.
e.
retrieveallobjectsnotcurrentintheindexcouldbeexpressedasSTATUS_CODE>0.
Note:thestateofobjectsthatareintheprocessofbeingupdated,orwereintheprocessofbeingupdatedwhenanupdatewascanceled,coulddifferfromreality.
Itispossibleforanobjecttoreporttherearechangespending,wheninfactthetexttobeindexedhasalreadybeensenttotheserver.
Itisalsopossibletoadocument'sstatustobe'INDEXED',buttheobjectmaynotbesearch-ableuntilafuturecommitpointintheupdateprocess.
TheSTATUS_CODEwillreflectrealitywhentheupdatestoredprocedurehascompleted.
Theresultsetissorted(descending)bystatuscode.
TEXT_STATUS-TheTextVersionofthestatuscode:0=INDEXED10=INDEXING20=CHANGESPENDING30=WARNINGandERROR40=WARNINGandERRORExamplesCallMYCOLLECTION.
GET_OBJECTS_NOT_INDEXED;RetrieveStatusofCollectionThisprocedurereturnsthestatusofthetextsearchcollection.
STATUSAuthorizationThisstoredprocedureiscreatedwithpublicauthority*EXCLUDEandisownedbythecreatorofthetextsearchcollection.
Theprocedurewilladopttheauthorityofthetextsearchcollectionowner'sprofile.
Authoritycanbegrantedtootheruserstoallowthemtoexecutetheprocedure.
SyntaxSTATUSTheschemaqualifieristhenameofthetextsearchcollection.
ParametersNone122IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriRESULTSETRETURNEDTheprocedurereturnsaresultsetthathasonerowforeachobjectset:SERVERIDINTEGERSERVERNAMEVARCHAR(128)SERVERSTATUSVARCHAR(32)LASTUPDATETIMETIMESTAMPLASTUPDATESTATUSVARCHAR(30)UPDATEFREQUENCYVARCHAR(512)SERVERID-TheserverIDforthetextsearchindex.
SERVERNAME-ThehostnameorIPaddressofthetextsearchserver.
SERVERSTATUS-Indicateswhethertheservercanbeusedasatextsearchservertocreatenewtextsearchindexes.
Thedefaultvalueis0(zero),whichmeansthattheservercanbeused.
LASTUPDATETIME-Thetimethatthetextsearchindexwaslastupdated.
LASTUPDATESTATUS-Indicatelastupdatestatusforthetextsearchindex.
NEVERUPDATED-Thetextsearchindexisneverupdatedbefore.
UPTODATE-Thetextsearchindexisuptodate.
CHANGESPENDING-Therearechangespending.
Userneedupdatetheindextomakethetextsearchindexuptodate.
FAILED-Thetextsearchindexwasupdatedfailedlasttime.
UPDATEFREQUENCY-Whentomakeautomaticupdatestothetextsearchindex.
ExamplesCallMYCOLLECTION.
STATUS;ToretrievestatusofcollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
DroppingaTextSearchCollectionThisprocedureremovesatextsearchcollectionfromthesystem.
SYSPROC.
SYSTS_DRPCOLandSYSPROC.
SYSTS_DROP_COLLECTIONAuthorizationTheSYSPROC.
SYSTS_DRPCOLandSYSPROC.
SYSTS_DROP_COLLECTIONstoredproceduresareshippedwithpublicauthority*EXECUTE.
Noauthorityisadoptedandtheprocedurerunsundertheuser'sprofile.
TheusermusthaveauthoritytodroptheSQLschema(includingallobjectswithin)inordertosuccessfullydropthecollection.
SyntaxSYSTS_DRPCOL(collection_name)OmniFindTextSearchServer123SyntaxSYSTS_DROP_COLLECTION(collection_name)TheschemaqualifierisSYSPROC.
Parameterscollection_nameThenameofthecollectionassuppliedontheSYSTS_CREATE_COLLECTIONstoredprocedure.
ThedatatypeforthisparameterisVARCHARE(128).
ExamplesCALLSYSPROC.
SYTS_DRPCOL('"MYCOLLECTION"')Otherexamplesusingtextsearchcollectionprocedures>CALLSYSPROC.
SYSTS_CREATE_COLLECTION('MYCOLLECTION','FORMATTEXT');Bydefault,alwayscalledthestoredproceduresassociatedwiththenewcollection>SETSCHEMAMYCOLLECTION>SETPATHMYCOLLECTIONAddanobjectsetforallspoolfilesownedbyuserZOOKEEPER>CALL.
ADD_SPLF_OBJECT_SET(ZOOKEEPER');Addanobjectsetforallspoolfilescreatedon06/14/2010>CALLADD_SPLF_OBJECT_SET(2010-06-14T00:00:00','2010-06-15T00:00:00');AddanobjectsetforallstreamfilesintheIFSdirectory'/home/zookeeper'>CALLADD_IFS_STMF_OBJECT_SET('/home/zookeeper');Updatethecollection>CALLUPDATE;Searchfor'LionsANDtigersANDbears'>CALLSEARCH('lionsANDtigersANDbears');Grantauthoritytoanotheruser(SEARCHER)>GRANTEXECUTEONSPECIFICPROCEDURESEARCH1TOSEARCHERDropthecollection>CALLSYSPROC.
SYSTS_DROP_COLLECTION('MYCOLLECTION')TodropatextsearchcollectionfromIBMNavigatorfori,followthesesteps.
1.
FromIBMNavigatorfori,expandIBMiManagement>System>AllTasks.
2.
Ontherightpanel,selectSystem>OmniFind>CollectionList.
3.
RightclickthecollectionandselectDelete.
IndependentASPConsiderationsforTextSearchCollectionsIASPconsiderationsforatextsearchcollection.
TextSearchCollectionsmaybecreatedinanIndependentAuxiliaryStoragePool(ASP)environmentwiththefollowingrestrictions:1.
IfanIndependentASPGroupisassociatedwiththecurrentthread,thenthecollectionmustresideonanASPintheindependentASPGroup.
ItisnotpossibletoadministeratextsearchcollectioninthesystemASPorabasicuserASPwhilethethreadissettoanIndependentASPgroup.
Itispossibleto124IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2forisearchacollectionthatexistsinthesystemASPorbasicuserASP,howeverbecausethatindexcannotincludedataontheIndependentASPgroup,onlyobjectsthatareaccessiblewhentheASPGroupis*NONEwillbeincluded.
2.
AtextsearchcollectioncanindexanyobjectsvisiblefromtheASPGroupNamespaceofthecollection.
3.
IfanobjectsetincludesobjectsthatexistwithinthenamespaceoftheASPgroup,butnotonthegroupitself,significantindexingtimecanoccuriftheIndependentASPGroupismovedtoanothersystem.
Forexample:IfacollectioniscreatedonIndependentASP33toindexallspoolfilesonthesystem,andtheASPgroupisswitchedtoadifferentmachine,thenallspoolfilesfromtheoldmachinenotintheASPgroupwillberemovedfromtheindex,andallspoolfilesonthenewmachinenotintheindexwillbeadded.
BackupandRestoreConsiderationsforTextSearchCollectionsSavingandrestoringatextsearchcollection.
Atextsearchcollectionmaybebackedupandrestoredbysavingandrestoringthelibraryoftheschemacreatedforthecollection.
Duringtherestore,anupdatewillbeinitiatedasynchronously.
Theupdatewillcrawltheobjectsonthesystem,andrepopulatetheindex.
Itisnecessarytobeabletorestorethetextsearchindexcontainedwithinthecollectioninorderforthecollectiontobeusable.
Theseconsiderationsforrestoringatextsearchindexmustbeconsidered:vAllrequiredproductsmustbeinstalled.
vThetextsearchservershouldbeavailable.
MessagesandcodesYoucanseethemessagesandSQLreturncodesforOmniFindTextSearchforDB2fori.
Themessagesarelistedinnumericsequence.
OmniFindmessagesYoucanseetheOmniFindmessagesforOmniFindTextSearchforDB2fori.
Themessagesarelistedinnumericsequence.
MessagesareaddedtotheOmniFindmessagefile(QDBTSLIB/QOMFMSGF)forthefollowingerrors.
Table38.
OmniFindmessagesNumberTypeMessageOMF0011InformationTextsearchindexrestoredwithdifferentconfigurationoptions.
OMF0012WarningTheFORMATtypefortheindexbeingcreatedisnotXML.
XMLsearchesarenotsupported.
OMF0334ErrorTheobject''{0}''.
''{1}''youspecifiedisnotsupported.
ATextSearchindexcanonlybebuiltoveranSQLtable,anSQLalias,orasinglememberofaphysicalfile.
OMF0358ErrorThecurrentuserdoesnothaveenoughauthoritytoperformtherequestedoperation.
OMF0359ErrorRestorefailed.
Thetextserverforthetextindexisnotavailable.
SQLCODE-0196Column&3in&1in&2cannotbedropped.
Explanation:Anattemptwasmadetodropcolumn&3.
Thecolumncannotbedroppedbecauseaview,aconstraint,atrigger,oranindexisdependentonthecolumnandRESTRICTwasspecified,orthecolumnispartofthepartitionkey.
Userresponse:SpecifyCASCADEontheALTERTABLEstatementtodropthecolumnandtheviews,constraints,triggers,andindexesthataredependentonit.
Ifthecolumnispartofthepartitionkey,specifySQLCODE-0196OmniFindTextSearchServer125||||||||||||||||||||||||||||||||||||||||DROPPARTITIONINGontheALTERTABLEstatementtoremovethepartitioningforthetable.
Trytherequestagain.
SQLSTATE:42817SQLCODE-5003Cannotperformoperationundercommitmentcontrol.
Explanation:ThefollowingoperationscannotbeperformedundercommitmentcontrolwithCOMMIT(*CHG),COMMIT(*CS),orCOMMIT(*ALL)specified:vDROPSCHEMAstatement.
vGRANTorREVOKEstatementtoanobjectthathasanauthorityholder.
vCREATEstatementinSQLnamingmodeofanobjectthathasanauthorityholder.
vDROPofatextsearchindex.
Theseoperationscannotbecommittedorrolledback.
Userresponse:SpecifyCOMMIT(*NONE),andtrythestatementagain.
SQLSTATE:42922SQLCODE-20423Erroroccurredduringtextsearchprocessing.
Explanation:AnerroroccurredduringthetextsearchprocessingofaCONTAINSorSCOREfunction.
Theerrorhappenedonserverserverusingtextsearchindexindex-nameforreasoncodereason-code.
Textdescribingtheproblemis:text.
server:ThehostnameorIPaddressandportofthetextsearchserverwheretheerrorwasencountered.
index-name:Thenameoftheindexusedinthetextsearchprocessing.
Note:Includetheschemaandaperiodwiththeindexnameinasingletoken.
reason-code:ThereasoncodereturnedfromtheOmniFindTextSearchServerforDB2fori.
text:ThetextreturnedfromtheOmniFindTextSearchServerforDB2fori.
Systemaction:Thestatementcannotbeprocessed.
Userresponse:ContactyoursystemadministratortocheckthattheOmniFindTextSearchServerforDB2foriissuccessfullyinstalled.
SQLSTATE:38H10Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
SQLCODE-20424Textsearchsupportisnotavailableforreasonreason-code.
Explanation:Aproblemwithoneofthetextsearchadministrativetableswasdetected.
Thereasoncodeisreason-code.
1Oneofthetextsearchadministrationtableswasnotfound(QSYS2.
SYSTEXTINDEXES,QSYS2.
SYSTEXTCOLUMNS,orQSYS2.
SYSTEXTSERVERS).
3TheTextSearchsupportisnotstarted.
4TheSTATUScolumninQSYS2.
SYSTEXTSERVERStablehasavalueof1,indicatingthatthesupportforthetextsearchisstopped.
7NoOmniFindTextSearchServerforDB2forishavebeendefined.
Systemaction:Thestatementcannotbeprocessed.
Userresponse:Contactyoursystemadministratortomakesurethatsupportfortextsearchingissuccessfullysetuponyoursystem.
SQLSTATE:38H11Relatedreference:"QSYS2.
SYSTEXTSERVERSadministrationtable"onpage99YoucanseewherethetextsearchserversareinstalledusingtheQSYS2.
SYSTEXTSERVERSadministrationtable.
"QSYS2.
SYSTEXTINDEXESadministrationtable"onpage97YoucanseeinformationabouteachtextsearchindexintheQSYS2.
SYSTEXTINDEXESadministrationtable.
Eachtextsearchindexhasaname,schemaname,andanassociatedcollectionnameonthetextsearchserver.
"QSYS2.
SYSTEXTCOLUMNSadministrationtable"onpage99YoucanseeinformationaboutthetextcolumnsforatextsearchindexintheQSYS2.
SYSTEXTCOLUMNSadministrationtable.
EachtextsearchindexhasanindexID,textcolumnnames,andtheschemanameofthebasetable.
SQLCODE-5003SQLCODE-20424126IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||SQLCODE-20425Textsearchnotallowedforcolumncolumn-name.
Explanation:ACONTAINSorSCOREtextsearchfunctionspecifiedcolumncolumn-nameintabletable-nameintable-schema.
Atextindexdoesnotexistforthiscolumnsotextsearchprocessingcannotbeperformed.
Systemaction:Thestatementcannotbeprocessed.
Userresponse:VerifythatthecolumnandtableareregisteredtotheOmniFindTextSearchServerforDB2fori.
SQLSTATE:38H12Relatedreference:"CONTAINS"onpage35YoucanusetheCONTAINSfunctiontosearchatextsearchindexusingcriteriayouspecifyinasearchargument.
Thefunctionreturnsaresultindicatingwhetheramatchwasfound.
"SCORE"onpage37YoucanusetheSCOREfunctiontosearchatextsearchindexusingcriteriathatyouspecifyinasearchargument.
Thefunctionreturnsarelevancescorethatmeasureshowwelladocumentmatchesthequery.
SQLCODE-20426Conflictingtextsearchadministrationprocedureisalreadyrunning.
Explanation:Aconflictingtextsearchadministrativeproceduresuchasupdateisalreadyrunningonthisindex.
Systemaction:Thestatementcannotbeprocessed.
Userresponse:Invoketheadministrationstoredprocedureagainafterthecurrentlyrunningstoredprocedurecompletes.
SQLSTATE:38H13SQLCODE-20427Erroroccurredduringtextsearchadministrativeprocedure.
Explanation:Anerroroccurredduringatextsearchadministrativeprocedure.
Thereasoncodeisreason-code.
Thetextreturnedis:text.
Theerrortextdescribestheproblem.
Systemaction:TheCALLstatementfailswiththisSQLCODE.
Userresponse:Fixtheproblemthatisindicatedbyerrorandinvoketheadministrativestoredprocedureagain.
SQLSTATE:38H14CPF32faOperationnotallowedontextsearchindex&2in&1.
Explanation:Anoperationwasattemptedthatisnotsupportedforatextsearchindex.
TextsearchindexesdonotallowsomeoperationsthatareallowedfortraditionalDB2indexesandviews.
Ifthiswasanattempttodeletetheindex,theoperationmayhavefailedbecausecommitmentcontrolwasactive.
Userresponse:PerformtextsearchadministrativeoperationsusingtheadministrativeSQLstoredproceduresthatareincludedwithOmniFindTextSearchServerforDB2fori.
Formoreinformationontextsearchindexes,andwhatrestrictionsapplytothem,refertothedocumentationintheInformationCenter:http://www.
ibm.
com/systems/i/infocenter/CPF32fbOperationontextsearchindex&2in&1couldnotbecompleted.
Explanation:Anoperationwasattemptedagainsttextsearchindex&2in&1.
Therequestedoperationisnotcurrentlyvalidforreasoncode&3reasoncodesandtheirmeaningsare:1.
Arequiredproductisnotinstalled2.
Therequestedtextsearchserver&4isnotavailableorisnotdefined.
3.
Arestoreoftheindexwasattemptedbuttheindexalreadyexists.
Theexistingindexcouldnotbemodifiedtomatchthesavedindex.
4.
Adependentobject&5in&6type&7didnotexist.
5.
Anobject&5in&6type&7wasnotavailable.
6.
Theauxiliarystoragepool(ASP)forthecurrentthreaddoesnotmatchtheASPofthetextsearchindex.
7.
Atextsearchindexalreadyexistsforcolumn&8,table&5inlibrary&6.
Userresponse:Correcttheproblemandretrytheoperation.
Formoreinformationontextsearchindexes,refertothedocumentationintheInformationCenter:http://www.
ibm.
com/systems/i/infocenterCPI321EFile&1inlibrary&2deferred.
Explanation:File&1inlibrary&2wasdeferredduringthisrestorerequestwithspecifiedDeferIDof&4.
Thefilewasdeferredbecauseofreasoncode&3.
Thereasoncodesare:1.
Based-onfile&5inlibrary&6didnotexistwhen&1wasbeingcreatedfortherestore.
2.
File&1failedtocreateforsomeotherreasonthanamissingbased-onfile.
Seepreviousmessage(s)todeterminewhythecreateofthefilefailed.
SQLCODE-20425CPI321EOmniFindTextSearchServer127||||||||||||||||||||||||||||||||||||||||||||||||||||||3.
Oneormoreofthemembersforfile&1failedtocreate.
Seepreviousmessage(s)todeterminewhythecreateofthemember(s)failed.
4.
ThefilerepresentsaTextSearchIndex,andtherequiredlicensedprogramobjectsdonotexistonthesystem.
5.
TheFilerepresentsaTextSearchIndex,andtheindexcouldnotberecreated.
Userresponse:Forreasoncode1:Eitherrestorethemissingbased-onfile,orusetheRestoreDeferredObjects(RSTDFROBJ)commandspecifyingthesameDeferID(DFRIDparameter)&4oneitherofthecommandsused.
Forreasoncodes2and3:Correctthereasonsforthefailedcreate,andthenusetheRestoreDeferredObjects(RSTDFROBJ)commandspecifyingthesameDeferID(DFRIDparameter)&4onthecommand.
Forreasoncode5:Seethepreviousmessagesinthejoblog,correctanyerrors,andthenusetheRestoreDeferredObjects(RSTDFROBJ)command,specifyingthesameDeferID(DFRIDparameter)&4onthecommand.
Relatedinformation:RestoreDeferredObjects(RSTDFROBJ)CodelicenseanddisclaimerinformationIBMgrantsyouanonexclusivecopyrightlicensetouseallprogrammingcodeexamplesfromwhichyoucangeneratesimilarfunctiontailoredtoyourownspecificneeds.
SUBJECTTOANYSTATUTORYWARRANTIESWHICHCANNOTBEEXCLUDED,IBM,ITSPROGRAMDEVELOPERSANDSUPPLIERSMAKENOWARRANTIESORCONDITIONSEITHEREXPRESSORIMPLIED,INCLUDINGBUTNOTLIMITEDTO,THEIMPLIEDWARRANTIESORCONDITIONSOFMERCHANTABILITY,FITNESSFORAPARTICULARPURPOSE,ANDNON-INFRINGEMENT,REGARDINGTHEPROGRAMORTECHNICALSUPPORT,IFANY.
UNDERNOCIRCUMSTANCESISIBM,ITSPROGRAMDEVELOPERSORSUPPLIERSLIABLEFORANYOFTHEFOLLOWING,EVENIFINFORMEDOFTHEIRPOSSIBILITY:1.
LOSSOF,ORDAMAGETO,DATA;2.
DIRECT,SPECIAL,INCIDENTAL,ORINDIRECTDAMAGES,ORFORANYECONOMICCONSEQUENTIALDAMAGES;OR3.
LOSTPROFITS,BUSINESS,REVENUE,GOODWILL,ORANTICIPATEDSAVINGS.
SOMEJURISDICTIONSDONOTALLOWTHEEXCLUSIONORLIMITATIONOFDIRECT,INCIDENTAL,ORCONSEQUENTIALDAMAGES,SOSOMEORALLOFTHEABOVELIMITATIONSOREXCLUSIONSMAYNOTAPPLYTOYOU.
128IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2fori||||||||||||||||||||||||Appendix.
NoticesThisinformationwasdevelopedforproductsandservicesofferedintheU.
S.
A.
IBMmaynotoffertheproducts,services,orfeaturesdiscussedinthisdocumentinothercountries.
ConsultyourlocalIBMrepresentativeforinformationontheproductsandservicescurrentlyavailableinyourarea.
AnyreferencetoanIBMproduct,program,orserviceisnotintendedtostateorimplythatonlythatIBMproduct,program,orservicemaybeused.
Anyfunctionallyequivalentproduct,program,orservicethatdoesnotinfringeanyIBMintellectualpropertyrightmaybeusedinstead.
However,itistheuser'sresponsibilitytoevaluateandverifytheoperationofanynon-IBMproduct,program,orservice.
IBMmayhavepatentsorpendingpatentapplicationscoveringsubjectmatterdescribedinthisdocument.
Thefurnishingofthisdocumentdoesnotgrantyouanylicensetothesepatents.
Youcansendlicenseinquiries,inwriting,to:IBMDirectorofLicensingIBMCorporationNorthCastleDriveArmonk,NY10504-1785U.
S.
A.
Forlicenseinquiriesregardingdouble-byte(DBCS)information,contacttheIBMIntellectualPropertyDepartmentinyourcountryorsendinquiries,inwriting,to:IntellectualPropertyLicensingLegalandIntellectualPropertyLawIBMJapan,Ltd.
3-2-12,Roppongi,Minato-ku,Tokyo106-8711ThefollowingparagraphdoesnotapplytotheUnitedKingdomoranyothercountrywheresuchprovisionsareinconsistentwithlocallaw:INTERNATIONALBUSINESSMACHINESCORPORATIONPROVIDESTHISPUBLICATION"ASIS"WITHOUTWARRANTYOFANYKIND,EITHEREXPRESSORIMPLIED,INCLUDING,BUTNOTLIMITEDTO,THEIMPLIEDWARRANTIESOFNON-INFRINGEMENT,MERCHANTABILITYORFITNESSFORAPARTICULARPURPOSE.
Somestatesdonotallowdisclaimerofexpressorimpliedwarrantiesincertaintransactions,therefore,thisstatementmaynotapplytoyou.
Thisinformationcouldincludetechnicalinaccuraciesortypographicalerrors.
Changesareperiodicallymadetotheinformationherein;thesechangeswillbeincorporatedinneweditionsofthepublication.
IBMmaymakeimprovementsand/orchangesintheproduct(s)and/ortheprogram(s)describedinthispublicationatanytimewithoutnotice.
Anyreferencesinthisinformationtonon-IBMWebsitesareprovidedforconvenienceonlyanddonotinanymannerserveasanendorsementofthoseWebsites.
ThematerialsatthoseWebsitesarenotpartofthematerialsforthisIBMproductanduseofthoseWebsitesisatyourownrisk.
IBMmayuseordistributeanyoftheinformationyousupplyinanywayitbelievesappropriatewithoutincurringanyobligationtoyou.
Licenseesofthisprogramwhowishtohaveinformationaboutitforthepurposeofenabling:(i)theexchangeofinformationbetweenindependentlycreatedprogramsandotherprograms(includingthisone)and(ii)themutualuseoftheinformationwhichhasbeenexchanged,shouldcontact:IBMCorporationCopyrightIBMCorp.
2002,2010129SoftwareInteroperabilityCoordinator,DepartmentYBWA3605Highway52NRochester,MN55901U.
S.
A.
Suchinformationmaybeavailable,subjecttoappropriatetermsandconditions,includinginsomecases,paymentofafee.
ThelicensedprogramdescribedinthisdocumentandalllicensedmaterialavailableforitareprovidedbyIBMundertermsoftheIBMCustomerAgreement,IBMInternationalProgramLicenseAgreement,IBMLicenseAgreementforMachineCode,oranyequivalentagreementbetweenus.
Anyperformancedatacontainedhereinwasdeterminedinacontrolledenvironment.
Therefore,theresultsobtainedinotheroperatingenvironmentsmayvarysignificantly.
Somemeasurementsmayhavebeenmadeondevelopment-levelsystemsandthereisnoguaranteethatthesemeasurementswillbethesameongenerallyavailablesystems.
Furthermore,somemeasurementsmayhavebeenestimatedthroughextrapolation.
Actualresultsmayvary.
Usersofthisdocumentshouldverifytheapplicabledatafortheirspecificenvironment.
Informationconcerningnon-IBMproductswasobtainedfromthesuppliersofthoseproducts,theirpublishedannouncementsorotherpubliclyavailablesources.
IBMhasnottestedthoseproductsandcannotconfirmtheaccuracyofperformance,compatibilityoranyotherclaimsrelatedtonon-IBMproducts.
Questionsonthecapabilitiesofnon-IBMproductsshouldbeaddressedtothesuppliersofthoseproducts.
AllstatementsregardingIBM'sfuturedirectionorintentaresubjecttochangeorwithdrawalwithoutnotice,andrepresentgoalsandobjectivesonly.
AllIBMpricesshownareIBM'ssuggestedretailprices,arecurrentandaresubjecttochangewithoutnotice.
Dealerpricesmayvary.
Thisinformationisforplanningpurposesonly.
Theinformationhereinissubjecttochangebeforetheproductsdescribedbecomeavailable.
Thisinformationcontainsexamplesofdataandreportsusedindailybusinessoperations.
Toillustratethemascompletelyaspossible,theexamplesincludethenamesofindividuals,companies,brands,andproducts.
Allofthesenamesarefictitiousandanysimilaritytothenamesandaddressesusedbyanactualbusinessenterpriseisentirelycoincidental.
COPYRIGHTLICENSE:Thisinformationcontainssampleapplicationprogramsinsourcelanguage,whichillustrateprogrammingtechniquesonvariousoperatingplatforms.
Youmaycopy,modify,anddistributethesesampleprogramsinanyformwithoutpaymenttoIBM,forthepurposesofdeveloping,using,marketingordistributingapplicationprogramsconformingtotheapplicationprogramminginterfacefortheoperatingplatformforwhichthesampleprogramsarewritten.
Theseexampleshavenotbeenthoroughlytestedunderallconditions.
IBM,therefore,cannotguaranteeorimplyreliability,serviceability,orfunctionoftheseprograms.
Thesampleprogramsareprovided"ASIS",withoutwarrantyofanykind.
IBMshallnotbeliableforanydamagesarisingoutofyouruseofthesampleprograms.
Eachcopyoranyportionofthesesampleprogramsoranyderivativework,mustincludeacopyrightnoticeasfollows:(yourcompanyname)(year).
PortionsofthiscodearederivedfromIBMCorp.
SamplePrograms.
CopyrightIBMCorp.
_entertheyearoryears_.
130IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriIfyouareviewingthisinformationsoftcopy,thephotographsandcolorillustrationsmaynotappear.
ProgramminginterfaceinformationThisOmniFindTextSearchServerforDB2foripublicationdocumentsintendedProgrammingInterfacesthatallowthecustomertowriteprogramstoobtaintheservicesofOmniFindTextSearchServerforDB2fori.
TrademarksIBM,theIBMlogo,andibm.
comaretrademarksorregisteredtrademarksofInternationalBusinessMachinesCorp.
,registeredinmanyjurisdictionsworldwide.
OtherproductandservicenamesmightbetrademarksofIBMorothercompanies.
AcurrentlistofIBMtrademarksisavailableontheWebatCopyrightandtrademarkinformationatwww.
ibm.
com/legal/copytrade.
shtml.
Adobe,theAdobelogo,PostScript,andthePostScriptlogoareeitherregisteredtrademarksortrademarksofAdobeSystemsIncorporatedintheUnitedStates,and/orothercountries.
JavaandallJava-basedtrademarksandlogosaretrademarksofSunMicrosystems,Inc.
intheUnitedStates,othercountries,orboth.
LinuxisaregisteredtrademarkofLinusTorvaldsintheUnitedStates,othercountries,orboth.
Windows,isatrademarkofMicrosoftCorporationintheUnitedStates,othercountries,orboth.
UNIXisaregisteredtrademarkofTheOpenGroupintheUnitedStatesandothercountries.
Othercompany,product,orservicenamesmaybetrademarksorservicemarksofothers.
TermsandconditionsPermissionsfortheuseofthesepublicationsisgrantedsubjecttothefollowingtermsandconditions.
PersonalUse:Youmayreproducethesepublicationsforyourpersonal,noncommercialuseprovidedthatallproprietarynoticesarepreserved.
Youmaynotdistribute,displayormakederivativeworksofthesepublications,oranyportionthereof,withouttheexpressconsentofIBM.
CommercialUse:Youmayreproduce,distributeanddisplaythesepublicationssolelywithinyourenterpriseprovidedthatallproprietarynoticesarepreserved.
Youmaynotmakederivativeworksofthesepublications,orreproduce,distributeordisplaythesepublicationsoranyportionthereofoutsideyourenterprise,withouttheexpressconsentofIBM.
Exceptasexpresslygrantedinthispermission,nootherpermissions,licensesorrightsaregranted,eitherexpressorimplied,tothepublicationsoranyinformation,data,softwareorotherintellectualpropertycontainedtherein.
IBMreservestherighttowithdrawthepermissionsgrantedhereinwhenever,initsdiscretion,theuseofthepublicationsisdetrimentaltoitsinterestor,asdeterminedbyIBM,theaboveinstructionsarenotbeingproperlyfollowed.
Youmaynotdownload,exportorre-exportthisinformationexceptinfullcompliancewithallapplicablelawsandregulations,includingallUnitedStatesexportlawsandregulations.
IBMMAKESNOGUARANTEEABOUTTHECONTENTOFTHESEPUBLICATIONS.
THEPUBLICATIONSAREPROVIDED"AS-IS"ANDWITHOUTWARRANTYOFANYKIND,EITHEREXPRESSEDORIMPLIED,INCLUDINGBUTNOTLIMITEDTOIMPLIEDWARRANTIESOFAppendix.
Notices131MERCHANTABILITY,NON-INFRINGEMENT,ANDFITNESSFORAPARTICULARPURPOSE.
132IBMi:e-businessandWebservingOmniFindTextSearchServerforDB2foriAppendix.
Notices133IBMPrintedinUSA