DB2 Server Table Space Locked

Table Space Locks

When the DB2 table or indexes are been accessed the table space gets locked. It gets accessed not updated. The space of the table gets locked when simple read only is getting access occurs.  Here are some lists of types of table space that gets locks which is acquired during the execution of SQL statement.

The table spaces that are present gets table space locked which implies two types accessibility: The access acquires by the lock requester and the accessibility allows other subsequent, concurrent processes.

Table 1. Table Space Locks

When the SQL statement gets issued and the first access of data takes an intent lock on the table space. Then the later process takes up the actual S-, U-, or X- locks. The intent locks (IS, IX, and SIX) makes the program to wait for the required S-, U-, or X-lock that are to be taken until other processes have released all the competing locks.

These types of table space locks are used by the DB2 during the process of dependence on several factors which includes table space LOCKSIZE that is specified in the DDL, the bind parameters which are chosen for the plan to run and the type of processing that are requested. The Table 2 provides a synopsis of the initial table space that requires lock under certain conditions.

Table 2. How the Table Space Locks are acquired

The U-lock of the table space indicates the update irrespective of update occurrence.  This case is caused when you are using cursor with the FOR UPDATE OF clause. The U-lock is non-exclusive as it can be taken while tasks have S-locks present on the same table space. Some more information related to table space lock compatibility is mentioned in table 3.

There are more additional considerations of table space locks that are taken in the combination with the table space and page locks, but these can be used on their own. To specify the –LOCKSIZE TABLESPACE DDL parameter table space locks alone which are used as locking mechanism for the data in the table space. The concurrent access is limited and is limited and concurrent update processing gets eliminated.

Similar to it is LOCKSIZE DDL parameter which locks up the LOCK TABLE statement. The LOCK TABLE statement requests for an immediate lock on the specified table. The LOCK TABLE statement is of two forms- one that request for sharing locks and the other that requests for exclusive lock.

LOCK TABLE table_name IN SHARE MODE;

LOCK TABLE table_name IN EXCLUSIVE MODE;

To issue the LOCK TABLE against any specific partition for example:

LOCK TABLE table-name

PARTITION integer IN SHARE MODE;

The Locking scheme is not so effective unless there is a multiple processes to secure different types of locks on the same resource concurrently. With the help of DB2 locking you can lock table spaces that are acquired concurrently by discrete processes. The two locks can be acquired concurrently on the same resources that are compatible with one another.

In the Table 3 you will find the breakdown of DB2 table space lock compatibility. In the matrix indicates that two locks are compatible and can be acquire by distinct processes on the same table space concurrently. The No is an indication that the concurrent processes have negative affect the integrity of data in the table space.

Table 3. Table Space Lock Compatability

Table Locks

When the segmented space gets involved in DB2 uses table locks. The Table Locks are associated with corresponding table space lock.

The same types of locks can be used for table locks which are used for table space locks. S, U, X, IS, IX, and SIX table locks are acquired by DB2 processes when the data in the segmented table space is accessed. The Table 1 describes the options which are available to lock DB2 table. The compatibility chart in the Table 3 applies to table locks which are used to lock the table space as well.

For the table lock to acquire IS-lock must be firstly acquired on the segmented table space in which table exists. The type of the table lock to be taken depends upon the LOCKSIZE which is specified in the DDL, the bind parameters that are chosen for the plan being run and the type of processing requested. The Table 4 (below) is a modified version of Table 2 that depicts the initial type of table space and table locks that acquires given and certain set of conditions.  The table locks does not get acquired when the LOCKSIZE TABLESPACE parameter is used.

Table 4. How Table Locks Are Acquired

To stop the table space from locking you need to use DBF File Repair Tool.  It helps you to repair and recover your corrupted or inaccessible data that are present in these table spaces of db2 files without losing any data.

Steps to unlock DB2 Server table: 

Step 1:  Download, Install and Run DB2 File Repair software. It will provides two options to repair db2 file, first option is for if the database is attached to DB2, then you can select the option ‘Repair the database stored at default location‘, and if the database is not attached to DB2 then choose the manual selection of database.

Step 2: It will provide you the list of database attached to DB2. Select the particular database you want to repair and recover corresponding database objects.

Step 3: In this step this tool provide the list of recoverable database files. You can see the preview by clicking on it.

Step 4: Give the path location for the recoverable database and its object to be saved. Click on ‘Ok’ button.

240 Total Views 1 Views Today