Hello DB2 gurus,

I have a database which is around 600GB and all tables are in a single SMS tablespace of 8KB size. To get better performance issues, we want to move those tables into a DMS tablespace with multiple containers. Can you please suggest the procedure to do that.

If I do an export and load, will I have to export the syscat tables too?

Also, will I have space limits issues with the DMS tablespace and my current tablespace is very huge.

Thanks.

You do not have to move the DB2 system catalog to DMS. It is probably in a different tablespace (SYSCTSPACE) than your user tables, and just make sure that the tablespace used for the system catalog is assigned to its own dedicated bufferpool with about 5000 4K pages. Also, consider increasing the CATALOGCACHE_SZ in your database config (db2 get db cfg) to 4096 pages. Once you have enough bufferpool memory and catalog cache for the entire catalog, then disk performance is completely irrelevant.

But you do need to worry about disk performance if you have 600 GB of user data and have a data warehouse with a lot of table scans. But if almost all of the queries are returning only a few rows with index access (no table scans) in an OLTP type system, then SMS vs. DMS is irrelevant (SMS is fine).

There are limits for tablespace sizes based on page size (see Appendix A of the SQL Reference Vol 1):

4K - 64 GB
8K - 128 GB
16K - 256 GB
32K - 512 GB

If you have a data warehouse, you will probably want large page sizes anyway (32K) for the data. Use a different tablespace for indexes so you can assign it to a separate bufferpool than the data (use a smaller page size for indexes if possible, even if you need to have multiple index tablespaces because of size limitations). So you should probably have about 3 bufferpools as follows:

- A 5000 4K page bufferpool for system catalog tablespace
- One Large 4K bufferpool for indexes and small tables
- ONe Large 32K bufferpool for large tables and TEMPSPACE32

Notes:

1. The size of the Large 4K and 32K Bufferpools should be roughly equal in total size (bytes) and dependant on the amount of memory available in your server. Just remember that the 4K bufferpool needs 8 times the number of pages to be the same size as a 32K bufferpool.

2. Create a TEMPSPACE32 tablespace of type SMS of 32K page size and use the 32K bufferpool, and delete the 4K TEMPSPACE1 tablespace.

Make sure your prefetch size is a multiple of your extent size times the number of containers for each tablespace (or number of disks per array). The Create Tablespace wizard in the Control Center will help you figure out how to set that up correctly.