How can I get the size of my UDB DB2 version 9.1 database? I tried firing getdbconfig from the command prompt. It gives all the configuration but not the size.
Thanks :)
There is a TechNote on IBM's support site:
How to calculate the size of a DB2 database
If you want to calculate the size and maximum capacity of your database, you can use the procedure
GET_DBSIZE_INFO
. This procedure first appeared in DB2 Universal Databaseā¢ (DB2 UDB) Version 8.2.
To execute this procedure you should follow these three steps:
GET_DBSIZE_INFO
.Here is the syntax of the procedure:
db2 call GET_DBSIZE_INFO (?,?,?,refresh-window)
The first three parameters are output parameters, and the refresh-window is an input parameter of type INTEGER that specifies the number of minutes until the cached values for database size and capacity are to be refreshed.
Refer to this documentation link for examples of the output from this procedure in DB2 UDB Version 9.1.
There's a script here which has been tested in 9.1 and, according to the author:
will calculate the size of a DB2 LUW database on a UNIX server using UNIX commands that will give you a more accurate measure of the space used by the database. This script also stores this information in an operational table that you may use as part of your DBA Monitoring Dash Board to help you monitor and manage space in relation to growth trends.
The GET_DBSIZE_INFO procedure calculates the database size and maximum capacity. The calculated values are returned as procedure output parameters and are cached in the SYSTOOLS.STMG_DBSIZE_INFO table. The procedure caches these values because the calculations are costly. The SYSTOOLS.STMG_DBSIZE_INFO table is created automatically the first time the procedure executes. If there are values cached in the SYSTOOLS.STMG_DBSIZE_INFO table and they are current enough, as determined by the snapshot-timestamp and refresh-window values, these cached values are returned. If the cached values are not current enough, new cached values are calculated, inserted into the SYSTOOLS.STMG_DBSIZE_INFO table and returned, and the snapshot-timestamp value is updated.
The below returns the database size and capacity using a refresh window of 0 minutes. The database size and capacity will be recalculated immediately.
CALL GET_DBSIZE_INFO(?, ?, ?, 0)
Value of output parameters
--------------------------
Parameter Name : SNAPSHOTTIMESTAMP
Parameter Value : 2004-02-29-18.33.34.561000
Parameter Name : DATABASESIZE
Parameter Value : 22302720
Parameter Name : DATABASECAPACITY
Parameter Value : 4684859392
Return Status = 0
You may query the systools.stmg_dbsize_info directly to get the results as well.
db2 "select db_size, db_capacity from systools.stmg_dbsize_info"
db2 "select db_size/1073741824 as SIZE_GB, db_capacity/1073741824 as CAPACITY_GB from systools.stmg_dbsize_info"
db2 "select db_size, db_capacity from systools.stmg_dbsize_info"
DB_SIZE DB_CAPACITY
-------------------- --------------------
1089248165888 1342416097280
1 record(s) selected.
db2 "select db_size/1073741824 as SIZE_GB, db_capacity/1073741824 as CAPACITY_GB from systools.stmg_dbsize_info"
SIZE_GB CAPACITY_GB
-------------------- --------------------
1014 1250
1 record(s) selected.