HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG (Continued)
Using UPDATE STATISTICS on System Views
The UPDATE STATISTICS statement, which is not normally allowed on a view,
is accepted on system views. A user with DBA authority can perform an
UPDATE STATISTICS on a system view. ALLBASE/SQL makes an exception for
the system views so that the DBA may monitor the system catalog for
storage use and performance tuning. The following system views are
updated by the UPDATE STATISTICS statement:
SYSTEM.COLUMN SYSTEM.CONSTRAINTINDEX SYSTEM.DBEFILE
SYSTEM.DBEFILESET SYSTEM.HASH SYSTEM.INDEX
SYSTEM.TABLE
You cannot use UPDATE STATISTICS on CATALOG views, and UPDATE STATISTICS
does not update the following pseudotables:
SYSTEM.ACCOUNT SYSTEM.CALL SYSTEM.COUNTER
SYSTEM.TRANSACTION SYSTEM.USER
Initially, only users with DBA authority have SELECT authority on views
owned by SYSTEM. SELECT authority can be granted to non-DBA users. Views
can be created on system views to grant partial access to non-DBA users.
If you do not want to restrict access to system views, you can grant
SELECT authority to the special DBEUserID PUBLIC. However, you also have
the option of keeping the contents of the DBEnvironment confidential.
Locking of the System Catalog
Many ALLBASE/SQL statements have the effect of obtaining locks on parts
of the system catalog. In particular, UPDATE STATISTICS acquires many
locks that can affect concurrency. This overhead can affect performance
considerably. Refer to the appendix, "Locks Held on the System Catalog
by SQL Statements," for a complete list of locks obtained on the system
catalog by ALLBASE/SQL statements.
The rest of this chapter contains the column definition for each system
view, plus a brief description of the purpose of the view, and a sample
SELECT. The examples contain the DBEUserIDs, tables, indexes, views,
groups, and other objects from the sample DBEnvironment. The columns of
each view are listed in the order in which they are created.
Storedsect.System.
STOREDSECT.SYSTEM contains the stored sections for the system DBEFileSet.
Even with DBA authority, you have limited access to STOREDSECT.SYSTEM.
You cannot access the table directly, but you can use ALTER TABLE or
UPDATE STATISTICS on it, as explained in the section "Using the System
Catalog" earlier in this chapter.
You can see an entry for STOREDSECT.SYSTEM in SYSTEM.TABLE.
Storedsect.DBEFileSetName.
The tables named STOREDSECT.DBEFileSetName are the stored section tables
for DBEFileSetName.
These tables are created when you issue the statement GRANT SECTIONSPACE
ON DBEFileSet.
Even with DBA authority, you have limited access to the
STOREDSECT.DBEFileSetName tables. You cannot access these tables
directly, but you can use ALTER TABLE or UPDATE STATISTICS on them, as
explained in the section "Using the System Catalog" earlier in this
chapter.
You can see entries for the STOREDSECT.DBEFileSetName tables in
SYSTEM.TABLE.
System.Account.
SYSTEM.ACCOUNT is a pseudotable that contains information about I/O
resource usage by users currently accessing the DBEnvironment. This
table is initially empty, and ALLBASE/SQL updates it whenever a user
queries the DBEnvironment, including accessing a system catalog view.
SYSTEM.ACCOUNT supports an unlimited number of users, transactions, and
sessions.
Table 8-2. System.Account
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | DBEUserID |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| CID | INTEGER | 4 | Unique connection identifier |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| SID | INTEGER | 4 | Session identifier. If the same USERID has |
| | | | multiple connections to the same |
| | | | DBEnvironment from the same application |
| | | | (including ISQL), all connections have the |
| | | | same SID. If the user has multiple |
| | | | connections to the same DBEnvironment from |
| | | | separate applications, each application (or |
| | | | ISQL session) has a different SID. For more |
| | | | information about multiple DBEnvironment |
| | | | connections, see the section "Using |
| | | | Multiple Connections and Transactions with |
| | | | Timeouts" in the "Using ALLBASE/SQL" |
| | | | chapter of the ALLBASE/SQL Reference |
| | | | Manual. |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPA | INTEGER | 4 | Number of page accesses |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NLB | INTEGER | 4 | Number of log bytes written |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NTP | INTEGER | 4 | Number of temporary pages allocated |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| NPP | INTEGER | 4 | Number of permanent pages allocated |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Account;
--------------------+-----------+-----------+-----------+-----------
USERID |CID |SID |NPA |NLB
--------------------+-----------+-----------+-----------+-----------
JOHN@BROCK | 108| 108| 23| 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]> e
+-----------+-----------
|NTP |NPP
+-----------+-----------
| 0| 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]> e
System.Call.
SYSTEM.CALL is a pseudotable that contains information about current
internal (DBCore) calls, such as whether a user's process is running,
waiting for a lock, waiting for a page to be freed, and so forth.
You can use SYSTEM.CALL to determine which users are accessing the
DBEnvironment. SYSTEM.CALL supports an unlimited number of users,
transactions, and sessions.
Table 8-3. System.Call
----------------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| USERID | CHAR | 20 | DBEUserID |
| | | | |
----------------------------------------------------------------------------------------------------------
| [REV BEG] | | | |
| | | | |
| CID | INTEGER | 4 | Unique connection identifier[REV END] |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| SID | INTEGER | 4 | Session identifier. If the same USERID has |
| | | | multiple connections to the same |
| | | | DBEnvironment from the same application |
| | | | (including ISQL), all connections have the |
| | | | same SID. If the user has multiple |
| | | | connections to the same DBEnvironment from |
| | | | separate applications, each application (or |
| | | | ISQL session) will have a different SID. |
| | | | For more information about multiple |
| | | | DBEnvironment connections, see the section |
| | | | "Using Multiple Connections and |
| | | | Transactions with Timeouts" in the "Using |
| | | | ALLBASE/SQL" chapter of the ALLBASE/SQL |
| | | | Reference Manual. |
| | | | |
----------------------------------------------------------------------------------------------------------
| | | | |
| FUNCTION | INTEGER | 4 | Internal function code: |
| | | | |
| | | | 1 start session |
| | | | 2 terminate session |
| | | | 3 begin transaction |
| | | | 4 end transaction |
| | | | 5 abort transaction |
| | | | 6 status |
| | | | 7 relation lock |
| | | | 8 checkpoint |
| | | | 9 open index scan |
| | | | 10 open thread scan |
| | | | 11 open relation scan |
| | | | 12 next |
| | | | 13 close scan |
| | | | 14 fetch TID |
| | | | 15 fetch first |
| | | | 16 insert |
| | | | 17 delete |
| | | | 20 define DBEFileSet |
| | | | 19 drop DBEFileSet |
| | | | 20 associate DBEFileSet |
| | | | 21 disassociate DBEFileSet |
| | | | 22 update |
| | | | |
----------------------------------------------------------------------------------------------------------
Table 8-3. System.Call (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| FUNCTION (continued) | INTEGER | 4 | Description |
| | | | |
| | | | 23 define table |
| | | | 24 drop table |
| | | | 25 alter table |
| | | | 26 create index |
| | | | 27 drop index |
| | | | 28 set index clustering |
| | | | 29 set index uniqueness |
| | | | 30 read counters |
| | | | 31 sort |
| | | | 32 get statistics |
| | | | 33 read account |
| | | | 34 create DBEFile |
| | | | 35 drop DBEFile |
| | | | 43 display parameter |
| | | | 44 display directory data |
| | | | 45 start server |
| | | | 46 terminate server |
| | | | 47 terminate DBCORE |
| | | | 48 start DBCORE |
| | | | 49 open list scan |
| | | | 50 savepoint |
| | | | 51 restore |
| | | | 52 begin archive |
| | | | 53 end archive |
| | | | 54 abort archive |
| | | | 55 change DBEFile type |
| | | | 56 trace on |
| | | | 57 trace off |
| | | | 58 log memo |
| | | | 59 define parent relationship |
| | | | 60 define child relationship |
| | | | 61 drop parent/child relationship |
| | | | 65 relation to relation |
| | | | 66 rename DBEFile |
| | | | 67 update session info |
| | | | 68 modify scan |
| | | | 69 verify predicate |
| | | | 70 extend DBEFile |
| | | | 71 retrieve single tuple |
| | | | 72 define scratch space |
| | | | 73 drop scratch space |
| | | | 74 add a new log |
| | | | 75 purge an existing log |
| | | | 76 display log info |
| | | | 77 get backup flag status |
| | | | 78 reset backup flag |
| | | | 79 resume recovery |
| | | | 80 terminate recovery |
| | | | 81 reset backup flag |
| | | | 82 version id |
| | | | |
-------------------------------------------------------------------------------------------------
Table 8-3. System.Call (cont.)
-------------------------------------------------------------------------------------------------
| | | | |
| Column Name | Type | Length | Description |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| FUNCTION (continued) | INTEGER | 4 | Description |
| | | | |
| | | | 83 delete a thread |
| | | | 84 quiesce the database |
| | | | 85 unquiesce the database |
| | | | 86 check index |
| | | | 87 open log scan |
| | | | 88 transmit log |
| | | | 89 apply log |
| | | | 90 close log scan |
| | | | 91 get synchronization checkpoint |
| | | | information |
| | | | 92 modify synchronization checkpoint |
| | | | information |
| | | | 93 alter integrity deferral |
| | | | 94 migrate MARSCH for dynamic space |
| | | | expansion |
| | | | 95 bind parent/child relationship |
| | | | 96 set timeout |
| | | | 97 increment table version |
| | | | 98 get table version number |
| | | | 99 put section to offline heap |
| | | | 100 get section from offline heap |
| | | | 101 purge section from offline heap |
| | | | 102 open recovery scan |
| | | | 103 close recovery scan |
| | | | 104 fetch from recovery scan |
| | | | 105 get tran info |
| | | | 106 log persistent information |
| | | | 107 forget record |
| | | | 108 detach transaction |
| | | | 109 alter table type |
| | | | 110 add to columns |
| | | | 111 fix DBCore structures through |
| | | | SQLMigrate |
| | | | 112 switchlog |
| | | | 113 parallel scan |
| | | | 114 truncate table |
| | | | 115 alter transaction attributes |
| | | | 116 detach |
| | | | 117 attach |
| | | | 118 open status scan |
| | | | 119 fetch from status scan |
| | | | 120 close status scan |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| XID | INTEGER | 4 | Unique transaction identifier |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| STATUS | CHAR | 20 | Internal status: |
| | | | |
| | | | 30 Running |
| | | | 31 Waiting on LOCK |
| | | | 32 Waiting on LATCH |
| | | | 33 Waiting for PAGE |
| | | | 35 Waiting for SERVICE |
| | | | 36 Waiting (Other) |
| | | | 37 Throttle Wait |
| | | | |
-------------------------------------------------------------------------------------------------
Example.
SELECT * FROM System.Call;
------------+--------+-----------+-----------+-----------+------------------
USERID |CID |SID |FUNCTION |XID |STATUS
------------+--------+-----------+-----------+-----------+------------------
JOHN@BROCK | 108| 108| 6|-2091903712|Running
---------------------------------------------------------------------------
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