News
Abstract
These two catalog views return allocation information for tables and indexes. The views can be useful in determining how much storage for a partition of index is allocated on Solid State Drives (SSD).
Content
Example 1. Return allocation information for DB2 tables and physical files in MJATST:
SELECT MAX(table_schema) AS table_schema, MAX(table_name) AS table_name,
MAX(table_partition) AS table_partition,
SUM(CASE WHEN unit_type = 1 THEN unit_space_used ELSE null END) AS ssd_space,
SUM(CASE WHEN unit_type = 0 THEN unit_space_used ELSE null END) AS non_ssd_space
FROM qsys2.syspartitiondisk a
WHERE system_table_schema = 'MJATST'
GROUP BY a.table_schema, a.table_name, table_partition
ORDER BY 1,2,3;
Example 2. Return allocation information for DB2 indexes (i.e. keyed files, constraint indexes, and SQL indexes) in MJATST:
Note: The DISTINCT keyword is required because spanning indexes will cause the size to be inaccurate due to duplication.
When using queries over QSYS2.SYSPARTITIONINDEXDISK you have to be careful of spanning indexes, because spanning indexes will return the same size for each underlying physical member and you can only count one of them.
Was this topic helpful?
Document Information
Modified date:
27 March 2025
UID
ibm11167742