 |
» |
|
|
|
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 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.IMAGEKEYThe 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.TPINDEXThe 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.INDEXThe 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. |
