HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Cont.)
Locking of the System Catalog (Cont.)
System.Plan.
SYSTEM.PLAN is a pseudotable which displays the access plan generated by
the optimizer for a SELECT, UPDATE or DELETE statement processed by the
GENPLAN statement. Information is displayed for only a single statement
at a time.
To display an access plan, you must first process a statement of the
above type with the GENPLAN statement as in the following example:
isql=> GENPLAN FOR SELECT * FROM Purchdb.Parts;
To display the access plan, issue the following statement within the same
transaction:
isql=> SELECT * FROM System.Plan;
Table 8-23. System.Plan
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| QUERYBLOCK | INTEGER | 4 | Queryblock in which operation is executed |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| STEP | INTEGER | 4 | Sequence within the query block in which |
| | | | operation is executed at run time |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LEVEL | INTEGER | 4 | Level of operation within the run tree |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OPERATION | CHAR | 20 | Type of Operation: |
| | | | |
| | | | merge join |
| | | | nestedloop join |
| | | | sort |
| | | | project |
| | | | filter |
| | | | distinct |
| | | | distinct sort |
| | | | group by |
| | | | or |
| | | | union |
| | | | serial scan |
| | | | index scan |
| | | | TID scan |
| | | | hash scan |
| | | | block scan (block number) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Table upon which operation is executed |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the table |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INDEXNAME | CHAR | 20 | Name of index used for operation |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
isql=> GENPLAN FOR
> SELECT *
> FROM Purchdb.Parts
> WHERE Partnumber =
> (SELECT Partnumber
> FROM PurchDB.SupplyPrice sp, PurchDB.Vendors v
> WHERE v.VendorName = 'Pro-Litho Inc.'
> AND sp.UnitPrice <= 200.00
> AND sp.VendorNumber = v.VendorNumber);
isql=> SELECT * FROM System.Plan;
SELECT * FROM System.Plan;
-----------+-----------+-----------+--------------------+------------------
QUERYBLOCK |STEP |LEVEL |OPERATION |TABLENAME
-----------+-----------+-----------+--------------------+------------------
1| 1| 3|serial scan |VENDORS
1| 2| 3|serial scan |SUPPLYPRICE
1| 3| 2|nestedloop join |
2| 1| 1|index scan |PARTS
---------------------------------------------------------------------------
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
+--------------------+----------------
|OWNER |INDEXNAME
+--------------------+----------------
|PURCHDB |
|PURCHDB |
| |
|PURCHDB |PARTNUMINDEX
--------------------------------------
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.Procauth.
SYSTEM.PROCAUTH contains EXECUTE authorities for procedures. This view,
along with the SYSTEM.COLAUTH, SYSTEM.MODAUTH, SYSTEM.SPECAUTH, and
SYSTEM.TABAUTH views, contains the security scheme for the DBEnvironment.
SYSTEM.PROCAUTH is initially empty, and it is updated whenever
ALLBASE/SQL processes a GRANT EXECUTE, REVOKE EXECUTE, a TRANSFER
OWNERSHIP of a procedure, or a DROP PROCEDURE (without the PRESERVE
option).
Table 8-24. System.Procauth
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | Authorized DBEUserID or authorization group |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the procedure for which the user |
| | | | has EXECUTE authority |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the procedure for which the user |
| | | | has EXECUTE authority |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Procauth;
--------------------+--------------------+--------------------
USERID |NAME |OWNER
--------------------+--------------------+--------------------
JIM@FRANCIS |REPORTMONITOR |PURCHDB
KAREN@RIZZO |PROCESS12 |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.Procedure.
SYSTEM.PROCEDURE contains information about each procedure created in the
DBEnvironment.
SYSTEM.PROCEDURE is initially empty, and it is updated whenever
ALLBASE/SQL processes a CREATE PROCEDURE, DROP PROCEDURE, or TRANSFER
OWNERSHIP statement on a procedure.
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-25. System.Procedure
-------------------------------------------------------------------------------------------------
| | | | |
| Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMP | INTEGER | 4 | Number of parameters ( 0 to 1023) to the |
| | | | procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMR | SMALLINT | 2 | Number of result columns ( 0 to 1024) for a |
| | | | procedure defined with a WITH RESULT clause |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| MULTIRESULT | SMALLINT | 2 | Number of SELECT statements with no INTO |
| | | | clause in the procedure (0 if there are |
| | | | none) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LANGUAGEID | SMALLINT | 2 | Code for the language of this procedure. |
| | | | Run NLUTIL.PUB.SYS to display a complete |
| | | | list of native languages and codes for your |
| | | | system. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of the DBEFileSet containing the |
| | | | procedure's definition and stored sections |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select * from system.procedure;
--------------------+--------------------+-----------+------
NAME |OWNER |NUMP |NUMR
--------------------+--------------------+-----------+------
PROCESS12 |MANUFDB | 3| 2
DISCOUNTPART |PURCHDB | 1| 3
---------------------------------------------------------------------------
Number of rows selected is 2
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
select multiresult, ctime, languageid from system.procedure;
-----------+----------------+----------+--------------
MULTIRESULT|CTIME |LANGUAGEID|DBEFILESET
-----------+----------------+----------+--------------
3|1991121010220700| 0|SYSTEM
1|1991121011442200| 0|PURCHFS
---------------------------------------------------------------------------
Number of rows selected is 2
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
System.ProcedureDef.
The SYSTEM.PROCEDUREDEF view displays information about procedure
definitions, including the text of the procedure itself. The procedure
text is stored in a series of segments of up to 64 bytes, starting with
the initial BEGIN and ending with the final semicolon. The procedure
definition does not include parameter definitions, which are stored
separately in SYSTEM.PARAMETER.
SYSTEM.PROCEDUREDEF is initially empty, and it is updated whenever
ALLBASE/SQL processes a CREATE PROCEDURE statement, a DROP PROCEDURE
statement, or a TRANSFER OWNERSHIP statement on a procedure.
When the procedure is dropped, the rows making up the byte string of the
procedure definition are deleted. If a TRANSFER OWNERSHIP is done on the
procedure, the owner field in this table is updated with the name of the
new owner.
Table 8-26. System.ProcedureDef
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| QUALIFIER | CHAR | 20 | Owner name to be used to qualify any |
| | | | unqualified objects referenced in the |
| | | | procedure definition |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGNUM | INTEGER | 4 | Segment number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGLEN | INTEGER | 4 | Length of segment in bytes (up to 64) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DEFINESTRING | CHAR | 64 | Procedure definition byte string segment |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.ProcedureDef WHERE NAME = 'REPORTMONITOR';
--------------------+--------------------+--------------------+------
NAME |OWNER |QUALIFIER |SEGNUM
--------------------+--------------------+--------------------+------
REPORTMONITOR |PURCHDB |PURCHDB | 1
REPORTMONITOR |PURCHDB |PURCHDB | 2
---------------------------------------------------------------------------
First 2 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
+------+---------------------------------------------------------------
|SEGLEN|DEFINESTRING
+------+---------------------------------------------------------------
| 55| begin insert into PurchDB.ReportMonitor values (:Type,
| 45| CURRENT_DATETIME, USER, :Name, :Owner); end;
---------------------------------------------------------------------------
First 2 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
System.ProcResult.
SYSTEM.PROCRESULT contains information about the result columns in
procedure result sets for procedures returning results of a single
format. Such procedures are created using the WITH RESULT clause.
SYSTEM.PROCRESULT is initially empty, and is updated whenever ALLBASE/SQL
processes a CREATE PROCEDURE statement including the WITH RESULT clause,
or a DROP PROCEDURE or TRANSFER OWNERSHIP statement specifying such a
procedure.
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-27. System.ProcResult
-------------------------------------------------------------------------------------------------
| | | | |
| Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| PROCEDURE | CHAR | 20 | Name of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| RESULTNUM | INTEGER | 4 | Number of the procedure result column |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| LENGTH | INTEGER | 4 | Maximum length of the result column if |
| | | | TYPECODE is 3 (VARCHAR), 9 (NATIVE |
| | | | VARCHAR), or 14 (VARBINARY) or number of |
| | | | bytes in the result column for all other |
| | | | data types |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPECODE | SMALLINT | 2 | Data type of the result column: |
| | | | |
| | | | 0 for INTEGER or SMALLINT (these two are |
| | | | distinguished by the LENGTH field) |
| | | | 1 for BINARY |
| | | | 2 for CHAR (ASCII only) |
| | | | 3 for VARCHAR (ASCII only) |
| | | | 4 for FLOAT or REAL (these two are |
| | | | distinguished by the LENGTH field) |
| | | | 5 for DECIMAL |
| | | | 6 for TID (for ALLBASE/SQL use only) |
| | | | 7 reserved |
| | | | 8 for NATIVE CHAR |
| | | | 9 for NATIVE VARCHAR |
| | | | 10 for DATE |
| | | | 11 for TIME |
| | | | 12 for DATETIME |
| | | | 13 for INTERVAL |
| | | | 14 for VARBINARY |
| | | | 15 for LONG BINARY |
| | | | 16 for LONG VARBINARY |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NULLS | SMALLINT | 2 | Nullability: |
| | | | |
| | | | 0 if the result column cannot contain |
| | | | null values |
| | | | 1 if the result column can contain null |
| | | | values |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-27. System.ProcResult (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) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select procedure, owner, resultnum, length from system.procresult;
--------------------+--------------------+-----------+-----------
PROCEDURE |OWNER |RESULTNUM |LENGTH
--------------------+--------------------+-----------+-----------
PROCESS12 |MANUFDB | 1| 30
PROCESS12 |MANUFDB | 2| 20
---------------------------------------------------------------------------
Number of rows selected is 2
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
select typecode, nulls, precision, scale, languageid from system.procresult;
--------+------+---------+------+----------
TYPECODE|NULLS |PRECISION|SCALE |LANGUAGEID
--------+------+---------+------+----------
0| 0 5| 0| -1
2| 1| 0| 0| 0
---------------------------------------------------------------------------
Number of rows selected is 2
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >
System.Rule.
SYSTEM.RULE contains detailed information about the rules defined for
database tables.
Initially, the table is empty. ALLBASE/SQL updates this table when
processing a CREATE RULE, DROP RULE, or TRANSFER OWNERSHIP statement that
affects a rule.
This table is accessed whenever an INSERT, UPDATE, or DELETE is performed
on a table to determine whether any rules need to be enforced. If there
is a rule for the table that is enforced at UPDATE time, the
SYSTEM.RULECOLUMN table is also checked.
Table 8-28. System.Rule
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| RULENAME | CHAR | 20 | Name of the rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the target table and rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TABLENAME | CHAR | 20 | Name of the target table |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OLDNAME | CHAR | 20 | Old Correlation Name |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NEWNAME | CHAR | 20 | New Correlation Name |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DELETE | CHAR | 2 | DELETE applicable for this rule |
| | | | Y Yes |
| | | | N No |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| INSERT | CHAR | 2 | INSERT applicable for this rule |
| | | | Y Yes |
| | | | N No |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| UPDATE | CHAR | 2 | UPDATE applicable for this rule |
| | | | Y Yes on all columns |
| | | | N No on any columns |
| | | | C Yes on specific columns only |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NUMC | INTEGER | 4 | Number of columns applicable for UPDATE on |
| | | | this rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of the DBEFileSet containing the |
| | | | rule's definition and stored sections |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select rulename, owner, tablename, oldname from system.rule;
--------------------+--------------------+--------------------+-----------------
RULENAME |OWNER |TABLENAME |OLDNAME
--------------------+--------------------+--------------------+-----------------
INSERTREPORT |PURCHDB |REPORTS |OLD
DELETEREPORT |PURCHDB |REPORTS |OLD
UPDATEREPORT |PURCHDB |REPORTS |OLD
CHECKVENDOR |PURCHDB |VENDORS |OLD
---------------------------------------------------------------------------
First 4 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
select newname, delete, insert, update,numc, ctime from system.rule;
--------------------+------+------+------+-----------+----------------
NEWNAME |DELETE|INSERT|UPDATE|NUMC |CTIME
--------------------+------+------+------+-----------+----------------
NEW |N |Y |N | 0|1991121013511100
NEW |Y |N |N | 0|1991121013511200
NEW |N |N |C | 2|1991121013511200
NEW |Y |N |N | 0|1991121216034600
---------------------------------------------------------------------------
First 4 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
select dbefileset from system.rule;
--------------------
DBEFILESET
--------------------
PURCHFS
PURCHFS
PURCHFS
PURCHFS
---------------------------------------------------------------------------
First 4 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r