In DB2 when a select statement is executed, it is taking lot of time and the table is being locked.
In DB2 when I'm giving a select statement its taking lot of time, the reason is like the table is a very huge table.
As a result some times it is locking the table.
Can any one help me for executing the select query with any option which would not lock the table.
Thank you in advance.
Software/Hardware used:
Software/Hardware used:
ASKED:
March 24, 2009 12:23 PM
UPDATED:
August 6, 2009 2:08 PM
Answer Wiki:
DB/2 on what platform?
Locking occurs because of commitment level
setting commitment level depends on the environment you are issuing the Select from.
Summary:
Need to know Platform?
Need to know Programming tool that select is issued from?
Phil
Hi, I'm using DB2 in AIX environment. That is what I can give u the details as far as I know.
Atleast for a simple select statement its lcoking and I'm looking for any option which would not lock the table even If I give any select statement just for a safe side.
We can only access the tables from Unix environment and also I issue statements in Unix environment on super user login. But we can't know any thing more than this.
Thank you for the reply.
////////////////////////////////
Sorry, I cann't help -- I'm an AS/400 pert. But it does sound like your commit level.
Hopefully an DB2-AIX pert will pick it up.
Phil
*********************************************
Thank you so much for the reply Phil.
I'll try to see the possible ways. Thanks a lot.
-----------
If your SELECT is not restrictive enough (WHERE clauses) it is possible that the DB2 PAGE LOCKS will be excalted to a DB2 TABLE (or tablespace) LOCK. Db2 has parms that one can set to try to maintain some control. But the theory is that once DB2 is maintaing a certain large number of page locks, she figures it is cheaper to just lock the whole table and quit fooling with the hundreds/thousands of individual page locks.
several reasons for this behaviour.
> using RR (repeatable read) isolation will maintain locks even on a SELECT (generally this is badness)
> not restrictive enough search criteria is causing a tablesapce scan (where DB2 has to read every page of data)
Put those 2 things together and the whole table gets locked.
Steve
To see all answers submitted to the Answer Wiki: View Answer History.
I agree with Phil, the problem seems to be how the system is acquiring locks. It is often called ‘the isolation level’, and common levels are ‘Read Committed’, ‘Serializable’ and others. Look for some session setting for the ‘Isolation level’ in your environment.
Have you investigated security?
You say you are being the super user – does this mean that that user profile will, by default, have *exclusive access?
Maybe you need to look at user profiles / authority levels too..