If you have a DB2 table and want to get information as to how much space a particular table is using, you could try and deduce it from things like the row length, or you could just query the view via a table function and make your life a heck of a lot easier.
For instance, start with something like this…
SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('XX', 'MY_TABLE'));
Previous versions of this function include ADMIN_GET_TAB_INFO_V97 and ADMIN_GET_TAB_INFO_V95. The nice thing is that it appears you don’t have to be a DBA to query the info which is nice for us app developers out there!
Keep in mind that sizes returned are in KB, so you may want to convert the data to something more useful like so…
SELECT TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) AS TABLE_NM, DATA_OBJECT_P_SIZE / 1024 AS DATA_P_SIZE_NB, LOB_OBJECT_P_SIZE / 1024 AS LOB_P_SIZE_MB, XML_OBJECT_P_SIZE / 1024 AS XML_P_SIZE_MB, LONG_OBJECT_P_SIZE / 1024 AS LONG_P_SIZE_MB, (DATA_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE) / 1024 AS TOTAL_P_SIZE_MB, INDEX_OBJECT_P_SIZE / 1024 AS INDEX_OBJECT_P_SIZE_MB FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('XX', 'MY_TABLE'));
See also: this linkLike this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon 🙂
You or someone you know looking to buy or sell?