Overview of space estimation
You can use space estimation for the following purposes:
- To project the number of tracks, cylinders, or blocks required to allocate the data set for that object
- To estimate the space requirements when you create a new structure or when you copy and modify an existing structure
- To estimate the space requirements for existing table spaces and indexes when the work load changes
ALTER and Change Manager can perform table and index space estimation by using statistics from the tables in the DASD MANAGER PLUS database (with the BMCSTATS utility), if the following conditions exist:
- You have the DASD MANAGER PLUS product installed (the DASDMAN installation option is set to Y).
- The installer selected to interface ALTER and Change Manager with DASD MANAGER PLUS.
If DASD MANAGER PLUS is not installed, ALTER and Change Manager use the Db2 catalog statistics (from the IBM RUNSTATS utility) to perform space estimation. If the Db2 catalog statistics are not available, ALTER and Change Manager use projected values for estimating table and index space. The products can also estimate space based on values that you provide.
Estimation of space for a table space
The space estimate for a table space is based on the number of pages, which is determined by the following attributes:
- Percent of free space on each page
- Percent of space reserved for update operations
- Free page frequency
- Number of rows expected in the table or partition
- Average row length
- Size for each partition
- Device type (for example, 3380)
- Percent compressed (reduces the average row length)
- Maximum number of partitions (for partition-by-growth table spaces)
You can estimate the space for a table space and modify the data structure definitions in the Tablespace Estimation panel (nonpartitioned)
Command ===> Scroll. . PAGE
Tablespace Name: R1ALL.T04SEG
Pagesize . . : 4 |------- Estimated -------|
Segsize . . . . 0 (0-64) | Pages : 2 |
Priqty . . . . -1 | Space : 48 K |
Secqty . . . . -1 | NbrSec : 0 |
Allocation Unit K (K,C,T) |-------------------------|
Device Type . . 3390
Freepage . . . 0 (0-255) Percent Compressed . . . 0 (0-99)
Pctfree . . . . 10 (0-99) Pctfree Update . . . . . -1 <DEFLT>,0-99
Compress . . . . N (Y,N) Lines 1 to 1 of 1
Maxrows . . . . 255 More: >
(CO=Columns) -Row Length- Avg -- Estimated --
Ac Owner Table Name Max Avg Orig NbrRows Rws/Pg Pages
************************************ TOP **************************************
R1ALL T_T04SEG 18 18 Proj 0 255 0
*********************************** BOTTOM ************************************
To estimate the space for a table space, you can modify the values for the fields that are listed in Attributes for estimating the table space (see the following table). When you modify one or more of these values, a space estimation marker ($) is added adjacent to the object on the Mixed List panel. The marker denotes that the object is stored in the CD tables by Specification solely because of space estimation parameters, and that no changes are requested which would affect analysis.
Attributes for estimating the table space
Field | Description |
|---|---|
Device Type | Type of disk device on which the Db2 data set is to be allocated Valid values are 3340, 3350, 3375, 3380, 3390, and 9345. |
Percent Compressed | Percentage used to reduce the average row length User-supplied percentages are stored on the CD tables. Percent Compressed is applied to the entire table space. It is not applied to the compressed average row length or estimated space if the average row length that is displayed was determined by the BMCSTATS utility and the table space or table space partition was already compressed. In that case, compression is already applied to those values. Valid values are 00 through 99. The default is zero. |
Avg Row Length | Average row length for the table The Avg Orig field indicates the origination or source of the Avg Row Length field. To derive the value of the Avg Row Length, the products gather information from one of the following sources (shown in order):
If a value for the average row length was saved in the product CD tables, space estimation uses that value, unless the value was projected. In that case, the products recalculate the value. |
NbrRows | Number of rows in the table |
To modify the data structure definitions, you can edit the values for the fields that are listed in Attributes for modifying the table space (see the following table). When you modify the definition, an 'A' is added adjacent to the object on the Mixed List panel. This marker indicates that a change request has been made for the object.
Attributes for modifying the table space
Field | Description |
|---|---|
Segsize | Segment size for the segmented table space |
Priqty | Primary space (expressed in Allocation Unit) that is allocated for the Db2-defined data set. Valid values are:
The default value comes from the catalog or data set. If the Allocation Unit is K, the value is rounded to a multiple of 4 KB. For example, if the Priqty is zero (1), the value is rounded to 4 KB. Entering the PE action code (or line command) for a partition propagates the estimated space to the Priqty field. Entering the PEALL command-line command propagates the estimated space to the Priqty field for all of the partitions in the table space. |
Secqty | Secondary space (expressed in Allocation Unit) allocated for the Db2-defined data set Valid values are:
The default value comes from the catalog or data set. If the Allocation Unit is K, the value will be rounded to a multiple of 4 KB. For example, if the SecQty is seven (7), the value is rounded to 8 KB. |
Allocation Unit | Unit to use for space estimation calculations, as well as primary and secondary quantities The default value comes from the installation standards. Possible values are K (kilobytes), T (tracks), or C (cylinders). |
Freepage | How often to leave a free page when the index, table space, or partition is loaded or reorganized Valid values are 0 through 255. The default value is 0, leaving no free pages. |
Pctfree | Percentage of each page to leave free when the table space or partition is loaded or reorganized Valid values are 0 through 99. The default value is 5. |
Pctfree Update | Percentage of each page to reserve as free space for future update operations Valid values are -1, 0 through 99, and <DEFLT>. |
Compress | Specifies whether compression is enabled for data within the table space or partition Changing the value of Compress from N to Y does not affect 'what if' estimates using Percent Compressed. |
Maxrows | Maximum number of rows on each data page. |
Dssize | In a partitioned table space, the maximum size for each partition in each data set |
Maxpartitions | For partition-by-growth table spaces, the maximum number of partitions |
Estimation of space for an index
The space estimate for an index is based on pages and levels, which are determined by the following attributes:
- Percent of free space on each page
- Free page frequency
- The maximum length of the index key (for padded indexes) or the average length of the index key (for nonpadded indexes)
- Number of rows expected in the table
- Average number of rows for each nonunique key
- Piecesize for nonpartitioned indexes
- The unique rule: whether the index key is unique (U), defined as UNIQUE WHERE NOT NULL (W), or allows duplicates (D)
- Page size (for compressed indexes)
You can estimate the space for an index and modify the data structure definitions in the Index Space Estimation panel (partitioned).
Command ===> Scroll. . PAGE
IX Name . . . . : J9QTALT.I_T01AICP1 |------ Estimated -------|
TB Name . . . . : J9QTALT.T_T01AICP | Pages : 12 |
Table Rows. . . : 0 | Space : 192 K |
Max Key Length : 17 | Levels: 2 |
Pagesize . . . : 8 |------------------------|
Unique . . . . . D (U,D,W)
Compress . . . . Y (Y,N)
Pct Compressed . 0 (0-99)
Lines 1 to 4 of 4
(PA=Propagate Attributes, PE=Propagate Est) More: >
Logical Avg Key Rows A --- Estimated ---
AC Part NbrRows Len Orig /Key PriQty SecQty U Space NbrSec
************************************ TOP **************************************
1 0 17 Proj 1 1440 720 K 48 K 0
2 0 17 Proj 1 1440 720 K 48 K 0
3 0 17 Proj 1 1440 720 K 48 K 0
4 0 17 Proj 1 1440 720 K 48 K 0
To estimate the space for an index, you can modify the values for the fields that are listed in Attributes for estimating index space (see the following table). When you modify one or more of these values, a space estimation marker ($) is added adjacent to the object on the Mixed List panel. The marker denotes that the object is put away by Specification solely because of space estimation parameters and that no changes are requested which would affect Analysis.
Attributes for estimating index space
Field | Description |
|---|---|
NbrRows | Estimated number of rows in the partition |
Rows/Key | Estimated number of rows per key value |
Avg Len (Average Key Length) | For padded indexes, the maximum key length; for nonpadded indexes, the average length of the index key The Key Orig field indicates the origination or source of the Avg Len field. To derive the value of the Avg Len field, the products gather information from one of the following sources (shown in order):
If a value for the average key length was saved in the product CD tables, space estimation uses that value, unless the value was projected. In that case, the products recalculate the value. |
Devt | Type of disk device on which the Db2 data set is to be allocated Valid values are 3330, 3340, 3350, 3375, 3380, 3390. |
Pct Compressed | Percentage is used to reduce the average key length User-supplied percentages are stored on the CD tables. Valid values are 00 through 99. The default is 0. |
To modify the data structure definitions, you can edit the values for the fields that are listed in Attributes for modifying the index space (see the following table). When you modify the definition, an 'A' is added adjacent to the object on the Mixed List panel. This marker indicates that a change request has been made for the object.
Attributes for modifying the index space
Field | Description |
|---|---|
Unique | Whether the index key is unique or allows duplicates Valid values are U (Unique), D (Duplicates), and W (defined as UNIQUE WHERE NOT NULL). |
Piecesize | Maximum piecesize for nonpartitioned indexes Avoid wasting space by ensuring that the sum of the PriQty and the SecQty values is evenly divided into the Piecesize value. |
Compress | Whether the index uses compression |
PriQty | Primary space (expressed in Aloc Unit) that is allocated for the Db2-defined data set Valid values are -1 and 1 through 67,108,864. The default value comes from the catalog or data set. If the Allocation Unit is K, the value is rounded to a multiple of 4 KB. For example, if the PriQty is one (1), the value is rounded to 4 KB. If a value of -1 is specified for the PriQty, space is estimated with a PriQty of 4 and a SecQty of 0. Entering the PE action code (or line command) for a partition propagates the estimated space to the PriQty field. Entering the PE command-line command propagates the estimated space to the PriQty field for all of the partitions in the index. To ensure that you do not waste space, specify a value for the PriQty that does not exceed the value of Piecesize. |
SecQty | Secondary space (expressed in Aloc Unit) allocated for the Db2-defined data set Valid values are -1 and 0 through 4,194,304. The default value comes from the catalog or data set. If the Allocation Unit is K, the value is rounded to a multiple of 4 KB. For example, if the SecQty is seven (7), the value is rounded to 8 KB. If a value of -1 is specified for the SecQty, space is estimated with a SecQty of 0. |
AU (Allocation Unit) | Unit to use for space estimation calculations, as well as primary and secondary quantities The default value comes from the installation standards. Possible values are K (kilobytes), T (tracks), or C (cylinders). |
Free Page | How often to leave a free page when the index or partition is loaded or reorganized Valid values are 0 through 255. The default value is 0, leaving no free pages. |
Pct Free | Percentage of each page to leave free when the partition is loaded or reorganized Valid values are 0 through 99. The default value is 10. |
NonLf KeyLn | Average key length of non-leaf pages Valid values are 0 through 99999. The default value is 0. |
Disk device type on which the partition is to be allocated Valid values are 3340, 3350, 3375, 3380, 3390, and 9345. | |
Comp KeyLn | Average key length with compression applied |
Maximum size of the partition in gigabytes This field is displayed for indexes based on partition-by-range table spaces with a relative page numbering format. Valid values are 1 through 1024. |