DB2 System Objects

This article gives overview on DB2 system objects and helps DB2 developers to know detail description about each system objects

Introduction

DB2 has a comprehensive infrastructure that enables it to provide data integrity, performance, and the ability to recover user data. DB2 controls and accesses system objects.

Below is the list of DB2 System Objects:

• DB2 Catalog
• DB2 Directory
• Active & Archive Logs
• Boot Strap Data Set
• Buffer Pools
• Data definition control support database
• Resource limit facility database
• Work file database
• Locks

DB2 Catalog

DB2 maintains a set of tables that contain information about the data that DB2 controls. These tables are collectively known as the catalog. The catalog tables contain information about DB2 objects such as tables, views, and indexes. When you create, alter, or drop an object, DB2 inserts, updates, or deletes rows of the catalog that describe the object.

DB2 Directory

The DB2 directory contains information that DB2 uses during normal operation.

Active and archive logs

DB2 records all data changes and other significant events in a log. By having this record of changes, DB2 can re-create those changes for you in the event of a failure or roll the changes back to a previous point in time.

Bootstrap data set:

The bootstrap data set (BSDS) is a VSAM key-sequenced data set (KSDS) that contains information that is critical to DB2, such as the names of the logs. DB2 uses information in the BSDS for system restarts and for any activity that requires reading the log.

Buffer pools:

Buffer pools are areas of virtual storage in which DB2 temporarily stores pages of table spaces or indexes.

Data definition control support database:

The data definition control support (DDCS) database refers to a user-maintained collection of tables that are used by data definition control support to restrict the submission of specific DB2 DDL (data definition language) statements to selected application identifiers (plans or collections of packages)

Resource limit facility database:

The resource limit facility database (DSNRLST) is a facility that lets you control the amount of processor resources that are used by dynamic SELECT statements.

Work file database:

Use the work file database as storage for processing SQL statements that require working space, such as that required for a sort.

DB2 SYSTEM OBJECTS:
DB2 catalog:

DB2 maintains a set of tables that contain information about the data that DB2 controls. These tables are collectively known as the catalog. The catalog tables contain information about DB2 objects such as tables, views, and indexes. When you create, alter, or drop an object, DB2 inserts, updates, or deletes rows of the catalog that describe the object. The system database DSNDB06 contains the DB2 catalog.

The catalog table describes table spaces, tables, columns, indexes, privileges, application plans, and packages. Authorized users can query the catalog; however, it is primarily intended for use by DB2 and is therefore subject to change. All catalog tables are qualified by SYSIBM. Do not use this qualifier for user-defined tables. The catalog tables are updated by DB2 during normal operations in response to certain SQL statements, commands, and utilities.

The DB2 catalog also contains information about communications with other DB2 and non-DB2 databases through the use of the communications database (CDB), which contains information about VTAM and TCP/IP addresses.

Table Name (SYSIBM.table)	Information Contents
IPLIST	Allows multiple IP addresses to be specified for a given LOCATION. Insert rows into this table when you want to define a remote DB2 data sharing group. Rows can be inserted, updated, and deleted.
IPNAMES	Defines the remote DRDA servers DB2 can access using TCP/IP. Rows in this table can be inserted, updated, and deleted.
LOCATIONS	Contains a row for every accessible remote server. The row associates a LOCATION name with the TCP/IP or SNA network attributes for the remote server. Requesters are not defined in this table. Rows in this table can be inserted, updated, and deleted.
LULIST	Allows multiple LU (Logical Unit) names to be specified for a given LOCATION. Insert rows into this table when you want to define a remote DB2 data sharing group. The same value for the LUNAME column cannot appear in both the SYSIBM.LUNAMES table and the SYSIBM.LULIST table. Rows in this table can be inserted, updated, and deleted.
LUMODES	Each row of the table provides VTAM with conversation limits for a specific combination of LUNAME and MODENAME. The table is accessed only during the initial conversation-limit negotiation between DB2 and a remote LU. This negotiation is called change-number-of-sessions (CNOS) processing. Rows in this table can be inserted, updated, and deleted.
LUNAMES	The table must contain a row for each remote SNA client or server that communicates with DB2. Rows can be inserted, updated, or deleted.
MODESELECT	Associates a mode name with any conversation created to support an outgoing SQL request. Each row represents one or more combinations of LUNAME, authorization ID, and application plan name. Rows in this table can be inserted, updated, and deleted.
SYSAUXRELS	Contains one row for each auxiliary table created for a LOB column. A base table space that is partitioned must have one auxiliary table for each partition of each LOB column.
SYSCHECKDEP	Contains one row for each reference to a column in a table check constraint.
SYSCHECKS	Contains one row for each table-check constraint.
SYSCHECKS2	Contains one row for each table-check constraint created in or after version 7.
SYSCOLAUTH	Records the UPDATE or REFERENCES privileges that are held by users on individual columns of a table or view.
SYSCOLDIST	Contains one or more rows for the first key column of an index key. Rows in this table can be inserted, updated, and deleted.
SYSCOLDIST_HIST	Contains rows from SYSCOLDIST. Whenever rows are added or changed in SYSCOLDIST, the rows are also written to the new history table. Rows in this table can be inserted, updated, and deleted.
SYSCOLDISTSTATS	Contains zero or more rows per partition for the first key column of a partitioning index or DPSI (Data Partitioned Secondary Index). Rows are inserted when RUNSTATS scans index partitions of the partitioning index. No row is inserted if the index is nonpartitioning. Rows in this table can be inserted, updated, and deleted.
SYSCOLSTATS	Contains partition statistics for selected columns. For each column, a row exists for each partition in the table. Rows are inserted when RUNSTATS collects either indexed column statistics or nonindexed column statistics for a partitioned table space. No row is inserted if the table space is nonpartitioned. Rows in this table can be inserted, updated, and deleted.
SYSCOLUMNS	Contains one row for every column of each table and view.
SYSCOLUMNS_HIST	Contains rows from SYSCOLUMNS. Whenever rows are added or changed in SYSCOLUMNS, the rows are also written to the new history table. Rows in this table can be inserted, updated, and deleted.
SYSCONSTDEP	Records dependencies on check constraints or user-defined defaults for a column.
SYSCOPY	Contains information needed for recovery.
SYSDATABASE	Contains one row for each database, except for database DSNDB01.
SYSDATATYPES	Contains one row for each distinct type defined to the system.
SYSDBAUTH	Records the privileges held by users over databases.
SYSDBRM	Contains one row for each DBRM of each application plan.
SYSDUMMY1	Contains one row. The table is used for SQL statements in which a table reference is required, but the contents of the table are not important.
SYSFIELDS	Contains one row for every column that has a field procedure.
SYSFOREIGNKEYS	Contains one row for every column of every foreign key.
SYSINDEXES	Contains one row for every index.
SYSINDEXES_HIST	Contains rows from SYSINDEXES. Whenever rows are added or changed in SYSINDEXES, they are also written to the new history table. Rows in this table can be inserted, updated, and deleted.
SYSINDEXPART	Contains one row for each nonpartitioning index and one row for each partition of a partitioning index or a DPSI.
SYSINDEXPART_HIST	Contains rows from SYSINDEXPART. Whenever rows are added or changed in SYSINDEXPART, they are also written to the new history table. Rows in this table can be inserted, updated, and deleted.
SYSINDEXSTATS	Contains one row for each partition of a partitioning index. Rows in this table can be inserted, updated, and deleted.
SYSINDEXSTATS_HIST	Contains rows from SYSINDEXSTATS. Whenever rows are added or changed in SYSINDEXSTATS, they are also written to the new history table. Rows in this table can be inserted, updated, and deleted.
SYSJARCLASS_SOURCE	Auxiliary table for SYSIBMSYSCONTENTS.
SYSJARCONTENTS	Contains Java class source for installed JAR (Java Archive).
SYSJARDATA	Auxiliary table for SYSIBMSYSOBJECTS.
SYSJAROBJECTS	Contains binary large object representing the installed JAR.
SYSJAVAOPTS	Contains build options used during INSTALL_JAR.
SYSKEYCOLUSE	Contains a row for every column in a unique constraint—primary key or unique key—from the SYSIBM.SYSTABCONST table.
SYSKEYS	Contains one row for each column of an index key.
SYSLOBSTATS	Contains one row for each LOB table space.
SYSLOBSTATS_HIST	Contains rows from SYSLOBSTATS. Whenever rows are added or changed in SYSLOBSTATS, they are also written to the new history table. Rows in this table can be inserted, updated, and deleted.
SYSPACKAGE	Contains a row for every package.
SYSPACKAUTH	Records the privileges that users hold over packages.
SYSPACKDEP	Records the dependencies of packages on local tables, views, synonyms, table spaces, indexes and aliases, functions, and stored procedures.
SYSPACKLIST	Contains one or more rows for every local application plan bound with a package list. Each row represents a unique entry in the plan's package list.
SYSPACKSTMT	Contains one or more rows for each statement in a package.
SYSPARMS	Contains one row for each parameter of a routine or multiple rows for table parameters—one for each column of the table.
SYSPKSYSTEM	Contains zero or more rows for every package. Each row for a given package represents one or more connections to an environment in which the package could be executed.
SYSPLAN	Contains one row for each application plan.
SYSPLANAUTH	Records the privileges that users hold over application plans.
SYSPLANDEP	Records the dependencies of plans on tables, views, aliases, synonyms, table spaces, indexes, functions, and stored procedures.
SYSPLSYSTEM	Contains zero or more rows for every plan. Each row for a given plan represents one or more connections to an environment in which the plan could be used.
SYSRELS	Contains one row for every referential constraint.
SYSRESAUTH	Records CREATE IN and PACKADM ON privileges for collections, USAGE privileges for distinct types, and USE privileges for buffer pools, storage groups, and table spaces.
SYSROUTINEAUTH	Records the privileges that users hold on routines. (A routine can be a user-defined function, a cast function, or a stored procedure.)
SYSROUTINES	Contains one row for every routine. (A routine can be a user-defined function, a cast function, or a stored procedure.)
SYSROUTINES_OPTS	Contains one row for each generated routine, such as one created by the DB2 Development Center tool, that records the build options for the routine. Rows in this table can be inserted, updated, and deleted.
SYSSCHEMAAUTH	Contains one or more rows for each user granted a privilege on a particular schema in the database.
SYSSEQUENCEAUTH	Records the privileges that users hold over sequences.
SYSSEQUENCES	Contains one row for each identity column.
SYSSEQUENCESDEP	Records the dependencies of identity columns on tables.
SYSSTMT	Contains one or more rows for each SQL statement of each DBRM.
SYSSTOGROUP	Contains one row for each storage group.
SYSSTRINGS	Contains information about character conversion. Each row describes a conversion from one coded character set to another.
SYSSYNONYMS	Contains one row for each synonym of a table or a view.
SYSTABAUTH	Records the privileges that users hold on tables and views.
SYSTABCONST	Contains one row for each unique constraint—primary key or unique key—created in DB2 for OS/390 version 7 or later.
SYSTABLEPART	Contains one row for each nonpartitioned table space and one row for each partition of a partitioned table space.
SYSTABLEPART_HIST	Contains rows from SYSTABLEPART. Rows are added or changed when RUNSTATS collects history statistics. Rows in this table can be inserted, updated, and deleted.
SYSTABLES	Contains one row for each table, view, or alias.
SYSTABLES_HIST	Contains rows from SYSTABLES. Rows are added or changed when RUNSTATS collects history statistics. Rows in this table can be inserted, updated, and deleted.
SYSTABLESPACE	Contains one row for each table space.
SYSTABSTATS	Contains one row for each partition of a partitioned table space. Rows in this table can be inserted, updated, and deleted.
SYSTABSTATS_HIST	Contains rows from SYSTABSTATS. Rows are added or changed when RUNSTATS collects history statistics. Rows in this table can be inserted, updated, and deleted.
SYSTRIGGERS	Contains one row for each trigger.
SYSUSERAUTH	Records the system privileges that users hold.
SYSVIEWDEP	Records the dependencies of views on tables, functions, and other views.
SYSVIEWS	Contains one or more rows for each view.
SYSVOLUMES	Contains one row for each volume of each storage group.
USERNAMES	Uses each row in the table to carry out one of the following operations: Outbound ID translation Inbound ID translation and "come from" checking Rows in this table can be inserted, updated, and deleted.

DB2 Directory
The DB2 directory is used to store information about the operation and housekeeping of the DB2 environment that DB2 uses during normal operation. This directory, unlike the DB2 catalog, cannot be accessed by using SQL. The DB2 directory contains information required to start DB2; activities and utilities in the DB2 environment do the updating and deleting of table entries in the DB2 directory. The directory consists of a set of DB2 tables that are stored in five table spaces in system database DSNDB01. Each of the table spaces that are listed in the following table is contained in a VSAM linear data set.

DB2 Directory Tables:

Directory Table	Information Contents
SPT01	Referred to as the skeleton package table (SKPT), this table contains information about the access paths and the internal form of the SQL for a package at bind time. Entries are made into this table during bind time (BIND PACKAGE), and entries are deleted when a package is freed (FREE PACKAGE). This table is loaded into memory at execution time, along with the SCT02 table described next.
skeleton package
SCT02	Referred to as the skeleton cursor table (SKCT), this table contains information about access paths and the internal form of the SQL for an application plan. Entries in this table are made when a plan is bound (BIND PLAN) and deleted when a plan is freed (FREE PLAN). This table is also loaded into memory at execution time.
Plan
DBD01	Information about DBDs (database descriptors), which are internal control blocks, is kept in this table. Each DB2 database has one DBD for its objects: table spaces, indexes, tables, referential integrity constraints, and check constraints. Updates to this table are made when a database is created or updated. This information is accessed by DB2 in place of continually using the DB2 catalog, permitting faster, more efficient access to this information. The information in the DBD01 directory table is also contained in the DB2 catalog.
Database Descriptors
SYSLGRNX	Referred to as the log range table, this table contains information from the DB2 logs about the RBA (relative byte address) range for updates. This allows DB2 to efficiently find the RBAs needed from the DB2 logs for recovery purposes. A row is inserted every time a table space or a partition is opened or updated and is updated when the object is closed.
Log range
SYSUTILX	This system utilities table stores information about the execution of DB2 utilities, including the status and the steps during execution. This information is used when a utility needs to be restarted. Information in this table is added when a utility is started, and the entry is removed when the execution has ended.
System utilities

Active and archive logs
DB2 records all data changes and other significant events in a log. By having this record of changes, DB2 can re-create those changes for you in the event of a failure or roll the changes back to a previous point in time.

DB2 is also responsible for keeping a log of all changes made to table spaces. With a few exceptions, all updates are recorded in the DB2 active log. DB2 writes each log record to a disk data set called the active log. When the active log is full, DB2 copies the contents of the active log to a disk or magnetic tape data set called the archive log, and then the active log is overwritten again. Archive logs are physical sequential datasets, and can be held on either disk or tape. DB2 will track up to 10,000 archive logs. All this information is stored in the DB2 Directory's SYSIBM.SYSLGRNX table and the Boot Strap Data Set (BSDS). There is some delay built into this process, so some log data will be held on both active and archived logs. Generally, archived logs are held on slower medium than active logs. The overall size of the active log must be enough to cater for fast recovery in a reasonable time frame.

You can choose either single logging or dual logging.

• A single active log contains up to 93 active log data sets.
• With dual logging, the active log has twice the capacity for active log data sets, because two identical copies of the log records are kept.

Each DB2 subsystem manages multiple active logs and archive logs. The following facts are true about each DB2 active log:

• Each log can be duplexed to ensure high availability.
• Each active log data set is a VSAM linear data set (LDS).
• DB2 supports striped active log data sets

SYSIBM.SYSLGRNX:

DB2 uses a catalog table in DB2 directory known as SYSIBM.SYSLGRNX that gives it the ability to determine the log records for a tablespace and recoverable indexes by recording the periods of updates for these objects. There will be a row for time period that a tablespace or index spaces had update activity occurring. Recorded in this row is the following:

• DBID & OBID of the object.
• LRSN and local log RBA of the first update after open.
• LRSN and local log RBA when a pseudo-close occurred ( no more update activity)
• If data sharing, the member which performed the update will be record.

The MODIFY utility with the RECOVERY option deletes records from the SYSIBM.SYSCOPY catalog table, related log records from the SYSIBM.SYSLGRNX directory table, and entries from the DBD, and recycles DB2 version numbers for reuse. You can remove records that were written before a specific date, you can remove records of a specific age, or you can ensure that a specified number of records are retained. You can delete records for an entire table space, partition, or data set. You should run MODIFY regularly to remove outdated information from SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX. These tables, and particularly SYSIBM.SYSLGRNX, can become very large and take up a considerable amount of space. By deleting outdated information from these tables, you can help improve performance for processes that access data from these tables.

Boot Strap Data Set

The bootstrap data set (BSDS) is a VSAM key-sequenced data set (KSDS) that contains information that is critical to DB2, such as the names of the logs. DB2 uses information in the BSDS for system restarts and for any activity that requires reading the log.

Specifically, the BSDS contains:

• An inventory of all active and archive log data sets that are known to DB2. DB2 uses this information to track the active and archive log data sets. DB2 also uses this information to locate log records to satisfy log read requests during normal DB2 system activity and during restart and recovery processing.
• A wrap-around inventory of all recent DB2 checkpoint activity. DB2 uses this information during restart processing.
• The distributed data facility (DDF) communication record, which contains information that is necessary to use DB2 as a distributed server or requester.
• Information about buffer pools.

Because the BSDS is essential to recovery in the event of subsystem failure, during installation DB2 automatically creates two copies of the BSDS and, if space permits, places them on separate volumes.

The BSDS can be duplexed to ensure availability.

The active logs are first registered in the BSDS when installed. You cannot replace the active logs without terminating and restarting the queue manager.

Archive log data sets are allocated dynamically. When one is allocated, the data set name is registered in the BSDS. The list of archive log data sets expands as archives are added, and wraps when a user-determined number of entries has been reached. The maximum number of entries is 1000 for single archive logging and 2000 for dual logging.

Conversely, the maximum number of archive log data sets could have been exceeded, and the data from the BSDS dropped long before the data set has reached its expiry date.

If the system parameter module specifies that archive log data sets are cataloged when allocated, the BSDS points to the integrated catalog facility (ICF) catalog for the information needed for later allocations. Otherwise, the BSDS entries for each volume register the volume serial number and unit information that is needed for later allocations.

Buffer Pools

Buffer pools are areas of virtual storage in which DB2 temporarily stores pages of table spaces or indexes.

When an application program accesses a row of a table, DB2 retrieves the page that contains the row and places the page in a buffer. If the required data is already in a buffer, the application program need not wait for it to be retrieved from disk, so the time and cost of retrieving the page is significantly reduced.

Buffer pools require monitoring and tuning. The size of buffer pools is critical to the performance characteristics of an application or group of applications that access data in those buffer pools.

Buffer pools reside in the DB2 DBM1 primary address space. This option offers the best performance. The maximum size of a buffer pool is 1 TB.

A buffer pool is associated with a single database and can be used by more than one table space. When considering a buffer pool for one or more table spaces, you must ensure that the table space page size and the buffer pool page size are the same for all table spaces that the buffer pool services. A table space can only use one buffer pool.

When the database is created, a default buffer pool named IBMDEFAULTBP is created which is shared by all table spaces. More buffer pools can be added by using the CREATE BUFFERPOOL statement. The buffer pool size defaults to the size specified by the BUFFPAGE database configuration parameter but can be overridden by specifying the SIZE keyword in the CREATE BUFFERPOOL command. Adequate buffer pool size is essential to good database performance since it will reduce disk I/O, the most time consuming operation. Large buffer pools will also have an effect on query optimization, since more of the work can be done in memory.

DB2 does not use extended storage for buffers. However, extended storage can be used to cache memory pages, making it faster to move the pages out from memory.

Example of CREATE BUFFERPOOL statement

Here is an example of the CREATE BUFFERPOOL statement:

CREATE BUFFERPOOL BP3 SIZE 2000 PAGESIZE 8K

You can use the ALTER TABLESPACE command to add the buffer pool to the existing table space:

ALTER TABLESPACE USERSPACE3 BUFFERPOOL BP3

Use the DISPLAY BUFFERPOOL commands to gather buffer pool information.

Buffer pool size

The size of buffer pools is critical to the performance characteristics of an application or a group of applications that access data in those buffer pools.

Tuning your buffer pools can improve the response time and throughput for your applications and provide optimum resource utilization. For example, applications that do online transaction processing are more likely to need large buffer pools because they often need to reaccess data. In that case, storing large amounts of data in a buffer pool enables applications to access data more efficiently.

By making buffer pools as large as possible, you can achieve the following benefits:

• Fewer I/O operations result, which means faster access to your data.



• I/O contention is reduced for the most frequently used tables and indexes.



• Sort speed is increased because of the reduction in I/O contention for work files.

Work file database

The work file database is used as storage for DB2 work files for processing SQL statements that require working space (such as that required for a sort), and as storage for created global temporary tables and declared global temporary tables.

DB2 creates a work file database and some table spaces in it for you at installation time. You can create additional work file table spaces at any time. You can drop, re-create, and alter the work file database or the table spaces in it, or both, at any time.


In a non-data-sharing environment, the work file database is named DSNDB07. In a data sharing environment, each DB2 member in the data sharing group has its own work file database.


You can also use the work file database for all temporary tables. DSNDB07 is a work file database but contains no permanent data, and can be deleted and redefined with the new qualifier

Work file database name cannot start with DSNDB except for DSNDB07.

Data definition support database

The Data Definition Control Support (DDCS) database refers to a user-maintained collection of tables that are used by data definition control support to restrict the submission of specific DB2 DDL (Data Definition Language) statements to selected application identifiers (plans or collections of packages).

This database is automatically created during installation. After this database is created, you must populate the tables to use this facility. The system name for this database is DSNRGFDB.

Resource limit facility database

The resource limit facility database (DSNRLST) is a facility that lets you control the amount of processor resources that are used by dynamic SELECT, UPDATE, INSERT, DELETE SQL statements.

You can establish a single limit for all users, different limits for individual users, or both. You can choose to have these limits applied before the statement is executed (this is called predictive governing), or while a statement is running (sometimes called reactive governing). You can even use both modes of governing. You define these limits in one or more resource limit specification tables (RLST).

Locks

DB2 lets more than one program access the same data at essentially the same time. This is known as concurrency. To control concurrency and prevent inconsistent data, DB2 uses locks.

The three types are shared, update and exclusive.

S (SHARE):

The lock owner and any concurrent processes can read, but not change, the locked page or row. Concurrent processes can acquire S or U locks on the page or row or might read data without acquiring a page or row lock.

U (UPDATE):

The lock owner can read, but not change, the locked page or row. Concurrent processes can acquire S locks or might read data without acquiring a page or row lock, but no concurrent process can acquire a U lock.

U locks reduce the chance of deadlocks when the lock owner is reading a page or row to determine whether to change it, because the owner can start with the U lock and then promote the lock to an X lock to change the page or row.

X (EXCLUSIVE):

The lock owner can read or change the locked page or row. A concurrent process cannot acquire S, U, or X locks on the page or row.

IS (INTENT SHARE):

The lock owner can read data in the table, partition, or table space, but not change it. Concurrent processes can both read and change the data. The lock owner might acquire a page or row lock on any data it reads.

IX (INTENT EXCLUSIVE):

The lock owner and concurrent processes can read and change data in the table, partition, or table space. The lock owner might acquire a page or row lock on any data it reads; it must acquire one on any data it changes.

SIX (SHARE with INTENT EXCLUSIVE) :

The lock owner can read and change data in the table, partition, or table space. Concurrent processes can read data in the table, partition, or table space, but not change it. Only when the lock owner changes data does it acquire page or row locks.

Lock mode compatibility

The major effect of the lock mode is to determine whether one lock is compatible with another.

Definition: Locks of some modes do not shut out all other users. Assume that application process A holds a lock on a table space that process B also wants to access. DB2 requests, on behalf of B, a lock of some particular mode. If the mode of A's lock permits B's request, the two locks (or modes) are said to be compatible.

Effects of incompatibility: If the two locks are not compatible, B cannot proceed. It must wait until A releases its lock. (And, in fact, it must wait until all existing incompatible locks are released.)

Compatible lock modes: Compatibility for page and row locks is easy to define. Table 2 shows whether page locks of any two modes, or row locks of any two modes, are compatible (Yes) or not (No). No question of compatibility of a page lock with a row lock can arise, because a table space cannot use both page and row locks.

Improved performance:

DB2 uses locks on a variety of data objects, including rows, pages, tables, table space segments, table space partitions, entire table spaces, and databases. When an application acquires a lock, the application "holds" or "owns" the lock.

The following lock modes provide different degrees of protection:

• Suspension

An application process is suspended when it requests a lock that another application process already holds, if that lock is not a shared lock. The suspended process temporarily stops running, and it resumes running in the following circumstances:

1) All processes that hold the conflicting lock release it.

2) The requesting process experiences a timeout or deadlock and the process resumes and handles an error condition.

• Timeout

An application process times out when it terminates because of a suspension that exceeds a preset interval. DB2 terminates the process, issues messages, and returns error codes. Commit and rollback operations do not time out. The STOP DATABASE command, however, can time out, in which case DB2 sends messages to the console; the STOP DATABASE command can be retried up to 15 times.

• Deadlock

A deadlock occurs when two or more application processes each hold locks on resources that the others need and without which they cannot proceed. After a preset time interval, DB2 can roll back the current unit of work for one of the processes or request a process to terminate. DB2 thereby frees the locks and allows the remaining processes to continue. Although some locking problems can occur, you can avoid system and application locking problems.

References

• http://publib.boulder.ibm.com



• DB2 for z/OS Version 8 DBA Certification Guide by Susan Lawson



• www.google.com