HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.Constraint.
SYSTEM.CONSTRAINT contains detailed information about the integrity
constraints contained in the database.
Initially, the table is empty. ALLBASE/SQL updates this table when
processing a CREATE TABLE, ALTER TABLE, CREATE VIEW, DROP TABLE, DROP
VIEW, or TRANSFER OWNERSHIP statement involving an integrity constraint.
This table is accessed whenever an INSERT, format II INSERT, UDPATE, or
DELETE is performed on a table to determine if any integrity constraints
need to be enforced. Further checking into SYSTEM.CONSTRAINTCOL is made
if a table is found in SYSTEM.CONSTRAINT, to enforce referential
constraints (UNIQUE, PRIMARY KEY, FOREIGN KEY) on a column-dependent
basis.
Table 8-8. System.Constraint
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CONSTRAINTNAME | CHAR | 20 | Name of the constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPE | CHAR | 2 | Type of constraint: |
| | | | |
| | | | C table check constraint |
| | | | P primary key |
| | | | R referential constraint (foreign key) |
| | | | U unique constraint (key other than |
| | | | primary) |
| | | | V view check constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INDEXTYPE | CHAR | 2 | Type of index used: |
| | | | |
| | | | C clustered index |
| | | | H hash structure |
| | | | N none specified |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns in the constraint if TYPE |
| | | | is one of P, R, or U. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| REFOWNER | CHAR | 20 | Owner of the key column referenced in a |
| | | | FOREIGN KEY constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| REFCONSTRAINT | CHAR | 20 | Constraint name of the key column |
| | | | referenced in a FOREIGN KEY constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of the DBEFileSet containing the |
| | | | stored section for a table check constraint |
| | | | (blank if not applicable) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Constraint;
--------------------+--------------------+--------------------+----+---------
CONSTRAINTNAME |OWNER |TABLENAME |TYPE|INDEXTYPE
--------------------+--------------------+--------------------+----+---------
SQLCON_00000001P00 |PURCHDB |PARTS1 |P |N
CLUBS_PK |RECDB |CLUBS |P |N
MEMBERS_PK |RECDB |MEMBERS |P |N
MEMBERS_FK |RECDB |MEMBERS |R |N
EVENTS_FK |RECDB |EVENTS |R |N
---------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
+-----------+--------------------+--------------------+--------------------
|NUMC |REFOWNER |REFCONSTRAINT |DBEFILESET
+-----------+--------------------+--------------------+--------------------
| 1| | |
| 1| | |
| 2| | |
| 1|RECDB |CLUBS_PK |
| 2|RECDB |MEMBERS_PK |
---------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
System.Constraintcol.
SYSTEM.CONSTRAINTCOL contains information about the columns used in
PRIMARY KEY, UNIQUE, and FOREIGN KEY integrity constraints.
Initially, the table is empty since the system catalog does not use
integrity constraints. ALLBASE/SQL updates this table when processing a
CREATE TABLE, DROP TABLE, or TRANSFER OWNERSHIP statement involving an
integrity constraint.
This table is accessed whenever an INSERT, type II INSERT, UPDATE, or
DELETE is performed on a table in the CONSTRAINT table to determine if
the constraint needs to be enforced. It is accessed whenever a REVOKE,
REMOVE FROM GROUP, or DROP GROUP statement is performed to determine
whether the constraint columns depend on the current authorization
scheme.
Table 8-9. System.ConstraintCol
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CONSTRAINTNAME | CHAR | 20 | Name of the constraint containing this |
| | | | column |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLUMNNAME | CHAR | 20 | Name of a column used in the constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| POSITION | INTEGER | 4 | Position of the occurrence of the column in |
| | | | the constraint, numbered 1, 2,...n, where n |
| | | | is the value of NUMC in the |
| | | | SYSTEM.CONSTRAINT tuple for this |
| | | | constraint. |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.ConstraintCol;
--------------------+--------------------+--------------------+-----------
CONSTRAINTNAME |OWNER |COLUMNNAME |POSITION
--------------------+--------------------+--------------------+-----------
SQLCON_00000001P00 |PURCHDB |PARTNUM | 1
CLUBS_PK |RECDB |CLUBNAME | 1
MEMBERS_PK |RECDB |MEMBERNAME | 1
MEMBERS_PK |RECDB |CLUB | 2
MEMBERS_FK |RECDB |CLUB | 1
EVENTS_FK |RECDB |COORDINATOR | 1
EVENTS_FK |RECDB |SPONSORCLUB | 2
---------------------------------------------------------------------------
Number of rows selected is 7
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
System.ConstraintIndex.
SYSTEM.CONSTRAINTINDEX contains an entry for each integrity constraint.
This view is initially empty, but is updated whenever ALLBASE/SQL
processes a CREATE TABLE, DROP TABLE, ALTER TABLE TRANSFER OWNERSHIP (of
a table), or UPDATE STATISTICS statement involving an integrity
constraint.
Table 8-10. System.ConstraintIndex
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CONSTRAINTNAME | CHAR | 20 | Name of the unique or referential |
| | | | constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table on which the index is |
| | | | defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table on which the index is |
| | | | defined |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns in the index |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNUMS | BINARY | 32 | A vector of 16 SYSTEM.COLUMN entries, each |
| | | | of type SMALLINT, identifying the column |
| | | | numbers the index is defined over. In |
| | | | ISQL, each SMALLINT (two-byte) entry is |
| | | | displayed as a field of 4 hexadecimal |
| | | | digits. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPAGES | INTEGER | 4 | Number of data pages containing the |
| | | | constraint index |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CCOUNT | INTEGER | 4 | Cluster count; 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 | CHAR | 16 | Time the index was created |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 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 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLDIRS | BINARY | 32 | A vector of 16 direction entries, each of |
| | | | type SMALLINT, indicating the direction of |
| | | | the corresponding column in the index |
| | | | definition. In ISQL, each SMALLINT |
| | | | (two-byte) entry is displayed as a field of |
| | | | 4 hexadecimal digits. |
| | | | |
| | | | The following values indicate a specific |
| | | | direction: |
| | | | |
| | | | 5 ASC (Ascending) |
| | | | |
| | | | 6 DESC (Descending) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.ConstraintIndex;
--------------------+--------------------+--------------------+-----------
CONSTRAINTNAME |TABLENAME |OWNER |NUMC
--------------------+--------------------+--------------------+-----------
SQLCON_00000001P00 |PARTS1 |PURCHDB | 1
CLUBS_PK |CLUBS |RECDB | 1
MEMBERS_PK |MEMBERS |RECDB | 2
MEMBERS_FK |MEMBERS |RECDB | 1
EVENTS_FK |EVENTS |RECDB | 2
---------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
+----------------------------------------------------------------+------------
|COLNUMS |NPAGES
+----------------------------------------------------------------+------------
|0001000000000000000000000000000000000000000000000000000000000000| 0
|0001000000000000000000000000000000000000000000000000000000000000| 0
|0001000000000000000000000000000000000000000000000000000000000000| 0
|0002000000000000000000000000000000000000000000000000000000000000| 0
|0001000000000000000000000000000000000000000000000000000000000000| 0
------------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> r
+-----------+----------------+------+-------
|CCOUNT |CTIME |UNIQUE|CLUSTER
+-----------+----------------+------+-------
| 0|1990040914185800| 1| 0
| 0|1990040914203800| 1| 0
| 0|1990040914280600| 1| 0
| 0|1990040914280600| 0| 0
| 0|1990040914295500| 0| 0
---------------------------------------------------------------------------
Number of rows selected is 5
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd]> e
+----------------------------------------------------------------+
|COLDIRS |
+----------------------------------------------------------------+
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
|0005000000000000000000000000000000000000000000000000000000000000|
------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.Counter.
SYSTEM.COUNTER is a pseudotable that contains internal system counters.
While the statistics are primarily for SQLCore's internal use, you can
use these statistics to monitor DBEnvironment use and make appropriate
changes to your DBEnvironment and/or application programs.
For example, you can use the DEADLOCK counter to determine if the number
of deadlocks is excessive, and the CHECKPTS counter to determine if you
need to change the size of the log file.
Values in SYSTEM.COUNTER are continually incremented from the time of a
START DBE until you issue the RESET statement, which sets all counter
values to 0. The counters are automatically reset each time the DBE is
started.
Some of the rows in this system view are reserved. They are designated
as 'UNUSED' when you select all entries from the table, and can be
deleted from your query with the following statement:
isql=> SELECT * FROM System.Counter
> WHERE NAME <> 'UNUSED';
SYSTEM.COUNTER supports an unlimited number of users, transactions, and
sessions.
Table 8-11. System.Counter
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 8 | Name of counter |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| VALUE | INTEGER | 4 | Counter value (time values are in |
| | | | milliseconds) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DESCRIPTION | CHAR | 20 | Description of the counter |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Counter;
--------+-----------+--------------------
NAME |VALUE |DESCRIPTION
--------+-----------+--------------------
TRANSBEG| 6|Transactions begun
TRANSEND| 4|Transactions ended
TRANSABT| 0|Transactions abort
CHECKPTS| 0|Checkpoints
LOCKREQS| 86|Lock requests
SHLTREQS| 69|Shared latch reqs
EXLTREQS| 537|Excl latch reqs
DEADLOCK| 0|Deadlocks
PAGEACCS| 152|Page accesses
PAGEREAD| 30|Page reads
PAGEWRTE| 0|Page writes
LGBLKRD | 17|Log block reads
LGBLKWR | 0|Log block writes
LGRECWR | 0|Log record writes
ERRORLG1| 0|Errors on log ds 1
ERRORLG2| 0|Errors on log ds 2
---------------------------------------------------------------------------
First 16 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> b
--------+-----------+--------------------
NAME |VALUE |DESCRIPTION
--------+-----------+--------------------
UNUSED | 0|------------------
UNUSED | 0|------------------
TIMEOUTS| 0|Expired timeouts
UNUSED | 0|------------------
UNUSED | 0|------------------
UNUSED | 0|------------------
UNUSED | 0|------------------
UNUSED | 0|------------------
RESETS | 1|Number of Resets
DBCALL | 117|DBCORE calls
SHLTWAIT| 0|Share latch waits
EXLTWAIT| 0|Excl latch waits
LOCKWAIT| 0|Lock waits
IO | 79|I/O s
SERVREQS| 0|Service requests
---------------------------------------------------------------------------
Number of rows selected is 31
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.DBEFile.
SYSTEM.DBEFILE contains information about the DBEFiles in the
DBEnvironment. Initially it contains the DBEFiles created when the
DBEnvironment is configured. A row is added, updated, or deleted
whenever ALLBASE/SQL processes an ADD DBEFILE, ALTER DBEFILE, CREATE
DBEFILE, DROP DBEFILE, or REMOVE DBEFILE statement.
SYSTEM.DBEFILE and SYSTEM.DBEFILESET contain all the information about
where tables are stored.
You can use the following query to determine which DBEFileSet contains a
certain DBEFile:
isql=> SELECT DBEFName, DBEFSName FROM System.DBEFile
> WHERE DBEFName='PURCHDF1';
To determine the DBEFiles in a DBEFileSet, use the following query:
isql=> SELECT * FROM System.DBEFile
> WHERE DBEFSName='PURCHFS';
Table 8-12. System.DBEFile
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFNAME | CHAR | 20 | Name of the DBEFile |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFTYPE | SMALLINT | 2 | Type: |
| | | | |
| | | | 90 for mixed |
| | | | |
| | | | 91 for index |
| | | | |
| | | | 92 for data |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| FILEID | CHAR | 44 | System identifier for the file being used |
| | | | for the DBEFile |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFNPAGES | INTEGER | 4 | Number of pages in the DBEFile |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFUPAGES | INTEGER | 4 | Number of pages in the DBEFile containing |
| | | | table and index data, excluding page table |
| | | | pages |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFINCRSZ | INTEGER | 4 | Number of pages the DBEFile will be |
| | | | expanded each time |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFIPAGES | INTEGER | 4 | Number of pages in the DBEFile at file |
| | | | creation time |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFMPAGES | INTEGER | 4 | Maximum number of pages the DBEFile can be |
| | | | expanded |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFSNAME | CHAR | 20 | Name of the DBEFileSet the DBEFile is |
| | | | associated with (if any) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFNUMBER | INTEGER | 4 | Number of the DBEFileSet the DBEFile is |
| | | | associated with |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| ATTACHED | CHARACTER | 2 | Whether the DBEFile is attached to the |
| | | | DBEnvironment (Y for yes, N for no) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.DBEFile;
--------------------+--------+--------------------------------------------
DBEFNAME |DBEFTYPE|FILEID
--------------------+--------+--------------------------------------------
PARTSDBE0 | 90|PartsF0
PURCHDATAF1 | 92|PurchDF1
PURCHINDXF1 | 91|PurchXF1
WAREHDATAF1 | 92|WarehDF1
WAREHINDXF1 | 91|WarehXF1
ORDERDATAF1 | 92|OrderDF1
ORDERINDXF1 | 91|OrderXF1
FILEDATA | 92|FileData
RECDATAF1 | 90|RecDF1
NEWFILE | 90|NewFile
NEWFILE2 | 90|NewFile2
---------------------------------------------------------------------------
Number of rows selected is 11
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+-----------+-----------+-----------+-----------+-----------
|DBEFNPAGES |DBEFUPAGES |DBEFINCRSZ |DBEFIPAGES |DBEFMPAGES
+-----------+-----------+-----------+-----------+-----------
| 150| 0| 0| 150| 150
| 50| 2| 0| 50| 50
| 50| 4| 0| 50| 50
| 50| 4| 0| 50| 50
| 50| 4| 0| 50| 50
| 50| 3| 0| 50| 50
| 50| 3| 0| 50| 50
| 50| 0| 0| 50| 50
| 50| 7| 0| 50| 50
| 150| 0| 0| 150| 150
| 100| 0| 0| 100| 100
---------------------------------------------------------------------------
Number of rows selected is 11
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+----------------+---------------
|CTIME |DBEFSNAME
+----------------+---------------
|1990090614175200|SYSTEM
|1990090614192820|PURCHFS
|1990090614193170|PURCHFS
|1990090614194600|WAREHFS
|1990090614195400|WAREHFS
|1990090614200640|ORDERFS
|1990090614200990|ORDERFS
|1991020613192200|SYSTEM
|1993072014054600|RECFS
|1994021415230500|SYSTEM
|1994021415252200|
---------------------------------------------------------------------------
Number of rows selected is 11
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
+----------------+---------------
|DBEFNUMBER |ATTACHED
+----------------+---------------
| 0|Y
| 1|Y
| 2|Y
| 3|Y
| 4|Y
| 5|Y
| 6|Y
| 7|Y
| 8|Y
| 9|Y
| 10|Y
---------------------------------------------------------------------------
Number of rows selected is 11
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e