When creating full text search environments in IBM DB2, you must set up the indexes manually. This configuration is performed when you set up a new environment, however, if the schema is dropped, you must re-configure the indexes.
Note that the tables must be set up using CIM before the index can be applied. The creation of the text search indexes must be done by procedure calls as CIM does not support CallableStatements.
Note: Ensure that you have the latest IBM DB2 fix packs. This ensures that you can grant the necessary privileges required for text searches, as well as use procedure calls. Refer to your IBM DB2 documentation for detailed information on working with text searches.
Creating the Order Index
Call the stored procedure with the (optional) desired update frequency. For example, to call a stored procedure that creates an index with no incremental updates:
CALL SYSPROC.SYSTS_ADMIN_CMD ('create index SRCH_SEARCH_ORDER_IDX for text
on SRCH_ORDER_TOKENS(TOKENS','en_US', ?)To run a stored procedure that creates an index that incrementally updates every minute:
CALL SYSPROC.SYSTS_ADMIN_CMD ('create index SRCH_SEARCH_ORDER_IDX for text
on SRCH_ORDER_TOKENS(TOKENS) update frequency D(*) H(*) M(0,1,2,3,4,5,
6,7,8, 9,10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,
30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,
54,55,56,57,58,59)','en_US', ?)Altering the Order Index
If you have created the index without an update frequency, you may alter the index specifying the update frequency by calling the stored procedure on the index:
CALL SYSPROC.SYSTS_ADMIN_CMD ('alter index SRCH_ORDER_IDX for text
update frequency D(*) H(*) M(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,
15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,
39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59) ',
'en_US',?)Updating the Order Index
Call the stored procedure to update the index:
CALL SYSPROC.SYSTS_ADMIN_CMD ('update index SRCH_ORDER_IDX for
text','en_US', ?)Dropping the Order Index
Note that if the update frequency option is still in effect, you may need to turn it off first before dropping the index by running the following command:
CALL SYSPROC.SYSTS_ADMIN_CMD ('alter index SRCH_SEARCH_ORDER_IDX for text update frequency none,'en_US', ?)Call the stored procedure to drop the index:
CALL SYSPROC.SYSTS_ADMIN_CMD ('drop index SRCH_ORDER_IDX for text,'en_US',
?)Create the Profile Index
Call the stored procedure and provide the desired update frequency. For example, to call a stored procedure that creates an index with no incremental updates:
CALL SYSPROC.SYSTS_ADMIN_CMD ('create index SRCH_SEARCH_PROFILE_IDX for
text on SRCH_PROFILE_TOKENS(TOKENS','en_US', ?)To call a stored procedure that creates an index that incrementally updates every minute enter the following command:
CALL SYSPROC.SYSTS_ADMIN_CMD ('create index SRCH_SEARCH_PROFILE_IDX for
text on SRCH_ORDER_TOKENS(TOKENS) update frequency D(*) H(*) M(0,1,2,
3,4,5, 6,7,8, 9,10, 11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,
27,28,29, 30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,
51,52,53, 54,55,56,57,58,59)','en_US', ?)Altering the Profile Index
If you have created the index without an update frequency, you may alter the index specifying the update frequency by calling the stored procedure on the index:
CALL SYSPROC.SYSTS_ADMIN_CMD ('alter index SRCH_PROFILE_IDX for text
update frequency D(*) H(*) M(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,
15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,
39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59) ','en_US',
?)Updating the Profile Index
Call the stored procedure to update the index:
CALL SYSPROC.SYSTS_ADMIN_CMD ('update index SRCH_PROFILE_IDX for
text','en_US', ?)Dropping the Profile Index
Note that if the update frequency option is still in effect, you may need to turn it off first before dropping the index by running the following command:
CALL SYSPROC.SYSTS_ADMIN_CMD ('alter index SRCH_PROFILE_IDX for text update frequency none,'en_US', ?)Call the stored procedure to drop the index:
CALL SYSPROC.SYSTS_ADMIN_CMD ('drop index SRCH_PROFILE_IDX for
text,'en_US',?)
