HP 3000 Manuals HP 3000 Manuals
Views for TurboIMAGE/XL
There are four SQL views added for TurboIMAGE/XL per DBEnvironment for
the G.1 version. These are the SQL views for TurboIMAGE/XL hash indices
on key items and search items and for TPIs:
* SYSTEM.IMAGEKEY
* CATALOG.IMAGEKEY
* SYSTEM.TPINDEX
* CATALOG.TPINDEX
SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY
The DBA can view all TurboIMAGE/XL hash indices associated with a
database by examining SYSTEM.IMAGEKEY. Other users can view the
TurboIMAGE/XL hash indices to which they have access by examining
CATALOG.IMAGEKEY.
For example, the DBA can issue this command:
_________________________________________________________________
| |
| isql => select * from system.imagekey where owner='music';|
| |
| |
| ----------------------------------------------------- |
| INDEXNAME | TABLENAME | OWNER | UNIQUE | ... |
| ----------------------------------------------------- |
| ALBUMCODE_M1 | ALBUMS | MUSIC | 1 | ... |
| COMPOSERNAME_M1 | COMPOSERS | MUSIC | 1 | ... |
| SELECTIONNAME_A1 | SELECTIONS_A| MUSIC | 1 | ... |
| ALBUMCODE_D1 | SELECTIONS | MUSIC | 0 | ... |
| SELECTIONNAME_D2 | SELECTIONS | MUSIC | 0 | ... |
| COMPOSERNAME_D3 | SELECTIONS | MUSIC | 0 | ... |
| ALBUMCODE_D1 | LOG | MUSIC | 0 | ... |
| SELECTIONNAME_D2 | LOG | MUSIC | 0 | ... |
| |
_________________________________________________________________
Columns in SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY.
The following columns exist in both SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY
(PRIMARIES, SCCCOUNT, and DCCCOUNT are not calculated or used in G.2):
INDEXNAME Name of the TurboIMAGE/XL item, plus a suffix.
The following suffixes are used by IMAGESQL:
_M1 is used when entering definition for hash index
on manual master key item.
_A1 is used when entering definition for hash index
on automatic master key item.
_Dn is used when entering definitions for hash
indices on detail data set search items, where n is
the path number from 1 to 16.
TABLENAME Name of the TurboIMAGE/XL data set on which the key
is defined.
OWNER Name of the TurboIMAGE/XL database (or the owner
name used during the ATTACH) on which the key is
defined.
UNIQUE Uniqueness indicator:
0 if duplicates are allowed, that is, the index is
not unique.
1 if duplicates are not allowed, that is, the key
is unique.
Hash index on the key item of a master data set
(both automatic and manual) is always unique,
except when defined on P and Z (decimal) data
types. Hash indices on search items of detail data
sets are always non-unique.
NUMC Number of columns in the index. NUMC is always 1.
COLNUMS A vector of 16 SYSTEM.COLUMN entries, which
identifies the column numbers that make up the key.
In ISQL, each column number is displayed as a field
of 4 hexadecimal digits.
NDISTINCT Number of distinct key values.
PRIMARIES Number of primary slots used.
SCCCOUNT Synonym Chain Cluster Count, which is a measure of
how well the data is clustered on pages as the
synonym chain (also known as the secondary chain)
is traversed.
DCCCOUNT Detail Chain Cluster Count, which is a measure of
how well the data is clustered on pages as the
detail chain is traversed.
SYSTEM.TPINDEX and CATALOG.TPINDEX
The DBA can view all TPIs associated with a database by examining
SYSTEM.TPINDEX. Other users can view the TPIs to which they have access
by examining CATALOG.TPINDEX. For example, the DBA can issue:
________________________________________________________________
| |
| isql => select * from system.tpindex where owner='music';|
| |
| |
| ------------------------------------------------ |
| INDEXNAME | TABLENAME | OWNER | UNIQUE | ... |
| ------------------------------------------------ |
| ACODE_T1 | ALBUMS | MUSIC | 0 | ... |
| ALBUMC_T2 | SELECTIONS | MUSIC | 0 | ... |
| SELECTNAME_T3| SELECTIONS | MUSIC | 0 | ... |
| COMPOSER_T4 | COMPOSERS | MUSIC | 0 | ... |
| |
________________________________________________________________
Columns in SYSTEM.TPINDEX and CATALOG.TPINDEX.
The following columns exist in both SYSTEM.TPINDEX and CATALOG.TPINDEX
(NPAGES, NLEVELS, NLEAVES, NDISTINCT, NFIRST, NPERKEY, and CCOUNT are not
calculated or used in G.2):
INDEXNAME Name of the TPI. The following suffix is used by
IMAGESQL when registering TPIs:
_Tn (Where n can be 1 to 400 depending on the TPIs
that exist on the database.)
TABLENAME Name of the data set on which the TPI is defined.
OWNER Name of the TurboIMAGE/XL database (or the owner
name used during the ATTACH) on which the TPI is
defined.
UNIQUE Uniqueness indicator:
0 if duplicates are allowed, that is, the index is
not unique.
1 if duplicates are not allowed, that is, the index
is unique.
CLUSTER Clustering indicator:
0 if the index is not a clustering index.
1 if the index is a clustering index.
TPIs are always registered as non-clustering.
NUMC Number of columns in the index.
COLNUMS A vector of 16 SYSTEM.COLUMN entries, which
identifies the column numbers that make up the
index. In ISQL, each column number is displayed as
a field of 4 hexadecimal digits.
NPAGES Number of pages containing the index.
NLEVELS Number of levels in the TPI.
NLEAVES Number of leaf pages in the TPI.
NDISTINCT Number of distinct key values.
NFIRST Number of distinct first key values.
NPERKEY Number of pages per key.
CCOUNT Cluster count, which indicates how well the data of
the index are sorted:
0 before first UPDATE STATISTICS statement is
processed.
n (efficiency of clustering) best clustering if
n=NPAGES of table indexed; worst if n=NROWS of
table indexed.
CTIME Time of creation: yyyymmddhhsstt.
COLDIRS A vector of 16 direction entries, which indicates
the direction of the corresponding column in the
index definition. In ISQL, each column number is
displayed as a field of 4 hexadecimal digits.
5 Ascending.
6 Descending.
SYSTEM.INDEX and CATALOG.INDEX
When you attach a database for which you have created one or more B-Tree
indices on the key items of the master data sets, the ATTACH command
enters definitions for the B-Tree indices on the key items and their
related search items of the detail data sets. See the chapter, "B-Tree
Indices," in the TurboIMAGE/XL Database Management System Reference
Manual for more information on how to create B-Tree indices.
The definitions of B-Tree indices on key items, except P and Z types, of
the master data sets are entered as UNIQUE indices. For the related
search items of ALL of the related detail data sets, the definitions are
entered as NON-UNIQUE indices.
View your new B-Tree indices in the existing views, SYSTEM.INDEX and
CATALOG.INDEX. For example:
__________________________________________________________________________________
| |
| isql=> SELECT * FROM SYSTEM.INDEX WHERE OWNER = 'BTREE3'; |
| Output will be truncated. (DBWARN 1) |
| |
| |
| select * from system.index where owner = 'BTREE3'; |
| --------------------+--------------------+--------------------+------+-----|
| INDEXNAME |TABLENAME |OWNER |UNIQUE|CLUST|
| --------------------+--------------------+--------------------+------+-----|
| TESTNAMEP16_B1 |PACKED |BTREE3 | 0| |
| TESTNAMELENR2_B1 |REAL |BTREE3 | 1| |
| TESTNAMELENR2_V1 |RDET1 |BTREE3 | 0| |
| TESTNAMELENR2_V1 |RDET2 |BTREE3 | 0| |
| TESTNAMELENR2_V1 |RDET3 |BTREE3 | 0| |
| TESTNAMEP16_V1 |PRZDET |BTREE3 | 0| |
| TESTNAMELENR2_V2 |PRZDET |BTREE3 | 0| |
__________________________________________________________________________________
From the above example, you can see that there are two master data sets,
PACKED and REAL, with B-Tree indices created on their key items. The
remaining are related detail data sets.
Columns in SYSTEM.INDEX and CATALOG.INDEX.
The following columns are in both SYSTEM.INDEX and CATALOG.INDEX:
INDEXNAME Name of the TurboIMAGE/XL item, plus a suffix.
The following suffixes are used by IMAGE/SQL:
_B1 is used when entering the
definition for B-Tree index
on the master key item.
_Vn is used when entering the
definition for B-Tree index
on the related search item of
detail data set, where n is
the path number from 1 to 16.
All other column names are the same as for SYSTEM.TPINDEX and
CATALOG.TPINDEX. Refer to "Columns in SYSTEM.TPINDEX and
CATALOG.TPINDEX," earlier in this appendix.