Sample Header Ad - 728x90

Query for DB2 z/OS Table Size

0 votes
1 answer
17 views
I need to check the size of some tables to get an indicative idea of how much space (in MB) they will occupy when I ingest them into another database. I have written this query (*) to obtain the aproximate size and the physical size informations. 1. Do you think this query is sufficient for my goal? 2. In the query, I noticed that I have a duplicate record because in the SYSTABLESPACESTATS table I have two different partitions. How should I consider them? Should I sum the two values to get the total size? Thank you! SELECT A.DBNAME, A.CREATOR, A.OWNER, A.NAME, A.TSNAME, A.TYPE, A.CARDF, A.AVGROWLEN, CASE WHEN A.CARDF -1.0 AND A.AVGROWLEN -1 THEN (A. CARDF * A.AVGROWLEN) / 1024 / 1024 ELSE CAST (NULL AS NUMERIC) END AS APPROX MB, B.UPDATESTATSTIME, B.STATSLASTTIME, B.PARTITION, B.NPAGES, C.PGSIZE, (B.NPAGES * C.PGSIZE) / 1024 AS PHYSICAL MB FROM SYSIBM.SYSTABLES A LEFT JOIN SYSIBM.SYSTABLESPACESTATS B ON B.DBNAME = A.DBNAME AND B.NAME = A.TSNAME LEFT JOIN SYSIBM.SYSTABLESPACE C ON C.DBNAME = A.DBNAME AND C.NAME = A.TSNAME WHERE A.NAME LIKE '%PIPPO%' AND A.TYPE= 'T' ORDER BY A.OWNER, A.NAME;
Asked by tuzzo
Jul 17, 2025, 05:38 PM
Last activity: Jul 20, 2025, 02:07 PM