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
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.
- In a Db2® pureScale®, specify 0.
- In a partitioned database environment, specify -1 for the current database member, or -2 for all database members.
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.