To my knowledge, the catalog cache is used to store table and object
descriptors (from the catalog tables) that are required to resolve,
compile SQL statements.
When a stmt is to be compiled, DB2 does a loookup in the catalog cache
for the descriptors of the objects required for the work and will insert
the ones it does not find.
From your previous post where you mention function bodies being held in
longvarchar, it is possible that the function is folded as a descriptore
on the catalog cache (but I don't really know this) but it would still
be a direct read as the only buffered read that I know of are the reads
that are done in the bp (for syscatspace) in this instance.
From your previous post, you mention a syscatcpace of 64MB and wonder
if you could het it all in memory. Yes you could could by building a
64MB buffer pool and assigning syscatcpace to it.
However, this still does not solve your problem of eliminating the
direct i/o reads. They are longvarchar and they still won't get stored
in the row and will have to read from the file in which they are stored.
Instead of giving that memory to a bp for syscatcpace, try to see what
happens if you assign the same space to catalog cache.
You are in V7 so be careful as the catalog cache is assigned pages from
the dbheap and you will have to increase it by 64MB before you update
the catalog cahe to 64MB.
HTH, Pierre.
Ian wrote:
[color=blue]
> Pierre Saint-Jacques wrote:
>[color=green]
>> If one looks at how LONGVACHAR is handled in DB2, it will always be
>> handled by direct I/O and thus will NEVER go to a buffer pool. So,
>> loading all syscatcpace tables in a private syscat.spce buffer pool
>> wouldn't be the right approach.
>> There's no sense to load LONGVARCHAR in bp's as they would flood the
>> bp and impact all other apps.
>> They are therefore retrieved one by one, on demand, to the appl.
>> private memory of the agent requesting them. Thepointer is read from
>> the row in the page in thwe bp but the data is directly retrieved,
>> using the pointer, by reading the file and the long data is put in
>> appl. private memoty.[/color]
>
>
> Isn't this mitigated by the catalog cache? I asked the OP about his
> catalog cache, not the bufferpool hit ratio for the catalog.
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
>
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----[/color]
--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.
