By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

Internal Catalog Cache Lock

P: n/a
Raj
I created a refresh deferred MQT, and during full refresh there were 4
or 5 lock waits, all waiting on a 'S' lock on Internal Catalog Cache ?
Can some one explain how to prevent this from happening?

Jul 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Raj:

Some things to explore.....

1. db2 get snapshot for locks on <dbname>
2. Do u have any indexes on this MQT ?
3. Did u run any EXPLAIN plans on this ?
what did the plan say ? Is that doing a Table scan?
Make sure that u have tuned this particular SQL and try the REFRESH
again.
And see if the problem goes away.
Also check what other applications are doing on the BASE tables while u
are doing this REFRESH.

cheers..
Shashi Mannepalli

Jul 6 '06 #2

P: n/a
Raj

Hi Shashi,
All the applications waiting for locks are doing selects
Following is from the snapshot

Select:
Database partition = 1
Lock Name = 0x000000630000470365F7B0A043
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 3
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Catalog Cache Lock
Mode = S
MQT:
Database partition = 1
Lock Name = 0x000100000780000065E6DA2043
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 255
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Catalog Cache Lock
Mode = X

the select statement used to build the MQT is not doing a tablescan. It
is an MDC table and is using one of the dimentions in the select.
THanks,
Raj

Shashi Mannepalli wrote:
Raj:
Jul 6 '06 #3

P: n/a
Raj:

This is a INTERNAL CATALOG CACHE LOCK with MODE S.
DB2 has often 3 types of Internal locks

P
V
S

These will be obtained when u run a SQL against the database.

As yours is a S lock i will try to explain S mode behaviour. For rest
of the modes check the adminstration guide.

S is a sequence lock to make sure that things happen in right order to
for each agent.
For example....u cannot drop a temporary tablespace is not dropped
while somebody else is using it...etc.

Generally S mode do not lock objects but in some cases they do.
For example

If u have a declared temp table.....it will put a S lock ...on the temp
tablespace so others cannot drop it. So in this case it puts a lock on
the tempspace.

In EEE sytem this is used in APM global cache processing.

Internal S - Package Cache sequencing lock

Please refer to the ADMIN PERFORMANCE GUIDE in what other cased this
will put a LOCK like this......

So that is why i have asked you to TUNE the SQL. So at the moment of
REFRESH it will be hard to avoid this lock. So u need to work around
it...

cheers....
Shashi Mannepalli
Raj wrote:
Hi Shashi,
All the applications waiting for locks are doing selects
Following is from the snapshot

Select:
Database partition = 1
Lock Name = 0x000000630000470365F7B0A043
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 3
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Catalog Cache Lock
Mode = S
MQT:
Database partition = 1
Lock Name = 0x000100000780000065E6DA2043
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 255
Hold Count = 0
Lock Object Name = 0
Object Type = Internal Catalog Cache Lock
Mode = X

the select statement used to build the MQT is not doing a tablescan. It
is an MDC table and is using one of the dimentions in the select.
THanks,
Raj

Shashi Mannepalli wrote:
Raj:
Jul 6 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.