As a database administrator, you need to estimate the size of tables and indexes, and to check the amount of space available in a table space adding more space to an existing table space when it gets full.
This section describes how to:
You can estimate the amount of storage space required for new or existing tables or indexes by invoking the Estimate Size dialog. Invoke this dialog by selecting individual tables and indexes and clicking mouse button 2 on them, or select Estimate Size from the Create Table and Create Index windows. The size is estimated on the definition of the particular table and its dependent indexes. The estimate is the projected amount of storage space that would be used when the table has a given number of rows. The minimum and maximum space is also estimated based on the smallest and largest size of variable length fields. When invoked on a table or an index, the Estimate Size dialog is prefilled with the specifications of the table, and contains numbers relating to the table and all of its dependent indexes. When you click the Refresh pushbutton, the estimated size, minimum size, and maximum size are updated based on the number you enter in the New total number of rows and New average row length fields.
Estimating the size of a table or index is helpful if you want to:
| Note: | When you use Estimate Size on an Enterprise-Extended Edition product, the size estimates are based on the logical size of the data in the table and not on the database partition. |
If you have not updated the statistics for the table for some time, you can click the Run statistics pushbutton to update the statistics for the selected table. If you select an index and then press the Run statistics button, the statistics are run on the related table.
To estimate the size for a table:
To check the amount of space available in a DMS table space:
You can customize the order of the columns and which columns are displayed by using the Customize Columns icon at the bottom of the Contents pane.
To check the amount of space available in an SMS table space, use the facilities provided by your operating system to monitor space usage and to ensure that available room in the directory for the table space is not exhausted.
Capacity for a DMS table space is the total size of containers allocated to the table space. When a DMS table space reaches capacity (depending on the usage of the table space, 90% is a possible threshold), you should add more space to it. The database manager will automatically rebalance the tables in the DMS table space across all available containers. During rebalancing, data in the table space remains accessible.
For a DMS table space that has reached its capacity, you can add another container:
In general, you cannot extend the size of an SMS table space very easily because SMS capacity depends on the space available in the file system and the maximum size of the file supported by the operating system. However, depending on your operating system, you may be able to increase the size of a file system using the operating system facilities. For an SMS table space on a UNIX-based system, you can increase the size of the table spaces by using the appropriate UNIX-based system commands. See the documentation for the UNIX-based system you are running. If the file system containing the SMS table space also contains non-DB2 files, you may be able to move these files to another file system, thus making more room available in the file system for DB2's use. You can also perform a redirected restore which involves restoring a table space into a larger number of containers than it was backed up from. You can perform a redirected restore from the Restore Database notebook: From the database you want to restore, select Restore -> Database from the pop-up menu.