DB2 10.5 for Linux, UNIX, and Windows

GET_DBSIZE_INFO procedure

The GET_DBSIZE_INFO procedure calculates the database size and maximum capacity.

Authorization

To execute the procedure, SYSMON authority is required, in addition to one of the following authorizations:

DATAACCESS authority
DBADM authority
SQLADM authority
EXECUTE privilege on the GET_DBSIZE_INFO, WLM_SET_CONN_ENV, MON_GET_CONTAINER, and MON_GET_TABLESPACE routines

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Syntax


>>-GET_DBSIZE_INFO--(--snapshot-timestamp--,--dbsize--,--------->

>--dbcapacity--,--refresh-window--)----------------------------><

The schema is SYSPROC.

Procedure parameters

snapshot-timestamp: An output parameter of type TIMESTAMP that returns the time at which dbsize and dbcapacity were calculated. This timestamp, along with the value of refresh-window, is used to determine when the cached values in the SYSTOOLS.STMG_DBSIZE_INFO table need to be refreshed.
dbsize: An output parameter of type BIGINT that returns the size of the database (in bytes). The database size is calculated as follows: dbsize = sum (used_pages * page_size) for each table space (SMS & DMS).
dbcapacity: An output parameter of type BIGINT that returns the database capacity (in bytes). This value is not available on partitioned database systems. The database capacity is calculated as follows: dbcapacity = SUM (DMS usable_pages * page size) + SUM (SMS container size + file system free size per container). If multiple SMS containers are defined on the same file system, the file system free size is included only once in the calculation of capacity.
refresh-window: An input argument of type INTEGER that specifies the number of minutes until the cached values for database size and capacity are to be refreshed. Specify -1 for the default refresh window of 30 minutes. A refresh window of 0 forces an immediate refreshing of the cached values.

Usage notes

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.

To ensure that the data is returned by all partitions for a global table space snapshot, the database must be activated.

The SYSTOOLSPACE is used for the routine's operation tables to store metadata; that is, data used to describe database objects and their operation.

Examples

Example 1: Get the database size and capacity using a default refresh window of 30 minutes. The database size and capacity will be recalculated when the cached data is older than 30 minutes.

CALL GET_DBSIZE_INFO(?, ?, ?, -1)

The procedure returns:

 Value of output parameters
 --------------------------
 Parameter Name  : SNAPSHOTTIMESTAMP
 Parameter Value : 2004-02-29-18.31.55.178000

 Parameter Name  : DATABASESIZE
 Parameter Value : 22302720

 Parameter Name  : DATABASECAPACITY
 Parameter Value : 4684793856

 Return Status = 0

Example 2: Get 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)

The procedure returns:

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

Example 3: Get the database size and capacity using a refresh window of 24 hours. The database size and capacity will be recalculated when the cached data is older than 1440 minutes.

CALL GET_DBSIZE_INFO(?, ?, ?, 1440)

The procedure returns:

 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