Execution of utility statements by SESAM/SQL

SESAM/SQL uses locking mechanisms for transactions to guarantee that utility statements are synchronized:

• The utility statement waits till all open transactions have been terminated which already have locks on the relevant spaces ).

• While the utility statement is being executed, all subsequent accesses wait until the internal transaction of the utility statement has been terminated if they need to access the spaces concerned.

CALL-DML-OPENs are closed and stored SQL cursors are invalidated when the relevant table has been modified by the utility. Subsequent CALL-DML accesses receive status 9U, accesses to the SQL cursor receive SQLSTATE 24SA5. The utility statements concerned are:

• REORG [CATALOG_]SPACE

• CHECK CONSTRAINTS

• LOAD OFFLINE

• IMPORT TABLE

• RECOVER CATALOG / CATALOG_SPACE

• RECOVER SPACE

• REFRESH REPLICATION

• REFRESH SPACE

• ALTER CATALOG

• ALTER PARTITIONING FOR TABLE

• ALTER DATA FOR TABLE

The following table shows the spaces affected by the execution of the individual utility statements.

Utility statement	Spaces affected by the utility statement
ALTER CATALOG	Catalog space
CREATE CATALOG	Catalog space
COPY	Specified backup unit (catalog space and all user spaces, catalog space, space set, one or more user spaces)
CREATE REPLICATION	Specified unit (replication)
REFRESH REPLICATION	Specified unit (replication or partial replication)
REFRESH SPACE	Specified spaces
RECOVER [USING/TO]	Specified unit for recovery (catalog space and all user spaces, catalog space, space set, space list, individual user space)
RECOVER USING/TO REPLICATION	Specified unit for the recovery (catalog space and all user spaces of the replication or of the partial replication, catalog space, space list, single user space)
RECOVER INDEX	All user spaces in which the specified indexes are located
REORG [CATALOG_]SPACE	Catalog space, specified user space
REORG ONLINE TABLE	User space in which the base table or the partition of the base table is located
LOAD	User space in which the base table is located into which user data is loaded with LOAD
UNLOAD	User space in which the base table is located from which user data is unloaded with UNLOAD
EXPORT TABLE	User space in which the base table that is to be exported to an export file is located
IMPORT TABLE	User space into which a base table is to imported by means of an export file; possibly user space in which the indexes are to be rebuilt
ALTER DATA FOR TABLE	User space on which the base table is located
ALTER PARTITIONING FOR TABLE	User spaces on which the relevant partitions of the base table are located
CHECK FORMAL	User space to be formally checked or the user space in which the base table or index to be checked is located
ALTER MEDIA DESCRIPTION CREATE MEDIA DESCRIPTION DROP MEDIA DESCRIPTION	Media table in the catalog space
MODIFY	RECOVERY_UNITS and DA_LOGS catalog tables in the catalog space (resp. CAT-REC file)
MIGRATE	Catalog space and user space in which the base table to be migrated is located

Table 40: Spaces affected by utility statements

Depending on the utility statement in question, other users can perform full or limited updates or queries, or no updates or queries, on the space affected by the utility statement while it is executing.

Concurrent access is not permitted in connection with:

• ALTER CATALOG

• ALTER PARTITIONING FOR TABLE (only for the spaces concerned)

• ALTER DATA FOR TABLE

• CREATE CATALOG

• CREATE REPLICATION

• MIGRATE

• RECOVER

• RECOVER INDEX

• REFRESH REPLICATION

• REFRESH SPACE

• REORG [CATALOG_]SPACE
  (while copying or renaming the work file to/in the user space and while reorganizing the catalog space)

• LOAD OFFLINE (if the GENERATE INDEX clause is specified)

• IMPORT TABLE

Concurrent queries on tables and indexes in the space not affected by the utility statement are permitted in connection with:

• CHECK CONSTRAINTS

• LOAD OFFLINE (if the GENERATE INDEX clause is not specified)

Concurrent queries on the whole of the space affected by the utility statement are permitted in connection with:

• CHECK FORMAL

• COPY

• UNLOAD

• EXPORT TABLE (if user data is exported)

• REORG [CATALOG_]SPACE (for the time taken to set up the work file)

Concurrent queries and updates on the whole of the space affected by the utility statement are permitted in connection with:

• COPY ONLINE

• EXPORT TABLE (if no user data is exported)

• LOAD ONLINE

• MODIFY

• ALTER/CREATE/DROP MEDIA DESCRIPTION

• REORG ONLINE TABLE

• UNLOAD ONLINE

SESAM/SQL denies illegal access attempts, returning a status code, and treats spaces, tables and indexes to which access is not permitted as unavailable spaces, tables and indexes.

Parallel RECOVER statements

You can shorten RECOVERY runs by running multiple RECOVER statements concurrently.

You can run the following RECOVER statements concurrently:

• RECOVER of a single space: RECOVER SPACE space USING rec_unit

• RECOVER of a space list: RECOVER SPACE space , space [,...] USING rec_unit

• RECOVER of a space set: RECOVER SPACESET AT CATALOG catalog USING time stamp

Prevalent conditions for parallel processing

• The spaces specified in the RECOVER statements must be disjointed, i.e. a space only may be processed by one of the concurrent RECOVER statements.

• The parameters TO, RESTART and ADJUST must not be specified for concurrent RECOVER statements. This means that only those RECOVER statements are permitted where the modifications logged in the logging files are to be applied.

• A sufficient number of service tasks must be started (one service task per concurrent RECOVER statement).

If one of these conditions is not fulfilled, the RECOVER statements concerned will be serialized by the locking mechanism.

If a partitioned table is located on the spaces to be recovered, for reasons of consistency these spaces should be recovered using a single RECOVER statement.