How to find the total number of database partitions and range (data) partitions held by a table?
Here is the query:
select t.tabschema SCHEMA, t.tabname TABLE, (select count(*) from syscat.dbpartitiongroupdef pgs where pgs.dbpgname = ts.dbpgname) DB_PARTITIONS, count(ts.tbspace) DATA_PARTITIONS from syscat.tables t, syscat.tablespaces ts, syscat.datapartitions d where ts.tbspaceid = d.tbspaceid and t.tabschema = d.tabschema and t.tabname = d.tabname and t.tabschema = 'schemaName' and t.tabname = 'tableName' group by t.tabschema, t.tabname, ts.dbpgname; SCHEMA TABLE DB_PARTITIONS DATA_PARTITIONS --------------- ------------------------------ ------------- --------------- TPCDS CATALOG_SALES 36 192