Estimating space requirements for a table space
Use this procedure to estimate the DASD requirements for existing DB2 table spaces by using the most current values in the DASD MANAGER PLUS statistics tables or any other values that you choose.
The space estimations (Estimated) are space that is required if you reorganize the table space. The product bases the estimates on the values for Pagesize, Segsize, Avg Row Length, Number of Rows, Priqty, Secqty, Alloc Unit, Device Type, Pctfree, Freepage, Type, Maxrows, Dssize, MaxParts, and Compress.
The default values are the most current values in the DASD MANAGER PLUS statistics tables for the object.
You can edit some of these fields in the Tablespace Estimation panel (nonpartitioned) and then press Enter to display new space estimates using different values. Some fields are informational only. The Tab key moves the cursor to fields that you can modify.
Tablespace Estimation panel (nonpartitioned)
COMMAND ===> SCROLL===> CSR
Type data and press Enter.
Tablespace Name QZUDA1.QZUS01A1 Type . . . (G, I or blank)
Pagesize . . . . . 4 (4,8,16,32) Maxrows 255 (0-255)
Segsize . . . . . 4 (0-64) Dssize . 0 |------- Estimated -------|
Pages . . 130 MaxParts 0 | Pages . . 130 |
Space (Trks) 90 | Space (Trks) . 11 |
| Space (KB) 528 |
Extents . . 1 | Extents . . 1 |
Priqty . . . . 90 | Data Sets . . . 1 |
Secqty . . . . 3 |-------------------------|
Pctfree . . . . . 0 (0-99) Freepage 0 (0-3)
Alloc Unit . . . . T (T or C) Compress (Y or blank)
Device Type. . . 3390
-Row Length- -- Estimated --
Owner Table Name Max #Rows Pages Rows/Page
/Avg /Pages
-------------------------------------------------------------------------------
QZU QZUT01_DA1S01
247 2036 128 16
247 128
The following information pertains to nonpartitioned table spaces:
- The panel shows current used space on the left:
- Pages is the number of 4-KB, 8-KB, 16-KB, or 32-KB blocks of physical storage that the table space uses.
- Space (Trks) is the space, in tracks, that the table space uses.
Extents is the number of total data set extents that the table space uses when you collected statistics.
If this value is greater than the VSAM maximum (7257 per data set), the product displays a message.
- Estimations for the entire table space (if reorganized) appear in the box on the right side of the panel, as follows:
- Pages is the number of pages that the table space requires.
- Space (Trks) is the space in tracks that the table space requires.
- Space (KB) is the space in kilobytes that the table space requires.
- Extents is the number of extents that the table space requires.
- Data Sets is the number of data sets that the table space contains.
- Estimations per table appear in edit list format, at bottom right, under the label Estimated, as follows:
- Rows/Page is the number of rows per page that the table uses.
- Pages is the number of pages that the table uses.
- Information about the table itself appears at the bottom of the panel:
- Owner and Table Name are self-explanatory.
- Row Length Max is the longest row length.
- Row Length /Avg is the average length of the rows.
- #Rows is the number of rows in the table.
- Pages is the number of pages in the table.
The following information appears for partitioned table spaces:
The Tablespace Estimation (Partitioned) panel shows currently used space, growth information, and the type of table space on the left and center of the panel.
Tablespace Estimation (Partitioned) panel
ASUZTPES Tablespace Estimation (Partitioned) Row 1 to 4 of 4
COMMAND ===> SCROLL===> CSR
Tablespace Name: QZUDPBR2.QZUS01PB Type . . . R (L, R, P, or blank)
Pagesize . . . . . . 8 (4,8,16,32) Maxrows. 255 |------- Estimated -------|
Pages . . 74 Dssize . 4 | Pages . . 424 |
Space (Trks) 90 Pagenum. . R | Space (Trks) 120 |
Segsize. . 32 | Space (KB) 5760 |
Table Name: QZU.QZUT01_DPBS01 |-------------------------|
MaxRow Len . 247
AvgRow Len . 247
Aloc Pct Free ---- Estimated ----
Part #Rows Priqty Unit Free Page Space Rows/ Tracks Ext
/AvgRow /Secqty /Devt /Dssize/Comp /Ext Page /Kbytes
-------------------------------------------------------------------------------
1 12036 3 C 5 0 45 31 75 3
247 1 3390 4 1 3600
2 0 1 C 5 0 15 0 15 1
247 1 3390 4 1 720
3 0 1 C 5 0 15 0 15 1
247 1 3390 4 1 720
4 0 1 C 5 0 15 0 15 1
247 1 3390 4 1 720
******************************* Bottom of data ********************************- Pages is the number of 4-KB, 8-KB, 16-KB, or 32-KB blocks of physical storage that the table space uses.
- Space (Trks) is the space in tracks that the table space uses.
- Type indicates the type of table space:
- L for large table spaces
- P for for Implicit table space created for XML columns
- R for range-partitioned universal table space
- blank for normal
- Maxrows displays the maximum number of rows per page (0 through 255).
- Dssize is the maximum size of a data set in kilobytes.
- (BMC.DB2.SPE2110) Pagenum – Can be A or R. For Db2 V11, the value displayed is A.
- Segsize is the number of pages in the segment for this table space. Segsize can only be non-zero if Type is R. If Type is R and you enter 0 in this field, the default segment size is set to 4.
- The Estimated box shows estimations for the entire table space (if reorganized):
- Pages is the number of 4-KB, 8-KB, 16-KB, or 32-KB blocks of physical storage that the table space uses.
- Space (Trks) is the space in tracks that the table space requires.
- Space (KB) is the space in kilobytes that the table space requires.
- Estimations for each partition appear in edit list form at bottom right under the word Estimated:
- Rows/Page is the rows per page that the partition required.
- Tracks/Kbytes are the tracks and kilobytes that the partition required.
Ext is the total number of data set extents that the table space partition used when you collected statistics.
If the Extents value is greater than the VSAM maximum (7257 per data set), the product displays a message.
- The bottom of the panel shows information about each partition:
- Part is the partition number.
- #Rows is the number of rows in the partition.
- AvgRow is the average row length for the partition.
- Priqty is the primary allocation VSAM value from the ICF catalog.
- Secqty is the secondary allocation VSAM value from the ICF catalog.
- Aloc unit is the allocation unit specified for the table space.
- Devt is the disk device type for the table space.
- Pct Free is the percentage of free space per page for the table space.
DSSIZE
- Relative 1-1024, any value
- This is applicable only if table space uses Relative Page numbering
- Free Page is the page increment for inserting a free page.
- Compress is whether the partition is compressed.
- Space is the amount of space in tracks in the partition.
- Ext is the number of data set extents in the partition.
The following information appears for partition-by-growth table spaces:
The Tablespace Estimation (Partitioned by Growth) panel shows currently used space, growth information, and the type of table space on the left and center of the panel:
Tablespace Estimation (Partitioned by Growth) panel
ASUZPGES Tablespace Estimation (Partitioned by Growth)
COMMAND ===> SCROLL===> PAGE
Tablespace Name ARMDBPBG.TS40UTS1 Type . . . G (I, G or blank)
Table Name . . . JTR.T40UTS1
#Rows . . 9009900
Pagesize . . . . . 4 (4,8,16,32) Maxrows 255 (0-255)
Segsize . . . . . 4 (0-64) Dssize . . 0 |------- Estimated -------
Pages . . . 936 MaxParts 4 | Pages . . . 237244
Space (Trks) 78 Alloc Parts 1 | Space (Trks) 19771
| Space (KB) . 949008
MaxRow Len . . . 93 AvgRow Len 93 | Extents . . 1319
Extents . . . . 3 | Est. Parts . . . 1
Priqty . . . . . 3 Secqty . . 15 |-------------------------
Pctfree . . . . . 5 (0-99) Freepage 0 (0-3)
Alloc Unit . . . . T (T or C) Compress (Y or blank)
Device Type. . . 3390
------------------------------ Estimated --------------------------
Parts #Rows Rows/Page Tracks Kbytes Ext
------------------------------------------------------------------------------
< 1 9009900 38 19771 949008 1319- Type indicates the type of table space:
- G for partition-by-growth table spaces
- I for partition-by-growth table spaces
- blank for normal
- #Rows is the number of rows in the partition.
- Pagesize is the size of the 4-KB, 8-KB, 16-KB, or 32-KB blocks of physical storage that the table space uses.
- Maxrows displays the maximum number of rows per page (0 through 255).
- Segsize is the number of pages in the segment for this table space.
Dssize is the maximum size of a data set in kilobytes.
If you specify this field, you must also specify a value for the MaxParts field. The following values are valid: 0, 1, 2, 4, 8, 16, 32, 64. For partition-by-growth table spaces, the value for this field must be greater than 0.
- Pages is the number of 4-KB, 8-KB, 16-KB, or 32-KB blocks of physical storage that the table space uses.
- Max Parts is the maximum number of partitions to which the partition-by-growth table space can grow. The value must be in the range from 1 through 4096 and depends on the corresponding value for the Dssize field.
- Space (Trks) is the amount of space in tracks in the partition.
- Alloc Parts is the number of partitions currently allocated for the table space.
- MaxRow Len is the maximum row length for the table. The value does not include overhead (header 6 bytes and map ID 2 bytes).
- AvgRow Len is the average row length for the partition.
- Extents is the number of data set extents in the partition.
- Priqty is the primary allocation VSAM value from the ICF catalog.
- Secqty is the secondary allocation VSAM value from the ICF catalog.
- Pctfree is the percentage of free space per page for the table space.
- Freepage is the page increment for inserting a free page.
- Alloc Unit is the allocation unit specified for the table space.
- Compress is whether the partition is compressed.
- Device Type is the disk device type for the table space
- Type indicates the type of table space:
- The Estimated box shows estimations for the entire table space (if reorganized):
- Pages is the number of 4-KB, 8-KB, 16-KB, or 32-KB blocks of physical storage that the table space uses.
- Space (Trks) is the space in tracks that the table space requires.
- Space (KB) is the space in kilobytes that the table space requires.
Extents is the total number of data set extents, based on the primary and secondary quantities, that the table space will require to accommodate the entered specifications.
If the Extents value is greater than the VSAM maximum (7257 per data set), the product displays a message.
- Est. Parts is the estimated number of partitions for the table space.
- Estimations for each partition appear in edit list form at bottom right under the word Estimated:
- Parts is the partition number. A less than sign (<) followed by a partition number indicates that all the partitions less than that number have the values listed in that row. The partition that is currently active is shown on the next line.
- #Rows is the number of rows in the partition.
- Rows/Page is the rows per page that the partition required.
- Tracks/Kbytes are the tracks and kilobytes that the partition required.
- Ext is the estimate for the number of secondary allocations required in allocation units specified. The value depends on the amount of space estimates and primary and secondary allocation quantities.
To estimate space requirements for a table space
- Create a DB2 object list that contains the table space for which you want to estimate space as instructed in Creating-a-Db2-object-list.
- Select the Space Estimation function by typing S in the Act field beside the table space for which you want space estimates.The Tablespace Estimation panel appears.
- Estimate space requirements for the entire table space or for individual tables or partitions (or both) as follows:
- Type over the existing values and press Enter to update the Estimated fields.
- You can repeat this step as many times as necessary.
- When you have finished estimating space requirements, press END to exit.