HP 3000 Manuals HP 3000 Manuals
External Commands and Utilities Affected
The root file, utilities, and intrinsics affected by the implementation
of B-Tree index files include the following:
* Root file
* DBSCHEMA
* DBUTIL
* DBCONTROL
* DBFIND
* DBGET
* DBINFO
* DBPUT
* DBDELETE
* DBUPDATE
Root File
The root file version level will be "C4" if at least one master data set
has a B-Tree index. If there are no B-Tree indices, the root file
version will be "C3" if any jumbo data sets exist, and "C2" otherwise.
In other words, a value greater than "C2" indicates that at least one
POSIX named file exists as part of the database.
DBSCHEMA
DBSCHEMA has an INDEXED option added to the data set NAME specification.
New Syntax for DBSCHEMA.
NAME: setname, {M[ANUAL] } [/INDEXED]
{A[UTOMATIC]}
[(read class list)/(write class list)] [,device class];
Example
NAME: Employeename, MANUAL /INDEXED(10,20/30);
DBUTIL
DBUTIL has three new commands:
* ADDINDEX
* DROPINDEX
* REBUILDINDEX
Syntax for ADDINDEX.
{ALL }
ADDI[NDEX] database name[/maintword] FOR { setnamelist}
{setnumlist }
Syntax for DROPINDEX.
{ALL }
DROPI[NDEX] database name[/maintword] FOR { setnamelist}
{setnumlist }
Syntax for REBUILDINDEX.
{ALL }
REBUILDI[NDEX] database name [/maintword] FOR {setnamelist}
{setnumlist }
Parameters
setnamelist is the list setname[,...]
setnumlist is the list setnum[,...]
ALL means all master data sets for the database.
Examples
>>ADDINDEX ORDERS/secret for ALL
>>DROPINDEX ORDERS/secret for 1,7
>>REBUILDINDEX ORDERS/secret FOR SalesrepName,Region,District
Discussion.
The ADDINDEX command adds the associated B-Tree index file and updates
the root file.
The DROPINDEX command drops the associated B-Tree index file and updates
the root file.
REBUILDINDEX rebuilds the index file for a master data set that has an
index file.
The KSAM file has the Native Language Support language specified to match
the language of the database, if the key is an ASCII (X or U) data type.
When using the ALL option and there is no master data set, a warning is
generated, but the command is considered to be successful.
Other DBUTIL Commands.
The following lists the impact of B-Tree indices on other DBUTIL
commands:
* CREATE does an implicit ADDINDEX command for each data set marked
by DBSCHEMA as indexed.
* ERASE erases any associated B-Tree index (.idx) files, but will
not delete them.
* MOVE does NOT allow a B-Tree index (.idx) file to be moved.
* PURGE purges any associated B-Tree index (.idx) files.
* SECURE and RELEASE apply to the associated B-Tree index (.idx)
files.
* SET has a new option, BTREEMODE1.
* SHOW has a new option: INDEX, INDEXES, or INDICES.
New Syntax for SET.
SET database name [/maintword] BTREEMODE1={ON } [,[WILDCARD=]c]
{OFF}
where c is any printable ASCII character, and the default character is @.
The BTREEMODE1 option sets DBFIND mode 1 access ON or OFF for a B-Tree
index search for X and U types. The default is OFF, in which case DBFIND
mode 1 with the argument containing a wildcard character will continue to
work the way it did in releases prior to a release with the B-Tree index
feature (C.07.00). When BTREEMODE1 is ON, DBFIND mode 1 with the
argument containing a wildcard character will be treated as a B-Tree
index search. Refer to the DBFIND section for more information on
BTREEMODE1.
Examples
>>SET ORDERS/secret BTREEMODE1 = ON
>>SET ORDERS/secret BTREEMODE1 = ON,%
The DBUTIL SHOW ALL command shows if any B-Tree index files exist and the
value of BTREEMODE1. Example output:
>> show ORDERS all
For database ORDERS
...
Dynamic capacity expansion is not used.
Database has at least one indexed data set.
BTREEMODE1 is off, wildcard = "@"
Logid is not present.
...
Example if no B-Tree index files exist:
>> show ORDERS all
For database ORDERS
...
Dynamic capacity expansion is not used.
BTREEMODE1 is off, wildcard = "@"
Logid is not present.
...
New Syntax for SHOW.
SHOW has a new option INDEX, alternatively INDEXES or INDICES.
SHOW database name [/maintword] INDEX
>>SHOW ORDERS INDEX
For database ORDERS
Database root version < "C"4; there are no indexes.
>>addindex orders for all
Found 4 master datasets.
Adding index to set# 1 (#entries = 162,730, capacity = 218,987)
Adding index to set# 2 (#entries = 84,164, capacity = 188,517)
Adding index to set# 3 (#entries = 18,784, capacity = 21,943)
Adding index to set# 4 (#entries = 783, capacity = 2583)
Done.
>>
>>SHOW ORDERS INDICES
For database ORDERS
Data Set Name Type Indexed?
DATE-MASTER A YES
CUSTOMER M YES
PRODUCT M YES
SUP-MASTER M YES
4 indexed datasets
>>
DBCONTROL
DBCONTROL allows DBUTIL and privileged callers to perform several B-Tree
index file related functions such as the addition, deletion, or
rebuilding of a B-Tree index file for a specified master data set. Some
functions require exclusive database access.
There are four additional DBCONTROL modes pertaining to B-Trees:
Mode 13 B-Tree index file functions.
Mode 14 obtain/control database-wide B-Tree index information.
Mode 15 sets BTREEMODE1 option ON and optionally sets the wildcard
character for the current DBOPEN.
Mode 16 sets BTREEMODE1 option OFF for the database for the current
DBOPEN.
NOTE General use of privileged mode can seriously damage your system, if
not used wisely.
Mode 13.
Mode 13 is used to perform functions related to B-Tree index files. The
caller must be privileged. Exclusive database access is required for
adding, dropping, and rebuilding B-Tree index, that is functions 1, 2,
and 3. Qualifier has a structured record containing data set information
and directives. This is the qualifier layout (an element is 16-bits):
Element Contents
1 Function code:
0 mode 13 inquiry (returns "OK" in status indicating
mode 13 is a valid mode for the TurboIMAGE/XL version
on the system. It also means that the B-Tree index
feature is available in the version of TurboIMAGE/XL.
Ignores the rest of the record).
1 add B-Tree index file.
2 drop B-Tree index file.
3 rebuild B-Tree index file.
4 attach XM to B-Tree index.
5 for internal use only.
6 release B-Tree index file (file system security)
7 secure B-Tree index file (file system security)
2 ignored when function code is 0 in element 1
data set number (1..199) when function code is 1 through 7 in
element 1
3-4 ignored on input, zeroed at entry to DBCONTROL mode 13, and
used to return a 32-bit status at exit if at least one of the
internal B-Tree index file routines was executed.
5 flags for internal use only:
bit 15: 1 = report progress, if appropriate, when function
code is 1 through 7 in element 1
6-16 reserved; should be 0.
Mode 14.
Mode 14 is used to set BTREEMODE1 option ON or OFF for the database and
set the wildcard character for the database. The changes will be made in
the root file, unlike mode 15 and 16 of DBCONTROL. The caller must be
privileged. The result is same as using the SET command with BTREEMODE1
option of DBUTIL. This is the qualifier layout (16 halfwords):
Element Contents
1 Function code:
0 mode support inquiry (returns "OK" in status, and
ignores the rest of the record). That is, to
validate if mode 14 is supported in the TurboIMAGE
software on the system.
1-6 reserved for internal use.
7 set wildcard (in root file, not DBU). Wildcard is
in lower 8 bits of the second element of qualifier.
An error is returned if the value is less than
ASCII 33, or greater than ASCII 126.
8 set BTREEMODE1 option ON (in root file, not DBU).
9 set BTREEMODE1 option OFF (in root file, not DBU).
2 ignored for function codes 0-6, 8, or 9.
wildcard in lower 8 bits for function code 7.
3-4 ignored on input, zeroed at entry to DBCONTROL mode 14, and
used to return a 32-bit status at exit ONLY IF the B-Tree
index routines are executed. (This is useful for debugging
purposes only.)
5-16 reserved; should be 0.
Mode 15.
Mode 15 sets BTREEMODE1 ON for the current database. It examines the
first byte of the qualifier. If it is null or blank, then the current
wildcard character is not changed. If it is in the ASCII range
(33..126), then the wildcard character is changed to that value for the
current database open. If the qualifier byte is any other value,
DBCONTROL returns an error. Mode 15 affects just the current database
open, not the root file. The caller need not be privileged. The setting
remains in effect until the database is closed or the application
terminates.
Mode 16.
For mode 16, the qualifier is ignored, and BTREEMODE1 is set to OFF for
the current database open. Mode 16 affects just the current database
open, not the root file. The caller need not be privileged. The setting
remains in effect until the database is closed or the application
terminates.
DBFIND
Prior to a release with the B-Tree index feature, DBFIND required the
caller to specify detail data set on the qualifier and a search item in
the item parameter. It used that pair of data to determine the master
set to search for the exact value in the item parameter. The B-Tree
index feature extends DBFIND to allow a master data set as well as the
key item in that data set. Also, it allows for searches other than just
equality. This is useful for obtaining sorted access by key value to
entries in a master data set as well as detail data set.
Supported Modes of DBFIND.
These are modes of DBFIND supported by the B-Tree index feature:
1 do a B-Tree DBFIND if all of the following are true:
* BTREEMODE1 is on.
* The item type is X or U.
* The item has a B-Tree index (explicit or implicit).
* The argument contains wildcard character.
Otherwise execute DBFIND as done without this feature.
For B-Tree DBFINDs, the argument is scanned for the first
occurrence of the wildcard character, and a trailing-@ search is
done. Subsequent text in the key value is ignored.
For B-Tree DBFINDs pertaining to detail data sets, the
chain-count is accurate, and status halfwords 7-8 and 9-10 give
the record numbers of the last entry in the last chain, and the
first entry in the first chain of the super-chain.
For B-Tree DBFINDs pertaining to master data sets, the status
halfwords 5-6 (chain-count for detail) reflect the total number
of entries qualified in the master data set. All other fields of
the status array return zeroes.
4 do a B-Tree index search, and give accurate chain counts and
record numbers in the status array. For masters, the status
array returns the same information as in B-Tree DBFIND mode 1.
The argument is in structured format described later.
10 do a non-B-Tree DBFIND mode 1 equivalent. This gives the same
result as if there were no B-Tree index.
21 is the same as mode 1, above, but the chain count and record
numbers for last entry and first entry are not accurate. (This
is a high-speed version of mode 1.) For detail data set, the
halfwords 5-6, 7-8, and 9-10 have 231-1. For master data set,
the halfword 5-6 has 231-1 and 7-8 and 9-10 have zeroes.
Following this DBFIND, you may do DBGET mode 5 or 6.
24 is the same as mode 4, above, but the chain count and record
numbers for last entry and first entry are not accurate. (This
is a high-speed version of mode 4.) For detail data set, the
halfwords 5-6, 7-8, and 9-10 will have 231-1. For master data
set, the halfword 5-6 has 231-1 and 7-8 and 9-10 have zeroes.
The argument is in structured format described later.
TPI Modes of DBFIND Not Supported for B-Tree Indices.
Third-Party Indexing (TPI) adds more DBFIND modes. The TPI modes 1 and
10 are similar to B-Tree index modes 1 and 10.
NOTE These are the known Third Party Indexing (TPI) modes of DBFIND
which are NOT supported for B-Tree searches:
11 do a B-Tree index DBFIND when the key is binary.
12 is a keyword search.
other 1nn, 2nn, 3nn, 4nn, and 5nn are other unsupported TPI
DBFIND modes.
A B-Tree index search will never be done with a DBFIND mode 1 style
argument if the key item is binary (not X or U) (this affects DBFIND
modes 1 and 21). For a B-Tree index search on binary (non-ASCII) data,
use modes 4 or 24 in conjunction with a structured argument.
A DBFIND mode 21 on a non-ASCII item returns an error.
All TurboIMAGE data types are allowed as keys for B-Tree searches.
DBFIND Modes.
Following is a DBFIND mode summary table (see footnotes for
explanations):
Table 11-1. DBFIND Mode Summary Chart
-------------------------------------------------------------------------------------------------
| | | | |
| DBFIND | B-Tree index | Accurate | Argument |
| Mode | search? | Chain counts? | style |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 1 | MAYBE#1 | YES#2 or YES#3 | mode 1#4 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 4 | YES | YES#3 | mode 4#5 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 10 | NO | YES #2 | mode 1#4 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 21 | YES#6 | NO#7 | mode 1#4 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 24 | YES | NO#7 | mode 4#5 |
| | | | |
-------------------------------------------------------------------------------------------------
Footnotes:
#1 If BTREEMODE1 is ON, a DBFIND mode 1 on an ASCII item with a B-Tree
index (explicit or implicit), and mode 1 style argument contains a
wildcard character, it will be treated as a B-Tree index search. If
BTREEMODE1 is OFF, a DBFIND mode 1 in the above scenario will be
treated as a non-B-Tree index search (as if there were no B-Tree
index feature). DBFIND mode 1 on binary items (not X and U) will be
treated as non-B-Tree search regardless of the presence of a B-Tree
index as well as the BTREEMODE1 option. To do B-Tree searches on
binary items, use modes 4 or 24 in conjunction with a structured
argument.
#2 The chain count is the number of entries in the single chain
(non-B-Tree DBFIND). Record number for the last entry and first
entry is obtained from the chain head.
#3 The chain count is the sum of all chain counts (that is, the number
of entries in the super-chain for a B-Tree DBFIND). Record number
for the last entry is obtained from the last entry in the last
chain. For first entry, record number of the first entry in the
first chain is obtained. For masters, the chain count reflects the
total number of master entries qualified, and last entry and first
entry values are zeroes.
#4 When doing a B-Tree index search, mode-1-style argument is scanned
to find the first occurrence of the wildcard character in the
argument text. If the wildcard is not found, a non-B-Tree index
search is done. If the wildcard is found, the rest of the argument
text is ignored.
When not doing a B-Tree search (mode is 1 and BTREEMODE1 is OFF, or
mode is 10), the entire argument, including any wildcard characters,
will be treated as the actual argument is for a DBFIND mode 1 in
releases prior to the release with B-tree index feature.
#5 See "DBFIND Structured Argument" description later in this chapter.
#6 For items that are text types (X, U), a B-Tree index search is done
if the wildcard is present in the argument. If the key item is a
non-ASCII item, then a B-Tree index find is not done, and a
non-B-Tree index find (mode 1 with BTREEMODE1 is OFF) is done
instead. A programmer can do a B-Tree index find with non-ASCII
items by explicitly using DBFIND modes 4 or 24. (See "DBFIND mode
1-style and non-ASCII keys" below.) This mode is similar to current
TPI mode 21.
#7 For detail data set, the halfwords 5-6, 7-8, and 9-10 have 231-1.
For master data set, the halfword 5-6 has 231-1 and 7-8 and 9-10
have zeroes.
NOTE The length of the argument may not exceed the item length.
DBFIND Arguments.
There are two distinct argument styles. The simple argument of the basic
DBFIND mode 1 argument is generally interpreted as a sequence of bytes,
perhaps containing a wildcard character.
Another argument style, the structured argument, has been defined for
DBFIND modes 4 and 24. This structure allows ranges of item values to be
requested, and is explained below.
DBFIND Structured Argument.
The structure of the structured argument for DBFIND modes 4 and 24:
Bytes Meaning
1-2 Type of generic search. An ASCII character pair is in this
field:
=_ search for key values equal to argument1
<_ search for key values less than argument1
<= search for key values less than or equal to argument1
>_ search for key values greater than argument1
>= search for key values greater than or equal to
argument1
[ ] search for key values greater than or equal to
argument1 AND less than or equal to argument2
@c wildcard search. Scan argument for the first wildcard
character. (Call that character position n, 1-based).
Search for keys that match first n-1 characters of
argument.
If c is non-blank and non-null, then it is the
wildcard character that will be used. Some examples
are: @* and @@.
If c is a blank or null, then the current default
wildcard (stored in the root file) is used. The
wildcard character is changeable via the DBUTIL SET
command or DBCONTROL mode 15.
PK Partial Key search. Search for key values that match
n characters in argument1 (n is length of argument1
provided in bytes 5-6). Argument1 need not contain a
wildcard. If it does within the n characters, it will
be included in the search. For example, if argument1
is ABC@, bytes 5-6 have 4 for length, and the wildcard
for the database is @, DBFIND will return records
containing ABC@ as the first four characters in the
key value.
3-4 version number. It must be numeric zero, or an error will be
returned.
5-6 The size (in bytes) of argument1 (not including these two
bytes) for search types <_, <=, =_, >=, >_, @c, PK.
7-8 The size (in bytes) of argument2 (not including these two
bytes) for the between search type [ ].
9... Argument1. The n bytes of argument data (for example, for an
9+n-1 X10 field, n = 10).
9+n... Argument2. For search-type [ ] only. The m bytes of the
9+n+m-1 second argument's data (for example, for an X10 field, m = 10,
n must match m). Must be numeric zero for other search types,
or an error is returned.
If a wildcard character is present in the argument(s), the wildcard will
be considered as part of the value for these B-tree search types: =_,
<_, <=, >_, >=, [ ], and PK on ASCII types.
Pascal/iX Example
A Pascal/iX view of the above is:
type
dbfind_structured_arg_type = $alignment 2$ record
dbf_type : pac2; {e.g., "<="} {0 @ 2}
dbf_version : shortint; {must be 0} {2 @ 2}
dbf_arg1_bytes : shortint; {4 @ 2}
dbf_arg2_bytes : shortint; {6 @ 2}
{NOTE: arg1 data is variable sized...2 to 256 bytes}
{ and, arg2 data might not even be present. }
{ Still, the following serve to define a record}
{ that can hold the worst case arg1 & arg2... }
dbf_arg1_data : packed array [1..256] of {8 @ x}
char;
{*REAL* dbf_arg1_data is variable sized}
{*REAL* dbf_arg2_data is variable sized}
spare_area : packed array [1..256] of {8 @ x}
char;
{if present, arg2 data starts at record + 8 + arg1_bytes}
end; {8 + 256 + 256}
Examples of Structured Argument
Three examples of using the structured argument type are given below.
Example 1
X4 field, looking for <= "JOHN", using SPL:
move arg' := ("<=", 0, 4, 0, "JOHN");
Example 2
X4 field, looking for keys >= JOHN, <= STAN, using Pascal:
type
structured_argument_type = crunched record
search_type : pac2; {bytes 0, 1}
version : shortint; {bytes 2, 3}
arg1_bytes : shortint; {bytes 4, 5}
arg2_bytes : shortint; {bytes 6, 7}
arg1 : pac4;
arg2 : pac4;
end;
var
arg : structured_argument_type;
fast_fill (addr (arg), 0, sizeof (arg)); {optional}
arg.search_type := '[]'; {in-range search}
arg.arg1_bytes := sizeof (arg.arg1);
arg.arg1 := 'JOHN';
arg.arg2_bytes := sizeof (arg.arg2);
arg.arg2 := 'STAN';
mode4 := 4;
item := 'FIRSTNAME;';
dbfind (base, set, mode4, status, item, arg);
Example 3
X20 field, looking for keys that start with "SMITH", using C:
char
arg [29]; /* 2 + 2 + 2 + 2 + 20 + 1 trailing null */
arg [0] = '@'; /* want a wildcard search */
arg [1] = '@'; /* use an @ as a wildcard character */
arg [2] = (char) 0; /* upper half of version */
arg [3] = (char) 0; /* bottom half of version */
arg [4] = (char) 0; /* upper half of arg1 size field */
arg [5] = (char) 20; /* bottom half of arg1 size field */
arg [6] = (char) 0; /* upper half of arg2 size field */
arg [7] = (char) 0; /* bottom half of arg2 size field */
strcpy (arg [8], "SMITH@");
Example 4
I2 field, looking for keys >= 123 and <= 45698, using SPL:
double array
arg'd (0 : 3); ! 4 byte prefix + three I2 fields
integer array
arg'i (*) = arg'd;
integer
mode4 := 4;
byte array
arg' (*) = arg'd;
! find super-chain for SALARY in range [123..45698]...
arg'i := "[]"; ! range search
arg'i (1) := 0; ! version
arg'i (2) := 4; ! size of arg1 (in bytes)
arg'i (3) := 4; ! size of arg2 (in bytes)
arg'd (2) := 123d; ! arg1
arg'd (3) := 45698d; ! arg2
move item := "SALARY;";
dbfind (base, set, mode4, status, item, arg'i);
DBGET
DBGET has no changes to the calling sequence. It has the semantic change
that chained access modes can be used for both masters and details. For
master data sets, DBGET mode 5 or 6 following a B-Tree DBFIND implies the
next or previous qualified record of the master data set. Also, DBGET
mode 5 (forward chained read) and mode 6 (backward chained read) may now
traverse super-chains for a B-Tree index DBFIND on a detail data set.
NOTE After a super-chain (B-Tree index DBFIND) read has started, a
directed DBGET (mode 4), may perturb the super-chain.
DBINFO
DBINFO has new modes 113 and 209.
Mode 113.
DBINFO mode 113 reports the wildcard character in use for the database,
and the value of BTREEMODE1. The qualifier is ignored.
The output buffer for mode 113 must be at least 32 bytes in size and is
shown below (elements are 16-bits wide and are counted starting with 1):
Element Contents
1 0 if BTREEMODE1 is off in the root file
1 if BTREEMODE1 is on in the root file
2 The first byte (8 bits) is always 0. The second byte (8 bits)
represents c, where c is the current wildcard character. For
example, if the current wildcard character is @, the element's
hex value will be $0040, or decimal 64.
3 Highest B-Tree index argument version supported (currently 0)
4 Number of sets with B-Tree indices attached
5 0 if BTREEMODE1 is OFF for current DBOPEN
1 if BTREEMODE1 is ON for current DBOPEN
6 The first byte (8 bits) is always 0 for current DBOPEN. The
second byte (8 bits) represents c, where c is the current
wildcard character.
7-16 (reserved)
Mode 209.
DBINFO mode 209 reports whether or not a data set has a B-Tree index
attached. The qualifier is a data set number or name. The output buffer
is required to be 64 bytes (32 halfwords) or larger. The output buffer
for mode 209 is shown below (elements are 16-bits wide, and are counted
starting with 1):
Element Contents
1 0 if no B-Tree index exists
1 if B-Tree index exists
2 1 if attached B-Tree is not damaged or index does not exist
3-32 For internal use
DBPUT, DBDELETE, and DBUPDATE
DBPUT and DBDELETE trigger similar operation to the associated B-Tree
index file for all adds or deletes to or from a master data set.
DBUPDATE to a master is not allowed even when critical item update is on.
Adding a record to the B-Tree index file is done with an FWRITE call.
The KSAM files do not have the DUP option, so KSAM will reject FWRITEs
that attempt to add a duplicate key value. The KSAM files are built with
the REUSE option, which means that the space occupied by deleted records
will eventually be reused.