Set up a database for text search
- Specify the default database. DB2 Text Search provides the option
to set an environment variable to specify the database that should
be used for text-search index administration commands. If the
necessary user authorizations are in place, you can issue
db2tscommands without specifying a connection clause. The environment variable can be set as follows:
Listing 1. Setting DB2DBDFT to specify the default database
Linux/UNIX (ksh): export DB2DBDFT=mytstest Windows: SET DB2DBDFT=mytstest
- Create a database. In this step, a database is created that is
suitable to store XML documents. Issue the following command at
the DB2 CLP command prompt:
Listing 2. Command to create the database
CREATE DATABASE mytstest USING CODESET UTF-8 TERRITORY US
- Start the text-search instance services. To start the text-search
instance services, use the command:
Listing 3. Command to start text-search services
db2ts "START FOR TEXT"
On Linux and UNIX operating systems, this command starts a daemon. This daemon controls the scheduling of text-search index updates on the DB2 database server. The text-search server is also started and runs as an independent process. The daemon and the text-search server are started under the authorization name of the DB2 instance owner.
On Windows operating systems, this command starts the DB2TS service. The DB2TS service starts the text-search server and henceforth controls the scheduling of text-search index updates. The user running the command must meet the Windows requirements for starting a service.
- Enable the database for text search. DB2 Text Search manages
information about text-search indices to support administration
tasks like index updates or gathering status. Enabling the
database creates the system tables and views that contain this
Listing 4. Command to enable the database for text search
db2ts "ENABLE DATABASE FOR TEXT"
Note that if DB2DBDFT has not been set, the enablement command needs to include the connection information, as shown in the following example:
Listing 5. Command to enable the database for text with specific connection information
db2ts "ENABLE DATABASE FOR TEXT CONNECT TO mytstest USER db2admin USING mypswd"
- Connect to the database mytstest:
Listing 6. Connect to the database mytstest
CONNECT TO mytstest
- Create and populate a table:
Listing 7. Table books
CREATE TABLE books ( isbn VARCHAR(18) NOT NULL PRIMARY KEY, author VARCHAR(30), title VARCHAR(128), year INTEGER, bookinfo XML)
Note that to enable creating text-search indices and running text-search queries, a primary key must be specified for the data table.
- Insert the following data into the books table:
Listing 8. Insert data into table books
INSERT INTO books VALUES ('123-014014014', 'Joe Climber', 'Climber''s Mountain Tops', 1995, XMLPARSE(DOCUMENT '<bookinfo> <author>Joe Climber</author> <title>Climber''s Mountain Tops</title> <story>This vivid description of Joe Climber''s experiences when tackling the mountains of his native Mountainland lets you hold your breath when you follow Joe on his adventures in the regions where the air is thin and the weather is treacherous. Includes beautiful color photos of Mountainland''s mountain ranges. </story> <year>1995</year> <price>16.00</price> <pages>176</pages> </bookinfo>')) INSERT INTO books VALUES ('678-014014078', 'Joe Smith', 'The Range', 1991, XMLPARSE(DOCUMENT '<bookinfo> <author>Joe Smith</author> <title>The Range</title> <story>All you need to know about kitchen ranges. A pictured description based on the most recent ergonomics studies for everybody who is involved in food preparation in the home. </story> <year>1991</year> <price>6.00</price> <pages>76</pages> </bookinfo>')) INSERT INTO books VALUES ('111-223334444', 'Sam Climber', 'Top of the Mountain: Mountain Lore', 1966, XMLPARSE(DOCUMENT '<bookinfo> <author>Sam Climber</author> <title>Top of the Mountain: Mountain Lore</title> <story>Sam Climber has traveled through the world to gather stories about mountains. This compendium includes the best stories and is beautifully illustrated. </story> <year>1966</year> <price>20.00</price> <pages>449</pages> </bookinfo>')) INSERT INTO books VALUES ('777-010101010', 'Samantha Smitt', 'The Database Compendium', 2001, XMLPARSE(DOCUMENT '<bookinfo> <author>Samantha Smitt</author> <title>The Database Compendium</title> <story>Follow Samantha into the world of database management. Covers a wide range of the most popular database architectures. </story> <year>2001</year> <price>19.00</price> <pages>222</pages> </bookinfo>')) INSERT INTO books VALUES ('123-918273645', 'Joanne Miller', 'The Travel Companion', 2005, XMLPARSE(DOCUMENT '<bookinfo> <author>Joanne Miller</author> <title>The Travel Companion </title> <story>If you like to travel, this is the book for you. Joanne Miller has compiled her funny and often surprising diary entries about places and people which were previously published as travel log in ''Here and There'' </story> <year>2005</year> <price>31.00</price> <pages>321</pages> </bookinfo>'))