HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.CheckDef.
SYSTEM.CHECKDEF contains check constraint definitions. Initially, the
table is empty. ALLBASE/SQL updates this table when processing a CREATE
TABLE, ALTER TABLE, DROP TABLE, or TRANSFER OWNERSHIP statement involving
a check constraint.
The text of the table check constraint comprising the search condition is
stored in this table for each check constraint along with the constraint
owner and constraint name. All other information about this constraint
is in SYSTEM.CONSTRAINT.
SQLGEN also uses this table for recreating a CREATE TABLE statement
containing check constraints.
Table 8-4. System.CheckDef
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CONSTRAINTNAME | CHAR | 20 | Name of the constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the constraint |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLUMNNAME | CHAR | 20 | Column to which the constraint applies |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGNUM | INTEGER | 4 | Segment number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGLEN | INTEGER | 4 | Length of segment in bytes |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CONDITIONSTRING | CHAR | 64 | Check constraint string segment |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select constraintname, owner, columnname from system.checkdef;
--------------------+--------------------+--------------------
CONSTRAINTNAME |OWNER |COLUMNNAME
--------------------+--------------------+--------------------
SQLCON_00090000200 |PURCHDB |
---------------------------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
select segnum, seglen, conditionstring from system.checkdef;
-----------+-----------+--------------------------------------------------------
SEGNUM |SEGLEN |CONDITIONSTRING
-----------+-----------+--------------------------------------------------------
1| 14|salesprice > 0
---------------------------------------------------------------------------
Number of rows selected is 1
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>
System.Colauth.
SYSTEM.COLAUTH contains records of authorities granted on specific
columns in a table or view; it does not contain records of UPDATE
authorities granted on an entire table or view. UPDATE and REFERENCES
authorities are the only authorities which can be granted on specific
columns. UPDATE and REFERENCES authorities granted on a table or view
basis instead of a column-by-column basis are recorded in SYSTEM.TABAUTH
(refer to SYSTEM.TABAUTH).
ALLBASE/SQL uses SYSTEM.COLAUTH to verify a user's UPDATE authority prior
to executing an UPDATE statement. If the user's UPDATE authority was not
found in SYSTEM.TABAUTH or SYSTEM.COLAUTH, ALLBASE/SQL will issue an
error message.
ALLBASE/SQL uses SYSTEM.COLAUTH to verify a user's REFERENCES authority
prior to executing a CREATE TABLE statement containing referential
constraint definitions. If the user's REFERENCES authority was not found
in SYSTEM.TABAUTH or SYSTEM.COLAUTH, ALLBASE/SQL will issue an error
message.
SYSTEM.COLAUTH is initially empty, and is revised whenever an UPDATE
authority on specific columns is granted or revoked. Each row specifies
a particular table (or view) column on which UPDATE authority has been
granted. If no column is entered, the authority is granted for all of
the columns in the table or view. For example, if you grant UPDATE
authority to PETER@CRANE:
isql=> GRANT UPDATE (BinNumber, QtyOnHand, LastCountDat)
> ON Inventory TO PETER@CRANE;
SYSTEM.TABAUTH would contain a row with a DBEUserID of PETER@CRANE, a
TableName of Inventory, a C in the UPDATE column, and a 3 in the NCOL
column. SYSTEM.COLAUTH would contain three entries for DBEUserID
PETER@CRANE; one for each of the three listed columns.
If UPDATE authority was granted without specifying specific columns,
SYSTEM.TABAUTH would contain a row with a Y in the update column and
SYSTEM.COLAUTH would not contain any rows.
This view, along with SYSTEM.MODAUTH, SYSTEM.PROCAUTH, SYSTEM.SPECAUTH,
and SYSTEM.TABAUTH, contains the security scheme for the DBEnvironment.
When you create a PUBLIC or PUBLICREAD table, ALLBASE/SQL implicitly
grants table authorities to the special user PUBLIC. In this case, the
GRANTOR column contains the table owner name and the GRANTABLE column
contains an N to indicate that privileges cannot be granted.
Table 8-5. System.Colauth
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table or view on which the user |
| | | | or authorization group has update authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table or view on which the |
| | | | user or authorization group has update |
| | | | authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNAME | CHAR | 20 | Name of a table or view column on which the |
| | | | user or authorization group has update |
| | | | authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPE | CHAR | 2 | Type of authority the user or group has: |
| | | | |
| | | | |
| | | | |
| | | | U UPDATE authority |
| | | | |
| | | | R REFERENCES authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| GRANTOR | CHAR | 20 | Name of the grantor of the privilege |
| | | | described in this row |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| GRANTABLE | CHAR | 2 | GRANTABLE privilege on the column: |
| | | | |
| | | | Y for yes, the user can grant this |
| | | | privilege to others |
| | | | N for no, the user cannot grant this |
| | | | privilege to others |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Colauth;
------------+--------------------+--------------------+------------+--------
USERID |TABLENAME |OWNER |COLNAME |TYPE
------------+--------------------+--------------------+------------+--------
KELLY@COTA |INVENTORY |PURCHDB |BINNUMBER |U
KELLY@COTA |INVENTORY |PURCHDB |QTYONHAND |U
KELLY@COTA |INVENTORY |PURCHDB |LASTCOUNTDAT|U
PETER@CRANE |INVENTORY |PURCHDB |BINNUMBER |U
PETER@CRANE |INVENTORY |PURCHDB |QTYONHAND |U
PETER@CRANE |INVENTORY |PURCHDB |LASTCOUNTDAT|U
KAREN@RIZZO |VENDORS |PURCHDB |PHONENUMBER |U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORSTREET|U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORCITY |U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORSTATE |U
KAREN@RIZZO |VENDORS |PURCHDB |VENDORZIPCOD|U
JIM@FRANCIS |VENDORS |PURCHDB |PHONENUMBER |U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORSTREET|U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORCITY |U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORSTATE |U
JIM@FRANCIS |VENDORS |PURCHDB |VENDORZIPCOD|U
----------------------------------------------------------------------------
Number of rows selected is 16
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+--------------------+---------
|GRANTOR |GRANTABLE
+--------------------+---------
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
|CLEM@DBMS |N
---------------------------------------------------------------------------
Number of rows selected is 16
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.Coldefault.
SYSTEM.COLDEFAULT contains detailed information about the default
constant values which have been specified for columns, as indicated in
SYSTEM.COLUMN. While SYSTEM.COLUMN contains a row for each column in each
table and view, SYSTEM.COLDEFAULT only contains a row for each column for
which a literal default value has been specified. Default values of
NULL, USER, CURRENT_DATE, CURRENT_TIME, or CURRENT_DATETIME do not appear
in SYSTEM.COLDEFAULT; they are indicated in SYSTEM.COLUMN by code
numbers.
This table is initially empty, and is updated whenever ALLBASE/SQL
processes a CREATE TABLE, DROP TABLE, or TRANSFER OWNERSHIP command in
which the column definitions use the DEFAULT Constant clause. The source
string containing the default value specification is stored in segments
of up to 64 characters. Only BINARY or CHARACTER string columns may
require more than one 64-byte segment. All other data types can fit in a
64 byte field.
Table 8-6. System.Coldefault
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNAME | CHAR | 20 | Name of the column with a default |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table containing this column |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGNUM | SMALLINT | 2 | Segment number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGLEN | SMALLINT | 2 | Length of segment in bytes |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DEFAULTVAL | CHAR | 64 | Literal value string segment |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Column;
--------------------+--------------------+--------------------+------+
COLNAME |TABLENAME |OWNER |SEGNUM|
--------------------+--------------------+--------------------+------+
COMPANY |PARTSOURCE |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
------+---------------------------------------------
SEGLEN|DEFAULTVAL
------+---------------------------------------------
22|Integrated Peripherals
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.Column.
SYSTEM.COLUMN contains detailed information about the columns in
the tables described in SYSTEM.TABLE (refer to "SYSTEM.TABLE").
While SYSTEM.TABLE contains a row for every table and view in the
DBEnvironment, SYSTEM.COLUMN contains a row for each column in each of
those tables and views.
Initially, only the columns of the system views are described.
ALLBASE/SQL updates this table when processing an ALTER TABLE, CREATE
TABLE, CREATE VIEW, DROP TABLE, DROP VIEW, TRANSFER OWNERSHIP, or UPDATE
STATISTICS statement.
Note that the value for PRECISION is only used for the decimal and float
data types and SCALE is only used for the decimal data type. For decimal
columns, PRECISION has to be a value between 1 and 15, and the value for
SCALE must be between 0 and the corresponding value for PRECISION. For
floating point columns, PRECISION has to be 53 when LENGTH is 8 and 24
when length is 4.
Note that the DBEFILESET column will only contain a DBEFileSet name for
LONG columns, which can reside in a separate DBEFileSet than the table.
Table 8-7. System.Column
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNAME | CHAR | 20 | Name of the column being described |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the table or view containing this |
| | | | column |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLNUM | INTEGER | 4 | Number of the column in the table or view. |
| | | | Columns are numbered 1, 2,...n, and n is |
| | | | kept in the NUMC column of SYSTEM.TABLE |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LENGTH | INTEGER | 4 | Either Maximum length of the column if |
| | | | TYPECODE is 3 (VARCHAR) |
| | | | Or Number of bytes in the column for |
| | | | all other data types |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| AVGLEN | INTEGER | 4 | Average column length; initially 0. This |
| | | | value is needed by ALLBASE/SQL. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPECODE | SMALLINT | 2 | Data type of the column: |
| | | | |
| | | | 0 INTEGER or SMALLINT (these two are |
| | | | distinguished by the LENGTH field) |
| | | | 1 BINARY |
| | | | 2 CHAR (ASCII only) |
| | | | 3 VARCHAR (ASCII only) |
| | | | 4 FLOAT or REAL (these two are |
| | | | distinguished by the LENGTH field) |
| | | | 5 DECIMAL |
| | | | 6 TID (for ALLBASE/SQL use only) |
| | | | 7 reserved |
| | | | 8 NATIVE CHAR |
| | | | 9 NATIVE VARCHAR |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-7. System.Column (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPECODE (continued) | SMALLINT | 2 | Data type of the column: |
| | | | |
| | | | 10 DATE |
| | | | 11 TIME |
| | | | 12 DATETIME |
| | | | 13 INTERVAL |
| | | | 14 VARBINARY |
| | | | 15 LONG BINARY |
| | | | 16 LONG VARBINARY[REV BEG] |
| | | | 19 CASE INSENSITIVE CHAR |
| | | | 20 CASE INSENSITIVE VARCHAR |
| | | | 21 CASE INSENSITIVE NATIVE CHAR |
| | | | 22 CASE INSENSITIVE NATIVE VARCHAR[REV |
| | | | END] |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NULLS | SMALLINT | 2 | Null value indicator: |
| | | | |
| | | | 0 if the column cannot contain null |
| | | | values |
| | | | 1 if the column can contain null values |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PRECISION | SMALLINT | 2 | Number of significant decimal or binary |
| | | | digits in the number (excluding the sign |
| | | | and the decimal point) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SCALE | SMALLINT | 2 | Number of digits after the decimal point |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LANGUAGEID | SMALLINT | 2 | Code for the language of this column. Run |
| | | | NLUTIL.PUB.SYS to display a complete list |
| | | | of native languages and codes for your |
| | | | system. A value of -1 means NOT APPLICABLE |
| | | | (for numeric type columns or columns in |
| | | | views) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DEFAULTTYPE | SMALLINT | 2 | Default value type indicator: |
| | | | |
| | | | 0 no default clause specified |
| | | | 1 DEFAULT NULL |
| | | | 2 DEFAULT USER |
| | | | 3 DEFAULT Constant |
| | | | 4 DEFAULT CURRENT_DATE |
| | | | 5 DEFAULT CURRENT_TIME |
| | | | 6 DEFAULT CURRENT_DATETIME |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of the DBEFileSet holding LONG column |
| | | | data |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Column;
--------------------+--------------------+--------------------+-----------+
COLNAME |TABLENAME |OWNER |COLNUM |
--------------------+--------------------+--------------------+-----------+
PARTNUMBER |PARTS |PURCHDB | 1|
PARTNAME |PARTS |PURCHDB | 2|
SALESPRICE |PARTS |PURCHDB | 3|
PARTNUMBER |SUPPLYPRICE |PURCHDB | 1|
VENDORNUMBER |SUPPLYPRICE |PURCHDB | 2|
VENDORPARTNUMBER |SUPPLYPRICE |PURCHDB | 3|
UNITPRICE |SUPPLYPRICE |PURCHDB | 4|
DELIVERYDAYS |SUPPLYPRICE |PURCHDB | 5|
DISCOUNTQTY |SUPPLYPRICE |PURCHDB | 6|
VENDORNUMBER |VENDORSTATISTICS |PURCHDB | 1|
VENDORNAME |VENDORSTATISTICS |PURCHDB | 2|
ORDERDATE |VENDORSTATISTICS |PURCHDB | 3|
ORDERQUANTITY |VENDORSTATISTICS |PURCHDB | 4|
TOTALPRICE |VENDORSTATISTICS |PURCHDB | 5|
ORDERNUMBER |ORDERS |PURCHDB | 1|
VENDORNUMBER |ORDERS |PURCHDB | 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]> r
+-----------+-----------+--------+------+---------+------+-----------------
|LENGTH |AVGLEN |TYPECODE|NULLS |PRECISION|SCALE |LANGUAGEID
+-----------+-----------+--------+------+---------+------------------------
| 16| 16| 2| 0| 0| 0| -1
| 30| 30| 2| 1| 0| 0| 0
| 8| 4| 4| 1| 0| 0| -1
| 16| 16| 2| 0| 0| 0| -1
| 4| 4| 0| 0| 0| 0| -1
| 16| 16| 2| 0| 0| 0| -1
| 8| 8| 4| 1| 0| 0| -1
| 2| 2| 0| 1| 0| 0| -1
| 2| 2| 2| 1| 0| 0| -1
| 4| 4| 0| 0| 0| 0| -1
| 30| 30| 2| 0| 0| 0| 0
| 8| 8| 2| 1| 0| 0| 0
| 2| 2| 0| 1| 0| 0| -1
| 8| 8| 4| 1| 0| 0| -1
| 4| 0| 0| 0| 0| 0| -1
| 4| 0| 0| 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
+-----------+---------------------
|DEFAULTTYPE|DBEFILESET
+-----------+---------------------
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 0|
| 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