Up to sixteen fields, totalling a maximum of 127 bytes, can be defined as secondary index fields. Additional secondary index fields can be defined, up to the maximum of sixteen, at a later time.
Good candidates for secondary index fields are fields in which the retrieved values identify a useful subset of the data. For example, the primary key of the @EMPLOYEES table is the employee number (
EMPNO) but searches often take place on the last name (
LNAME) field because it is easier to remember than the employee number. The last names are not necessarily unique as the employee numbers are but they provide a small enough subset of the data to be useful.
Numeric fields that can potentially contain null data should not be used as secondary index fields. An index cannot be created on a table that contains null values in numeric fields.
Fields that contain many duplicate values are also poor candidates for secondary index fields. For example, in the @EMPLOYEES table, if many employees live in the same state or province (
STATE_PROV), a large subset of data could be returned in response to a data access.
If you run an index creating tool against an empty table, the index is defined, but not built, because there are no values in the table on which to build index entries. The index is built when you insert data into the table.
To use the SIXBUILD tool, execute it from a rule by specifying:
A call must be made for each field that is to be a secondary index field. If the table contains data, the index is built when you call
SIXBUILD. If the table is empty, the index is built when data is loaded into it.
SIXBUILD cannot be used for large tables (tables with more than approximately 3000 data pages); you must use S6BBRSIX or hrnbrsix instead.
S6BBRSIX and hrnbrsix are batch utilities that you can use to build secondary indexes. S6BBRSIX is the z/OS version and hrnbrsix is the Open Systems version.
Used to build secondary indexes on populated TDS tables. Indexes on tables of more than 3,000 data pages or that exceed site limitations for online building must be built using this utility.
When you define an index on an empty TDS table, the index is not built until you put data in the table. After the indexes are defined, the index entries are built on every occurrence subsequently inserted into the table.
If you used S6BBRSIX or hrnbrsix to build the index, you must use the Table Editor to edit the FIELDS table. To edit this table, complete the following steps:
The letters that you add through the FIELDS table appear in the KEY field when you look at the definition through the Table Definer.
Utilities for your platform for specific information on how to use the S6BBRTBL/hrnbrtbl and S6BBRSIX/hrnbrsix utilities.
TIBCO Object Service Broker Programming in Rules for more information about the INSERT statement.