Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 6 Secondary Indexes : Procedure to Create a Secondary Index

Procedure to Create a Secondary Index
Creating a secondary index consists of the following tasks:
1.
2.
3.
4.
These tasks are described in the following sections.
 
Task A Choose the secondary index fields
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
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.
Poor Candidates
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.
Task B Define and Build the Secondary Index
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.
If you run an index creating tool against a populated table, the index is both defined and built.
Tools Available
Use the following table to determine the tools to use to define/build a secondary index:
Table Type
Using an Interactive Tool
Using Rules
Using the Define Table Option
To use the Define Table option, complete the following process:
1.
A list of tables similar to the following appears.

 
List of defined tables
Command ==> Scroll P
Enter one or more line commands or a primary command
NAME TYPE CREATOR CREATD UNIT MODIFIER MODIFD BI*
---------------- ---- -------- ---------- -------- -------- ---------- ---
_ ABC1 TDS USR40 2000-04-07 USR40 USR40 2000-04-07 N
_ DATA_SRCH_EX TDS RPSUSR 2000-04-06 SEARCH SEARCH 2000-04-06 N
_ DATA_SRCH_EXCLUD TDS RPSUSR 2000-04-06 SEARCH SEARCH 2000-04-06 N
_ FOOTA TDS ABC10 2000-04-03 ABC10 ABC10 2000-04-03 N
_ FOOTAS SUB ABC10 2000-04-03 ABC10 ABC10 2000-04-03 N
_ DATA_SRCH TDS RPSUSR 2000-03-28 SEARCH SEARCH 2000-03-28 Y
_ LOCTEST TDS RPSUSR 2000-03-09 RPSUSR RPSUSR 2000-03-09 N
_ LOCTESTS SUB RPSUSR 2000-03-09 RPSUSR RPSUSR 2000-03-09 N
_ DOC_TABLES TDS RBPADM 2000-03-09 RPSUSR RPSUSR 2000-03-09 N
_ SESSTST2 TDS RPSUSR 2000-03-06 RPSUSR RPSUSR 2000-03-06 N
_ DATA_SRCH_ERR SES RPSUSR 2000-03-06 SEARCH SEARCH 2000-03-06 N
_ DATA_SRCH_RESULT TDS RPSUSR 2000-03-06 SEARCH SEARCH 2000-03-06 Y
_ DATATEST TDS RPSUSR 2000-03-06 SEARCH SEARCH 2000-03-06 N
_ NRGTEST TDS USR40 2000-03-06 USR40 USR40 2000-03-06 N
_ USRWEB TDS USR00 2000-03-06 USR40 USR40 2000-03-06 N
_ @EMPLOYEE2 TDS USR50 1999-12-14 DOCEXMPL USR50 2000-03-05 N
B-Bind C-Clear D-Delete M-Move P-Prt R-Reset Bind S-Sel X-SIX Bld/Del
PFKEYS: 12=EXIT 13=PRINT 3=END 5=FIND NEXT 9=RECALL

 
2.
Type the line command X beside the required table and press Enter.
A list of fields for that table appears. An example of the display for the @EMPLOYEES table follows:

 
List of fields in table TABLENAME: @EMPLOYEE2
Command ==> Scroll P
Enter one or more line commands or a primary command
NAME TYPE SYNTAX LENGTH DECIMAL PRIMARYKEY SECONDARYINDEX
---------------- ---- ------ ------ ------- ---------- --------------
_ EMPNO I P 3 0 Y
_ LNAME S C 22 0 Y
_ POSITION S C 14 0
_ MGR# I P 3 0
_ DEPTNO I B 2 0
_ SALARY Q P 4 2
_ ADDRESS S V 38 0
_ CITY S C 20 0
_ STATE_PROV S C 4 0
_ ZP_CODE S C 7 0
_ HIREDATE D B 4 0
 
B-Build SIX D-Delete SIX
PFKEYS: 12=EXIT 13=PRINT 3=END 5=FIND NEXT 9=RECALL

 
3.
Type the line command B next to fields that you are defining as secondary index fields and press Enter.
A Y appears in the SECONDARYINDEX attribute for the fields, for example LNAME in this example screen. If the table has a composite primary key, you can define a secondary index on any of the key fields except the first one.
Using SIXBUILD
To use the SIXBUILD tool, execute it from a rule by specifying:
CALL SIXBUILD(tablename, fieldname);
where
 
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.
The following example defines and builds a secondary index on the field LNAME in the populated @EMPLOYEE2 table:
CALL SIXBUILD('@EMPLOYEE2', 'LNAME');
Using S6BBRSIX/hrnbrsix
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.
S6BBRSIX/ hrnbrsix
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.
You must also prepare a set of control cards before invoking the secondary index build utilities. These control cards are defined using the SIXBUILD_CARDS tool.
Task C Load the table
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.
You can use the following to load a table with data and consequently build the index:
 
A batch utility that loads data into TDS tables. When using this utility, you must specify the fields that have secondary indexes.
Task D Edit the FIELDS 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:
1.
2.
tablename is the name of the table on which you created secondary indexes. For example, to mark the secondary indexes for the @EMPLOYEE2 table, type: Fields(@EMPLOYEES)
3.
If the field is part of a composite key, put an uppercase Q in the KEYTYPE field to show that the field is both a primary and secondary key.
The letters that you add through the FIELDS table appear in the KEY field when you look at the definition through the Table Definer.
See Also
TIBCO Object Service Broker Shareable Tools for more information about SIXBUILD, LOAD and SIXBUILD_CARDS.
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.

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved