Lock escalation in SYSCOLDIST

Praveen_db2

Hi All
Db2 v8.1.3 ,Windows
I am seeing this message quite often in db2diag.log

ADM5502W The escalation of "1205" locks on table "SYSIBM .SYSCOLDIST" to
lock
intent "S" was successful.

In db server there are 8 databases in a single instances.
Four of these databases have same database objects but with different
versions and are used at
different stages of development.The stats have been updated 3 days ago and
there is no major change in database after that.

Please advise how to avoid this message and what can be the possible
reasons behind it

TIA
Praveen

Apr 6 '06 #1

m0002a

Praveen_db2 wrote:

Hi All
Db2 v8.1.3 ,Windows
I am seeing this message quite often in db2diag.log

ADM5502W The escalation of "1205" locks on table "SYSIBM .SYSCOLDIST" to
lock
intent "S" was successful.

In db server there are 8 databases in a single instances.
Four of these databases have same database objects but with different
versions and are used at
different stages of development.The stats have been updated 3 days ago and
there is no major change in database after that.

Please advise how to avoid this message and what can be the possible
reasons behind it

TIA
Praveen

DB2 is reading that table to determine the best access path needed for
SQL statements. Since they are share locks, it should be of no concern
to you.

You are obviously collecting a lot distribution stats in your runstats
command and it appears likely that you are not getting a lot of package
reuse from package cache (applications not using prepared statements in
programs).

Apr 6 '06 #2

Praveen_db2

Hi
Thanks for the reply.
You are correct.I collect stats using "WITH DISTRIBUTION" clause.Actually
I was of thought the more detailed the stats are,the more help db2 can get
to make better access plans.But it seems the more the options for db2,more
the time it takes to optimize it.
Regarding package cache our applications have a lot of static and dynamic
sql used.About 3 weeks back I was getting error of Package Cache
overflow.I increased the cache for it and that error is gone.Can you tell
me how to increase the package reuse??
TIA
Praveen

Apr 6 '06 #3

m0002a

You might want to cut back and only collect distribution on key
columns, unless you have a data warehouse system.

You will get package reuse in cache if the SQL statements are identical
to previous ones that already reside in cache. This would include most
static SQL with host variables, or Prepared Statements with parameter
markers (?). It would also include dynamic SQL with the exact same
literals as perviously used in the predicate (WHERE clause) as previous
SQL statements, but this may unlikely for most applications.

Apr 6 '06 #4

Pierre Saint-Jacques

With DISTRIBUTION option is only useful under the following conditions:
1) Several 100k or m rows
2) Columns chosen for distribution have non-uniform (skewed) distribution
3) Columns are used in EQUAL = predicates ( take frequency)
or
Columns are used in range delimiting (take quantiles)
4) No paramater markers and or host variables. (Unless you want to get in a
discussion about REOPT)

As a guideline, start with those 4 rules, take distribution stats only for
those columns and once you got what you think is right, save the profile in
your last runstats command.
That way, you can come back to that table and runstats again with USE
PROFILE and not have to temember.

HTH, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Praveen_db2" <pa************@gmail.com> a écrit dans le message de news:
5b******************************@loc...tdatabases.com...

Hi
Thanks for the reply.
You are correct.I collect stats using "WITH DISTRIBUTION" clause.Actually
I was of thought the more detailed the stats are,the more help db2 can get
to make better access plans.But it seems the more the options for db2,more
the time it takes to optimize it.
Regarding package cache our applications have a lot of static and dynamic
sql used.About 3 weeks back I was getting error of Package Cache
overflow.I increased the cache for it and that error is gone.Can you tell
me how to increase the package reuse??
TIA
Praveen

Apr 6 '06 #5