DBPATHS administrative view - Retrieve database paths

One of the following authorizations is required:

• SELECT privilege on the DBPATHS administrative view
• CONTROL privilege on the DBPATHS administrative view
• DATAACCESS authority

In addition, one of the following privileges or authorities is also required:

• EXECUTE privilege on the ADMIN_LIST_DB_PATHS table function
• DATAACCESS authority

Retrieve all database paths.

SELECT * FROM SYSIBMADM.DBPATHS

The following example is a sample output for this query.

DBPARTITIONNUM TYPE                    ...
-------------- ------------------------...
             0 LOGPATH                 ...
             0 MIRRORLOGPATH           ...
             0 DB_STORAGE_PATH         ...
             0 DB_STORAGE_PATH         ...
             0 TBSP_CONTAINER          ...
             0 TBSP_CONTAINER          ...
             0 TBSP_CONTAINER          ...
             0 TBSP_DIRECTORY          ...
             0 TBSP_DIRECTORY          ...
             0 LOCAL_DB_DIRECTORY      ...
             0 DBPATH                  ...

  11 record(s) selected.

Output for this query (continued).

... PATH
... ------------------------------------------------
... S:\dbfiles\INST5\NODE0000\SQL00001\SQLOGDIR\
... S:\mirrorlogs\NODE0000\
... S:\dbfiles\
... S:\dbfile2\
... S:\dbfiles\INST5\NODE0000\SQL00001\TS3
... S:\dbfiles\INST5\NODE0000\SQL00001\long3
... S:\dbfiles\INST5\NODE0000\SQL00001\regular05
... S:\dbfiles\INST5\NODE0000\SQL00001\usertemp3\
... S:\dbfiles\INST5\NODE0000\SQL00001\systemp3\
... S:\dbfiles\INST5\NODE0000\SQLDBDIR\
... S:\dbfiles\INST5\NODE0000\SQL00001\

One of the following authorizations is required:

• EXECUTE privilege on the ADMIN_LIST_DB_PATHS table function
• DATAACCESS authority

In addition, one of the following authorities is also required:

• SYSMON
• SYSCTRL
• SYSMAINT
• SYSADM

The ADMIN_LIST_DB_PATHS table function can be invoked as follows:

SELECT DBPARTITIONNUM, TYPE, PATH FROM TABLE(ADMIN_LIST_DB_PATHS()) AS FILES 

The following example is a sample output from this query.

DBPARTITIONNUM    TYPE             PATH
---------------------------------------------------------------------
0                 TBSP_CONTAINER   C:\tablespaces\dms\dms1 
0                 TBSP_CONTAINER   C:\tablespaces\dms\dms2 
1                 TBSP_CONTAINER   C:\tablespaces\dms\dms3 
1                 TBSP_DIRECTORY   D:\tablespaces\sms\sms1\
2                 TBSP_DIRECTORY   D:\tablespaces\sms\sms2\
2                 TBSP_DIRECTORY   D:\tablespaces\sms\sms3\ 
0                 LOGPATH          C:\DB2\NODE0000\SQL00004\SQLOGDIR\
0                 DBPATH           C:\DB2\NODE0000\SQL00004\ 
1                 LOGPATH          C:\DB2\NODE0001\SQL00004\SQLOGDIR\
1                 DBPATH           C:\DB2\NODE0001\SQL00004\ 
2                 LOGPATH          C:\DB2\NODE0002\SQL00004\SQLOGDIR\
2                 DBPATH           C:\DB2\NODE0002\SQL00004\

If the storage library performing the split mirror operation treats files and directories on RAW devices differently than on normal file systems, you can use the following two queries to obtain, first, the list for all locations on RAW devices:

SELECT DBPARTITIONNUM, TYPE, PATH 
   FROM TABLE(ADMIN_LIST_DB_PATHS()) AS FILES 
   WHERE TYPE LIKE '%_DEVICE%'

Second, the list of files and directories on regular file systems:

SELECT DBPARTITIONNUM, TYPE, PATH 
   FROM TABLE(ADMIN_LIST_DB_PATHS()) AS FILES 
   WHERE TYPE NOT LIKE '%_DEVICE%'

• For table spaces using automatic storage, both used and unused storage paths are returned. The unused automatic storage paths are needed in case the split mirror backup is restored. Consider the following example: A split mirror backup is taken on a production system. After the backup completes, 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 split mirror backup. At this point, it is necessary to roll forward the logs from the production database. In order to roll forward the logs, all of the automatic storage paths are required, because all automatic storage paths are now in use.
• Table space containers managed by automatic storage are not returned individually. Instead, they are reflected in the automatic storage path column.
• The automatic storage paths are returned once per database partition.
• The values returned for LOGPATH and MIRRORLOGPATH are the values stored in memory. Changed values stored on disk, which are only applicable after a database restart, are not returned.
• If output from SELECT * FROM SYSIBMADM.DBPATHS is being used to create a db2relocatedb configuration file (a file containing the configuration information necessary for relocating a database), the DBPATH output must be modified appropriately before it can be used in the configuration file.
  For example, the following DBPATH output:

  /storage/svtdbm3/svtdbm3/NODE0000/SQL00001/

  can be used to specify the DB_PATH parameter in a db2relocatedb configuration file as follows:

  DB_PATH=/storage/svtdbm3,/storage_copy2/svtdbm3

• The LOCAL_DB_DIRECTORY path might contain information belonging to multiple databases. Because the sqldbdir is not separated for multiple databases created in the same directory, ensure that the target system to which files will be copied does not have any databases already existing in that path.
• If two or more databases share at least one automatic storage path, the split mirror operation for one of these databases might affect more than one database, causing I/O problems for the databases that were not intended to be split.