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
Last activity: Jul 20, 2025, 02:07 PM