Get current db2 database size
Today I needed to get database size for one of my databases.
I find a partial solution here.
My first choice was this stored procedure, which needs to be called from command line:
db2 'call get_dbsize_info(?,?,?,1)'
Output:
[db2inst1@gina64 ~]$ db2 "call get_dbsize_info(?,?,?,-1)" Value of output parameters -------------------------- Parameter Name : SNAPSHOTTIMESTAMP Parameter Value : 2013-07-04-13.00.05.476704 Parameter Name : DATABASESIZE Parameter Value : 6993657856 Parameter Name : DATABASECAPACITY Parameter Value : 5311283195 Return Status = 0
Later I realized that if I would like to use this data in a script, I will need to parse this data. Before doing that I tried the second option:
db2 "select (SUM(total_pages)*4)/1024.0/1024 TOTAL_ALLOCATED_SPACE_IN_GB from table (snapshot_tbs_cfg('dbname',-1)) TBS_SPCE"
Output:
TOTAL_ALLOCATED_SPACE_IN_GB
---------------------------------
6.99011230468
This query is more easy to use in a script, but being a lazy person I corrected this query to work without any “customization” for each database…
As you can see, the original query calculate database size assuming that page size is 4K:
...(SUM(total_pages)*4)/1024.0/1024...
I changed to this:
... (SUM(total_pages*page_size))/1024.0/1024/1024 ...
The second thing to change is the dbname value:
From:
... table (snapshot_tbs_cfg('dbname',-1)) ...
to:
... table (snapshot_tbs_cfg(CURRENT SERVER,-1)) ...
The final query looks like this:
select (SUM(total_pages*page_size))/1024.0/1024/1024 TOTAL_ALLOCATED_SPACE_IN_GB from table (snapshot_tbs_cfg(CURRENT SERVER,-1)) TBS_SPCE
No Comments Yet.