Size of the root dbspace

You can calculate the size of the root dbspace, which stores information that describes your database server.

The following storage structures can be stored in the root dbspace:

Physical and logical logs (200 KB minimum for each type)

Although the root dbspace is the default location for the physical log and logical log files, move the log files to other dbspaces. You can set the AUTO_LLOG configuration parameter to specify the dbspace for logical log files. You can store the physical log in a plogspace.

Recommendation: Set up the system with a small physical log and a few small logical logs. For example, create three 1000 KB logical log files, or 3000 KB for the total log space. After the initial setup is complete, create a new dbspace for logical logs in an area that does not compete for I/O with other dbspaces, and set the AUTO_LLOG configuration parameter to that dbspace. Create a set of larger logical-log files in the dbspace for logical logs, and drop the original logs from the root dbspace. Then create a plogspace for the physical log. Make the plogspace large enough to hold your final physical log, and isolate it from other dbspaces as much as possible. This configuration optimizes logging performance and the root dbspace for the following reasons:

• The unused space that is left in the root dbspace after you move the logs is minimized.
• The physical and logical logs do not contend for space and I/O on the same disk as each other or the root dbspace.
• The server automatically increases the total logical log space and the size of the physical log if increasing logs measurably improves performance.

Recommendation: Set up the system with a small log size (for example, three 1000 KB log files, or 3000 KB for the total log size). After setup is complete, create new dbspaces, move and resize the logical-log files, and drop the original logs in the root dbspace. Then move the physical log to another dbspace. This procedure minimizes the effect of the logs in the root dbspace because:

• A large amount of space is not left unused in the root dbspace after you move the logs.
• The logs do not contend for space and I/O on the same disk as the root dbspace.

Temporary tables

Analyze user applications to estimate the amount of disk space that the database server might require for temporary tables. Try to estimate how many of these statements are to run concurrently. The space that is occupied by the rows and columns that are returned provides a good basis for estimating the amount of space required. The largest temporary table that the database server creates during a warm restore is equal to the size of your logical log. You calculate the size of your logical log by adding the sizes of all logical-log files. You must also analyze user applications to estimate the amount of disk space that the database server might require for explicit temporary tables.

Data

Although the root dbspace is the default location for databases, do not store databases and tables in the root dbspace.

System databases (the size varies between versions)

The sysmaster, sysutils, syscdr, and sysuuid databases, and the system catalogs must be stored in the root dbspace. The sysadmin database is stored in the root dbspace by default, however, you can move the sysadmin database to a different dbspace.

Reserved pages (~24 KB)

The reserved pages contain control and tracking information that is used by the database server. Reserved pages must be stored in the root dbspace.

Tblspace tblspace (100 - 200 KB minimum)

The tblspace tblspace contains information about tblspaces. The tblspace tblspace must be stored in the root dbspace.

This estimate is the root dbspace size before you initialize the database server. The size of the root dbspace depends on whether you plan to store the physical log, logical logs, and temporary tables in the root dbspace or in another dbspace. The root dbspace must be large enough for the minimum size configuration during disk initialization.

Allow extra space in the root dbspace for the system databases to grow, for the extended reserved pages, and ample free space. The number of extended reserved pages depends on the number of primary chunks, mirror chunks, logical-log files, and storage spaces in the database server.

If you need to make the root dbspace larger after the server is initialized, you can add a chunk to the root dbspace. You can enable automatic space management to expand the root dbspace as needed.