For system catalog tablespace, what do you recommend?

- SMS with filesystem caching
- DMS with filesystem caching

- SMS with no filesystem caching
- DMS with no filesystem caching


For all DMS -> file container

Hi,

i'ld use DMS with no filesystem caching. The Systables don't have many changes in their structure and also there won't be many table created and dropped in the syscat. If you want to keep it in memory you could adjust the size of the IBMDEFAULTBP and use different BPs for the User-Tables.

Also Automatic Storage uses DMS for syscatspace.

I'ld also use File Containers for DMS, because they are easier to manage.

By default, syscatspace is DMS with no filesystem caching (I believe staring with v9.5). System catalog tables have many LOB columns so that is why I'm questioning about whether filesystem caching should be enabled for it.

I believe that I have figured out why DMS is now the default for SYSCATSPACE starting in 9.7 (and maybe in later fixpacks for 9.5). PureScale requires that all tablespaces be automatic storage and all must be DMS. A DBA can convert from SMS to DMS in most cases, except for the SYSCATSPACE which requires that a new database be created (with db2look and db2move to transfer schema and data) if the old one is SMS for SYSCATSPACE.

Regarding file system caching for SYSCATSPACE, I thought that LOB data in SYSCATSPACE was cached in the CATALOGCACHE_SZ heap, so I would not think that file system caching is needed. I prefer to create a dedicated bufferpool for SYSCATSPACE of about 4000 4K pages (or equivalent) to make sure the regular SYSCATSPACE (non-LOB) data is always cached.

I asked this question on another forum and here's the reply:

"I would suggest not using SMS. DMS is the default and for any new tablespace (other than temps), should always be the first choice.

In general what operations are being done on the catalog tables? If it is just simply using them when compiling queries, then the catalog cache will kick in for those operations and whether lobs are cached or not is a non-issue performance-wise.

If there are lots of direct queries on the catalog tables which access lobs, then there might be a performance difference and you can turn on filesystem caching on DMS without issue.

For 99% of database though it really doesn't matter what the tablespace is though (due hot data being in the catalog cache) and the default would be the best in those cases."


----

I used to have a good list of what gets cached in the cat cache, but it looks like this email got purged. Packed descriptors for tables, views, etc... are LOBs and they cached along with some other info (I use db2pd with -cat to see what's cached). Some are now inlined, but I'm still seing direct reads for SYSCATSPACE (assigned to its own bufferpool) when executing queries against user tables (maybe that's when a user table is first accessed and its info gets loaded and/or not all LOBs get cached in the catalog cache ?)

The above answer in the other forum doesn't seem definitive to me, especially in terms of "why", even if the advise is good. SMS was always the default for syscatspace and no one had concern about syscatspace SMS performance.

I will stick to my theory about PureScale being the reason why the default for syscatspace was changed to DMS, since the tables in that tablespace cannot be moved to another tablespace, and a new database would have be created if PureScale was ever implemented for a database (PureScale requires that all tablespaces be DMS managed by automatic storage).

The default for System Temporary is still SMS (I believe), but those are easy to change if moving to PureScale (just drop them and recreate as DMS).

Do you use SMS or DMS for SYSCATSPACE?

I have always taken the default in the past, which means some of them are SMS. I am not sure if the default of DMS for syscatspace was first changed in 9.7 or in a 9.5 fixpack, but I know that early 9.5 releases it was SMS as default. I would advise anyone to make sure that syscatspace is DMS for all new databases, just in case they ever need PureScale. Automatic Storage is also a requirement for PureScale.

If SYSCATSPACE is defined as SMS, then:

"If large object (LOB) data is not inlined, then the following statements apply:

For SMS table spaces, non-buffered I/O access is not requested for long field (LF) data and large object (LOB) data even when the NO FILE SYSTEM CACHING table space attribute is set. Buffering occurs in the file system cache, subject to operating system configuration and behavior, and potentially improves performance."

IBM DB2 9.7 for Linux, UNIX and Windows Information Center


Regardless of whether SMS-SYSCATSPACE is defined using FILE SYSTEM CACHING or NO FILE SYSTEM CACHING, LOB/LF is always cached in the filesystem. So, this means that some catalog LOB data can get cached in two places -cat cache and filesystem cache. Right?

you need to use file system caching otherwise there will be I/Os which are slower. LOBs are not cached by db2 unless they are inlined.

from db2 v9.7 manual:

Using LOB or LONG data
When an application retrieves either LOB or LONG data, the database manager does not cache the data in its buffers, Each time an application needs one of these pages, the database manager must retrieve it from disk. However, if LOB or LONG data is stored in SMS or DMS file containers, file system caching might provide buffering and, as a result, better performance.

Because system catalogs contain some LOB columns, you should keep them in DMS-file table spaces or in SMS table spaces.