GET_DBSIZE_INFO procedure
The GET_DBSIZE_INFO procedure calculates the database size and maximum capacity.
Authorization
- 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
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
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
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
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