Walker News

How To Check IBM DB2 Database Size?

After some time, you might wonder how big is the database size. If you’re using IBM DB2, there is a system stored procedure called GET_DBSIZE_INFO to check a DB2 database size and maximum capacity.
The GET_DBSIZE_INFO stored procedure syntax requires 3 output parameters and 1 input parameter:
GET_DBSIZE_INFO (snapshot-timestamp, dbsize, dbcapacity, refresh-window)

where the snapshot-timestamp refers to the time at which dbsize and dbcapacity were calculated;

dbsize refers to the database size in bytes as a sum of (used_pages x page_size) for each table space (SMS and DMS).

dbcapacity refers to the database maximum capacity in bytes as a sum of (DMS usable_pages x page size) and (SMS container size + file system free size per container).

In the case of multiple SMS containers are defined on the same file system, the file system free size is included only once in the calculation of maximum database capacity.

refresh-window is in the unit of minute to decide whether the cached output of previous GET_DBSIZE_INFO has to be recalculated or presents the cached value as it to reduce overhead.

For example, if the snapshot-timestamp at the point of executing GET_DBSIZE_INFO is older than the sum of (cached snapshot-timestamp + refresh-window), then the cached dbsize and dbcapcity are deemed as latest and returned.

Otherwise, GET_DBSIZE_INFO calculates the new statistics and cache them in the SYSTOOLS.STMG_DBSIZE_INFO table.

Before calling GET_DBSIZE_INFO procedure, there must be an active database connection, e.g. db2 connect to database_alias.

You can use DB2 CLP (command line processor) to call this stored procedure directly in Linux command prompt, as this:
db2 "CALL GET_DBSIZE_INFO(?, ?, ?, 0)"

where the the output parameters are denoted with question mark (?), and specify the refresh-window (input parameter) with an integer value (0 means immediately; -1 means the default refresh window of 30 minutes; 15 means 15 minutes, etc).

For more information, refer to GET_DBSIZE_INFO procedure and Calling stored procedure from DB2 CLP (command line processor) of DB2 version 9 for LUW.

Custom Search

  1. Mario Roberto 15-04-10@00:34

    Thanks a lot for this useful information.

  2. kaiyip 27-10-10@12:48

    Thank you!

  3. fernando 02-02-13@01:03

    excellent…very useful

  4. Narasimha A 13-04-14@13:01

    Excellent.
    Thanks a lot for providing this valuable information.

2014  •  Privacy Policy