DB2 Version 9.7 for Linux, UNIX, and Windows

SNAPSTORAGE_PATHS administrative view and SNAP_GET_STORAGE_PATHS_V97 table function - Retrieve automatic storage path information

The SNAPSTORAGE_PATHS administrative view and the SNAP_GET_STORAGE_PATHS_V97 table function return a list of automatic storage paths for the database including file system information for each storage path, specifically, from the db_storage_group logical data group.

SNAPSTORAGE_PATHS administrative view

This administrative view allows you to retrieve automatic storage path information for the currently connected database.

Used with the SNAPDB, SNAPDETAILLOG, SNAPHADR and SNAPDB_MEMORY_POOL administrative views, the SNAPSTORAGE_PATHS administrative view provides information equivalent to the GET SNAPSHOT FOR DATABASE ON database-alias CLP command.

The schema is SYSIBMADM.

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

Authorization

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

SELECT or CONTROL privilege on the SNAPSTORAGE_PATHS administrative view
EXECUTE privilege on the SNAP_GET_STORAGE_PATHS_V97 table function
DATAACCESS authority

To access snapshot monitor data, one of the following authorities is required:

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

Example

Retrieve the storage path for the currently connected single-partition database.

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(DB_STORAGE_PATH,1,8) 
   AS DB_STORAGE_PATH, SUBSTR(HOSTNAME,1,10) AS HOSTNAME 
   FROM SYSIBMADM.SNAPSTORAGE_PATHS

The following example is a sample output from this query.

DB_NAME  DB_STORAGE_PATH HOSTNAME
-------- --------------- ----------
STOPATH  d:              JESSICAE

  1 record(s) selected.

SNAP_GET_STORAGE_PATHS_V97 table function

The SNAP_GET_STORAGE_PATHS_V97 table function returns the same information as the SNAPSTORAGE_PATHS administrative view, but allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.

Used with the SNAP_GET_DB_V95, SNAP_GET_DETAILLOG_V91, SNAP_GET_HADR and SNAP_GET_DB_MEMORY_POOL table functions, the SNAP_GET_STORAGE_PATHS_V97 table function provides information equivalent to the GET SNAPSHOT FOR ALL DATABASES CLP command.

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

Syntax


>>-SNAP_GET_STORAGE_PATHS_V97--(--dbname--+------------------+--)-><
                                          '-, dbpartitionnum-'

The schema is SYSPROC.

Table function parameters

dbname: An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database. Specify a database name that has a directory entry type of either "Indirect" or "Home", as returned by the LIST DATABASE DIRECTORY command. Specify an empty string to take the snapshot from the currently connected database. Specify a NULL value to take the snapshot from all databases within the same instance as the currently connected database.
dbpartitionnum: An optional input argument of type INTEGER that specifies a valid database partition number. Specify -1 for the current database partition, or -2 for an aggregate of all active database partitions. If dbname is not set to NULL and dbpartitionnum is set to NULL,-1 is set implicitly for dbpartitionnum. If this input option is not used, that is, only dbname is provided, data is returned from all active database partitions. An active database partition is a partition where the database is available for connection and use by applications.

If both dbname and dbpartitionnum are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_STORAGE_PATHS_V97 table function takes a snapshot for the currently connected database and database partition number.

Authorization

One of the following authorities is required:

EXECUTE privilege on the SNAP_GET_STORAGE_PATHS_V97 table function
DATAACCESS authority

To access snapshot monitor data, one of the following authorities is required:

SYSMON authority
SYSCTRL authority
SYSMAINT authority
SYSADM authority

Examples

Retrieve the storage path information for all active databases.

SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, DB_STORAGE_PATH
   FROM TABLE(SNAP_GET_STORAGE_PATHS_V97(CAST (NULL AS VARCHAR(128)), -1)) AS T

The following example is a sample output from this query.

DB_NAME  DB_STORAGE_PATH        
-------- -------------------... 
STOPATH  /home/jessicae/sdb     
MYDB     /home/jessicae/mdb     

  2 record(s) selected

Information returned

The BUFFERPOOL monitor switch must be turned on in order for the file system information to be returned.

Table 1. Information returned by the SNAPSTORAGE_PATHS administrative view and the SNAP_GET_STORAGE_PATHS_V97 table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	snapshot_timestamp - Snapshot timestamp monitor element
DB_NAME	VARCHAR(128)	db_name - Database name
DB_STORAGE_PATH	VARCHAR(256)	db_storage_path - Automatic storage path
DB_STORAGE_PATH_WITH_DPE	VARCHAR(256)	db_storage_path_with_dpe - Storage path including database partition expression monitor element
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.
DB_STORAGE_PATH_STATE	VARCHAR(16)	db_storage_path_state - Storage path state monitor element
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_sz - Automatic storage path free space