HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Continued)
Locking of the System Catalog (Continued)
System.RuleColumn.
SYSTEM.RULECOLUMN records the columns listed for the UPDATE statement
type in the rule.
Initially, the table is empty, since the system catalog does not use
rules. ALLBASE/SQL updates the table when processing a CREATE RULE, DROP
RULE, or TRANSFER OWNERSHIP involving a rule.
RULENAME and OWNER uniquely identify a particular rule. The table it is
defined upon may be deduced by searching the SYSTEM.RULE table for a
match on these two columns. The SYSTEM.RULE view contains the TABLENAME.
Table 8-29. System.RuleColumn
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| RULENAME | CHAR | 20 | Name of the rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| COLUMNNAME | CHAR | 20 | Name of the column for which an UPDATE |
| | | | results in firing the rule |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select * from system.rulecolumn;
--------------------+--------------------+--------------------
RULENAME |OWNER |COLUMNNAME
--------------------+--------------------+--------------------
UPDATEREPORT |PURCHDB |REPORTOWNER
UPDATEREPORT |PURCHDB |REPORTNAME
---------------------------------------------------------------------------
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.RuleDef.
SYSTEM.RULEDEF contains character strings that can be used by SQLGEN to
generate the rules associated with a table when recreating a
DBEnvironment. SYSTEM.RULEDEF stores the part of the rule string that
begins with the WHERE clause and continues to the end of the string (not
including the final semicolon).
SYSTEM.RULEDEF is initially empty, and it is updated whenever ALLBASE/SQL
processes a CREATE RULE, DROP RULE, or TRANSFER OWNERSHIP statement on a
rule.
When the rule is dropped, the rows making up the character string of the
rule definition are deleted. If a TRANSFER OWNERSHIP is done on the
rule, the owner field in this table is updated with the name of the new
owner.
Table 8-30. System.RuleDef
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| RULENAME | CHAR | 20 | Name of the rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the rule |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| QUALIFIER | CHAR | 20 | Owner name to be used to qualify any |
| | | | unqualified objects referenced in the rule |
| | | | definition. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGNUM | INTEGER | 4 | Segment Number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGLEN | INTEGER | 4 | Length of segment in bytes (up to 64) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| RULESTRING | CHAR | 64 | RULE character string segment |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
select * from system.ruledef;
--------------------+--------------------+--------------------+-----------+
RULENAME |OWNER |QUALIFIER |SEGNUM |
--------------------+--------------------+--------------------+-----------+
INSERTREPORT |PURCHDB |PETERW | 1|
INSERTREPORT |PURCHDB |PETERW | 2|
DELETEREPORT |PURCHDB |PETERW | 1|
DELETEREPORT |PURCHDB |PETERW | 2|
UPDATEREPORT |PURCHDB |PETERW | 1|
UPDATEREPORT |PURCHDB |PETERW | 2|
---------------------------------------------------------------------------
Number of rows selected is 6
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
isql=> select seglen, rulestring from system.ruledef;
select seglen, rulestring from system.ruledef;
-----------+---------------------------------------------------------------
SEGLEN |RULESTRING
-----------+---------------------------------------------------------------
57|execute procedure PurchDB.ReportMonitor (NEW.ReportName,
26|NEW.ReportOwner, 'INSERT')
57|execute procedure PurchDB.ReportMonitor (OLD.ReportName,
26|OLD.ReportOwner, 'DELETE')
57|execute procedure PurchDB.ReportMonitor (NEW.ReportName,
26|NEW.ReportOwner, 'UPDATE')
---------------------------------------------------------------------------
Number of rows selected is 6
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
System.Section.
SYSTEM.SECTION contains information about modules, sections, and views.
It initially contains the definitions of the system views, and is updated
whenever ALLBASE/SQL processes a statement that creates, modifies, or
drops an object on which a section depends.
You can use the following query on this table to determine which sections
are invalid:
isql=> SELECT OWNER, NAME, STYPE FROM System.Section
> WHERE Valid=0;
Table 8-31. System.Section
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the module, procedure, or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the section or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| DBEFILESET | CHAR | 20 | Name of the DBEFileSet containing the |
| | | | section or view |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SECTION | INTEGER | 4 | Section number: |
| | | | |
| | | | 0 for views |
| | | | 1 to n for sections (numbers are |
| | | | sequentially assigned) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CTIME | CHAR | 16 | Time of creation: yyyymmddhhmmsstt |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPE | INTEGER | 2 | Type of SQL statement represented in the |
| | | | section: |
| | | | |
| | | | 0 for sections that are neither views |
| | | | nor cursors |
| | | | 1 for cursors |
| | | | 2 for views |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| STYPE | INTEGER | 2 | Type of section: |
| | | | |
| | | | 0 for module section |
| | | | 1 for procedure section |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| VALID | INTEGER | 2 | Validity indicator: |
| | | | |
| | | | 0 for view or invalid section |
| | | | 1 for procedure |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OPTFLAG | INTEGER | 2 | Setopt indicator: |
| | | | |
| | | | 0 optimization plan not specified by |
| | | | SETOPT |
| | | | 1 optimization plan specified by SETOPT |
| | | | |
| | | | (See System.Setoptinfo) |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Section;
--------------------+--------------------+--------------------+-----------
NAME |OWNER |DBEFILESET |SECTION
--------------------+--------------------+--------------------+-----------
TABLE |SYSTEM |SYSTEM | 0
COLUMN |SYSTEM |SYSTEM | 0
INDEX |SYSTEM |SYSTEM | 0
SECTION |SYSTEM |SYSTEM | 0
DBEFILESET |SYSTEM |SYSTEM | 0
DBEFILE |SYSTEM |SYSTEM | 0
SPECAUTH |SYSTEM |SYSTEM | 0
TABAUTH |SYSTEM |SYSTEM | 0
COLAUTH |SYSTEM |SYSTEM | 0
MODAUTH |SYSTEM |SYSTEM | 0
GROUP |SYSTEM |SYSTEM | 0
PARTINFO |PURCHDB |SYSTEM | 0
VENDORSTATISTICS |PURCHDB |SYSTEM | 0
CEXP06 |JOHN@BROCK |SYSTEM | 1
CEXP06 |JOHN@BROCK |SYSTEM | 2
CEXP06 |JOHN@BROCK |SYSTEM | 3
--------------------------------------------------------------------------
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
+----------------+------+------+------
|CTIME |TYPE |STYPE |VALID
+----------------+------+------+------
|1985090614181790| 2| 0| 0
|1985090614182250| 2| 0| 0
|1985090614182800| 2| 0| 0
|1985090614183320| 2| 0| 0
|1985090614183950| 2| 0| 0
|1985090614184680| 2| 0| 0
|1985090614185320| 2| 0| 0
|1985090614190000| 2| 0| 0
|1985090614190470| 2| 0| 0
|1985090614191190| 2| 0| 0
|1985090614191690| 2| 0| 0
|1985100915341710| 2| 0| 0
|1985100915342620| 2| 0| 0
|1985101211291030| 0| 0| 0
|1985101211291510| 0| 0| 0
|1985101211292020| 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
System.Setoptinfo.
The SYSTEM.SETOPTINFO view displays the access optimization plan of a
section as specified by the SETOPT statement. The information is stored
in a series of segments of up to 64 bytes. The access plan in
SYSTEM.SETOPTINFO is used by the section if the OPTFLAG column of
SYSTEM.SECTION is set to 1. SYSTEM.SETOPTINFO is initially empty, and it
is updated whenever ALLBASE/SQL processes a SETOPT statement.
Table 8-32. System.Setoptinfo
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NAME | CHAR | 20 | Name of the section |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| OWNER | CHAR | 20 | Owner of the section |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SECTION | INTEGER | 4 | Section number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| TYPE | INTEGER | 2 | Type of section: |
| | | | |
| | | | 0 for module section |
| | | | 1 for procedure section |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGNUM | INTEGER | 4 | Segment number |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SEGLEN | INTEGER | 4 | Segment length (Up to 64) |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SETOPTSTRING | CHAR | 64 | SETOPT byte string segment |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Setoptinfo;
--------------------+--------------------+-----------+-----------+
NAME |OWNER |SECTION |TYPE |
--------------------+--------------------+-----------+-----------+
CEX9 |PROGM1 | 1| 0|
CEX9 |PROGM1 | 2| 0|
CEX9 |PROGM1 | 3| 0|
CEX9 |PROGM1 | 4| 0|
CEX9 |PROGM1 | 5| 0|
CEX9 |PROGM1 | 6| 0|
First 6 rows have been selected.
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> r
-----------+-----------+-------------------------
SEGNUM |SEGLEN |SETOPTSTRING
-----------+-----------+-------------------------
1| 25|setopt general indexscan;
1| 25|setopt general indexscan;
1| 25|setopt general indexscan;
1| 25|setopt general indexscan;
1| 25|setopt general indexscan;
1| 25|setopt general indexscan;
---------------------------------------------------------------------------
Number of rows selected is 6
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]> e