IBM Support

QSYS2.SYSPARTITIONDISK and QSYS2.SYSPARTITIONINDEXDISK

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 SSD.

Content

You are in: IBM i Technology Updates > Db2 for i - Technology UpdatesDb2 for i Functional Enhancements > QSYS2.SYSPARTITIONDISK and QSYS2.SYSPARTITIONINDEXDISK

The SQL Reference contains more detail:

SYSPARTITIONDISK view

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;

image-20200115124357-1


Example 2. Return allocation information for DB2 indexes (i.e. keyed files, constraint indexes, and SQL indexes) in MJATST:

SELECT index_schema, index_name, index_member, index_type,
SUM(CASE unit_type WHEN 1 THEN unit_space_used ELSE 0 END)/COUNT(*) AS ssd_space,
SUM(CASE unit_type WHEN 0 THEN unit_space_used ELSE 0 END)/COUNT(*) AS nonssd_space
FROM qsys2.syspartitionindexdisk b
WHERE system_table_schema = 'MJATST'
GROUP BY index_schema, index_name, index_member, index_type;

 

image-20200115124407-2

[{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB08","label":"Cognitive Systems"}}]

Document Information

Modified date:
15 January 2020

UID

ibm11167742