Tivoli Service Desk 6.0 Developer's Toolkit Script Language Reference
Tivoli Service Desk (TSD) uses an IBM text retrieval product called Intelligent Miner for Text (IM4T) to create indexes for tables in your database. The indexes it creates can be used by your service desk analysts to quickly find particular entries (pre-existing data files or documents) in the database that contain certain words or combinations of words. The indexes do not affect the original format in which data is stored.
This section describes how to:
IM4T is included with your TSD software and there is no additional license fee for its
use.
IM4T uses client/server architecture for indexing database tables. All the indexes are
created and maintained on a single server. Each client workstation running TPM configures
itself as a client to that server.
Before you can use the text retrieval capability of TSD, you must create and configure a
server for your indexes. The first step of this process is to install TSD on the machine
you have designated to be your IM4T server. You must install the package entitled
"IM4T Server Installation." To configure an IM4T server do the following:
Result: The Tivoli Service Desk IMT Maintenance Window opens. Fill in the fields as follows:
Section/Field | Required Information |
Name | Name of the server |
Connection Information section | |
Host | IP name of your computer |
Port | Port number. You should create an entry in the etc/services file for this server. Note: This number should be for a port not being used by any other programs on your LAN. |
Task Information section | |
Maximum | Maximum number (between 1 and 100) of tasks the search service can handle simultaneously |
Timeout | Time required by the search service to receive information from the client |
Available | Number of tasks to start upon server start up. |
You should only administer indexes on the machine you have configured as an IM4T server.
Note: Under Unix operating systems, you will also have to be logged in to
the imoadm administrative account that was created when TSD was
installed.
You can create indexes by running TSD Text Retrieval Administration Utility (tsd_text.kbc). This is the same utility you used to configure the server.
The utility will first ask you to login to the TSD datasource you will be creating indexes for. If you login to a data source for which no IM4T server is configured, the utility will prompt you to do so first.
Once you've logged in and a server is configured, you will see the utility's main scren which is entitled, "Tivoli Service Desk IM4T Maintenance."
The tsd_text utility builds indexes of the Description or Solution columns that occur
in certain kinds of Tivoli Problem Management (TPM) diagnostic aid records. When you have
a large number of records in your database, it can be faster to use IM4T to locate
specific diagnostic aid records than to use the Description or Solution boxes available in
an Inquiry dialog box.
Text searching can be available at these dialog boxes:
Note:
This tsd_text utility is also used to build indexes of problems, changes, and descriptions that are used during impact analysis in Tivoli Change Management. See Performing Impact Analysis for more details.With IM4T, you can create new indexes and update existing indexes. In some cases, you may need to delete an index. Creating, updating, and deleting indexes can take a significant amount of time, depending on the size of your database. If your help desk is not active during the night, consider starting maintenance activities on your IM4T indexes at the end of the day.
You must create indexes before your help desk analysts can use the Text Inquiry functions available at these Inquiry dialog boxes: Common Problem Inquiry, Error Message Inquiry, Hot News Inquiry, and Solutions Inquiry.
To ensure your help desk analysts search the most recent solutions and problem descriptions, you need to update your indexes on a regular basis. Because the update process can take a significant amount of time, consider starting it at the end of the day. After the process begins, you can leave it unattended.
On occasion, you may want to delete a IM4T index. If an index appears to be corrupted, delete and recreate the index. Database data is not deleted when you delete an index.
The goal of querying a database text index is to rapidly locate fields in the database. To aid this, the SQLSelect command seeks and acts on the presence of a special escape sequence, $TextSearch. This sequence allows a query to a Text Retrieval index as well as to the database. (A valid entry in a $TextSearch sequence is a valid WHERE clause for a Text Retrieval datasource). Review the following examples. Select all the solutions that relate to hardware maintenance.
Select * from solutions where $TextSearch (contains 'hardware' & 'maintenance')
Select all the active solutions that relate to hardware maintenance.
Select * from solutions where $TextSearch (contains 'hardware' & 'maintenance') AND Active = 1
If multiple Text Retrieval indexes are associated with a single table in a database, you must specify the index name in the $TextSearch clause. The index name should precede the query and be followed by a semicolon (;).
Select * from solutions where $TextSearch (solNDX; description contains 'hardware') AND Active = 1 OR solution_id > 3000
To configure an IM4T server do the following:
Section/Field | Required Information |
Search Service | Name of the service instance (same as server information) |
Host | IP name of your IM4T server. |
Port | Port number. Note: This number should be for a port not being used by other programs on your LAN. This number will be the same as the server port number. |
Advanced button | Allows you to set advanced options |
This section is a reference to common document retrieval functions used with IM4T.
FUNCTION FTRSQLTextIndexCreate(IndexName : STRING, Fields : LIST OF IndexFieldRec) : INTEGER;
The IndexName must be eight characters or less. The IndexFieldRec type is declared as follows:
IndexFieldRec IS RECORD TableName : STRING; --Name of the table to be indexed FieldName : STRING; --Name of the field to be indexed Flags : INTEGER; --Combination of KEY, VALUE,LITERAL, and LONGCHAR END;
This record is defined in ftr.kb.
Definitions for flags in IndexFieldRec are listed in the following table.
Flag |
Description |
SAI_DBTRNDX_KEY | A key field in the database table. At least one IndexFieldRec passed to FTRSQLTextIndexCreate must have this flag set. SAI_DBTRNDX_VALUE indicates that the column contains a numerical value. |
SAI_DBTRNDX_LITERAL | Indicates that the column contains a literal value (usually a string) |
AI_DBTRNDX_LONGCHAR | Indicates that the column contains a character string (usually a long text field). At least one IndexFieldRec passed to FTRSQLTextIndexCreate must have this flag set. |
Note: Database text indexes must contain every primary key field in the table being indexed as well as at least one field of type LONGCHAR.
Based on the data it receives, FTRSQLTextIndexCreate creates an index with the following characteristics: all fields are indexed and are able to be referenced as part of the text column of the index table. All Key fields are stored in their own column in the index table. For example:
VARIABLES NewIndex : LIST OF IndexFieldRec; Entry : IndexFieldRec; ACTIONS Entry.TableName = `EQ_Defects'; Entry.FieldName = `Defect_Title'; Entry.Flags = SAI_DBTRNDX_LITERAL; ListInsert(NewIndex,Entry); Entry.TableName = `EQ_Defects'; Entry.FieldName = `Defect_Desc_Text'; Entry.Flags = SAI_DBTRNDX_LONGCHAR; ListInsert(NewIndex, Entry); Entry.TableName = `EQ_Defects'; Entry.FieldName = `Defect_Steps_Text'; Entry.Flags = SAI_DBTRNDX_LONGCHAR; ListInsert(NewIndex, Entry); Entry.TableName = `EQ_Defects'; Entry.FieldName =`Defect_Title'; Entry.Flags = BitOr(SAI_DBTRNDX_KEY, SAI_DBTRNDX_VALUE); ListInsert(NewIndex, Entry); FTRSQLTextIndexCreate(`DefNDX', NewIndex); END;
Return Codes |
Description |
1 | Success |
2001 | DBTR ERR INVALID TABLE NAME |
2002 | DBTR ERR INVALID INDEX NAME |
2003 | DBTR ERR INVALID INDEX FIELD |
(other) | Error Code (Refer to IM4T Success/Error Messages) |
FUNCTION FTRSQLTextIndexDelete(IndexName : STRING) : INTEGER;
Function | Description |
IndexName | Specifies the name of a database text index to be deleted |
FTRSQLTextIndexDelete finds the specified index and drops the index. For example:
FTRSQLTextIndexDelete(`DEFNDX');
Return Codes |
Description |
1 | Success |
2002 | DBTR_ERR_INVALID_INDEX_NAME |
(other) | Error Code (Refer to IM4T Success/Error Messages) |
FUNCTION FTRSQLTextIndexUpdate(IndexName : STRING, Method : INTEGER) : INTEGER;
Function | Description |
IndexName | Specifies the name of the index to be updated |
Method | Specifies if the index is updated or rebuilt from scratch.
This variable can have one of two values:
|
FTRSQLTextIndexUpdate finds the specified index and performs the
action specified in Method. For example:
FTRSQLTextIndexUpdate (`DEFNDX', SAI_NDX_REBUILD);
Return Codes |
Description |
1 | Success |
2002 | DBTR ERR INVALID INDEX NAME |
(other) | Error code (Refer to IM4T Success/Error Messages) |
FUNCTION FTRSQLTextIndexUpdateAll(Method : INTEGER):INTEGER;
Method specifies if indexes are updated or rebuilt from scratch. This variable can have one of two values:
SAI_NDX_UPDATE is the default. If indexes already exist, they are updated to account for changes in the data. If indexes do not exist yet (that is, are merely defined), they are created at this point.
SAI_NDX_REBUILD builds indexes whether they exist or not.
FTRSQLTextIndexUpdateAll updates every database text index that exists on the current data source using the method defined by Method. Example:
FTRSQLTextIndexUpdateAll(SAI_NDX_REBUILD);
Return Codes |
Description |
1 | Success |
(other) | Error code (Refer to IM4T Success/Error Messages section) |
FTRSQLSetFilterWhere (IndexName : STRING , WhereStmt : STRING) : INTEGER;
This function will set the Where Filter of an index if the index exists.
ret:=FTRSQLSetFilterWhere('MYINDEX', 'USER_ID>10000');
Return Codes |
Description |
1 | Success |
2002 | DBTR ERR INVALID INDEX NAME |
2005 | DBTR ERR NVALID WHERE STATEMENT |
(other) | Error Code (Refer to IM4T Success/Error Messages section) |
FTRSQLGetFilterWhere (IndexName : STRING, WhereStmt : STRING) : INTEGER;
This function will get the Where Filter of an index if a Where filter exists.
WhereStmt : STRING;
Return Codes |
Description |
1 | Success |
2002 | DBTR ERR INVALID INDEX NAME |
(other) | Error Code (Refer to IM4T Success/Error Messages section) |
Syntax
FTRSQLDeleteFileterWhere (WhereStmt : STRING) : INTEGER;
This function will delete the Where Filter of an index if the index exists.
ret:=FTRSQLDeleteFilterWhere('MYINDEX');
Return Codes |
Description |
1 | Success |
2002 | DBTR ERR INVALID INDEX NAME |
(other) | Error Code (Refer to IM4T Success/Error Messages section) |
Syntax
FTRListIndexes (Servername : STRING , ResultList : LIST OF STRINGS ) : INTEGER;
This function will List all of the available indexes of a server.
IdxList:LIST OF STRING; ret:=FTRListIndexes('MYSRVR',IdxList);
Return Codes |
Description |
1 | Success |
2002 | DBTR ERR INVALID INDEX NAME |
(other) | Error Code (Refer to IM4T Success/Error Messages section) |
FTRSQLTextIndexCreate
The following example is an index for the Tivoli Problem Management (TPM) Solutions
table and displays
the resulting Success/Error message from the call.
KNOWLEDGEBASE MakeNDX;
USES TEXTRET;
ROUTINES
PROCEDURE TestMain;
PRIVATE
ROUTINES
PROCEDURE TestMain IS
VARIABLES
Col : IndexFieldRec;
ColList : LIST OF IndexFieldRec;
Lines : List of String;
nRC : Integer;
whdl : Window;
ACTIONS
SQLCommand(`connect TOOLKIT');
Col.TableName := 'solutions';
Col.FieldName := 'solution_id';
Col.Flags := BitOr (SAI_DBTRNDX_VALUE,
SAI_DBTRNDX_KEY);
ListPush(ColList, Col);
Col.TableName := 'dbo.solutions';
Col.FieldName := 'description';
Col.Flags := SAI_DBTRNDX_LONGCHAR;
ListPush(ColList, Col);
nRC := FTRSQLTextIndexCreate('solndx', ColList);
ListInsert(Lines, nRC, $BEFORE);
WinCreateScrollWindow($Desktop, whdl,
$NullHandler,
5,5,50,15,
'Create Index',
$SystemMonospaced,
10,
$WinDefaultStyle);
WinWriteLN(whdl, Lines);
WinWait(whdl);
END;
Following is a code example of an index query.
KNOWLEDGEBASE querysol; ROUTINES PROCEDURE TestMain; PRIVATE ROUTINES PROCEDURE TestMain IS VARIABLES Lines : List of String; nRC : Integer; whdl : Window; cursor : SQLCURSOR; System : String; ACTIONS nRC := SQLCommand('CONNECT ADVISOR'); IF (nRC <> 1) THEN EXIT; END; ListInsert(Lines, nRC, $BEFORE); nRC := SQLSelect(cursor, 'SELECT SYSTEM FROM SOLUTIONS WHERE $TextSearch(SOLNDX;DESCRIPTION CONTAINS ''PROBLEM'')'); ListInsert(Lines, nRC, $BEFORE); nRC := SQLFetch(cursor, System); WHILE (nRC = 1) DO ListInsert(Lines, System, $BEFORE); nRC := SQLFetch(cursor, System); END; ListInsert(Lines, nRC, $BEFORE); SQLCloseCursor(cursor); WinCreateScrollWindow($Desktop, whdl, $NullHandler, 5,5,50,15, 'Query Index', $SystemMonospaced, 10, BitOr($WinDefaultStyle, $WinVScroll)); WinWriteLN(whdl, Lines); WinWait(whdl); END;
Error Code | Error Message |
-17407 | SQLERR_NO_KEY |
-17408 | SQLERR_NO_LONGCHAR |
-17409 | SQLERR_COL_TYPE_CONFLICT |
-17410 | SQLERR_INDEX_NAME_NOT_FOUND |
-17411 | SQLERR_BAD_KEY_TYPE |
-17415 | SQLERR_TR_NOT_INITIALIZED |
-17417 | SQLERR_INVALID_TEXTSEARCH |
-17420 | SQLERR_INVALID_INDEX_NAME |
-17424 | SQLERR_TEXTMINER_DATA_BUILD |
-17425 | SQLERR_CANT_DETERMINE_SERVER_LOCATION |
-17426 | SQLERR_SECTION_FILE_SYNTAX_ERROR |
-17427 | SQLERR_DEFINITION_FILE_SYNTAX_ERROR |
-17428 | SQLERR_CANT_OPEN_FILE |
-17429 | SQLERR_CANT_WRITE_FILE |
-17430 | SQLERR_CANT_READ_FILE |
-17431 | SQLERR_TABLE_NAME_NOT_SET |
-17432 | SQLERR_ENVIRONMENT_VARIABLE_NOT_SET |
-17433 | SQLERR_INVALID_REMOTE_PROCEDURE |
-17434 | SQLERR_MISSING_LANGUAGE |
-17435 | SQLERR_CANT_GET_CODEPAGE |
-17700 | SQLERR_NOT_ENOUGH_MEMORY |
-17701 | SQLERR_DICTIONARY_NOT_FOUND |
-17702 | SQLERR_STOPWORD_IGNORED |
-17703 | SQLERR_CCS_NOT_SUPPORTED |
-17704 | SQLERR_LANGUAGE_NOT_SUPPORTED |
-17705 | SQLERR_CONFLICT_WITH_INDEX_TYPE |
-17706 | SQLERR_INVALID_MASKING_SYMBOL |
-17707 | SQLERR_INDEX_GROUP_SEARCH_ERROR |
-17708 | SQLERR_INDEX_SPECIFIC_ERROR |
-17709 | SQLERR_UNEXPECTED_ERROR |
-17710 | SQLERR_DATASTREAM_SYNTAX_ERROR |
-17711 | SQLERR_UNKNOWN_SESSION_POINTER |
-17712 | SQLERR_PROCESSING_LIMIT_EXCEEDED |
-17713 | SQLERR_REQUEST_IN_PROGRESS |
-17714 | SQLERR_MAX_NUMBER_OF_RESULTS |
-17715 | SQLERR_SERVER_NOT_AVAILABLE |
-17716 | SQLERR_SERVER_BUSY |
-17717 | SQLERR_SERVER_CONNECTION_LOST |
-17718 | SQLERR_INDEX_DELETED |
-17719 | SQLERR_INDEX_SUSPENDED |
-17720 | SQLERR_INDEX_NOT_ACCESSIBLE |
-17721 | SQLERR_EMPTY_QUERY |
-17722 | SQLERR_EMPTY_INDEX |
-17723 | SQLERR_FUNCTION_DISABLED |
-17724 | SQLERR_FUNCTION_IN_ERROR |
-17725 | SQLERR_INSTALLATION_PROBLEM |
-17726 | SQLERR_COMMUNICATION_PROBLEM |
-17727 | SQLERR_IO_PROBLEM |
-17728 | SQLERR_WRITE_TO_DISK_ERROR |
-17729 | SQLERR_MAX_NUMBER_OF_BUSY_INDEXES |
-17730 | SQLERR_UNKOWN_SECTION_NAME |
-17731 | SQLERR_DOCMOD_READ_PROBLEM |
-17732 | SQLERR_INCORRECT_AUTHENTICATION |
-17733 | SQLERR_CONFLICTING_TASK_RUNNING |
-17734 | SQLERR_NO_ACTION_TAKEN |
-17735 | SQLERR_LS_NOT_EXECUTABLE |
-17736 | SQLERR_LS_FUNCTION_FAILED |
-17737 | SQLERR_MAX_NUMBER_OF_TASKS |
-17738 | SQLERR_MISSING_DEFAULT_MODEL |
-17739 | SQLERR_UNKNOWN_DOCUMENT_MODEL-NAME |
-17740 | SQLERR_MEMBER_OF_INDEX_GROUP |
-17741 | SQLERR_UNKNOWN_INDEX_NAME |
-17742 | SQLERR_INDEX_ALREADY_OPENED |
-17743 | SQLERR_MAX_NUMBER_OF_OPEN_INDEXES |
-17744 | SQLERR_CONTINUATION_MODE_ENTERED |
-17745 | SQLERR_EMPTY_LIST |
-17746 | SQLERR_SERVER_IN_ERROR |
-17747 | SQLERR_FUNCTION_NOT_SUPPORTED |
-17748 | SQLERR_UNKNOWN_INDEX_TYPE |
-17749 | SQLERR_INCORRECT_INDEX_NAME |
-17750 | SQLERR_INCORRECT_LS_EXECUTABLES |
-17751 | SQLERR_INCORRECT_LIBRARY_ID |
-17752 | SQLERR_INCORRECT_LOCATION |
-17753 | SQLERR_INDEX_ALREADY_EXISTS |
-17754 | SQLERR_MAX_NUMBER_OF_INDEXES |
-17755 | SQLERR_LOCATION_IN_USE |
-17756 | SQLERR_UNKNOWN_SERVER_NAME |
-17757 | SQLERR_UNKNOWN_COMMUNICATION_TYPE |
-17758 | SQLERR_UNKNOWN_SERVER_INFORMATION |
-17759 | SQLERR_INCORRECT_HANDLE |
-17760 | SQLERR_QUERY_TOO_COMPLEX |
Warning Code | Warning Message |
-17510 | SQLWARN_CFG_NULL_ITEM |
-17511 | SQLWARN_TEXTMINER_CLEANUP_ERROR |
-17512 | SQLWARN_PARTIAL_FAILURE |
For more information refer to the Intelligent Miner for Text (IM4T) documentation at:
http://www.software.ibm.com/data/iminer/fortext
Tivoli Service Desk 6.0 Developer's Toolkit Script Language Reference