HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.Table.
SYSTEM.TABLE contains a record of each table and view in the
DBEnvironment including one for itself and one for each of the other
system views. The columns for the tables and views contained in this
table are described in SYSTEM.COLUMN.
Table 8-37. System.Table
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of the DBEFileSet containing the table |
| | | | or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPE | SMALLINT | 2 | Type of object: |
| | | | |
| | | | 0 for table |
| | | | |
| | | | 1 for view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| RTYPE | SMALLINT | 2 | Locking mode for the table: |
| | | | |
| | | | 0 for view |
| | | | 1 for PUBLICREAD table |
| | | | 2 for PRIVATE table |
| | | | 3 for PUBLIC table |
| | | | 4 for temporary table |
| | | | 5 for PUBLICROW table |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns in the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMI | INTEGER | 4 | The number of indexes (including HASH and |
| | | | constraint structures) on a table; 0 for a |
| | | | view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMIC | INTEGER | 4 | The number of constraints on the table or |
| | | | view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMR | | 4 | The number or rules on the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPAGES | INTEGER | 4 | Number of data pages containing the table; |
| | | | 0 for all views except system views |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NROWS | INTEGER | 4 | Number of rows in a table; 0 for all views |
| | | | except system views |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| AVGLEN | INTEGER | 4 | Average row length of the table; 0 for all |
| | | | views except system views |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USTIME | CHAR | 16 | Time of the most recent execution of the |
| | | | UPDATE STATISTICS statement |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LANGUAGEID | SMALLINT | 2 | Code for the language of this table. Run |
| | | | NLUTIL.PUB.SYS to display a complete list |
| | | | of native languages and codes for your |
| | | | system. A value of -1 means NOT APPLICABLE |
| | | | (that is, the entry is a view, not a table) |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-37. System.Table (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PARTITION | CHAR | 20 | Name of the partition containing the table: |
| | | | |
| | | | |
| | | | NONE for a view |
| | | | DEFAULT if not specified |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Table;
--------------------+--------------------+--------------------+------
NAME |OWNER |DBEFILESET |TYPE
--------------------+--------------------+--------------------+------
COUNTER |SYSTEM |SYSTEM | 0
USER |SYSTEM |SYSTEM | 0
TRANSACTION |SYSTEM |SYSTEM | 0
CALL |SYSTEM |SYSTEM | 0
ACCOUNT |SYSTEM |SYSTEM | 0
TABLE |SYSTEM |SYSTEM | 1
COLUMN |SYSTEM |SYSTEM | 1
INDEX |SYSTEM |SYSTEM | 1
SECTION |SYSTEM |SYSTEM | 1
DBEFILESET |SYSTEM |SYSTEM | 1
DBEFILE |SYSTEM |SYSTEM | 1
SPECAUTH |SYSTEM |SYSTEM | 1
TABAUTH |SYSTEM |SYSTEM | 1
COLAUTH |SYSTEM |SYSTEM | 1
MODAUTH |SYSTEM |SYSTEM | 1
GROUP |SYSTEM |SYSTEM | 1
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+------+-----------+-----------+-----------+-----------+-----------
|RTYPE |NUMC |NUMI |NUMIC |NUMR |NPAGES
+------+-----------+-----------+-----------+-----------+-----------
| 3| 3| 0| 0| 0| 0
| 3| 2| 0| 0| 0| 0
| 3| 4| 0| 0| 0| 0
| 3| 5| 0| 0| 0| 0
| 3| 6| 0| 0| 0| 0
| 0| 12| 0| 0| 0| 2
| 0| 10| 0| 0| 0| 9
| 0| 10| 0| 0| 0| 1
| 0| 7| 0| 0| 0| 2
| 0| 5| 0| 0| 0| 1
| 0| 7| 0| 0| 0| 1
| 0| 4| 0| 0| 0| 1
| 0| 10| 0| 0| 0| 1
| 0| 4| 0| 0| 0| 0
| 0| 3| 0| 0| 0| 0
| 0| 4| 0| 0| 0| 1
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+----------+-----------+----------------+----------------+----------
|NROWS |AVGLEN |CTIME |USTIME |LANGUAGEID
+----------+-----------+----------------+----------------+----------
| 0| 32|1985090614175200|Not done yet | 0
| 0| 24|1985090614175200|Not done yet | 0
| 0| 32|1985090614175200|Not done yet | 0
| 0| 52|1985090614175200|Not done yet | 0
| 0| 40|1985090614175200|Not done yet | 0
| 39| 140|1985090614181790|1985101613380630| -1
| 258| 108|1985090614182250|1985101613351330| -1
| 9| 160|1985090614182800|1985101613362650| -1
| 44| 128|1985090614183320|1985101613365940| -1
| 4| 68|1985090614183950|1985101613355530| -1
| 9| 122|1985090614184680|1985101613354100| -1
| 10| 26|1985090614185320|1985101613373740| -1
| 7| 84|1985090614190000|1985101613375100| -1
| 0| 0|1985090614190470|1985101613345170| -1
| 0| 0|1985090614191190|1985101613364530| -1
| 38| 64|1985090614191690|1985101613361280| -1
---------------------------------------------------------------------------
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
+-------------------
|PARTITION
+-------------------
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
|NONE
---------------------------------------------------------------------------
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.TempSpace.
SYSTEM.TEMPSPACE contains information about TempSpace names. This view
is initially empty, but is updated whenever ALLBASE/SQL processes a
CREATE TEMPSPACE or DROP TEMPSPACE. You can use this view to determine
the defined TempSpaces.
isql=> SELECT * from SYSTEM.TEMPSPACE;
Table 8-38. System.TempSpace
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TEMPSNAME | CHAR | 20 | Name of the TempSpace |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LOCATION | CHAR | 8 | System identifier for the group being used |
| | | | for TempSpace files |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| MAXFILEPAGES | INTEGER | 4 | Maximum number of pages per file opened in |
| | | | the TempSpace |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select * from system.tempspace;
--------------------+--------------------------------------------
TEMPSNAME |LOCATION
--------------------+--------------------------------------------
MJTMPSP |TEMPGRP
---------------------------------------------------------------------------
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
+------------+----------------
|MAXFILEPAGES|CTIME
+------------+----------------
| 128|1990041216384500
---------------------------------------------------------------------------
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.TPIndex. [REV BEG]
SYSTEM.TPINDEX contains an entry for each third-party index associated
with the IMAGE datasets associated with this DBEnvironment. Third-party
indexes cannot be used in association with normal ALLBASE/SQL tables. In
ALLBASE/SQL terms the datasets are viewed as tables within the
DBEnvironment.
Table 8-39. System.TPIndex
-------------------------------------------------------------------------------------------------
| | | | |
| 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. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 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-39. System.TPIndex (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]
System.Transaction.
SYSTEM.TRANSACTION is a pseudotable that records the transactions of
users currently accessing the DBEnvironment.
SYSTEM.TRANSACTION supports an unlimited number of users, transactions,
and sessions.
Table 8-40. System.Transaction
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | DBEUserID of the user who started the |
| | | | transaction |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CID | INTEGER | 4 | Unique connection identifier |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SID | INTEGER | 4 | Session identifier. If the same USERID has |
| | | | multiple connections to the same |
| | | | DBEnvironment from the same application |
| | | | (including ISQL), all connections have the |
| | | | same SID. If the user has multiple |
| | | | connections to the same DBEnvironment from |
| | | | separate applications, each application (or |
| | | | ISQL session) will have a different SID. |
| | | | For more information about multiple |
| | | | DBEnvironment connections, see the section |
| | | | "Using Multiple Connections and |
| | | | Transactions with Timeouts" in the "Using |
| | | | ALLBASE/SQL" chapter of the ALLBASE/SQL |
| | | | Reference Manual. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| XID | INTEGER | 4 | Unique transaction identifier |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PRIORITY | INTEGER | 4 | Transaction priority: |
| | | | |
| | | | 0 (highest) - 255 (lowest) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| ISOLATION LEVEL | CHAR | 2 | Transaction isolation level: |
| | | | |
| | | | RR (Repeatable Read, Serializable) |
| | | | |
| | | | CS (Cursor Stability) |
| | | | |
| | | | RU (Read Uncommitted) |
| | | | |
| | | | RC (Read Committed) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LABEL | CHAR | 8 | Transaction label string |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Transaction;
--------------------+-----------+-----------+-----------+-----------
USERID |CID |SID |XID |PRIORITY
--------------------+-----------+-----------+-----------+-----------
JOHN@BROCK | 108| 108| 11320| 127
---------------------------------------------------------------------------
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
--------------------+---------
ISOLATION LEVEL |LABEL
--------------------+---------
RC +Xact2
------------------------------
Number of rows selected is 1
System.User.
SYSTEM.USER is a pseudotable that contains a row for each user currently
accessing the DBEnvironment.
Table 8-41. System.User
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | DBEUserID |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CID | INTEGER | 4 | Unique connection identifier |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SID | INTEGER | 4 | Session identifier. If the same USERID has |
| | | | multiple connections to the same |
| | | | DBEnvironment from the same application |
| | | | (including ISQL), all connections have the |
| | | | same SID. If the user has multiple |
| | | | connections to the same DBEnvironment from |
| | | | separate applications, each application (or |
| | | | ISQL session) will have a different SID. |
| | | | For more information about multiple |
| | | | DBEnvironment connections, see the section |
| | | | "Using Multiple Connections and |
| | | | Transactions with Timeouts" in the "Using |
| | | | ALLBASE/SQL" chapter of the ALLBASE/SQL |
| | | | Reference Manual. |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.User;
--------------------+-----------+-----------
USERID |CID |SID
--------------------+-----------+-----------
JOHN@BROCK | 108| 108
---------------------------------------------------------------------------
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