473,882 Members | 1,628 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Internal Catalog Cache Lock

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
3 6682
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
Raj

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

Select:
Database partition = 1
Lock Name = 0x0000006300004 70365F7B0A043
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 = 0x0001000007800 00065E6DA2043
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
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 = 0x0000006300004 70365F7B0A043
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 = 0x0001000007800 00065E6DA2043
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
7367
by: Jane | last post by:
Our db2diag.log is full of messages like this: 2004-05-31-17.15.10.383766 Instance:tminst1 Node:000 PID:394948(db2agent (TMDB1) 0) TID:1 Appid:GA140956.EF26.03A4B1202647 data management sqldEscalateLocks Probe:3 Database:TMDB1 ADM5502W The escalation of "4759" locks on table "I2TM .SHPM_T" to lock intent "S" was successful. The message is always about Shared locks. What can I do to eliminate these errors?
3
4948
by: Amy DBA | last post by:
Hi all, I'm not new to DB2, but I am new to Version 8. Does this mean I should increase my CATALOGCACHE_SZ ? The old diag log messages used to mention which specific parameter was causing problems, so that's why I'm not sure. Catalog Cache Overflow total memory : 1067540 memory needed: 2580 current configured size: 1064960 current used size (OSS): 1064388
6
4000
by: Mark | last post by:
I understand the concept of catalog cache (memory allocated from the dbheap to allow catalog lookups without the need to access disk each time). But the DB2 catalog tablespace (SYSCATSPACE) is assigned a buffer pool, which seems to perform the same function as catalog cache. Is this simply a case of double buffering? Can anyone clear this up?
1
1712
by: hype | last post by:
Hi, could anyone explain the relationship between the "Catalog cache heap" in the db2mtrk output and the database config parameter catalogcache_sz. Thanks, Hype.
4
5373
by: Mat | last post by:
Hi, I've stumbled onto a problem when using the caching object in ASP.Net. I'm placing a static dataset to the cache as the data only changes once a day. Whilst writing to the cache I'm using a lock using code like below (just typed this in); Cache thisCache = HttpContext.Current.Cache lock(thisCache)
2
5427
by: rrossney | last post by:
Please look at the "what I've already done" section of this message before responding to it: I believe that I've done everything that the people who experience this error are typically told to do. I have created an ASP.NET web service that I have running on my development machine, and am trying to get it to run on my test server. Both machines are running Windows 2000 SP4, IIS 5, and the .NET 2.0 framework. When I attempt to access...
1
3067
by: Ramchandra | last post by:
Hi , i am facing problem in package cache hit ratio its between 47-45% which is very less:- here are my configuration related to package cache:- Catalog cache size (4KB) (CATALOGCACHE_SZ) = 2500
6
7729
by: michael.spoden | last post by:
Hi, how can I fix lock-waits during an online backup? Is an online backup in DB2 V8.2 not realy online? I'm using DB2 V8.2 Fixpak 15 on Linux. The command to perform the backup is: db2 backup db BPEDB online to / var/tmp During the backup the application hangs on just one table
3
7628
by: Frederick Tant | last post by:
DB2 V9.7 The DB is used by 1 application with no concurrent applications. I got the problem that large update/insert cause lock escalation, to solve the issue I set a lock on the table so there's no row locking any more. BUT after setting a lock on a table (LOCK TABLE <table> IN EXCLUSIVE MODE;) I remain having the message " DB2 is performing lock escalation". This together with very bad performance. Any sugestion why LOCK table...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11106
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10724
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10829
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7113
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5978
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4198
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3226
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.