DB2 Version 10.1 for Linux, UNIX, and Windows

CONTAINER_UTILIZATION administrative view - Retrieve table space container and utilization information

The CONTAINER_UTILIZATION administrative view returns information about table space containers and utilization rates. It retrieve a similar report to the LIST TABLESPACES command on a single partitioned database. Its information is based on the SNAPCONTAINER administrative view.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the CONTAINER_UTILIZATION administrative view
CONTROL privilege on the CONTAINER_UTILIZATION administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Default PUBLIC privilege

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

Example

Retrieve a list of all table spaces containers in the connected single partition database, including information about the total and usable pages as well as their accessibility status.

SELECT SUBSTR(TBSP_NAME,1,20) AS TBSP_NAME, INT(TBSP_ID) AS TBSP_ID, 
   SUBSTR(CONTAINER_NAME,1,45) AS CONTAINER_NAME, INT(CONTAINER_ID) 
   AS CONTAINER_ID, CONTAINER_TYPE, INT(TOTAL_PAGES) AS TOTAL_PAGES, 
   INT(USABLE_PAGES) AS USABLE_PAGES, ACCESSIBLE 
   FROM SYSIBMADM.CONTAINER_UTILIZATION

The following is an example of output for this query.

TBSP_NAME            TBSP_ID     CONTAINER_NAME                             ...
----------------...- ----------- -------------------------------------...-- ...
SYSCATSPACE                    0 D:\DB2\NODE0000\SQL00001\SQLT0000.0        ...
TEMPSPACE1                     1 D:\DB2\NODE0000\SQL00001\SQLT0001.0        ...
USERSPACE1                     2 D:\DB2\NODE0000\SQL00001\SQLT0002.0        ...
SYSTOOLSPACE                   3 D:\DB2\NODE0000\SQL00001\SYSTOOLSPACE      ...
SYSTOOLSTMPSPACE               4 D:\DB2\NODE0000\SQL00001\SYSTOOLSTMPSPACE  ...
                                                                               
  5 record(s) selected.

Output for this query (continued).

... CONTAINER_ID CONTAINER_TYPE TOTAL_PAGES USABLE_PAGES ACCESSIBLE  
... ------------ -------------- ----------- ------------ ----------  
...            0 PATH                     0            0          1  
...            0 PATH                     0            0          1  
...            0 PATH                     0            0          1  
...            0 PATH                     0            0          1  
...            0 PATH                     0            0          1

Information returned

The BUFFERPOOL snapshot monitor switch must be enabled at the database manager configuration for the file system information to be returned.

Table 1. Information returned by the CONTAINER_UTILIZATION administrative view
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	The date and time that the snapshot was taken.
TBSP_NAME	VARCHAR(128)	tablespace_name - Table space name
TBSP_ID	BIGINT	tablespace_id - Table space identification
CONTAINER_NAME	VARCHAR(256)	container_name - Container name
CONTAINER_ID	BIGINT	container_id - Container identification
CONTAINER_TYPE	VARCHAR(16)	container_type - Container type This is a text identifier based on the defines in sqlutil.h and is one of: DISK_EXTENT_TAG DISK_PAGE_TAG FILE_EXTENT_TAG FILE_PAGE_TAG PATH
TOTAL_PAGES	BIGINT	container_total_pages - Total pages in container
USABLE_PAGES	BIGINT	container_usable_pages - Usable pages in container
ACCESSIBLE	SMALLINT	container_accessible - Accessibility of container
STRIPE_SET	BIGINT	container_stripe_set - Stripe set
FS_ID	VARCHAR(22)	fs_id - Unique file system identification number
FS_TOTAL_SIZE_KB	BIGINT	fs_total_size - Total size of a file system . This interface returns the value in KB.
FS_USED_SIZE_KB	BIGINT	fs_used_size - Amount of space used on a file system . This interface returns the value in KB.
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element