The SNAPSTORAGE_PATHS administrative view and the SNAP_GET_STORAGE_PATHS 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.
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.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(DB_STORAGE_PATH,1,8)
AS DB_STORAGE_PATH FROM SYSIBMADM.SNAPSTORAGE_PATHS
DB_NAME DB_STORAGE_PATH
-------- ---------------
STOPATH d:
1 record(s) selected.
The SNAP_GET_STORAGE_PATHS 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 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.
>>-SNAP_GET_STORAGE_PATHS--(--dbname--+------------------+--)-->< '-, dbpartitionnum-'
The schema is SYSPROC.
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 table function takes a snapshot for the currently connected database and database partition number.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, DB_STORAGE_PATH
FROM TABLE(SNAP_GET_STORAGE_PATHS(CAST (NULL AS VARCHAR(128)), -1)) AS T
DB_NAME DB_STORAGE_PATH
-------- -------------------...
STOPATH /home/jessicae/sdb
MYDB /home/jessicae/mdb
2 record(s) selected
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 |
DBPARTITIONNUM | SMALLINT | The database partition from which the data was retrieved for this row. |
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 |