In DB2 when a select statement is executed, it is taking lot of time and the table is being locked.

80 pts.
Tags:
DB2
DB2 query
SELECT statement
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    69,175 pointsBadges:
    report
  • Yorkshireman
    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..
    5,580 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following