HP 3000 Manuals HP 3000 Manuals
Overview of B-Tree Indices
An index on an item allows generic and range searches. To optimize the
search of an index, a technique commonly know as "Binary Tree Searching"
is used. Hence, the name B-Tree index is used to refer to an index in
TurboIMAGE/XL.
You can create a B-Tree index only on the master data set's key item.
Nevertheless, you are able to perform index searches using all of its
corresponding detail data set search items as well. A master data set
key item is perceived as having an explicit B-Tree index and all of its
corresponding detail data set search items are perceived as having
implicit B-Tree indices. The index searches are done using DBFIND. If
you create an index on a key item of the master data set, you can use
this master data set in the DBFIND intrinsic.
Terminology
Explicit B-Tree is an index actually created on the key item of the
index master data set. The index searches can be done
using this key item in the DBFIND intrinsic.
Implicit B-Tree is an implied index, which does not physically exist,
index but index search is allowed on the detail data set
search item whose corresponding master data set key
item has a B-Tree index.
B-Tree DBFIND is a DBFIND on a master or detail data set using a
key item or a search item that has a B-Tree index
(explicit or implicit). For a master set, it is a
set of entries in the master which satisfy the DBFIND
criteria. For a detail data set, DBFIND is also
referred to as a super-find which locates a set of
master data set entries, all of which have keys that
satisfy the DBFIND criteria. The total of all
corresponding chains in a detail data set is referred
to as a super-chain. TurboIMAGE/XL fetches each
qualifying master entry in order to determine the
total number of associated detail entries to
calculate the sum of the chain counts. To retrieve
all qualified entries in a detail data set using
DBGET, TurboIMAGE "walks" (traverses) this
super-chain.
B-Tree Search is the same as B-Tree DBFIND.
Super-chain is the sum of all detail data set chains involved in
the B-Tree DBFIND.
Trailing-@ Search is a B-Tree search where only the characters left of
the @ (wildcard character) of the argument are
compared to qualify an entry. (For example, "cat@"
is a trailing-@ search, but "cat@dog" is not and the
result will be the same for both arguments. That is,
"dog" will be ignored.)
Wildcard Character refers to a printable ASCII character. The default
is @ which means "matches all trailing characters"
(unlike "?" or "#" in MPE terms).
BTREEMODE1 is an option which can be turned ON or OFF using the
SET command of DBUTIL or programmatically using
DBCONTROL. When it is ON, DBFIND mode 1 of an item of
X or U type having a B-Tree index (explicit or
implicit), and having a wildcard character in the
argument will result in a B-Tree search. The ON
option allows applications to benefit from B-Tree
indices without modifying applications. When it is
OFF, which is also the default, DBFIND mode 1
described above works as it did in releases prior to
a release with the B-Tree index feature (C.07.00).
Simple Argument is a DBFIND argument that consists of a sequence of
bytes, as in DBFIND mode 1 argument in releases prior
to a release with the B-Tree index feature (C.07.00).
This is used predominantly for modes 1, 10, and 21.
For DBFIND modes 1 and 21, an X or U type item having
B-Tree index (explicit or implicit) and having the
BTREEMODE1 option ON, the text is scanned for a
wildcard character, if any. If a wildcard is found,
DBFIND is treated as a B-Tree DBFIND. Otherwise, it
is treated as DBFIND in releases prior to a release
with the B-Tree index feature (C.07.00).
Structured is a DBFIND fixed format argument construct
Argument introduced with B-Trees enhancement. It is defined
as a record structure, containing search control
information as well as key data. This is used for
DBFIND modes 4 and 24, and is described later in this
chapter.
Key Points
These are the key points of TurboIMAGE/XL B-Tree indices:
* You can create a B-Tree index only on the key item of the master
data set.
* Although you can create a B-Tree index only for the key item of a
master data set, you can still perform a B-Tree search using the
search items of all of its corresponding detail sets as well. A
master data set is perceived as having an explicit B-Tree index
and each of its corresponding detail data sets is perceived as
having an implicit B-Tree index.
* You may create B-Tree indices for zero, one, or more data sets.
* DBSCHEMA has a new option, INDEXED, for the SET specification.
* DBUTIL has new commands and options.
a. New ADDINDEX, DROPINDEX, and REBUILDINDEX commands (to be
used for one, more than one, or all masters)
b. The SET command has a new BTREEMODE1 option to set DBFIND
mode 1 access ON or OFF for a B-Tree wildcard search for X
and U types. The default is OFF. The ON setting allows you
to use B-Tree indices for generic search without making
application changes.
c. The SET command allows you to define your own database-wide
wildcard character.
d. CREATE, ERASE, PURGE, SECURE, RELEASE, and SHOW commands
include B-Tree index files in their operation.
e. The MOVE command does not allow moving the index file.
* DBCONTROL has these modes pertaining to B-Tree indices:
13 is for B-Tree index file control. That is, to ADD,
DROP, REBUILD, ATTACH, or DETACH a B-Tree index file.
14 is used to set database-wide BTREEMODE1 and wildcard
character.
15 sets BTREEMODE1 ON for the current DBOPEN (base
parameter), and optionally allows the wildcard
character to be set for the current DBOPEN.
16 sets BTREEMODE1 OFF for the current DBOPEN (base
parameter).
* DBINFO has these modes pertaining to B-Tree indices:
209 informs whether or not a B-Tree index exists for a
master.
113 gives BTREEMODE1 setting and the wildcard character for
the database as well as current DBOPEN.
* DBPUT or DBDELETE to an indexed master triggers a similar
operation to indexed master's B-Tree index file. (DBUPDATE to a
master data set to modify a key item, despite CIPUDATE setting, is
not allowed.)
* DBFIND has these features pertaining to B-Tree indices:
a. Can be used for details as well as masters to specify
B-Tree index searches.
b. DBFIND modes with added functionality pertaining to B-Tree
indices:
1 can continue to work as it does in releases
prior to the release of B-Tree index feature
(C.07.00), despite the presence of a B-Tree
index, or it can be used for a B-Tree search if
BTREEMODE1 is ON.
4 used for B-Tree index searches on numeric as
well as ASCII types and returns accurate chain
(super-chain) counts. Requires a structured
argument.
10 allows you to simulate the DBFIND mode 1 in
releases prior to the release with B-tree index
feature even when the item has a B-Tree index
(explicit or implicit), BTREEMODE1 is on, and
the argument contains a wildcard character. It
returns accurate chain count. This is the same
as TPI mode 10. Requires a simple argument.
21 is the same as B-Tree index search using DBFIND
mode 1, except it is a faster version and does
not return accurate chain counts. Requires a
simple argument.
24 is the same as B-Tree index search using DBFIND
mode 4, except it is a faster version and does
not return accurate chain counts. Requires a
structured argument.
c. Allows wildcard search, as well as range search (<_, <=,
>_, >=,"PK", or [ ] for between).
d. If BTREEMODE1 is ON, DBFIND mode 1 of X or U type item
having a B-Tree index (explicit or implicit) and the
argument containing a wildcard character will result in a
B-Tree index search.
e. For wildcard (generic) search, the wildcard should be the
terminating character in the argument. Characters beyond
that will be ignored.
f. Status array reflects information based on data set, mode,
and search type. B-Tree index searches for modes 1 and 4
for details give super-chain (multiple detail chain) counts
and record numbers of first entry in first detail chain and
last entry in last detail chain.
* DBGET modes 5 and 6 can be used for B-Tree index retrieval for
masters or details. Super-chains are traversed for detail data
sets.
* KSAM/iX files are used for B-Tree index files. These are KSAM
related key points:
a. There is one KSAM/iX privileged file with a filecode of
-412 for each B-Tree index file. The size limit for this
B-Tree index file is 4 Gbytes. A jumbo master (larger than
4 Gbytes) can have a B-Tree index file provided the B-Tree
index file remains within its 4 Gbyte limit.
b. KSAM/iX B-Tree index file is named using the POSIX file
format with the "idx" extension (lowercase). For example:
/ACTSALES/GRPSALES/ORDERS03.idx
c. KSAM/iX B-Tree index file records consist only of a single
key, which is a duplicate of the master data set key value.
No pointer information is present in the B-Tree index
records.
d. The KSAM file has the Native Language Support language
specified to match the language of the database, if the key
is a text (X or U) data type.
* Record zero of the root file contains "C4" for the root file
version if at least one B-Tree index file exists for the database.
When all B-Tree indices are dropped, it reverts to the appropriate
version: "C3" if at least one jumbo set exists, "C2" otherwise.
* A new bit map is added in the root file for B-Tree indices.
* Third-party indices can coexist with B-Tree indices, that is, on
the same item of the data set.