HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.Index (Continued).
Example. [REV BEG]
SELECT * FROM System.Index;
--------------------+--------------------+--------------------+------+-----
INDEXNAME |TABLENAME |OWNER |UNIQUE|CLUST
--------------------+--------------------+--------------------+------+-----
PARTNUMINDEX |PARTS |PURCHDB | 1|
PARTTONUMINDEX |SUPPLYPRICE |PURCHDB | 0|
PARTTOVENDINDEX |SUPPLYPRICE |PURCHDB | 0|
VENDPARTINDEX |SUPPLYPRICE |PURCHDB | 1|
VENDORNUMINDEX |VENDORS |PURCHDB | 1|
ORDERNUMINDEX |ORDERS |PURCHDB | 1|
ORDERVENDINDEX |ORDERS |PURCHDB | 0|
ORDERITEMINDEX |ORDERITEMS |PURCHDB | 1|
INVPARTNUMINDEX |INVENTORY |PURCHDB | 1|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
-+--------------------+------+-------+-----------+-------------------------
|OWNER |UNIQUE|CLUSTER|NUMC |COLNUMS
-+--------------------+------+-------+-----------+-------------------------
|PURCHDB | 1| 0| 1|0001000000000000000000000
|PURCHDB | 0| 1| 1|0001000000000000000000000
|PURCHDB | 0| 0| 1|0002000000000000000000000
|PURCHDB | 1| 0| 1|0003000000000000000000000
|PURCHDB | 1| 0| 1|0001000000000000000000000
|PURCHDB | 1| 1| 1|0001000000000000000000000
|PURCHDB | 0| 0| 1|0002000000000000000000000
|PURCHDB | 1| 1| 2|0001000200000000000000000
|PURCHDB | 1| 0| 1|0001000000000000000000000
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
---------+----------------------------------------------------------------+
MC |COLNUMS |
---------+----------------------------------------------------------------+
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0002000000000000000000000000000000000000000000000000000000000000|
1|0003000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
1|0002000000000000000000000000000000000000000000000000000000000000|
2|0001000200000000000000000000000000000000000000000000000000000000|
1|0001000000000000000000000000000000000000000000000000000000000000|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
----------------------------------+-----------+-----------+-----------+----
|NPAGES |NLEVELS |NLEAVES |NDIS
----------------------------------+-----------+-----------+-----------+----
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 1| 0| 1|
0000000000000000000000000000000000| 0| 0| 0|
0000000000000000000000000000000000| 1| 0| 1|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
------+-----------+-----------+-----------+-----------+-----------+--------
S |NLEVELS |NLEAVES |NDISTINCT |NFIRST |NPERKEY |CCOUNT
------+-----------+-----------+-----------+-----------+-----------+--------
1| 0| 1| 22| 22| 1|
1| 0| 1| 22| 22| 1|
1| 0| 1| 15| 15| 1|
1| 0| 1| 69| 69| 1|
1| 0| 1| 18| 18| 1|
1| 0| 1| 17| 17| 1|
1| 0| 1| 12| 12| 1|
0| 0| 0| 0| 0| 0|
1| 0| 1| 22| 22| 1|
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
--+-----------+-----------+-----------+----------------+-------------------
|NFIRST |NPERKEY |CCOUNT |CTIME |COLDIRS
--+-----------+-----------+-----------+----------------+-------------------
22| 22| 1| 1|1996020600084900|0005000000000000000
22| 22| 1| 1|1996020600085000|0005000000000000000
15| 15| 1| 1|1996020600085000|0005000000000000000
69| 69| 1| 1|1996020600085000|0005000000000000000
18| 18| 1| 1|1996020600085100|0005000000000000000
17| 17| 1| 1|1996020600085100|0005000000000000000
12| 12| 1| 1|1996020600085100|0005000000000000000
0| 0| 0| 0|1996070805153400|0005000500000000000
22| 22| 1| 1|1996020600085200|0005000000000000000
---------------------------------------------------------------------------
Number of rows selected is 9
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
---------------+-----------------------------------------------------------
TIME |COLDIRS
---------------+-----------------------------------------------------------
996020600084900|00050000000000000000000000000000000000000000000000000000000
996020600085000|00050000000000000000000000000000000000000000000000000000000
996020600085000|00050000000000000000000000000000000000000000000000000000000
996020600085000|00050000000000000000000000000000000000000000000000000000000
996020600085100|00050000000000000000000000000000000000000000000000000000000
996020600085100|00050000000000000000000000000000000000000000000000000000000
996020600085100|00050000000000000000000000000000000000000000000000000000000
996070805153400|00050005000000000000000000000000000000000000000000000000000
996020600085200|00050000000000000000000000000000000000000000000000000000000
---------------------------------------------------------------------------
Number of rows selected is 9
[REV END]
System.Installauth. [REV BEG]
SYSTEM.INSTALLAUTH lists all users and authorization groups that have
been granted INSTALL authority. ALLBASE/SQL updates SYSTEM.INSTALLAUTH
when processing a GRANT INSTALL or REVOKE INSTALL statement, or when
dropping a group having INSTALL authority.
SYSTEM.INSTALLAUTH, SYSTEM.COLAUTH, SYSTEM.MODAUTH, SYSTEM.PROCAUTH,
SYSTEM.SPACEAUTH, SYSTEM.SPACEDEFAULT, SYSTEM.SPECAUTH, and
SYSTEM.TABAUTH, contain the security scheme for the DBEnvironment.
CATALOG.INSTALLAUTH is identical in format to SYSTEM.INSTALLAUTH;
it permits users without DBA authority or SELECT authority on
SYSTEM.INSTALLAUTH to examine rows to which they have access.
Table 8-18. System.Installauth
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | An owner name the USERID is authorized to |
| | | | use. If blank, the USERID is authorized to |
| | | | use any owner name. |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Installauth;
--------------------+--------------------
USERID |OWNER
--------------------+--------------------
GEORGE@DBMS |
CLEM@DBMS |JOHN@BROCK
CLEM@DBMS |SUSAN@SMITH
---------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
[REV END]
System.Modauth.
SYSTEM.MODAUTH contains RUN authorities for application programs. This
view, along with the SYSTEM.COLAUTH, SYSTEM.PROCAUTH, SYSTEM.SPECAUTH,
and SYSTEM.TABAUTH views, contains the authorities for the DBEnvironment.
Table 8-19. System.Modauth
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the module for which the user has |
| | | | RUN authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the module for which the user has |
| | | | RUN authority |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Modauth;
--------------------+--------------------+--------------------
USERID |NAME |OWNER
--------------------+--------------------+--------------------
JIM@FRANCIS |CEXP06 |JOHN@BROCK
KAREN@RIZZO |CEXP06 |JOHN@BROCK
---------------------------------------------------------------------------
Number of rows selected is 2
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e
System.Paramdefault.
SYSTEM.PARAMDEFAULT contains information about the default values of
parameters that may be passed to and from procedures.
SYSTEM.PARAMDEFAULT is initially empty, and it is updated whenever
ALLBASE/SQL processes a CREATE PROCEDURE, DROP PROCEDURE, or TRANSFER
OWNERSHIP statement for a procedure that includes default parameter
values.
If a TRANSFER OWNERSHIP is done on the procedure, the owner field in this
view is updated with the name of the new owner.
For literal default values, the source string containing the default
value specification is stored in segments of up to 64 bytes.
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-20. System.Paramdefault
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the parameter with a default |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PROCNAME | CHAR | 20 | Name of the procedure containing this |
| | | | parameter |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGNUM | SMALLINT | 2 | Segment number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGLEN | SMALLINT | 2 | Length of segment in bytes |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DEFAULTVAL | CHAR | 64 | Literal value string segment |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT Name, ProcName, Owner FROM System.Paramdefault;
------------+--------------------+--------------------
NAME |PROCNAME |OWNER
------------+--------------------+--------------------
SHIFTNAME |PROCESS12 |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]> e
SELECT SegNum, SegLen, DefaultVal FROM System.Paramdefault;
------+------+--------------------------------------------------------------
SEGNUM|SEGLEN|DEFAULTVAL
------+------+--------------------------------------------------------------
1| 3|Day
----------------------------------------------------------------------------
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.Parameter.
SYSTEM.PARAMETER contains information about parameters that may be passed
to and from procedures. SYSTEM.PARAMETER is initially empty, and it is
updated whenever ALLBASE/SQL processes a CREATE PROCEDURE, DROP
PROCEDURE, or TRANSFER OWNERSHIP statement for a procedure that includes
parameters.
If a TRANSFER OWNERSHIP is done on the procedure, the owner field in this
view is updated with the name of the new owner.
Table 8-21. System.Parameter
-------------------------------------------------------------------------------------------------
| | | | |
| Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the parameter |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PROCNAME | CHAR | 20 | Name of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUM | INTEGER | 4 | Number of the parameter within the |
| | | | procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LENGTH | INTEGER | 4 | Either Maximum length of the column if |
| | | | TYPECODE is 3 (VARCHAR), 9 (NATIVE |
| | | | VARCHAR), or 14 (VARBINARY) |
| | | | Or Number of bytes in the column for |
| | | | all other data types |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPECODE | SMALLINT | 2 | Data type of the column: |
| | | | |
| | | | 0 INTEGER or SMALLINT (set by the LENGTH |
| | | | field) |
| | | | 1 BINARY |
| | | | 2 CHAR (ASCII only) |
| | | | 3 VARCHAR (ASCII only) |
| | | | 4 FLOAT or REAL (set by the LENGTH |
| | | | field) |
| | | | 5 DECIMAL |
| | | | 6 TID |
| | | | 7 reserved |
| | | | 8 NATIVE CHAR |
| | | | 9 NATIVE VARCHAR |
| | | | 10 DATE |
| | | | 11 TIME |
| | | | 12 DATETIME |
| | | | 13 INTERVAL |
| | | | 14 VARBINARY |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NULLS | SMALLINT | 2 | Null value indicator: |
| | | | |
| | | | 0 if the column cannot contain null |
| | | | values |
| | | | 1 if the column can contain null values |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-21. System.Parameter (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 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 non-character type columns) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| 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 |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OUTPUT | SMALLINT | 2 | Parameter type: |
| | | | |
| | | | 0 input |
| | | | 1 input/output |
| | | | 2 output only |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select * from system.parameter;
--------------------+--------------------+--------------------+-----------
NAME |PROCNAME |OWNER |NUM
--------------------+--------------------+--------------------+-----------
OPERATOR |PROCESS12 |MANUFDB | 1
SHIFT |PROCESS12 |MANUFDB | 2
FAILURETYPE |PROCESS12 |MANUFDB | 3
PARTNUMBER |DISCOUNTPART |PURCHDB | 1
NAME |REPORTMONITOR |PURCHDB | 1
OWNER |REPORTMONITOR |PURCHDB | 2
TYPE |REPORTMONITOR |PURCHDB | 3
VENDORNUMBER |DELVENDOR |PURCHDB | 1
VENDORNUMBER |CHECKVENDOR |PURCHDB | 1
PARTNUMBER |PROCESS15 |PETERW@WEYGANT | 1
PERCENTAGE |DISCOUNTALL |PURCHDB | 1
NROWS |ENTERTEST |PETERW@WEYGANT | 1
---------------------------------------------------------------------------
Number of rows selected is 12
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
select length, typecode,nulls,precision from system.parameter;
-----------+--------+------+---------
LENGTH |TYPECODE|NULLS |PRECISION
-----------+--------+------+---------
20| 2| 0| 0
20| 2| 0| 0
10| 2| 0| 0
16| 2| 1| 0
20| 2| 0| 0
20| 2| 0| 0
10| 2| 0| 0
4| 0| 0| 10
4| 0| 0| 10
16| 2| 0| 0
4| 5| 1| 4
4| 0| 1| 10
---------------------------------------------------------------------------
Number of rows selected is 12
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
select scale, languageid, defaulttype, output from system.parameter;
------+----------+-----------+------
SCALE |LANGUAGEID|DEFAULTTYPE|OUTPUT
------+----------+-----------+------
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
0| 0| 0| 0
2| 0| 0| 0
0| 0| 0| 0
---------------------------------------------------------------------------
Number of rows selected is 12
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
System.Partition.
The SYSTEM.PARTITION view is used to describe each partition. The base
table (which is HPRDBSS.PARTITION) is locked with an exclusive lock
if you execute a CREATE PARTITION or DROP PARTITION statement.
HPRDBSS.PARTITION is locked with a share lock if you execute a CREATE
TABLE statement that specifies a partition or an ALTER TABLE statement
that modifies a partition.
The SYSTEM.PARTITION table initially specifies the DEFAULT partition for
all tables, if they are created when the DBEnvironment is configured. A
row is added, updated, or deleted from SYSTEM.PARTITION whenever one of
the following occurs:
* A CREATE PARTITION or DROP PARTITION statement is processed.
* A START DBE NEWLOG statement defining or altering the DEFAULT
partition is processed.
* A CREATE TABLE or ALTER TABLE statement specifying a partition is
processed.
The PARTITION table format is shown in Table 8-22 .
Table 8-22. SYSTEM.PARTITION
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PARTITIONNAME | CHAR | 20 | Name of the Partition |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PARTITIONID | INTEGER | 4 | Number of the partition this table is |
| | | | assigned to. 0 if default, -1 if none. |
| | | | This column is not exposed in the system |
| | | | view, and is used to minimize table |
| | | | accesses. |
| | | | |
-------------------------------------------------------------------------------------------------