Verifying the text search installation
Db2 provides a sample application that you can use to verify installation of the text search feature. The sample application consists of job DSNTEJ6O and two programs, DSN8DTS1 and DSN8DTS2.
The program DSN8DTS1 is a C-language source module that shows how to use the Db2-supplied stored procedures SYSPROC.SYSTS_START, SYSPROC.SYSTS_CREATE, and SYSPROC.SYSTS_UPDATE to create and initialize a text search index. Program DSN8DTS1 accepts the following three input parameters:
- The name of the schema for the text search index that is to be created. For example, DSN8910.
- The name of the text search index that is to be created. For example, IAA3.
- The name of the table and column on which to create the text search index. For example, DSN8910.TEXT_SEARCH(AA3).
The program DSN8DTS2 is a C-language source module that shows how to use the Db2-supplied stored procedures SYSPROC.SYSTS_START and SYSPROC.SYSTS_DROP to drop a text search index. Program DSN8DTS2 accepts the following two input parameters:
- The name of the schema for the text search index that is to be dropped. For example, DSN8910.
- The name of the text search index that is to be dropped. For example, IAA3.
The JCL job DSNTEJ6O performs the following tasks:
- Creates and populates a sample table called DSN8910.TEXT_SEARCH that contains Unicode-encoded text data in fixed-character, varying character, varying graphic, XML, and character LOB (CLOB) format.
- Prepares, binds, and runs sample program DSN8DTS1 to create text search indexes on table DSN8910.TEXT_SEARCH.
- Processes queries on DSN8910.TEXT_SEARCH using the SCORE and CONTAINS functions.
- Prepares, binds, and runs sample program DSN8DTS2 to drop the text search index on DSN8910.TEXT_SEARCH.
The steps of job DSNTEJ6O are listed below. You need to customize job DSNTEJ60 to run on your Db2 system. For instructions, see the job prolog. See the source code for DSN8DTS1 and DSN8DTS1 for more information about those parts.
Step Procstep Description Return code
-------- -------- ----------------------------------------------- ------------
PH06OS01 Drop the sample search table 0000
PH06OS02 Create and populate sample search table 0000
PH06OS03 Prepare DSN8DTS1
PC - precompile 0000
C - compile 0000
PLKED - pre-link edit 0004
LKED - link edit 0000
PH06OS04 Bind the plan and package for DSN8DTS1 0000 or 0004
PH06OS05 Create a text search index on a VARCHAR col 0000
PH06OS06 Create a text search index on a CHAR col 0000
PH06OS07 Create a text search index on a VARGRAPHIC col 0000
PH06OS08 Create a text search index on a XML col 0000
PH06OS09 Create a text search index on a CLOB col 0000
PH06OS10 Select data using CONTAINS and SCORE functions 0000
PH06OS11 Prepare DSN8DTS2
PC - precompile 0000
C - compile 0000
PLKED - pre-link edit 0004
LKED - link edit 0000
PH06OS12 Bind the plan and package for DSN8DTS2 0000 or 0004
PH06OS13 Drop the text search index on the VARCHAR col 0000
PH06OS14 Drop the text search index on the CHAR col 0000
PH06OS15 Drop the text search index on the VARGRAPHIC cl 0000
PH06OS16 Drop the text search index on the XML col 0000
PH06OS17 Drop the text search index on the CLOB col 0000
Validating step PHO60S10
The correct results for validation step PHO60S10 are as follows:
SELECT INT1, CONTAINS(VC2, 'text')
FROM DSN8910.TEXT_SEARCH;
+---------------------------------+
| INT1 | |
+---------------------------------+
1_| 1 | 0 |
2_| 2 | 1 |
3_| 3 | 0 |
4_| 4 | 1 |
5_| 5 | 0 |
6_| 6 | 0 |
7_| 7 | 1 |
8_| 8 | 1 |
9_| 9 | 0 |
10_| 10 | 1 |
+---------------------------------+
SUCCESSFUL RETRIEVAL OF 10 ROW(S)
SELECT INT1, SCORE(VC2, 'text')
FROM DSN8910.TEXT_SEARCH
WHERE CONTAINS(VC2, 'text') = 1;
+-----------------------------------------+
| INT1 | |
+-----------------------------------------+
1_| 2 | 4.100000000000000E-02 |
2_| 4 | 4.100000000000000E-02 |
3_| 7 | 4.100000000000000E-02 |
4_| 8 | 4.100000000000000E-02 |
5_| 10 | 4.100000000000000E-02 |
+-----------------------------------------+
SUCCESSFUL RETRIEVAL OF 5 ROW(S)
SELECT INT1, CONTAINS(AA3, 'DB2')
FROM DSN8910.TEXT_SEARCH;
+---------------------------------+
| INT1 | |
+---------------------------------+
1_| 1 | 0 |
2_| 2 | 0 |
3_| 3 | 0 |
4_| 4 | 0 |
5_| 5 | 1 |
6_| 6 | 0 |
7_| 7 | 0 |
8_| 8 | 1 |
9_| 9 | 0 |
10_| 10 | 0 |
+---------------------------------+
SUCCESSFUL RETRIEVAL OF 10 ROW(S)
SELECT INT1, SCORE(AA3, 'DB2')
FROM DSN8910.TEXT_SEARCH
WHERE CONTAINS(AA3, 'DB2') = 1;
+-----------------------------------------+
| INT1 | |
+-----------------------------------------+
1_| 5 | 6.000000000000000E-02 |
2_| 8 | 6.000000000000000E-02 |
+-----------------------------------------+
SUCCESSFUL RETRIEVAL OF 2 ROW(S)
SELECT INT1, VC2, AA3
FROM DSN8910.TEXT_SEARCH
WHERE CONTAINS(AA3, 'DB2') = 1
AND CONTAINS(VC2, 'text') = 1;
+-------------------------------------------------------------+
| INT1 | VC2 | AA3 |
+-------------------------------------------------------------+
1_| 8 | text search capabilities | in DB2. |
+-------------------------------------------------------------+
SUCCESSFUL RETRIEVAL OF 1 ROW(S)