HP 3000 Manuals HP 3000 Manuals
Appendix E Locks Held on the System Catalog by SQL Statements
This appendix shows the locks that you can obtain on the tables in the
system catalog. This appendix may be useful in determining the source of
repeated locking problems. For more information, refer to the chapter
"Concurrency Control Through Locks and Isolation Levels" in the
ALLBASE/SQL Reference Manual.
If an SQL statement listed in Table E-2 (which appears later in this
chapter) is embedded in a procedure or a preprocessed application and a
section is stored for the statement, system catalog pages will also be
locked as follows:
* At INSTALL time (in ISQL), preprocess time, or CREATE PROCEDURE
time (ISQL): exclusive page locks on SYSTEM.SECTION.
* At run time or EXECUTE PROCEDURE time: shared page locks on
SYSTEM.SECTION. If the section is found to be invalid at run time,
all pages accessed for a PREPARE statement could be locked in
addition to the pages normally locked for the statement contained
in the section.
* At VALIDATE time: exclusive page locks on SYSTEM.SECTION pages
containing invalid sections.
As an example, consider an UPDATE statement embedded in an application
program. When you preprocess the application, a page in the
SYSTEM.SECTION table is locked exclusively as the new section is added.
When you run the application, the EXECUTE statement implicitly runs as
the stored section executes. EXECUTE obtains share locks on pages in
SYSTEM.SECTION. If the section becomes invalid and then you run the
application again, the PREPARE statement implicitly runs, obtaining an
exclusive lock on pages in SYSTEM.SECTION; then the EXECUTE statement
implicitly runs as the stored section executes.
Some of the system catalog views overlap with each other, because they
are based on the same underlying table. The following groups of system
views overlap in such a way that a lock on one member of the group
effectively is a lock on all the members of the group:
* Views containing definitions, based on the table HPRDBSS.VIEWDEF:
SYSTEM.VIEWDEF
SYSTEM.CHECKDEF
SYSTEM.RULEDEF
SYSTEM.PROCEDUREDEF
* Views with index information, based on the table HPRDBSS.INDEX:
SYSTEM.INDEX
SYSTEM.HASH
SYSTEM.CONSTRAINTINDEX
* Views containing defaults, based on the table HPRDBSS.COLDEFAULT:
SYSTEM.COLDEFAULT
SYSTEM.PARAMDEFAULT
* Views containing file definitions, based on the table
HPRDBSS.DBEFILE:
SYSTEM.DBEFILE
SYSTEM.TEMPSPACE
* Views containing authorizations, based on the table
HPRDBSS.MODAUTH:
SYSTEM.MODAUTH
SYSTEM.PROCAUTH
Table E-1 lists the base tables from which the system catalog views
are derived.
Table E-1. Mapping the System Views to the Base System Tables
---------------------------------------------------------------------------------------------
- View Name - Table Name -
---------------------------------------------------------------------------------------------
- SYSTEM.CHECKDEF - HPRDBSS.VIEWDEF -
- SYSTEM.COLAUTH - HPRDBSS.COLAUTH -
- SYSTEM.COLDEFAULT - HPRDBSS.COLDEFAULT -
- SYSTEM.COLUMN - HPRDBSS.COLUMN -
- SYSTEM.CONSTRAINT - HPRDBSS.CONSTRAINT -
- SYSTEM.CONSTRAINTCOL - HPRDBSS.CONSTRAINTCOL -
- SYSTEM.CONSTRAINTINDEX - HPRDBSS.INDEX -
- SYSTEM.DBEFILE - HPRDBSS.DBEFILE -
- SYSTEM.DBEFILESET - HPRDBSS.DBEFILESET -
- SYSTEM.GROUP - HPRDBSS.GROUP -
- SYSTEM.HASH - HPRDBSS.INDEX[REV BEG] -
- SYSTEM.IMAGEKEY - HPRDBSS.INDEX -
- SYSTEM.INDEX - HPRDBSS.INDEX -
- SYSTEM.INSTALLAUTH - HPRDBSS.MODAUTH[REV END] -
- SYSTEM.MODAUTH - HPRDBSS.MODAUTH -
- SYSTEM.PARAMDEFAULT - HPRDBSS.COLDEFAULT -
- SYSTEM.PARAMETER - HPRDBSS.PARAMETER -
- SYSTEM.PARTITION - HPRDBSS.PARTITION -
- SYSTEM.PROCAUTH - HPRDBSS.MODAUTH -
- SYSTEM.PROCEDURE - HPRDBSS.PROCEDURE -
- SYSTEM.PROCEDUREDEF - HPRDBSS.VIEWDEF -
- SYSTEM.PROCRESULT - HPRDBSS.PROCRESULT -
- SYSTEM.RULE - HPRDBSS.RULE -
- SYSTEM.RULECOLUMN - HPRDBSS.RULECOLUMN -
- SYSTEM.RULEDEF - HPRDBSS.VIEWDEF -
- SYSTEM.SECTION - HPRDBSS.SECTION -
- SYSTEM.SETOPTINFO - HPRDBSS.SETOPTINFO -
- SYSTEM.SPACEAUTH - HPRDBSS.SPACEAUTH -
- SYSTEM.SPACEDEFAULT - HPRDBSS.SPACEDEFAULT -
- SYSTEM.SPECAUTH - HPRDBSS.SPECAUTH -
- SYSTEM.TABAUTH - HPRDBSS.TABAUTH -
- SYSTEM.TABLE - HPRDBSS.TABLE -
- SYSTEM.TEMPSPACE - HPRDBSS.DBEFILE[REV BEG] -
- SYSTEM.TPINDEX - HPRDBSS.INDEX[REV END] -
- SYSTEM.VIEWDEF - HPRDBSS.VIEWDEF -
---------------------------------------------------------------------------------------------
Locks on the system catalog are held to the end of the transaction, no
matter what isolation level is used, to ensure the integrity of database
objects while they are being used.
Whenever the HPRDBSS.SECTION table is locked, similar locks are placed on
a STOREDSECT.DBEFileSetName table.
Some statements by their nature incorporate one or more other SQL
statements. When a statement from the following list includes other SQL
statements, locks may also be obtained for each statement incorporated:
* CREATE PROCEDURE (most SQL statements)
* CREATE SCHEMA (data definition statements)
* CREATE VIEW (SELECT statement)
* DECLARE CURSOR (SELECT statement or EXECUTE PROCEDURE statement)
* EXECUTE IMMEDIATE (most SQL statements)
* EXECUTE (most SQL statements, when not valid)
* GENPLAN (SELECT, UPDATE, or DELETE statements)
* PREPARE (most SQL statements)
* VALIDATE (SQL statements contained in stored sections or
procedures)
The following group of statements used only within procedures do not
obtain locks:
* Assignment
* BEGIN...END
* DECLARE Variable
* GOTO
* IF...THEN...ELSE...ENDIF
* Labeled Statement
* PRINT
* RETURN
* WHILE...DO...ENDWHILE
NOTE The information in this appendix is general in nature, and it shows
worst case locking for a particular SQL statement. Not all locks
are necessarily requested in all instances.
Table E-2. Locks Held on the System Catalog by SQL Statements
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- ADD DBEFILE - HPRDBSS.DBEFILE - Exclusive -
- - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- ADD TO GROUP - HPRDBSS.GROUP - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- ADVANCE - HPRDBSS.SECTION - Shared -
| | Same as statement in procedure | Same as statement in procedure |
| | if not valid | if not valid |
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- ALTER DBEFILE - HPRDBSS.DBEFILE - Exclusive -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- ALTER TABLE - HPRDBSS.CHECKDEF - Exclusive -
- - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.COLDEFAULT - Exclusive -
- - HPRDBSS.CONSTRAINT - Exclusive -
- - HPRDBSS.CONSTRAINTCOL - Exclusive -
- - HPRDBSS.CONSTRAINTINDEX - Exclusive -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- BEGIN ARCHIVE - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- BEGIN DECLARE SECTION - - -
-----------------------------------------------------------------------------------------------
- BEGIN WORK - - -
-----------------------------------------------------------------------------------------------
- CHECKPOINT - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- CLOSE - HPRDBSS.SECTION - Shared -
- (select cursor) - - -
-----------------------------------------------------------------------------------------------
- CLOSE - HPRDBSS.SECTION - Shared -
| (procedure cursor) | Same as statement in procedure | Same as statement in procedure |
| | if not valid | if not valid |
-----------------------------------------------------------------------------------------------
- COMMIT ARCHIVE - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- COMMIT WORK - - -
-----------------------------------------------------------------------------------------------
- CONNECT - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- CREATE DBEFILE - HPRDBSS.DBEFILE - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TEMPSPACE - Shared -
-----------------------------------------------------------------------------------------------
- CREATE DBEFILESET - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- CREATE GROUP - HPRDBSS.COLAUTH - Shared -
- - HPRDBSS.GROUP - Exclusive -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
-----------------------------------------------------------------------------------------------
- CREATE INDEX - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.INDEX - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- CREATE PROCEDURE - HPRDBSS.GROUP - Shared -
- - HPRDBSS.PROCEDURE - Exclusive -
- - HPRDBSS.PROCEDUREDEF - Exclusive -
- - HPRDBSS.PARAMDEFAULT - Exclusive -
- - HPRDBSS.PARAMETER - Exclusive -
- - HPRDBSS.PROCRESULT - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
| | Views accessed by SQL | Same as for SQL statements in |
| | statements in the procedure | the procedure |
-----------------------------------------------------------------------------------------------
- CREATE RULE - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.RULE - Exclusive -
- - HPRDBSS.RULECOLUMN - Exclusive -
- - HPRDBSS.RULEDEF - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- CREATE SCHEMA - HPRDBSS.COLAUTH - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
| | Views accessed by data | Same as for data definition |
| | definition statements in the | statements in the schema |
| | schema | |
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- CREATE TABLE - HPRDBSS.CHECKDEF - Exclusive -
- - HPRDBSS.COLAUTH - Shared -
- - HPRDBSS.COLDEFAULT - Exclusive -
- - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.CONSTRAINT - Exclusive -
- - HPRDBSS.CONSTRAINTCOL - Exclusive -
- - HPRDBSS.CONSTRAINTINDEX - Exclusive -
- - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.HASH - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
| | HPRDBSS.TABAUTH | Exclusive (for PUBLIC or |
| | | PUBLICREAD tables) |
- - HPRDBSS.TABLE - Exclusive and Shared -
-----------------------------------------------------------------------------------------------
- CREATE TEMPSPACE - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TEMPSPACE - Exclusive -
-----------------------------------------------------------------------------------------------
- CREATE VIEW - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABLE - Exclusive -
- - HPRDBSS.VIEWDEF - Exclusive -
- - Same as for SELECT - Same as for SELECT -
-----------------------------------------------------------------------------------------------
| DECLARE CURSOR | Same as for SELECT or EXECUTE | Same as for SELECT or EXECUTE |
| | PROCEDURE | PROCEDURE |
-----------------------------------------------------------------------------------------------
- DELETE - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.CONSTRAINT - Shared -
- - HPRDBSS.CONSTRAINTINDEX - Shared -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.HASH - Shared -
- - HPRDBSS.INDEX - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.PROCEDURE - Shared -
- - HPRDBSS.RULE - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- DELETE WHERE - HPRDBSS.COLUMN - Shared -
- CURRENT - HPRDBSS.CONSTRAINT - Shared -
- - HPRDBSS.CONSTRAINTINDEX - Shared -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.INDEX - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.PROCEDURE - Shared -
- - HPRDBSS.RULE - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
-----------------------------------------------------------------------------------------------
- DESCRIBE - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
| | Same as for statement being | Same as for statement being |
| | DESCRIBED | DESCRIBED |
-----------------------------------------------------------------------------------------------
- DROP DBEFILE - HPRDBSS.DBEFILE - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TEMPSPACE - Exclusive -
-----------------------------------------------------------------------------------------------
- DROP DBEFILESET - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- DROP GROUP - HPRDBSS.COLAUTH - Exclusive -
- - HPRDBSS.CONSTRAINTCOL - Exclusive -
- - HPRDBSS.GROUP - Exclusive -
- - HPRDBSS.MODAUTH - Exclusive -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Exclusive -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Shared -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- DROP INDEX - HPRDBSS.GROUP - Shared -
- - HPRDBSS.INDEX - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- DROP MODULE - HPRDBSS.GROUP - Shared -
- - HPRDBSS.MODAUTH - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
-----------------------------------------------------------------------------------------------
- DROP PROCEDURE - HPRDBSS.GROUP - Shared -
- - HPRDBSS.MODAUTH - Exclusive -
- - HPRDBSS.PARAMETER - Exclusive -
- - HPRDBSS.PARAMDEFAULT - Exclusive -
- - HPRDBSS.PROCEDURE - Exclusive -
- - HPRDBSS.PROCEDUREDEF - Exclusive -
- - HPRDBSS.PROCRESULT - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- DROP RULE - HPRDBSS.GROUP - Shared -
- - HPRDBSS.RULE - Exclusive -
- - HPRDBSS.RULECOLUMN - Exclusive -
- - HPRDBSS.RULEDEF - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- DROP TABLE - HPRDBSS.COLAUTH - Exclusive -
- - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.CONSTRAINT - Exclusive and Shared -
- - HPRDBSS.CONSTRAINTCOL - Exclusive -
- - HPRDBSS.CONSTRAINTINDEX - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.HASH - Exclusive -
- - HPRDBSS.INDEX - Exclusive -
- - HPRDBSS.RULE - Exclusive -
- - HPRDBSS.RULECOLUMN - Exclusive -
- - HPRDBSS.RULEDEF - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Exclusive -
- - HPRDBSS.VIEWDEF - Exclusive -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- DROP TEMPSPACE - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TEMPSPACE - Exclusive -
-----------------------------------------------------------------------------------------------
- DROP VIEW - HPRDBSS.COLAUTH - Exclusive -
- - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Exclusive -
- - HPRDBSS.VIEWDEF - Exclusive -
-----------------------------------------------------------------------------------------------
- END DECLARE SECTION - - -
-----------------------------------------------------------------------------------------------
- EXECUTE - HPRDBSS.GROUP - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
| | Views accessed by SQL | Same as for statements |
| | statements executed if not | executed if not valid |
| | valid | |
-----------------------------------------------------------------------------------------------
| EXECUTE IMMEDIATE | Views accessed by SQL | Same as for statements |
| | statements executed | executed |
-----------------------------------------------------------------------------------------------
- FETCH - - -
-----------------------------------------------------------------------------------------------
- EXECUTE PROCEDURE - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.PARAMDEFAULT - Shared -
- - HPRDBSS.PARAMETER - Shared -
- - HPRDBSS.PROCEDURE - Shared -
- - HPRDBSS.PROCRESULT - Shared -
- - HPRDBSS.SECTION - Shared -
| | Same as statement in procedure | Same as statement in procedure |
| | if not valid | if not valid |
-----------------------------------------------------------------------------------------------
- GENPLAN - HPRDBSS.PLAN - Exclusive -
- - Same as SELECT - Same as SELECT -
-----------------------------------------------------------------------------------------------
- GRANT Table Authority - HPRDBSS.COLAUTH - Exclusive -
- GRANT UPDATE - HPRDBSS.GROUP - Shared -
- GRANT REFERENCES - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Shared -
-----------------------------------------------------------------------------------------------
- GRANT RUN - HPRDBSS.GROUP - Shared -
- GRANT EXECUTE - HPRDBSS.MODAUTH - Exclusive[REV BEG] -
- GRANT INSTALL - HPRDBSS.SPECAUTH - Shared[REV END] -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
--------------------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
--------------------------------------------------------------------------------------------------------
| [REV BEG] | | |
| GRANT Special Authorities | HPRDBSS.SPECAUTH | Exclusive |
- - HPRDBSS.GROUP - Shared -
--------------------------------------------------------------------------------------------------------
- GRANT SECTIONSPACE - HPRDBSS.SPACEAUTH - Exclusive -
- GRANT TABLESPACE - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.GROUP - Shared [REV END] -
--------------------------------------------------------------------------------------------------------
- INCLUDE - - -
--------------------------------------------------------------------------------------------------------
- INSERT - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.COLDEFAULT - Shared -
- - HPRDBSS.CONSTRAINT - Shared -
- - HPRDBSS.CONSTRAINTINDEX - Shared -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.HASH - Shared -
- - HPRDBSS.INDEX - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.PROCEDURE - Shared -
- - HPRDBSS.RULE - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
--------------------------------------------------------------------------------------------------------
- LOCK TABLE - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
--------------------------------------------------------------------------------------------------------
- OPEN - HPRDBSS.GROUP - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
| | Same as for SELECT or EXECUTE | Same as for SELECT or EXECUTE |
| | PROCEDURE in DECLARE CURSOR | PROCEDURE in DECLARE CURSOR |
| | statement | statement |
--------------------------------------------------------------------------------------------------------
- PREPARE (Permanent - HPRDBSS.DBEFILESET - Shared -
| sections created with | HPRDBSS.GROUP | Shared |
| ISQL) | | |
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
| | Views accessed for SQL | Same as for statements |
| | statements prepared | prepared |
--------------------------------------------------------------------------------------------------------
- PREPARE (Temporary - HPRDBSS.DBEFILESET - Shared -
- sections created in - HPRDBSS.GROUP - Shared -
- applications) - HPRDBSS.SPECAUTH - Shared -
| | Views accessed for statements | Same as for statements |
| | PREPAREd | prepared |
--------------------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- RAISE ERROR - - -
-----------------------------------------------------------------------------------------------
- REFETCH - - -
-----------------------------------------------------------------------------------------------
- RELEASE - - -
-----------------------------------------------------------------------------------------------
- REMOVE DBEFILE - HPRDBSS.DBEFILE - Exclusive -
- - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TEMPSPACE - Exclusive -
-----------------------------------------------------------------------------------------------
- REMOVE FROM GROUP - HPRDBSS.CONSTRAINTCOL - Shared -
- - HPRDBSS.GROUP - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Exclusive -
-----------------------------------------------------------------------------------------------
- RESET - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- REVOKE Table Authority - HPRDBSS.COLAUTH - Exclusive -
- REVOKE UPDATE - HPRDBSS.CONSTRAINTCOL - Shared -
- REVOKE REFERENCES - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Shared -
-----------------------------------------------------------------------------------------------
- REVOKE EXECUTE - HPRDBSS.GROUP - Shared -
- REVOKE RUN - HPRDBSS.MODAUTH - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- REVOKE Special - HPRDBSS.CONSTRAINTCOL - Shared -
- Authorities - HPRDBSS.GROUP - Shared -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Exclusive -
-----------------------------------------------------------------------------------------------
- ROLLBACK WORK - - -
-----------------------------------------------------------------------------------------------
- SAVEPOINT - - -
-----------------------------------------------------------------------------------------------
- SELECT - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
| | HPRDBSS.SECTION | Shared in user programs; none |
| | | in ISQL |
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
- If table has index: - HPRDBSS.CONSTRAINTINDEX - Shared -
- - HPRDBSS.HASH - Shared -
- - HPRDBSS.INDEX - Shared -
-----------------------------------------------------------------------------------------------
- SQLEXPLAIN - - -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
| START DBE | HPRDBSS.SPECAUTH | Shared (Exclusive the first |
| | | time the statement is used |
| | | after the DBECreator has |
| | | changed following a restore) |
- - HPRDBSS.GROUP - Shared -
-----------------------------------------------------------------------------------------------
| START DBE NEW | | System catalog is being |
| | | created |
-----------------------------------------------------------------------------------------------
- START DBE NEWLOG - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- START DBE NEWLOG - HPRDBSS.SPECAUTH - Shared -
- (After migration) - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- STOP DBE - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- TERMINATE USER - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- TRANSFER OWNERSHIP - HPRDBSS.GROUP - Shared -
- (PROCEDURE) - HPRDBSS.PARAMDEFAULT - Exclusive -
- - HPRDBSS.PARAMETER - Exclusive -
- - HPRDBSS.MODAUTH - Exclusive -
- - HPRDBSS.PROCEDURE - Exclusive -
- - HPRDBSS.PROCEDUREDEF - Exclusive -
- - HPRDBSS.PROCRESULT - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
- - HPRDBSS.SPECAUTH - Exclusive -
-----------------------------------------------------------------------------------------------
- TRANSFER OWNERSHIP - HPRDBSS.CHECKDEF - Exclusive -
- (TABLE) - HPRDBSS.CONSTRAINT - Exclusive -
- - HPRDBSS.CONSTRAINTCOL - Exclusive -
- - HPRDBSS.COLAUTH - Exclusive -
- - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.CONSTRAINTINDEX - Exclusive -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.HASH - Exclusive -
- - HPRDBSS.INDEX - Exclusive -
- - HPRDBSS.RULE - Exclusive -
- - HPRDBSS.RULECOLUMN - Exclusive -
- - HPRDBSS.RULEDEF - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Exclusive -
-----------------------------------------------------------------------------------------------
- TRANSFER OWNERSHIP - HPRDBSS.CONSTRAINT - Exclusive -
- (VIEW) - HPRDBSS.COLAUTH - Exclusive -
- - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Exclusive -
- - HPRDBSS.TABLE - Exclusive -
- - HPRDBSS.VIEWDEF - Exclusive -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- TRANSFER OWNERSHIP - HPRDBSS.GROUP - Exclusive -
- (GROUP) - HPRDBSS.SPECAUTH - Shared -
-----------------------------------------------------------------------------------------------
- UPDATE - HPRDBSS.COLAUTH - Shared -
- - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.CONSTRAINT - Shared -
- - HPRDBSS.CONSTRAINTINDEX - Shared -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.HASH - Shared -
- - HPRDBSS.INDEX - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.PROCEDURE - Shared -
- - HPRDBSS.RULE - Shared -
- - HPRDBSS.RULECOLUMN - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
-----------------------------------------------------------------------------------------------
- UPDATE STATISTICS - HPRDBSS.COLUMN - Exclusive -
- - HPRDBSS.CONSTRAINT - Shared -
- - HPRDBSS.CONSTRAINTINDEX - Exclusive -
- - HPRDBSS.DBEFILE - Exclusive -
- - HPRDBSS.DBEFILESET - Exclusive -
- - HPRDBSS.HASH - Exclusive -
- - HPRDBSS.INDEX - Exclusive -
- - HPRDBSS.TABLE - Exclusive -
- - HPRDBSS.TEMPSPACE - Exclusive -
-----------------------------------------------------------------------------------------------
- UPDATE WHERE - HPRDBSS.COLAUTH - Shared -
- CURRENT - HPRDBSS.COLUMN - Shared -
- - HPRDBSS.CONSTRAINT - Shared -
- - HPRDBSS.DBEFILESET - Shared -
- - HPRDBSS.GROUP - Shared -
- - HPRDBSS.INDEX - Shared -
- - HPRDBSS.MODAUTH - Shared -
- - HPRDBSS.PROCEDURE - Shared -
- - HPRDBSS.RULE - Shared -
- - HPRDBSS.RULECOLUMN - Shared -
- - HPRDBSS.SECTION - Shared -
- - HPRDBSS.SPECAUTH - Shared -
- - HPRDBSS.TABAUTH - Shared -
- - HPRDBSS.TABLE - Shared -
-----------------------------------------------------------------------------------------------
Table E-2. Locks Held on the System Catalog by SQL Statements (cont.)
-----------------------------------------------------------------------------------------------
- SQL Statement - System Table - Type of Lock -
-----------------------------------------------------------------------------------------------
- VALIDATE MODULE or - HPRDBSS.MODAUTH - Shared -
- VALIDATE PROCEDURE - HPRDBSS.PROCEDURE - Exclusive -
- - HPRDBSS.SECTION - Exclusive -
| | Views accessed for the SQL | Same as for sections being |
| | statements in the sections | validated |
| | being validated | |
-----------------------------------------------------------------------------------------------
- WHENEVER - - -
-----------------------------------------------------------------------------------------------