Copyright © Cloud Software Group, Inc. All Rights Reserved

Deleting and Rebuilding a Secondary Index

A secondary index must be deleted before it can be rebuilt. You can explicitly delete a secondary index using the TIBCO Object Service Broker supplied SIXDELETE tool or the Table Definer option from the administrator’s workbench.

Why Rebuild a Secondary Index?

A number of reasons can cause you to need to rebuild a secondary index:

•

If you are doing massive insertions or deletions to a table, the data pages could split or merge, even though the secondary index pointer is not updated. If it is not updated, the index is flagged as having invalid page pointers and the primary key value is used to search the occurrences instead. Because this causes unnecessary reads of data you should rebuild an index if you did a large number of updates into a table.

•	If the secondary index build failed, for example because a numeric field contained null data, you must rebuild the index. A lowercase s or lowercase q in the KEY field of the table definition indicates that an index is unusable.

•	If you unload the data and definition of a table from a source system and load the definition and data into a target system, the secondary index information is not retained. You must rebuild the secondary index in the target system.

Steps Required

Deleting and rebuilding a secondary index consists of the following tasks:

1.	Delete a Secondary Index

2.	Update the data or correct an index build failure

3.	Rebuild the index

 

Task A Delete a Secondary Index

You can use the SIXDELETE tool from within a rule to delete secondary indexes for TDS tables. If the table type is TDS, you also have the option of using the Table Definer from the administrator’s workbench.

Using SIXDELETE

To use the SIXDELETE tool, execute it from a rule by specifying:

CALL SIXDELETE(tablename, fieldname);

 

tablename	Name of the table from where the secondary index is being deleted
fieldname	Name of the field from where the secondary index is being deleted

A call must be made to SIXDELETE for each field that is having the secondary index deleted. The following example deletes a secondary index from the LNAME field in the @EMPLOYEE2 table:

CALL SIXDELETE('@EMPLOYEE2', 'LNAME');

Using the Define Table Option

To use the Define Table option, complete the following steps:

1.	On the administrator’s workbench, position the cursor beside the DT Define Table option and press Enter. 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.

3.	A list of fields for that table appears. The following shows an example of the display for the @EMPLOYEE2 table.

This displayed screen uses a Y to indicate which fields are key fields.

---

 

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

---

 

4.	Type the line command D next to fields that you are deleting as secondary index fields and press Enter. The secondary key specification for the fields is deleted from the table definition. If the field is part of a composite primary key, the Q displayed in the table definition is changed to P.

Task B Update the data or correct an index build failure

The reasons you are rebuilding the secondary index determine the actions that you take. Use the following table to determine the appropriate action:

Reason for the Rebuild	Action
Large updates made to a table.	Update your data.
Unloaded data and definition from a source system.	Load the definition and data onto the target system.
An index build failure occurred.	Correct the reason for the failure.

Task C Rebuild the index

Rebuild your index as described in Procedure to Create a Secondary Index.

Copyright © Cloud Software Group, Inc. All Rights Reserved