You can use the space manager feature of Db2 Admin Tool to estimate the space requirements for
an index space. For example, if you plan to add a significant amount of data to a table, you
might want to determine how this additional will impact the space that is needed for an
index.
About this task
The space
management features of estimating the space requirements for an index space and estimating
the number of extents are available as REST APIs. See Provided REST APIs.
Procedure
To estimate the space requirements for an index space:
Tip: If you are viewing an
index on the Indexes (ADBADB25C21X)
panel or a partition on the
Index Parts (ADB21XP)
panel, you can specify the
SE line command to navigate directly to the space estimator and
then start with step 3.
On the DB2 Administration Menu (ADB2)
panel, specify option
SM, and press Enter.
On the Space Manager (ADB2M)
panel, specify option
3 and optionally an index name, and press Enter.
Figure 1. Space Manager (ADB2M)
panel
DB2 Admin ---------------------- DB2 Space Manager ---------------------- 16:33
Option ===>
1 - Display page set space by database DB2 System: DD1A
2 - Table space estimator DB2 SQL ID: ADM001
3 - Index space estimator
For option 2 (optional):
Table space name . . . . (? to look up)
In database . . . . . . . (? to look up. Default DSNDB04)
For option 3 (optional):
Index name . . . . . . . > (? to look up)
Schema . . . . . . . . . > (Default ADM001)
Switch catalog copy . . . N (N/S/C)
On the DB2 Index Space Estimator
(ADB2MEX) panel specify information
about the index space in the fields in the Input values section,
and press Enter:
Figure 2. DB2 Index Space Estimator
(ADB2MEX) panel
ADB2MEX n -------------- DB2 Index Space Estimator ------------- 18:46
Command ===>
Input values:
No. of keys . . . . (required)
Key length . . . . (required, 1-2000)
Unique . . . . . . (required, Yes/No)
Distinct . . . . (for non-unique: no. of distinct keys)
OR rows/key . . . (for non-unique: avg. rows per key)
Compression ratio . 0 (0 or 12.5-100, optional, default 0)
Page size . . . . . 4 (4, 8, 16, or 32, default 4)
Pctfree . . . . . . (0-99, default 5)
Freepage . . . . . (0-255, default 0)
Large TSpace . . . (Yes/No, default No)
Unit type . . . . . 3390 (3380/3390, default 3390)
EAV support . . . . NO (Yes/No, default No)
No. of pieces . . . (1-32, 1-4096 with large table space)
OR piecesize . . . (nX, n=numeric value, see help,X=K/M/G)
Estimates: Suggested:
Usable page size : Primary . . . . :
Keys per page . . : Secondary . . . :
Leaf pages . . . : Piecesize . . . :
Index levels . . : Disk estimates:
Total pages . . . : Number of trks . :
Number of KB . . : Number of cyls . :
Note: Initially, all fields on this panel are blank. For more information about each
field, see the panel help.
After you press Enter, the panel is refreshed. Based on the input values that
you entered, the Index Space Estimator provides information about the estimated space that
is required by the index space and suggests the amount of space to allocate for this index
space. This information is displayed at the bottom of the panel:
Figure 3. DB2 Index Space Estimator
(ADB2MEX) panel example
ADB2MEX n -------------- DB2 Index Space Estimator ------------- 18:46
Command ===>
Commands: EXTENTS
Input values:
No. of keys . . . . 100000 (required)
Key length . . . . 10 (required, 1-2000)
Unique . . . . . . Y (required, Yes/No)
Distinct . . . . (for non-unique: no. of distinct keys)
OR rows/key . . . (for non-unique: avg. rows per key)
Compression ratio . 0 (0 or 12.5-100, optional, default 0)
Page size . . . . . 4 (4, 8, 16, or 32, default 4)
Pctfree . . . . . . 5 (0-99, default 5)
Freepage . . . . . (0-255, default 0)
Large TSpace . . . NO (Yes/No, default No)
Unit type . . . . . 3390 (3380/3390, default 3390)
EAV support . . . . NO (Yes/No, default No)
No. of pieces . . . (1-32, 1-4096 with large table space)
OR piecesize . . . 256K (nX, n=numeric value, see help,X=K/M/G)
Estimates: Suggested:
Usable page size : 3836 Primary . . . . : 1824
Keys per page . . : 225 Secondary . . . : 48
Leaf pages . . . : 445 Piecesize . . . : 256 K
Index levels . . : 3 Disk estimates:
Total pages . . . : 450 Number of trks . : 38
Number of KB . . : 1808 Number of cyls . : 3
For more information about each field, see the panel help.
Optional: To see the estimated number of
extents, specify the EXTENTS command and press Enter.
Note: The EXTENTS command is available only after input values are specified, and you
press Enter.
On the resulting pop-up window, you can override the
PRIQTY and SECQTY field values (in KB) to
see how the number of extents changes: Figure 4. DB2 Extents Estimator (ADB2ME2)
panel
The space
management features of estimating the space requirements for an index space and estimating
the number of extents are available as REST APIs. See Provided REST APIs.