HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.DBEFileSet.
SYSTEM.DBEFILESET contains information about DBEFileSets. This table
initially contains a row for each DBEFileSet created when the
DBEnvironment is configured. A row is added, updated, or deleted
whenever ALLBASE/SQL processes an ADD DBEFILE, CREATE DBEFILESET, REMOVE
DBEFILE, or DROP DBEFILESET statement.
SYSTEM.DBEFILESET and SYSTEM.DBEFILE contain all the information about
where tables and indexes are stored.
Table 8-13. System.DBEFileSet
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSNAME | CHAR | 20 | Name of the DBEFileSet |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSNDBEFILES | INTEGER | 4 | Number of DBEFiles in the DBEFileSet |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSNPAGES | INTEGER | 4 | Number of pages in the DBEFileSet (the sum |
| | | | of the pages in all associated DBEFiles) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSUPAGES | INTEGER | 4 | Number of pages in the DBEFileSet |
| | | | containing table and index data, excluding |
| | | | page table pages (the sum of the used pages |
| | | | in all associated DBEFiles) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSPTPAGES | INTEGER | 4 | Number of page table pages in the |
| | | | DBEFileSet |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSMPAGES | INTEGER | 4 | Total maximum number of pages the |
| | | | DBEFileSet may be expanded (the sum of all |
| | | | DBEFMPAGES in System.DBEFile for the |
| | | | associated DBEFile) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| ATTACHED | CHAR | 2 | Attached: Y for yes, N for no |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.DBEFileSet ;
--------------------+--------------+-----------+-----------
DBEFSNAME |DBEFSNDBEFILES|DBEFSNPAGES|DBEFSUPAGES
--------------------+--------------+-----------+-----------
SYSTEM | 2| 100| 8
PURCHFS | 2| 100| 6
WAREHFS | 2| 100| 4
ORDERFS | 2| 100| 5
---------------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+------------+-----------+----------------+--------
|DBEFSPTPAGES|DBEFSMPAGES|CTIME |ATTACHED
+------------+-----------+----------------+--------
| 4| 100|1990090614175200|Y
| 2| 100|1990090614192370|Y
| 2| 100|1990090614194160|Y
| 2| 100|1990090614200170|Y
---------------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.Group.
SYSTEM.GROUP contains a row for each user or group that belongs to an
authorization group. Each user, whether an individual user or another
authorization group, has an entry for each authorization group to which
he or she is a member. This view is initially empty, but is updated
whenever ALLBASE/SQL processes a CREATE GROUP, ADD TO GROUP, TRANSFER
OWNERSHIP (of a group), REMOVE FROM GROUP, or DROP GROUP statement.
SYSTEM.GROUP can be used with SYSTEM.COLAUTH, SYSTEM.MODAUTH,
SYSTEM.SPECAUTH, and SYSTEM.TABAUTH to determine authorities granted to
authorization groups.
You can use this table to determine all the members of a particular
authorization group or how many authorization groups to which a
particular user belongs. For example, if you want to know all the users
in the Receiving authorization group, enter:
isql=> SELECT UserId FROM System.Group
> WHERE GroupId='RECEIVING';
Table 8-14. System.Group
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | DBEUserID or authorization group that is a |
| | | | member of the GROUPID |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| GROUPID | CHAR | 20 | Authorization group name |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NMEMBERS | INTEGER | 4 | Number of members in the authorization |
| | | | group |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Group;
--------------------+--------------------+--------------------+-----------
USERID |GROUPID |OWNER |NMEMBERS
--------------------+--------------------+--------------------+-----------
PURCHMANAGERS |PURCHMANAGERS |JOHN@BROCK | 3
MARGY@RYAN |PURCHMANAGERS |JOHN@BROCK | 0
RON@HART |PURCHMANAGERS |JOHN@BROCK | 0
SHARON@MULDOON |PURCHMANAGERS |JOHN@BROCK | 0
PURCHDBMAINT |PURCHDBMAINT |JOHN@BROCK | 4
ANNIE@MELCHOIR |PURCHDBMAINT |JOHN@BROCK | 0
DOUG@DOLAN |PURCHDBMAINT |JOHN@BROCK | 0
DAVID@BLOOM |PURCHDBMAINT |JOHN@BROCK | 0
PURCHASING |PURCHASING |JOHN@BROCK | 5
AJ@SERED |PURCHASING |JOHN@BROCK | 0
JORGE@GUERRERO |PURCHASING |JOHN@BROCK | 0
RAGAA@QUILLEN |PURCHASING |JOHN@BROCK | 0
GREG@SAWYER |PURCHASING |JOHN@BROCK | 0
KAREN@RIZZO |PURCHASING |JOHN@BROCK | 0
RECEIVING |RECEIVING |JOHN@BROCK | 4
AL@DAL |RECEIVING |JOHN@BROCK | 0
---------------------------------------------------------------------------
First 16 rows have been selected
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.Hash.
SYSTEM.HASH contains an entry for each hash structure you create. This
view is initially empty, but is updated whenever ALLBASE/SQL processes a
hash-related CREATE TABLE, DROP TABLE, TRANSFER OWNERSHIP, or UPDATE
STATISTICS statement.
Table 8-15. System.Hash
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table on which the hash is |
| | | | defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table on which the hash |
| | | | structure is defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns in the hash key |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNUMS | BINARY | 32 | A vector of 16 SYSTEM.COLUMN entries, each |
| | | | of type SMALLINT, identifying the column |
| | | | numbers the hash is defined over. In ISQL, |
| | | | each SMALLINT (two-byte) entry is displayed |
| | | | as a field of 4 hexadecimal digits. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PRIMPAGES | INTEGER | 4 | Number of primary pages allocated for the |
| | | | hash structure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPPAGES | INTEGER | 4 | Number of primary pages in use, excluding |
| | | | page table pages |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| AVGLEN | FLOAT | 8 | Average chain length |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| MAXLEN | INTEGER | 4 | Maximum chain length |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NFULL | INTEGER | 4 | Number of primary pages that are more than |
| | | | half full |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NOVERFLOW | INTEGER | 4 | Number of primary pages with overflow pages |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| UNIQUE | SMALLINT | 2 | Uniqueness indicator; currently always 1 |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Hash;
+--------------------+--------------------+-----------
|TABLENAME |OWNER |NUMC
+--------------------+--------------------+-----------
|VENDORS |PURCHDB | 1
-------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+----------------------------------------------------------------
|COLNUMS
+----------------------------------------------------------------
|0001000000000000000000000000000000000000000000000000000000000000
+----------------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+-----------+-----------+----------------+---------
|PRIMPAGES |NPPAGES |AVGLEN |MAXLEN
+-----------+-----------+----------------+---------
| 10| 18| 1.00| 1
---------------------------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+-----------+-----------+------
|NFULL NOVERFLOW UNIQUE
+-----------+-----------+------
| 0| 0| 1
------------------------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.Imagekey. [REV BEG]
SYSTEM.IMAGEKEY contains an entry for each IMAGE key on each dataset in
each IMAGE database attached to this DBE. In ALLBASE/SQL terms the IMAGE
keys are viewed as indexes, and the datasets on which they are defined
are viewed as tables. The IMAGE database in which these datasets reside
is listed as the owner of these IMAGE tables.
Table 8-16. System.Imagekey
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INDEXNAME | CHAR | 20 | Index name give to the IMAGE key |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table (dataset) on which the |
| | | | IMAGE key is defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table (dataset) on which the |
| | | | IMAGE key is defined. This is always the |
| | | | name of the IMAGE database where the |
| | | | dataset is stored. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| UNIQUE | SMALLINT | 2 | 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 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns in the IMAGE key (always |
| | | | 1 for IMAGE keys) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNUMS | BINARY | 64 | A vector of 16 SYSTEM.COLUMN entries, each |
| | | | of type SMALLINT, identifying the column |
| | | | numbers the IMAGE key is defined over |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NDISTINCT | INTEGER | 4 | Number of distinct values |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PRIMARIES | INTEGER | 4 | Number of primary slots used. (not |
| | | | currently used) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SCCCOUNT | INTEGER | 4 | Synonym chain cluster count; indicates the |
| | | | number of page changes necessary to read |
| | | | through the entire synonym chain. (not |
| | | | currently used) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DCCCOUNT | INTEGER | 4 | Detail chain cluster count; indicates the |
| | | | average number of page changes necessary to |
| | | | read through a detail chain. (not |
| | | | currently used) |
| | | | |
-------------------------------------------------------------------------------------------------
[REV END]
Example. [REV BEG]
SELECT * FROM System.Imagekey;
--------------------+--------------------+--------------------+------+-----
INDEXNAME |TABLENAME |OWNER |UNIQUE|NUMC
--------------------+--------------------+--------------------+------+-----
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|
---------------------------------------------------------------------------
Number of rows selected is 8
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
-+--------------------+------+-----------+---------------------------------
|OWNER |UNIQUE|NUMC |COLNUMS
-+--------------------+------+-----------+---------------------------------
|MUSIC | 1| 1|000100000000000000000000000000000
|MUSIC | 1| 1|000100000000000000000000000000000
|MUSIC | 1| 1|000100000000000000000000000000000
|MUSIC | 0| 1|000100000000000000000000000000000
|MUSIC | 0| 1|000200000000000000000000000000000
|MUSIC | 0| 1|000300000000000000000000000000000
|MUSIC | 0| 1|000100000000000000000000000000000
|MUSIC | 0| 1|000200000000000000000000000000000
---------------------------------------------------------------------------
Number of rows selected is 8
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
-+----------------------------------------------------------------+--------
|COLNUMS |NDISTINC
-+----------------------------------------------------------------+--------
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0002000000000000000000000000000000000000000000000000000000000000|
1|0003000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0002000000000000000000000000000000000000000000000000000000000000|
---------------------------------------------------------------------------
Number of rows selected is 8
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
--------------------------+-----------+-----------+-----------+-----------
|NDISTINCT |PRIMARIES |SCCCOUNT |DCCCOUNT
--------------------------+-----------+-----------+-----------+-----------
00000000000000000000000000| 3| 0| 0| 0
00000000000000000000000000| 7| 0| 0| 0
00000000000000000000000000| 9| 0| 0| 0
00000000000000000000000000| 3| 0| 0| 0
00000000000000000000000000| 9| 0| 0| 0
00000000000000000000000000| 7| 0| 0| 0
00000000000000000000000000| 3| 0| 0| 0
00000000000000000000000000| 9| 0| 0| 0
---------------------------------------------------------------------------
Number of rows selected is 8
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
[REV END]
System.Index. [REV BEG]
SYSTEM.INDEX contains an entry for each index created by a user on a
table.[REV END] This view is initially empty, but is updated whenever
ALLBASE/SQL processes a CREATE INDEX, DROP INDEX, DROP TABLE, TRANSFER
OWNERSHIP (of a table), or UPDATE STATISTICS statement.
Table 8-17. System.Index
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INDEXNAME | CHAR | 20 | Name of the index |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table on which the index or |
| | | | hash structure is defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table on which the index is |
| | | | defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| UNIQUE | SMALLINT | 2 | 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 | SMALLINT | 2 | Clustering indicator: |
| | | | |
| | | | 0 if the index is not a clustering index |
| | | | 1 if the index is the clustering index |
| | | | for the table |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns in the index or hash key |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNUMS | BINARY | 64 | A vector of column numbers, each of type |
| | | | SMALLINT, identifying the columns the index |
| | | | is defined over. In ISQL, each SMALLINIT |
| | | | (two-byte) entry is displayed as a field of |
| | | | 4 hexadecimal digits.[REV BEG] |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPAGES | INTEGER | 4 | Number of index pages containing the index |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NLEVELS | INTEGER | 4 | Number of B-tree levels |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NLEAVES | INTEGER | 4 | Number of B-tree leaf pages |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NDISTINCT | INTEGER | 4 | Number of distinct keys |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NFIRST | INTEGER | 4 | Number of distinct first column values of |
| | | | the B-tree key |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPERKEY | INTEGER | 4 | Number of pages per B-tree key[REV END] |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-17. System.Index (cont.)
----------------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
----------------------------------------------------------------------------------------------------------
| [REV BEG] | | | |
| | | | |
| CCOUNT | INTEGER | 4 | Cluster count; indicates how well the data |
| | | | of the index is sorted |
| | | | |
| | | | 0 before first UPDATE STATISTICS |
| | | | statement is processed |
| | | | n efficiency of clustering: best |
| | | | clustering if n=NPATES of table |
| | | | indexed; worst if n=NROWS of table |
| | | | indexed |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhsstt |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| COLDIRS | BINARY | 64 | A vector of direction entries, each of type |
| | | | SMALLINT indicating the direction of the |
| | | | corresponding column in the index |
| | | | definition. In ISQL, each SMALLINIT |
| | | | (two-byte) entry is displayed as a field of |
| | | | 4 hexadecimal digits. |
| | | | |
| | | | 5 ASC (Ascending) |
| | | | 6 DESC (Descending) |
| | | | |
----------------------------------------------------------------------------------------------------------
[REV END]