DB2 10.5 for Linux, UNIX, and Windows

ADMIN_GET_STORAGE_PATHS table function - retrieve automatic storage path information

The ADMIN_GET_STORAGE_PATHS table function returns a list of automatic storage paths for each database storage group, including file system information for each storage path.

Refer to Table 1 for a complete list of information that can be returned.

Syntax


>>-ADMIN_GET_STORAGE_PATHS--(--storage_group_name--,--member--)-><

The schema is SYSPROC.

Table function parameters

storage_group_name: An input argument of type VARCHAR(128) that specifies a valid storage group name in the currently connected database when this function is called. If the argument is NULL or an empty string, information is returned for all storage groups in the database. If the argument is specified, information is only returned for the identified storage group.
member: An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all database members. If the NULL value is specified, -1 is set implicitly.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Example

Determine which dropped storage paths are still being used:

SELECT VARCHAR(STORAGE_GROUP_NAME, 30) AS STOGROUP, VARCHAR(DB_STORAGE_PATH, 40)
   AS STORAGE_PATH FROM TABLE(ADMIN_GET_STORAGE_PATHS('',-1)) AS T 
   WHERE DB_STORAGE_PATH_STATE = 'DROP_PENDING'

The following is an example of the output from this query.

STOGROUP                       STORAGE_PATH
------------------------------ ----------------------------------------
HOTSTORAGE                     /home/hotel55/hotpath1

1 record(s) selected.

List all the storage paths for the currently connected database:

SELECT VARCHAR(STORAGE_GROUP_NAME, 30) AS STOGROUP, VARCHAR(DB_STORAGE_PATH, 40)
   AS STORAGE_PATH FROM TABLE(ADMIN_GET_STORAGE_PATHS('',-1)) AS T

The following is an example of the output from this query.

STOGROUP                       STORAGE_PATH
------------------------------ ----------------------------------------
IBMSTOGROUP                    /home/hotel55/instowner
HOTSTORAGE                     /home/hotel55/hotpath1
COLDSTORAGE                    /home/hotel55/coldpath1

3 record(s) selected.

Information returned by ADMIN_GET_STORAGE_PATHS

Table 1. Information returned by the ADMIN_GET_STORAGE_PATHS table function
Column Name	Data Type	Description or corresponding monitor element
STORAGE_GROUP_NAME	VARCHAR(128)	storage_group_name - Storage group name
STORAGE_GROUP_ID	INTEGER	storage_group_id - Storage group identifier
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number
DB_STORAGE_PATH	VARCHAR(256)	db_storage_path - Automatic storage path
DB_STORAGE_PATH_WITH_DPE	VARCHAR(256)	db_storage_path_with_dpe - Database storage path with database partition expression
DB_STORAGE_PATH_STATE	VARCHAR(16)	db_storage_path_state - Storage path state Value is one of: `IN_USE` `NOT_IN_USE` `DROP_PENDING`
DB_STORAGE_PATH_ID	BIGINT	db_storage_path_id - Storage path identifier
FS_ID	VARCHAR(22)	fs_id - Unique file system identification number
FS_TOTAL_SIZE	BIGINT	fs_total_size - Total size of a file system
FS_USED_SIZE	BIGINT	fs_used_size - Amount of space used on a file system
STO_PATH_FREE_SIZE	BIGINT	sto_path_free_size - Automatic Storage path free space