query_log index parameter
You can determine the frequency of queries that are run against a bts index by logging queries.
About this task
When tracking is enabled, each query that is run against the bts index produces a log record in the $INFORMIXDIR/tmp/bts_query.log file. Each log record has five fields, which are separated by pipe characters (|):
query time stamp|index name|partn|query|number of rows|
The fields are described in the following table.
Field name | Data type | Description |
---|---|---|
query time stamp | DATETIME YEAR TO FRACTION | The time when the query was run. |
index name | LVARCHAR | The name of the index. |
partn | INTEGER | The identifying code of the physical location of the fragment in which the index is located. |
query | LVARCHAR | The syntax of the query. |
number of rows | INTEGER | The number of rows that are returned by the query. |
You can view the log records by loading them into a table and then querying the table.
Procedure
This example shows how to track queries.
- Create the bts index with tracking enabled:
CREATE INDEX bts_idx ON products (brands bts_char_ops) USING bts (query_log="yes") IN sbsp1;
- Create a table to hold the log records:
CREATE TABLE bts_query_log_data( qwhen DATETIME YEAR TO FRACTION, idx_name LVARCHAR, partn INTEGER, query LVARCHAR, rows INTEGER);
- Load the log records into the log table:
LOAD FROM '$INFORMIXDIR/tmp/bts_query.log' INSERT INTO bts_query_log_data;
- Query the log table to view the log records:
SELECT ids_name,query,rows FROM bts_query_log_data; idx_name bts_idx query melville rows 14 idx_name bts_idx query dickens rows 29 idx_name bts_idx query austen rows 3 3 row(s) retrieved.