The DBPATHS administrative view and the ADMIN_LIST_DB_PATHS table function return the values for database paths that are required for tasks such as creating split mirror backups.
The schema is SYSIBMADM.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
select dbpartitionnum, substr(type,1,20) as type, path from sysibmadm.dbpaths
DBPARTITIONNUM TYPE PATH
-------------- -------------------- ------------------------------------------------
0 LOGPATH /home/sun/sun/NODE0000/SQL00001/LOGSTREAM0000/
0 DB_STORAGE_PATH /home/sun/
0 LOCAL_DB_DIRECTORY /home/sun/sun/NODE0000/sqldbdir/
0 DBPATH /home/sun/sun/NODE0000/SQL00001/
0 DBPATH /home/sun/sun/NODE0000/SQL00001/MEMBER0000/
5 record(s) selected.
EThe ADMIN_LIST_DB_PATHS table function returns the list of files required for backup mechanisms such as split mirror backup.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT DBPARTITIONNUM, TYPE, PATH FROM TABLE(ADMIN_LIST_DB_PATHS()) AS FILES
DBPARTITIONNUM TYPE
-------------- ----------------------
2 LOGPATH
2 DB_STORAGE_PATH
2 TBSP_DIRECTORY
2 TBSP_CONTAINER
2 LOCAL_DB_DIRECTORY
2 DBPATH
2 DBPATH
0 LOGPATH
0 DB_STORAGE_PATH
0 TBSP_DIRECTORY
0 TBSP_CONTAINER
0 LOCAL_DB_DIRECTORY
0 DBPATH
0 DBPATH
1 LOGPATH
1 DB_STORAGE_PATH
1 TBSP_DIRECTORY
1 TBSP_CONTAINER
1 LOCAL_DB_DIRECTORY
1 DBPATH
1 DBPATH
PATH
--------------------------------------------------
/home/sun/sun/NODE0002/SQL00001/LOGSTREAM0002/
/home/sun/
/home/sun/tablespace/sms/sms2/
/home/sun/tablespace/dms/dms2
/home/sun/sun/NODE0002/sqldbdir/
/home/sun/sun/NODE0002/SQL00001/
/home/sun/sun/NODE0002/SQL00001/MEMBER0002/
/home/sun/sun/NODE0000/SQL00001/LOGSTREAM0000/
/home/sun/
/home/sun/tablespace/sms/sms0/
/home/sun/tablespace/dms/dms0
/home/sun/sun/NODE0000/sqldbdir/
/home/sun/sun/NODE0000/SQL00001/
/home/sun/sun/NODE0000/SQL00001/MEMBER0000/
/home/sun/sun/NODE0001/SQL00001/LOGSTREAM0001/
/home/sun/
/home/sun/tablespace/sms/sms1/
/home/sun/tablespace/dms/dms1
/home/sun/sun/NODE0001/sqldbdir/
/home/sun/sun/NODE0001/SQL00001/
/home/sun/sun/NODE0001/SQL00001/MEMBER0001/
21 record(s) selected.
SELECT DBPARTITIONNUM, TYPE, PATH
FROM TABLE(ADMIN_LIST_DB_PATHS()) AS FILES
WHERE TYPE LIKE '%_DEVICE%'
SELECT DBPARTITIONNUM, TYPE, PATH
FROM TABLE(ADMIN_LIST_DB_PATHS()) AS FILES
WHERE TYPE NOT LIKE '%_DEVICE%'
Column name | Data type | Description |
---|---|---|
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
TYPE | VARCHAR(64) | Describes the type of database object that the path belongs to. For example the path to the log directory indicated by the LOGPATH database configuration parameter would be shown in this column as LOGPATH. See Table 2 for a list of possible return values. |
PATH | VARCHAR(5000) | Path to location where the database manager has a file or directory located. If the path ends with the file system delimiter ('/' on UNIX environments, '\' on Windows environments), the path points to a directory. |
Type value | Description |
---|---|
TBSP_DEVICE | Raw device for a database managed space (DMS) table space. |
TBSP_CONTAINER | File container for a DMS table space. |
TBSP_DIRECTORY | Directory for a system managed space (SMS) table space. |
LOGPATH | Primary log path. |
LOGPATH_DEVICE | Raw device for primary log path. |
MIRRORLOGPATH | Database configuration mirror log path. |
DB_STORAGE_PATH | Automatic storage path. |
DBPATH | Database directory path. |
LOCAL_DB_DIRECTORY | Path to the local database directory. |
Consider the following example. A split mirror backup is taken on a production system. After the backup is completed, the automatic storage paths that were not in use before the backup are now in use in production. Assume that there is now a need to restore the database by using the split mirror backup. At this point, it is necessary to roll forward the logs from the production database. To roll forward the logs, all of the automatic storage paths are required, because all automatic storage paths are now in use.
/storage/svtdbm3/svtdbm3/NODE0000/SQL00001/
You can use
this output to specify the value of the DB_PATH parameter in a db2relocatedb command configuration file, as follows: DB_PATH=/storage/svtdbm3,/storage_copy2/svtdbm3
You cannot call the administrative view when the database is in WRITE SUSPEND mode. Also, you must ensure that the physical layout of the database does not change between the invocation of the view and the activation of WRITE SUSPEND mode, which is needed to perform a split mirror operation. You might not be able to restore from the split mirror backup image if, for example, the table space layout changed in that time.