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 $ONEDB_HOME/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.

Table 1. Query tracking fields
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.
  1. Create the bts index with tracking enabled: 
    CREATE INDEX bts_idx ON products (brands  bts_char_ops)
 USING bts (query_log="yes") IN sbsp1;
  2. 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);
  3. Load the log records into the log table: 
    LOAD FROM '$ONEDB_HOME/tmp/bts_query.log' INSERT INTO bts_query_log_data;
  4. 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.