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?

Marcus, That's a thought-provoking question ...

To the best of my understanding, the system catalog tables contain information that can be accessed using SQL , organized, like any other table, using pages, extents etc. But, how will DB2 use this information ? It has to format the data in a 'better' manner for it to 'understand' or for it to perform better ... Well, on first access to a table/database object, DB2 converts information to such an internal format ... To avoid this conversion whenever an object is referenced, the internal format is inserted into a memory area, called catalog cache ..

The catalog cache, stores the following information
1) Table information
2) Package Descriptors (Package cache has acces plans, whereas catalog cache has just the descriptors)
3) Authorization information for tables, view, packages, database etc.
4) Information on SPs, UDFs etc

There may be more...

If you are familar with DB2 on zOS, though I know it cannot be a like-to-like comaprison, the catalog cache has information similar to the DB2 Directory ... On the mainframe too, the DBDs in the direcotry are loaded into the EDM pool memory for performance reasons...

A quick look at how catalog cache is used :

When query referneces a tables , the catalog cache is checked to see if the descriptor for the table is available ... If yes, then use that information ... If no, then go to the sytem catalog table to get the information and convert them to internal format
1) If there is free space in the cache, insert the descriptor
2) If there is no free space, look for unused objects sitting in the cache
a) If an unused object descriptor is found, then clear that space and insert this descriptor
b) If all objects are being used, then it is written to the database shared memory and this is called catalog cache overflow.

I'm not sure if this can be called double buffering ...

HTH

Sathyaram

I am familiar with the DBD on z/OS. But what about the indexes and stats used for query optimization (assuming the package is not in cache)? Do these require access to the DB2 Catalog via the buffer pools?

I'm not 100% sure, but I guess the stats and indexes will also will be stored in the catalog cache ...

But it may be worthwhile using the snapshots and looking at what it does

Cheers
Sathyaram