Is it a PLAN? Is it a PACKAGE? NO, It's a CONFUSION !!!

No matter how long programmers have worked with DB2 for z/OS , they still have the confusion on difference between a plan and a package and what the heck is that collection anyway.

In this post i will be specific on the PLAN/PACKAGE difference and the things related to it (Such as COLLECTION and PKLIST). If you want to know from basic and detailing of what is PLAN/PACKAGE and how it get formed and why we need it then ,THIS is the great place to start up with,rather i would recommend you to read it before going further with this post.

OK so here it goes.

The PLAN/PACKAGE are nothing but the container which contains the logic of how your SQL statement in the program should get executed.[Remember when we write any query in SQL, we say what we want and NOT how it is to be done (We don't care how DB2 internally does it)].So PLAN/PACKAGE contains the LOGIC how it is to be done.Then this PLAN/PACKAGE is used with load module (For COBOL) to run your application program smoothly.

Now why two things?? PLAN and  PACKAGE , if both are the same (Contains the LOGIC for executing your SQL) and what exactly is the difference between the two.


Binding DBRM to a PLAN :

• The PLAN contains the (Logic of) one or DBRM OR one or more PACKAGES OR the combination of both.

• The PLAN is executable.When i say executable it means ,it can be executed with COBOL load module to run your application program.

• Now let's consider there are 10 programs with names A TO J. So there will be 10 DBRM ( 1 DBRM for each program). I bind all 10 DBRM's to a PLAN (with name 'PLANA').Now let's say i got a requirement to change one of the Program ( let's say Program 'B') then i NOT ONLY need to bind this DBRM again to plan PLANA but all remaining 9 DBRM too (even though they didn't went under any change) and that is the biggest disadvantage of a binding a DBRM to a PLAN.NOT only this, if in my system there are various other PLAN's which uses this DBRM (The one which corresponds to Program 'B') then all those PLAN'S need to rebind along with all the other existing DBRM'S they may be having.

• There are various parameters called as Bind parameter are available, when you bind your DBRM to a PLAN. These Bind parameters helps in deciding various important aspects for you program such as when to acquire a lock for the particular resource in your program,when to release the lock,what qualifier to use for the objects refered in your application program,the owner of plan etc. So when you bind your all DBRM's (10 DBRM in this example) to PLAN you have only one set of combination of these bind parameters for all 10 DBRM (For example you can not set different isolation level for different Program/DBRM , it has to be same)

Binding DBRM to a PACKAGE:

• The PACKAGE contains the (Logic of) ONLY one DBRM.It's a single bound DBRM.
• The PACKAGE is not executable that means it can not be use with COBOL load module to run your Application program.In order to make your PACKAGE to be executed it has to bonded to PLAN again.So you bind your DBRM to PACKAGE and then in turn this PACKAGE to PLAN.I know what you must be thinking now,why to go this way,  isn't it better to bind DBRM to straight away to PLAN.NO IT IS NOT, i will explain you why it is so.When i say you have to bind the DBRM to PACKAGE and then this PACKAGE to PLAN again,you need not to do it for ever package. What i mean is, let's say i have 50 PACKAGES to bind to a PLAN no need to BIND it 50 times instead just bind a single package to PLAN using PKLIST(Package List) that's it ,you are done and remaining 49 PACKAGES will be automatically added to your plan (For this all your 49 Packages need to be in same PKLIST what we have bonded with PLAN earlier)

• Let's take the same example what we use above for PLAN. I have 10 programs A TO J with 10 DBRM. Now in case of PACKAGE , I will bind each DBRM to one PACKAGE so i will have 10 Packages then include all 10 PACKAGES to single PKLIST (Package List) bind the Package list to a PLAN.Now if one of my Program changes , then i have to rebind only the DBRM corresponding to the Program changing and NOT the remaining DBRM'S/PROGRAM (Which didn't went under the change)

• Regarding Bind parameter, since i can bind 10 DBRM to 10 different PACKAGES, I can have various combination of Bind parameters for all PACKAGES.(For example Isolation level of CS for 1 PACKAGE and RR to other etc.)

Having said that, you will wonder,you have only the advantages of binding DBRM to PACKAGE and don't have any reason to go for binding DBRM to a PLAN.

That is the reason , IBM is going to remove this facility of binding DBRM to PLAN in DB2 V9 Onwards
For more detail on the same refer HERE.

JQEUME5S4HKU

Lock escalation in DB2.

Lock escalation as it's name suggest is nothing but escalating the lock on the object from lower level to the higher.Lock escalation is controlled by the parameter LOCKMAX of CREATE TABLESPACE and ALTER TABLESPACE.

The purpose of lock escalation is to reduce the impact of low level locks, both in terms of CPU and storage, at the possible expense of concurrency. When the number of low level locks exceeds a specified threshold, DB2 attempts to promote the high level intent locks, either IS or IX, to S or X, respectively. If this succeeds, then the low level locks are all released and no more low level locks are requested. The lock promotion request may itself fail if other processes hold incompatible locks, either low or high level, for longer than the timeout period. If the higher level lock cannot be acquired before the timeout value has been reached, then the process times out, all updates are rolled back, and all locks are released.

LOCKMAX :

The parameter LOCKMAX of CREATE TABLESPACE and ALTER TABLESPACE controls the maximum number of page or row locks that an application process can hold simultaneously in a single table space. In segmented table spaces, a single table can be locked. Segmented table space organization allows some tables to perform page or row locking and other tables in the same table space to perform table locking. If the number of locked pages or rows for a table exceeds the limit, a table lock is acquired for that table.

Lock escalation is controlled by the LOCKMAX option. If a process requests more than the number specified in LOCKMAX, the intent lock on the table space, partition, or table is promoted to S or X and the page or row child locks are released.


Lock escalation occurs when the number of locks held by a single process exceeds the LOCKMAX value on a table space. Lock escalation involves obtaining a table or table space lock, then releasing all of the page or row locks. Lock escalation is a safety valve that DB2 provides in case an application overuses system resources. LOCKMAX 0 means that you are disabling this useful feature. Loss of concurrency is less likely to be incurred with lock escalation if the majority of the rows or pages in a table space are already locked, as long as RELEASE(COMMIT) is used. Lock escalation in such a situation reduces storage use, but more importantly, reduces the CPU time needed to traverse a lock hash synonym chain. Lock escalation also reduces the Internal Resource Lock Manager (IRLM) latch suspension time, which also reduces CPU time.

Lock escalation for a partitioned table space will cause DB2 to promote the intent locks for every partition where child locks are currently held. Any subsequent access to a new partition will cause DB2 to request gross locks on that partition from the outset. The LOCKMAX figure applies to all locks held at the table space level when it is a multi-table table space.

The column LOCKMAX on the catalog table SYSIBM.SYSTABLESPACE stores the maximum number of locks per user on the table or table space before escalating to the next locking level. A value of -1 indicates LOCKMAX=SYSTEM. Any escalation process is suspended during the execution of SQL statements for ALTER, CREATE, DROP, GRANT, and REVOKE.

Lock escalation is an expensive process, both in terms of lock management and concurrency. In today’s high availability environment, you should design the application to allow no reason for lock escalations to occur. The benefit of lock escalation is in protecting DB2 from an excessive number of locks held in the IRLM on behalf of poorly designed applications that are not releasing their locks.

Lock escalation also prevents poorly designed applications from reaching the maximum number of locks per user specified by the NUMLKUS DSNZPARM. From an individual application perspective, this may seem like a good thing, but from an overall availability and performance standpoint, it is merely rewarding bad design. The focus should be on identifying and correcting those poorly designed applications that take an excessive number of locks.

For critical large table spaces that are accessed by many application processes concurrently, choose the LOCKMAX value so that there will not be any lock escalation within a unit of work, if NUMLKTS DSNZPARM is not large enough.

Row Identifier in DB2 (V9 Onwards)

I use to wonder why we don't have something called as row identifier in DB2 as it is there in oracle or MySQL.Finally IBM added it in V9 and continued in V10.

Let me explain what row identifier is and what purpose it serves.

The RID function returns the record ID (RID) of a row. The RID is used to uniquely identify a row.

The function might return a different value when it is invoked multiple times for a row. For example, after the REORG utility is run, the RID function might return a different value for a row than would have been returned prior to the REORG utility being run. The RID function is not deterministic.

DB2 might reuse RID numbers when a REORG operation is performed. If the RID function is used to obtain a value for a row and an application depends on that value remaining the same as long as the row exists, consider the following alternatives:

• Add a ROW ID column to the table to provide a value that can be associated with each row, rather than invoking the RID function to generate a value for a row.
• Define a primary key for the table, using the columns of the primary key to ensure uniqueness, rather than invoking the RID function to generate a value for a row.

Example : Return the RID and last name of employees who are in department '20':

 SELECT RID(EMP), LASTNAME
     FROM EMP
     WHERE DEPTNO = '20';

Let me give you the practical example of use of row identifier in my shop.

We had one table in our database which didn't had any primary key to it and hence no facility to identify any unique row in the table (Agree, the table was badly designed by my DBA)

There was one program which use to insert rows in this table, but sometimes it inserts the duplicate and we didn't had any alarm for it as there is no primary key and hence no unique index.So while fetching row from this table in the program,it used to give -811 sqlcode.So to resolve this abend we have to delete two rows(duplicate) and insert one (we can't delete only one duplicate row as we don't have anything unique to identify the row ) So here it was the RID which helped us to delete the duplicate row straight away (no need to insert two rows and then insert one).

Isolation Level in DB2.

As i mentioned earlier, i wanted to take this concept in a separate post.Isolation level parameter is the one you specify while binding your Application program's DBRM in to Plan/Package.

The problem with Isolation level is no matter how many times you read it or from which source you read it you won't understand it.I mean you just read it but when you have to actually use it in you shop, you wonder how to use it and this is because you don't have any practical example of it .once you have any real world example of each isolation level, you most probably will remember and most importantly use wisely in your instances.

So let's just understand each isolation level in detail with the real world example.

First of all understand what is isolation level and why we need it??

Several different users can access and modify data stored in a DB2 database at the same time, the DB2 Database Manager must be able to allow users to make necessary changes while ensuring that data integrity is never compromised.The sharing of resources by multiple interactive users or application programs at the same time is known as concurrency.
One of the ways DB2 enforces concurrency is through the use of isolation levels, which determine how data accessed and/or modified by one transaction is "isolated from" other transactions.

DB2 recognizes and supports the following isolation levels:

• Repeatable Read     (RR)
• Read Stability          (RS)
• Cursor Stability       (CS)
• Uncommitted Read (UR)

The Repeatable Read Isolation Level 

The Repeatable Read isolation level is the most restrictive isolation level available.

When it's used, the effects of one transaction are completely isolated from the effects of other concurrent transactions.

When this isolation level is used, every row that's referenced in any manner by the owning transaction is locked for the duration of that transaction. As a result, if the same SELECT SQL statement is issued multiple times within the same transaction, the result data sets produced are guaranteed to be identical. In fact, transactions running under this isolation level can retrieve the same set of rows any number of times and perform any number of operations on them until terminated, either by a commit or a rollback operation. However, other transactions are prohibited from performing insert, update, or delete operations that would affect any row that has been accessed by the owning transaction as long as that transaction remains active.

To ensure that the data being accessed by a transaction running under the Repeatable Read isolation level is not adversely affected by other transactions, each row referenced by the isolating transaction is locked—not just the rows that are actually retrieved or modified.

Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are acquired and held on all 1,000 rows scanned—not just on the 10 rows retrieved.

Real World Example : So how does this isolation level work in a real-world situation? lets's see..

Suppose you use a DB2 database to keep track of hotel records that consist of reservation and room rate information, and you have a Web-based application that allows individuals to book rooms on a first-come, first-served basis.

If your reservation application runs under the Repeatable Read isolation level, a customer scanning the database for a list of rooms available for a given date range will prevent you (the manager) from changing the room rate for any of the room records that were scanned to resolve the customer's query. Similarly, other customers won't be able to make or cancel reservations that would cause the first customer's list of available rooms to change if the same query were to be run again (provided the first customer's transaction remained active). However, you would be allowed to change room rates for any room record that was not read when the first customer's list was produced; likewise, other customers would be able to make or cancel room reservations for any room whose record was not read in order to produce a response to the first customer's query.

The Read Stability Isolation Level

The Read Stability isolation level is not quite as restrictive as the Repeatable Read isolation level; therefore, it does not completely isolate one transaction from the effects of other, concurrent transactions.

When the Read Stability isolation level is used, only rows that are actually retrieved or modified by the owning transaction are locked.

Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are only acquired and held on the 10 rows retrieved, not on the 1,000 rows scanned.

Because fewer locks are acquired, more transactions can run concurrently. As a result, if the same SELECT SQL statement is issued two or more times within the same transaction, the result data set produced may not be the same each time.

As with the Repeatable Read isolation level, transactions running under the Read Stability isolation level can retrieve a set of rows and perform any number of operations on them until terminated. Other transactions are prohibited from performing update or delete operations that would affect the set of rows retrieved by the owning transaction as long as that transaction exists; however, other transactions can perform insert operations.

Real World Example : We will take the same example and see how read stability change it.

Now, when a customer scans the database to obtain a list of rooms available for a given date range, you (the manager) will be able to change the rate for any room that does not appear on the customer's list. Likewise, other customers will be able to make or cancel reservations that would cause the first customer's list of available rooms to change if the same query were to be run again. As a result, if the first customer queries the database for available rooms for the same date range again, the list produced may contain new room rates and/or rooms that were not available the first time the list was generated.

The Cursor Stability Isolation Level

The Cursor Stability isolation level is even more relaxed than the Read Stability isolation level in the way it isolates one transaction from the effects of other concurrent transactions.

The cursor stability isolation level only locks the row that is currently referenced by a cursor that was declared and opened by the owning transaction. (The moment a record is retrieved from a result data set, a pointer—known as a cursor—will be positioned on the corresponding row in the underlying table, and that row will be locked. The lock acquired will remain in effect until the cursor is repositioned—more often than not by executing the FETCH SQL statement—or until the owning transaction terminates.) And because only one row-level lock is acquired, more transactions can run concurrently.

The Cursor Stability isolation level is the isolation level used by default.

When a transaction using the Cursor Stability isolation level retrieves a row from a table via a cursor, no other transaction is allowed to update or delete that row while the cursor is positioned on it. Other transactions, however, can add new rows to the table as well as perform update and/or delete operations on rows positioned on either side of the locked row—provided the locked row itself wasn't accessed using an index. Once acquired, the lock remains in effect until the cursor is repositioned or until the owning transaction is terminated. (If the cursor is repositioned, the lock being held is released and a new lock is acquired for the row to which the cursor is moved.) Furthermore, if the owning transaction modifies any row it retrieves, no other transaction is allowed to update or delete that row until the owning transaction is terminated, even though the cursor may no longer be positioned on the modified row.

As you might imagine, when the Cursor Stability isolation level is used, if the same SELECT SQL statement is issued two or more times within the same transaction, the results returned may not always be the same. In addition, transactions using the Cursor Stability isolation level will not see changes made to other rows by other transactions until those changes have been committed.

Real world example: Let's reserve the hotel again by using Cursor Stability

When a customer scans the database for a list of rooms available for a given date range and then views information about each room on the list produced (one room at a time), you (the manager) will be able to change the room rates for any room in the hotel except the room the customer is currently looking at (for the date range specified). Likewise, other customers will be able to make or cancel reservations for any room in the hotel except the room the customer is currently looking at (for the date range specified). However, neither you nor other customers will be able to do anything with the room record the first customer is currently looking at. When the first customer views information about another room in the list, you and other customers will be able to modify the room record the first customer was just looking at (provided the customer did not reserve it for himself). Again, neither you nor other customers will be able to do anything with the room record at which the first customer is currently looking.

The Uncommitted Read Isolation Level

The Uncommitted Read isolation level is the least restrictive isolation level available. In fact, when the Uncommitted Read isolation level is used, rows retrieved by a transaction are only locked if the transaction modifies data associated with one or more rows retrieved or if another transaction attempts to drop or alter the table the rows were retrieved from.

This isolation level is typically used for transactions that access read-only tables and views and for transactions that execute SELECT SQL statements for which uncommitted data from other transactions will have no adverse affect.

As the name implies, transactions running under the uncommitted read isolation level can see changes made to rows by other transactions before those changes have been committed. However, such transactions can neither see nor access tables, views, and indexes that are created by other transactions until those transactions themselves have been committed. The same applies to existing tables, views, or indexes that have been dropped; transactions using the uncommitted read will learn that these objects no longer exist only when the transaction that dropped them is committed. (It's important to note that when a transaction running under this isolation level uses an updatable cursor, the transaction will behave as if it is running under the Cursor Stability isolation level, and the constraints of the Cursor Stability isolation level will apply.)

Real World example :  Haven't you reserve the hotel yet?? ooh poor boy..

When a customer scans the database to obtain a list of available rooms for a given date range, you (the manager) will be able to change the room rates for any room in the hotel over any date range. Likewise, other customers will be able to make or cancel reservations for any room in the hotel, including the room at which the customer is currently looking. In addition, the list of rooms produced for the first customer may contain records for rooms for which other customers are in the processing of reserving or canceling reservations.

Choosing the Proper Isolation Level

So how do you decide which isolation level to use? The best way is to identify which types of phenomena are unacceptable, and then select an isolation level that will prevent those phenomena from occurring. A good rule of thumb is:

• Use the Repeatable Read isolation level if you're executing large queries and you don't want concurrent transactions to have the ability to make changes that could cause the query to return different results if run more than once.

• Use the Read Stability isolation level when you want some level of concurrency between applications, yet you also want qualified rows to remain stable for the duration of an individual transaction.

• Use the Cursor Stability isolation level when you want maximum concurrency between applications, yet you don't want queries to see uncommitted data.

• Use the Uncommitted Read isolation level if you're executing queries on read-only tables/views/databases or if it doesn't matter whether a query returns uncommitted data values.

Always keep in mind that choosing the wrong isolation level for a given situation can have a significant negative impact on both concurrency and performance—performance for some applications may be degraded as they wait for locks on resources to be released.

Specifying the Isolation Level to Use

Although isolation levels control concurrency at the transaction level, they are actually set at the application level. Therefore in most cases, the isolation level specified for a particular application is applicable to every transaction initiated by that application. (It is important to note that an application can be constructed in several different parts, and each part can be assigned a different isolation level, in which case the isolation level specified for a particular part is applicable to every transaction that is created within that part.)

For embedded SQL applications, the isolation level is specified at precompile time or when the application is bound to a database (if deferred binding is used). In this case, the isolation level is set using the ISOLATION [RR | RS | CS | UR] option of the PRECOMPILE and BIND commands.

DB2 Version 8.1 and later provides a WITH clause (WITH [RR | RS | CS | UR]) that can be appended to a SELECT statement to set a specific query's isolation level to Repeatable Read (RR), Read Stability (RS), Cursor Stability (CS), or Uncommitted Read (UR). A simple SELECT statement that uses this clause looks something like this:

SELECT * FROM employee WHERE empid = '001' WITH RR

I hope after reading this article you will no more have any problems on isolation level.

Understanding Locking in DB2 - IV

Read Part-I 

Read Part-II

Read Part-III

Lock duration

Lock Duration is a very important concept in locking when the lock states are not compatible with each other.This is the reason, you get those famous deadlock and timeout issues.So you should have sound knowledge of this concept before designing your program.

The duration of a lock is defined as the length of time a lock is held and varies according to the type of lock. Page or row locks are acquired when individual rows are accessed and are released at different times, depending on whether the data is accessed for read-only or for update and on the isolation level specified.High level locks are typically acquired implicitly, based on options specified at BIND or REBIND time.
Because DB2 does implicit high level locking, it is critical that the application designer understand the options that influence when high level locks are acquired, when they are released, and the state of the lock.

Duration of table space, partition, and table locks

Every plan executed in DB2 must acquire a high level lock that can either be a table space or partition lock and, if the table space is segmented, then a table lock as well. The exception this rule is that locks are not acquired on the table space or table if uncommitted read isolation is specified. However, DB2 does acquire a mass delete lock. A mass delete is when a DELETE statement is executed with no WHERE clause such that all rows are deleted. DB2 uses the mass delete lock to serialize a mass delete process with an uncommitted reader.

DB2 provides options to control the point at which the high level locks are requested.

The BIND and REBIND options ACQUIRE and RELEASE is used to control when the high level locks are taken and released.

The possible values for the ACQUIRE option are ALLOCATE and USE. If you use ACQUIRE(ALLOCATE), all of the needed locks on all of the table spaces, partitions, and tables used by the plan are acquired at the first SQL call. If you use ACQUIRE(USE), only the needed locks on the table spaces, partitions, and tables are acquired for a shorter time, as and when the objects are accessed.

DB2 packages and dynamic SQL always use ACQUIRE(USE).

The time when the high level locks are released is influenced by the RELEASE option. The possible values for this option are DEALLOCATE and COMMIT. If you use RELEASE(DEALLOCATE), the locks are released only when the thread terminates. If you use RELEASE(COMMIT), the locks are released at commit time unless there are held cursors.


Duration of page or row locks

The duration of page or row locks depends on whether they are acquired and released in a read-only or read and write environment.

Read-only

BIND and REBIND ISOLATION option controls when page or row locks are acquired and released in a read-only environment.

The duration and number of page or row locks depend on the nature of the process and the value for the ISOLATION option specified at BIND or REBIND. The most common ISOLATION option is cursor stability (CS), which is the default as of DB2 9 for z/OS. With cursor stability as the ISOLATION option, DB2 returns committed data and provides stability of data under updateable cursors. If a query is answered through a work file,

For example, a concurrent thread may update the data in the database that generated the data in the work
file.The repeatable read (RR) isolation retains page locks even though the process logic moves through several pages in the same table during a unit of work. If the pages are accessed again, repeatable read provides repeatability: the data cannot be changed by any other concurrent process. This is true for read stability isolation also.

Read and write

A process that uses a cursor adds another dimension to locking. Pages read acquire a U-lock, initially indicating update intent. But before a column can be updated or a row can be deleted, the U-lock on the page is changed to an X-lock and is released at commit.


Here we come to an end of this Understanding of locking series.I will explain Isolation level in detail in different post as this topic deserve it, due to its immense importance to the programmer.Then there are various other important concept in terms of locking as lock avoidance,timeout,deadlock etc. which will be covered in coming posts.

Understanding Locking in DB2 - III

 Read Part-I 

 Read Part-II

In this third installment ,we will discuss the Compatibility rules for lock states.


The major effect of the lock state is to determine whether one lock state is compatible with another. Locks of some states do not exclude all other users. Assume that process A holds a lock on a table space that process B also wants to access. DB2 requests, on behalf of process B, a lock of some particular state. If the lock mode of process A permits a request from process B, then the two lock states are said to be compatible.

If the two locks are not compatible, process B cannot proceed. It must wait until process A releases the lock.

Compatibility of page and row lock states.

   I will just draw the table which will make you better understand it.

 This table shows whether page or row locks of any two states are compatible (Yes) or not (No).


No question of compatibility of page lock with a row lock can arise, because a table space cannot use both page and row locks.


Two or more processes can concurrently access data by using a page or row share lock (S lock). However, two or more processes cannot concurrently access data using a page or row update-intent lock (U lock). That is, only one process can acquire a U lock on a specific page or row at any instant. Even though a page or row may be U locked, other processes can access that page or row (but for read-only) by acquiring an S lock. If the process holding the U lock wants to do an update or delete, the U lock must be promoted to X lock before this can be
done.

However, the promotion to an X lock does not occur if any other concurrent process holds an S lock on that page or row. Eventually, assuming the S locks are released before the timeout interval, the U lock is promoted to an X lock. While a page or row is U locked, if any other concurrent process wants to do an update or delete, it must first acquire a U or X lock, but has to wait and eventually either the U or X lock is acquired or a timeout occurs.

Compatibility of table space, partition, and table lock states

Compatibility for table space, partition, and table locks is slightly more complex.

Below table shows whether or not the high level locks of table space, partition, or table of any two modes are compatible.






The next and the final post on this topic will be coming soon, which will be on Lock duration.

Understanding Locking in DB2 - II

This is the second installment of locking concept ( Check the first part here ).

In first part, we saw how on various objects locks can be taken and the types of lock (Lock states) for those objects.

In this part we are going to discuss in detail what are these different lock states and their meaning.In the sense when i say that there is an Intent share lock on my table then what does it exactly mean, what kind of lock my particular program holds and more importantly what kind of lock it allows to other application on the same object(resource).

Let's first discuss for all the lock states for high level lock (Table space, partition, and table).

( If you have any problem in understanding the terms here such as high level and level locks or lock state , then just check out the first part).


As discuss , there are the six possible lock states for a high level lock :

• Intent share (IS)

The lock owner can read data in the table space, partition, or table, but not change it. The lock owner may acquire an S(Share) lock on the row or page containing the row. Concurrent processes may both read and change data.

• Intent exclusive (IX)

The lock owner and concurrent processes can read and change data in the table space,partition, or table. The lock owner acquires a U(Update) lock or S (Share) lock on a page or row. When the data is to be changed, the U lock or S lock is promoted to an X(Exclusive) lock.

• Share (S)

The lock owner and concurrent processes can read, but not change, data in the tablespace, partition, or table.There are no S locks on a page or row.

• Update (U)

The lock owner can read, but not change, the locked data. However, the owner can promote the U lock to an X lock and then change the data. Processes concurrent with the U lock can acquire an S lock and read the data, but no concurrent process can acquire a U lock. The lock owner does not need page or row locks.

• Share with intent exclusive (SIX)

The lock owner can read and change data in the table or table space. Concurrent processes can read the data in the table or table space, but not change it. Only when the lock owner changes data is the X lock acquired on a page or row.

• Exclusive (X)

The lock owner may read and change data in the table space or table. Only the concurrent processes using uncommitted read isolation may access the table space or table.

Now we will discuss the lock states for the low level locks ( Page and row).

There are the three possible lock states (S,U AND X) for the low level lock size of a page or row.

Remember (As mentioned in previous post) these LOCKS are applicable only if there is an intent lock on the table space or partition or the table).

• Share lock

The lock owner and any concurrent process can read, but not change, the locked DB2 object. Other concurrent processes may acquire share or update locks on the DB2 object. Also called an S lock.

• Update

The lock owner can read the DB2 object and has the intention to change it. Concurrent processes may acquire share locks and read the DB2 object, but no other process can acquire an update lock. Update locks are promoted to exclusive locks before DB2 actually changes the DB2 object. Promotion to exclusive lock may cause a suspension if other processes are holding share locks. Also called a U lock.

• Exclusive

Only the lock owner can read or change the locked data, with the following exceptions. Concurrent applications using uncommitted read isolation can read the exclusively locked data. Also, if lock avoidance techniques indicate that the exclusively locked data is require currency of data.

Fair enough, In next part we will discuss compatibility between various lock states.