Question & Answer
Question
How do you load a table and create a Basic Text Search index on a specific column?
Answer
This article details a simple method of setting up a table with a Basic Text Search (BTS) index.
1. Create an external space for the BTS index to reside in (Note that this is on a file system and not stored within the database instance.)
$ mkdir /informix/bts_index_directory
$ onspaces -c -x bts_index -l /informix/bts_index_directory
2. Add a BTS VP to the database server.
Update the $ONCONFIG for the instance with the appropriate VPCLASS entry and restart the instance :
VPCLASS bts,num=1
Note: Instead of restarting the instance, a BTS VP can be added dynamically using the command onmode -p +1 bts
3. Create a database and a table where the bts index will be created against and load some data.
dbaccess - - <<EOF
create database demo_bts with buffered log;
create table tab1 (col1 int primary key, col2 char(20)) lock mode row;
insert into tab1 values (1,"bts_index_directory");
insert into tab1 values (2,"dbspace_1");
insert into tab1 values (3,"dbspace_2");
insert into tab1 values (4,"dbspace_blob1");
insert into tab1 values (5,"dbspace_sblob");
insert into tab1 values (6,"dbspace_sblob2");
insert into tab1 values (7,"dbspace_temp1");
insert into tab1 values (8,"dbspace_temp2");
insert into tab1 values (9,"rootdbs");
EOF
4. Register the BTS blade against the database.
$ echo "register bts.1.00 demo_bts" | blademgr
5. Create the BTS index.
dbaccess demo_bts - <<EOF
create index col2_idx1 on tab1 (col2 bts_char_ops) using bts (delete = 'immediate') in bts_index;
EOF
The following are some example queries :
Example 1
select * from tab1 where bts_contains(col2,'dbspace_temp1');
returns
7 dbspace_temp1
Example 2
- select * from tab1 where bts_contains(col2,'dbspace_*');
returns
2 dbspace_1
3 dbspace_2
4 dbspace_blob1
5 dbspace_sblob
6 dbspace_sblob2
7 dbspace_temp1
8 dbspace_temp2
Example 3
- select * from tab1 where bts_contains(col2,'dbspace_t?mp?');
returns
7 dbspace_temp1
8 dbspace_temp2
Was this topic helpful?
Document Information
More support for:
Informix Spatial DataBlade
Software version:
1.0
Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows
Document number:
351913
Modified date:
06 April 2023
UID
swg21272326