DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
The SNAPSTORAGE_PATHS administrative view and the SNAP_GET_STORAGE_PATHS_V97 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.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
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_PATHSDB_NAME DB_STORAGE_PATH HOSTNAME
-------- --------------- ----------
STOPATH d: JESSICAE
1 record(s) selected.The SNAP_GET_STORAGE_PATHS_V97 table function returns similar information as the SNAPSTORAGE_PATHS administrative view. It allows you to retrieve the information for a specific database on a specific database member, aggregate of all database members or all database members.
Used with the SNAP_GET_DB, SNAP_GET_DETAILLOG, 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.
>>-SNAP_GET_STORAGE_PATHS_V97--(--dbname--+----------+--)------>< '-, member-'
The schema is SYSPROC.
If both dbname and member 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 member.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
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 TDB_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 | The date and time that the snapshot was taken. |
| 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 | dbpartitionnum - Database partition number monitor element |
| 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_size - Automatic storage path free space |