DB2 Server for VSE & VM: Performance Tuning Handbook


Placing Tables into Dbspaces

Each large table should be placed in its own dbspace, so that rows from other tables do not have to be examined during a dbspace scan. Another advantage is that if you later wish to eliminate that table, you can do so with a DROP DBSPACE statement, which will run very fast because the data, index, and header pages do not have to be examined.

Very small tables may be grouped together in the same dbspace, because relatively few additional pages have to be read during a dbspace scan. However, avoid page level locking in this situation.

Dbspace scans are done during CREATE INDEX, DROP TABLE, and UPDATE STATISTICS processing, and may be used to satisfy other SQL requests, depending on index availability.

By default, locking takes place at the page-level. This is usually the best trade-off between concurrency and locking overhead. You should consider locking at the row-level when many applications access one small part of the database. The tables there could be put in their own dbspaces, for which you would request row-level locking (using an ACQUIRE DBSPACE or ALTER DBSPACE statement).

When you request row-level locking for a dbspace, key-level locking is also done for indexes in that dbspace. Key-level locking on indexes reduces contention, but increases overhead.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]