Appendix B.2 Lock mode for utilities

Table B-2 Lock modes for the database load utility through Table B-17 Lock modes for the integrity check utility show the lock modes for each of the utilities.

The utilities listed as follows are not described here, because they do not provide the automatic lock function (the database definition utility is the same as for a definition SQL; see the HiRDB Version 9 UAP Development Guide):

Legend:
env: environment
RD-A: RDAREA
Tbl ID: Table ID
IX ID: Index ID
NW tbl: Table (during NOWAIT search)
Rsrc mgmt tbl: Resource management table
Seq: Sequence
acqst'n: acquisition
Note
When data loading is executed on a LOB column structure base table only, the lock mode is the same as for data loading by table when loading data by table or the same as for data loading by RDAREA when loading data by RDAREA.

#1: For index loading by RDAREA, lock is not placed on a non-partitioning key index. Because lock is placed on a row-partitioned index in units of RDAREAs, data loading by RDAREA can be executed concurrently.

#2: During data deletion processing, the EX lock mode takes place. For other operations, if the option statement specifies nowait=no, the EX lock mode takes place; if it specifies nowait=yes, the PU lock mode takes place.

#3: For a partitioned table, lock is not placed on an RDAREA that is not subject to data loading.

Table B-3 Lock modes for the database load utility (for shared tables)

Exe envResource
TableIndexNW tblRsrc mgmt tblSequence generator
Updatable BESRef-only BESSR for upd BESSR for ref BESIX IDSeq generator IDRD-A
SRTbl IDSRTbl IDNbr btch acq mthdOthr acq mthd
Data loadEXEXEXEXEXEXEXEXSREXSUSU
Legend:
Exe env: Execution environment
Updtable BES: Updatable back-end server
SR: Shared RDAREA
Tbl ID: Table ID
Ref-only BES: Reference-only back-end server
SR for upd BES: Shared RDAREA for updatable back-end server
SR for ref BES: Shared RDAREA for reference-only back-end server
IX ID: Index ID
NW tbl: Table (during NOWAIT search)
Rsrc mgmt tbl: Resource management table
Seq: Sequence
Nbr btch acq mthd: Number batch acquisition method
Othr acq mthd: Other acqst'n methods

Table B-4 Lock modes for the database structure modification utility

Exe envResource
DB struct mod utilityData dict tableRD AREA statusRD AREATableRes mgmt tableInner replica config mgmt infoRep group config mgmt infoTemp table entity mgmtTemp table RD AREA mgmt infoTemp table RD AREA info
RDAREA additionEXEXEX#4EX#4--------EX#5EX#5EX#5
RDAREA expansionEXEXEX#4PU(EX)#4--EX#4----------
RDAREA reinitEXEXEX#4EX#4EX------EX#5EX#5EX#5
RDAREA deletionEXEXEX#4EX#4----EX#4EX#4EX#5EX#5EX#5
Modification of data dictionary table attribute definitionEXEX--EX#2--------------
Modification of RDAREA attributeEXEXEXEX--------------
Changing an RDAREA from a HiRDB single server configuration to a HiRDB parallel server configurationEXEXEXEX--------------
Changing to a back-end server structure#1EXEXEXEX--------------
Moving an RDAREAEXEXEX#4EX#4--------------
Registration of a generation of a HiRDB file system areaEXEX------------------
Deletion of a generation of a HiRDB file system areaEXEX------------------
Definition of an RDAREA replicaEXEXEX#4EX#4----EX#4EX#4------
Copying RDAREA configuration informationEXEXEX#4EX#4----EX#4EX#4------
Integration of RDAREAsEXEXEX#3, #4EX#3, #4----EX#4EX#4------
Auditor registrationEXEX------------------
Creation of an audit trail tableEXEX--EX--EX----------
Legend:
config: configuration
DB: Database
Data dict table: Data dictionary table
Exe: Execution
info: information
mgmt: management
mod: modification
reinit: reinitialization
Rep: Replica
Res: Resources
struct: structure
Tbl: Table
Temp: Temporary

#1: This applies only to a HiRDB parallel server configuration, and is not applicable to a HiRDB single server configuration.

#2: Applies only to the data dictionary RDAREA.

#3: Applicable to both original and replica RDAREAs.

#4: Applicable to both updatable back-end servers and referencing-only back-end servers in the case of shared RDAREAs.

#5: Applicable to temporary table RDAREAs only.

Table B-5 Lock modes for the database reorganization utility

Execution environmentResource
TableIndexNW tblRes mgmt tableIX info fileUser LOB RDAREA#1
RD AREA#1TBL IDRD AREA#1IX ID
Unloading by table
(-k unld))
unldenq=tblenq is specified in option statementSRPRSR#2----SR--SR
unldenq=rdenq is specified in option statementPRSRPR#2----SR--PR
unldenq=nowait is specified in option statementSR--SR#2--SRSR--SR
Unloading by RDAREA (-k unld)unldenq=tblenq is specified in option statementSRPRSR#2----SR--SR
unldenq=rdenq is specified in option statementPRSRPR#2----SR--PR
unldenq=nowait is specified in option statementSR--SR#2--SRSR--SR
Reloading by table
(-k reld)
SUEXSUEXEXSR--SU
Reloading by RDAREA (-k reld)EXSRSUEX#3--SR--EX
Reorganization by table
(-k rorg)
SUEXSUEXEXSR--SU
Reorganization by RDAREA
(-k rorg)
EXSR#7SUEX#3--SR--EX
Batch creation of index
(-k ixmk)
SRSRSUEX--SR----
Re-creation of index
(-k ixrc)
index statement specified#4EX [Figure] SR#5-- [Figure] SR#5SUEX--SR--EX [Figure] --#5
SU [Figure] SR#5EX [Figure] SR#5EX [Figure] --#5SU [Figure] --#5
idxname statement specifiedSUEXSUEXEXSR----
Reorganization of index
(-k ixor)#6
SRPRSUEX--SR----
EX--
Batch creation of plug-in index (-k ixmk)SRSRSUEX--SREX--
Re-creation of plug-in index
(-k ixrc)
index statement specified#4EX [Figure] SR#5-- [Figure] SR#5SUEX--SREXEX [Figure] --#5
SU [Figure] SR#5EX [Figure] SR#5EX [Figure] --#5SU [Figure] --#5
idxname statement specifiedSUEXSUEXEXSR--SU
Reloading of LOB data only (-k reld)SRPR------SR--EX
Reorganization of LOB data only (-k rorg)SUEXSR#2--EXSR--SU
Legend:
RD AREA: RDAREA
TBL ID: Table ID
IX ID: Index ID
NW tbl: Table (during NOWAIT search)
Res mgmt table: Resource management table
IX info file: Index information file
Note
When reload or reorganization is executed on a LOB column structure base table only, the lock mode is the same as for reloading or reorganization by table when loading or reorganizing data by table or the same as for reloading or reorganization by RDAREA when reloading or reorganizing data by RDAREA.
#1
An RDAREA that is not the target of processing is not locked.
#2
If data is unloaded in physical order, lock is not placed.
#3
Lock is not placed on a non-partitioned index during reload processing. Lock is placed on a row-partitioned index, but reloading and reorganization in units of RDAREAs can be performed concurrently because the lock is placed in units of RDAREAs.
#4
The upper row applies to a row-partitioned index; the lower row applies to a non-partitioned index.
#5
The lock mode during unload processing on the index information (from KFPL00725-I to KFPL00726-I) is changed during index creation (from KFPL00715-I to KFPL00716-I).
#6
The upper row applies when the index storage RDAREA is in command shutdown status; the lower row applies when it is not in command shutdown status.
#7
Lock is not placed during reloading.

Table B-6 Lock modes for the database reorganization utility (for shared tables)

Exe envResource
TableIndexNW tblRes mgmt tableIX info file
Updatable back-end serverRef-only back-end serverShared RD AREA for updatbl back-end serverShared RD AREA for ref-only back-end serverIX ID
Shared RD AREATbl IDShared RD AREATbl ID
Unload by table
(-k unld)
SRPR----SR#1------SR--
Reload by table (-k reld)EXEXEXEXEXEXEXEXSR--
Reorg by table (-k rorg)EXEXEXEXEXEXEXEXSR--
Batch index creation (-k ixmk)SRSR----EXEXEX--SR--
Re-crtn of index (-k ixrc, and index stmt specif)#2EX [Figure] SREX [Figure] SREXEXEXEXEXEX [Figure] --SR--
Re-crtn of index (-k ixrc, and idxname stmt specif)EXEXEXEXEXEXEXEXSR--
Index reorg (-k ixor)SRPR----EXEXEX--SR--
Legend:
Exe: Execution
env: environment
info: information
IX: Index
mgmt: management
NW tbl: Table (during NOWAIT search)
RD AREA: RDAREA
Re-crtn: Re-creation
Ref: referencing
Reorg: Reorganization
Res mgmt table: Resource management table
specif: specified
stmt: statement
Tbl: Table
updatbl: updatable
#1: If data is unloaded in physical order, lock is not placed.
#2: The lock mode for index information during an unload operation (from KFPL00725-I through KFPL00726-I) is changed when the index is loaded (from KFPL00715-I to KFPL00716-I).

Table B-7 Lock modes for the rebalancing utility

Execution environmentResource
RDAREATableNW tblIndexRebalance#Preprocessed table
Shared modeSUEXEXEXEXSR
Exclusive modeSUEXEXEX--SR
Legend:
NW tbl: Table (during NOWAIT search)

#: For the rebalancing resources, lock is placed at the single server or front-end server.

Table B-8 Lock modes for free page release utility

Execution environmentResource
TableNW tblIndex
RD-ARsrc mgmt tblTbl IDrclmtrxn cmplRD-ARsrc mgmt tblIX IDrclmtrxn cmpl
Releasing free pages from tableSUSR--EX--SR----------
Releasing free segments from table (-j)EXSR--EX--------------
Releasing free pages and free segments from table (-a)SUSRSREXPRSR----------
Releasing free pages from index----------SRSUSREXEXPR
Releasing free segments from index (-j)----SR------EXSR--EX--
Releasing free pages and free segments from index (-a)----SR----SRSUSREXEXPR
Legend:
RD-A: RDAREA
Rsrc mgmt tbl: Resource management table
Tbl ID: Table ID
rclm: Free page release utility pdreclaim
trxn cmpl: Wait for transaction completion
NW tbl: Table (during NOWAIT search)
IX ID: Index ID

Table B-9 Lock modes for the free page release utility (for shared tables)

Exe envResource
TableIndex
Upd BESRef-only BESRes mgm tblrcltrxn cmpSR for upd BESSR for ref BESRes mgm tblIX IDrcltrxn cmp
SRTbl IDSRTbl ID
Releasing free pages from tableEXEXEXEXSREX--------------
Releasing free segments from table (-j)EXEXEXEXSREX--------------
Releasing free pages and free segments from table (-a)EXEXEXEXSREXPR------------
Releasing free pages from index--SR----------EXEXSREXEXPR
Releasing free segments from index (-j)--SR----------EXEXSR--EX--
Releasing free pages and free segments from index (-a)--SR----------EXEXSREXEXPR
Legend:
Exe env: Execution environment
Upd BES: Updatable back-end server
SR: Shared RDAREA
Tbl ID: Table ID
Ref-only BES: Reference-only back-end server
Res mgm tbl: Resource management table
rcl: Free page release utility pdreclaim
trxn cmp: Wait for transaction completion
SR for upd BES: Shared RDARA for updatable back-end server
SR for ref BES: Shared RDAREA for reference-only back-end server
IX ID: Index ID

Table B-10 Lock modes for the global buffer residence utility

Exe envResource
TableIndex
RDAREARes mgm tblNW tblRDAREARes mgm tblIndex ID
Reading data page
(-k table)
SRSRSR------
Reading index page
(-k index)
----SRSRSRSR
Legend:
env: environment
Exe: Execution
NW tbl: Table (during NOWAIT search)
Res mgm tbl: Resource management table

Table B-11 Lock modes for the global buffer residence utility (for shared tables)

Exe envResource
TableIndex
Updatable back-end serverReferencing-only back-end serverUpdatable back-end serverReferencing-only back-end server
RRes mgm tblNW tblRRes mgm tblNW tblRRes mgm tblIX IDRRes mgm tblIX ID
Reading data page
(-k table)
SRSRSRSRSRSR------------
Reading index page
(-k index)
----SR----SRSRSRSRSRSRSR
Legend:
env: environment
Exe: Execution
IX: Index
NW tbl: Table (during NOWAIT search)
R: RDAREA
Res mgm tbl: Resource management table

Table B-12 Lock modes for the database condition analysis utility

Execution environmentResource
RDAREARes mgmt tableTableTable (during NOWAIT search)#1HiRDB file
For tableFor indexSpecified RDAREA
Analysis by RDAREALogical analysis----SRSR--SR--
Physical analysis----SRSR----SR
Analysis by tableSR----SRSRSR--
Analysis by index--SR--SRSRSR--
Facility for accumulating condition analysis resultsSRSRSRSRSR----
Facility for predicting reorganization time#2SRSRSRSRSRSR--
Legend:
Res mgmt table: Resource management table

#1: Lock is placed on the table (during NOWAIT search) only when the -d option is specified.

#2: This lock is applicable when the -m option is specified.

Table B-13 Lock modes for the optimizing information collection utility

Execution environmentResource
RDAREAResources management tableTableTable (during NOWAIT search)Data dictionary table
For tableFor indexFor tableFor indexRowKey
Acquisition of optimization informationSRSRSRSRSR/SU#1SRPR#1/EX#2PR#1/EX#2
Deletion of optimization information--------SU#1--PR#1/EX#2PR#1/EX#2
Registering optimization information--------SU#1--PR#1/EX#2PR#1/EX#2
Saving optimization information--------SU#1--PR#1PR#1

#1: Lock is placed temporarily during data dictionary table retrieval.

#2: Lock is placed during data dictionary table update processing.

Table B-14 Lock modes for the database copy utility

Execution environmentResource
Data dictionary tableRDAREADirectory block 6#1Resources management tableIn-memory synchronization
-M optionxPRPR------
rOther than in-memory RDAREAPRPR------
In-memory RDAREAPR------PR
s----PRSR#2--

#1: This is dictionary segment information or user directory segment information.

#2: Lock is obtained when RDAREA automatic extension is specified.

Table B-15 Lock modes for the database recovery utility

Execution environmentResource
Data dictionary tableRDAREA
Recovery of RDAREAEX#EX
#: Lock is placed if the master directory RDAREA is to be recovered.

Table B-16 Lock modes for the registry facility initialization utility

Execution EnvironmentResource
Database structure modification utilityData dictionary tableRDAREA conditionRDAREA
Addition of registry RDAREAs, registry LOB RDAREAs, and registry management tables (-k all)EXEXEXEX

Table B-17 Lock modes for the integrity check utility

Execution environmentResource
User tableData dictionary table
RDAREATBLNW TBLRowPP TBLTBLRDAREA
For TBLFor IXFor TBLFor IX
Integrity checking (-k check)When searching data dictionary table--------PRPRSRSRSR
When acquiring RDAREA check pending status#SRSRPR------------
When performing integrity checking[Figure][Figure][Figure]------------
When setting check pending statusSUSUEXEXEXPRSUSUSU
Forced setting of check pending status (-k set) or forced release of check pending status (-k release)When searching data dictionary table--------PRPRSRSRSR
When acquiring RDAREA check pending status*SRSRPR------------
When setting or releasing check pending statusSU[Figure]EXEXEXPRSUSUSU
Legend:
For TBL: For table
For IX: For index
NW TBL: Table (during NOWAIT search)
PP TBL: Preprocessing table
TBL: Table
#: Lock is placed by UNTIL DISCONNECT (lock is maintained until DISCONNECT is executed after the setting of check pending status is completed).

Table B-18 Audit log output utility for JP1/NETM/Audit

Execution environmentResource
Audit trail table (user table)Data dictionary tableInner replica configuration management information
RDAREATBLRowRowPP TBLTBLRDAREA
For TBLFor IXBLOBFor TBLFor IX
-k iWhen searching a data dictionary table----------PR#1PR#1SR#1SR#1SR#1--
-k mWhen searching a data dictionary table----------PR#1PR#1SR#1SR#1SR#1--
-k rWhen searching a data dictionary table----------PR#1PR#1SR#1SR#1SR#1--
When searching an audit trail tableSRSR#2--SRPR------------
Legend:
For TBL: For table
For IX: For index
PP TBL: Preprocessing table
TBL: Table
#1
Lock is placed temporarily when the data dictionary table (SQL_USERS,SQL_TABLES) is searched.
#2
Lock is placed if an index is defined for the audit trail table.