Using BTPSEQ and key changes

A customer called to ask "If I use BTPFIND to locate a record, then use BTPDEL to delete the record's B-tree entry, then change some attributes in the record that affect its B-tree sort key, then use BTPINS to put the record and its new key back in the B-tree, can I then use BTPSEQ to pick up where the BTPFIND left off and get the next sequential record in the B-tree?"

The answer is no. Any call to BTPDEL or BTPINS may cause a complete restructuring of the B-tree. That means the node pointers returned by BTPFIND and BTPSEQ are only valid for immediate subsequent calls to BTPSEQ. Any intervening use of BTPDEL or BTPINS can cause unpredictable results.

If you need to sequentially step through records in B-tree order, and change keys as you go, the proper procedure is to use BTPFIND to locate record X, and immediately call BTPSEQ to extract the identifier for the next record Y before any changes to the B-tree occur. Then use BTPDEL to remove record X's index from the B-tree, change the attributes in record X that affect the key, and call BTPINS to re-index record X back into the B-tree. At this point, the whole structure of the B-tree may have changed, so now call BTPFIND again to find the current position of record Y, and immediately call BTPSEQ to find the next record Z. Then use BTPDEL and BTPINS on record Y, use BTPFIND to re-find record Z, and so on.

The above procedure is the only way to guarantee every record will be extracted from the B-tree in the proper order, if BTPINS and BTPDEL calls are desired between calls to BTPFIND and BTPSEQ. As usual, the B-tree should also be locked if multiple ports are using BTPDEL and BTPINS for updates.

Note that sequentially changing all keys might move a record "back in line", causing it to be processed more than once. For example, imagine a B-tree keeping three records in alphabetical order as [A,B,C]. A program might find the first record A, delete it from the B-tree to leave [B,C], change A's key to D and re-insert it in the B-tree to generate [B,C,D]. The program then continues stepping through the B-tree, processing record B then record C then record D again (which was already processed as record A)!

If you are changing keys in such a way that a record may move back in line for repeated processing, and you don't want to completely rebuild the B-tree, then you must either delay B-tree reinsertion of new keys until after all old keys have been deleted from the B-tree (for example, by keeping a list or scratch file of all records that have been deleted and had their keys changed and are waiting for reinsertion), or you must keep a flag in each record to indicate the record was already deleted and reinserted in the B-tree and should not be processed again if found during subsequent stepping through the tree. If you use the flag approach, be careful not to leave behind any obsolete leftover flags after all records have been processed, to avoid fooling the next execution of a program that sequentially steps through the B-tree.