DBPATHS administrative view and ADMIN_LIST_DB_PATHS table function - Retrieve database paths

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.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

DBPATHS administrative view

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:
  • SELECT privilege on the DBPATHS administrative view
  • CONTROL privilege on the DBPATHS administrative view
  • DATAACCESS authority
  • DBADM authority
  • SQLADM authority
  • ACCESSCTRL authority
  • SECADM authority
One of the following is required to use the table function:
  • SYSMON
  • SYSCTRL
  • SYSMAINT
  • SYSADM

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

Example

The following statement retrieves all database paths:
   select dbpartitionnum, substr(type,1,20) as type, path from sysibmadm.dbpaths
The following is an example of output for this query. 
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.

ADMIN_LIST_DB_PATHS table function

The ADMIN_LIST_DB_PATHS table function returns the list of files required for backup mechanisms such as split mirror backup.

Syntax

Read syntax diagramSkip visual syntax diagramADMIN_LIST_DB_PATHS()

The schema is SYSPROC.

Authorization

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

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

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 is an example of output from this query. 
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.
If the storage library performing the split mirror operation treats files and directories on raw devices differently than those on regular file systems, you can use the following query to obtain 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%'

Information returned

Table 1. Information returned by the DBPATHS administrative view and the ADMIN_LIST_DB_PATHS table function
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.
Table 2. TYPE column values
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 Active 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.
  • For table spaces using automatic storage, both used and unused storage paths are returned. You require the unused automatic storage paths if you restore by using a split mirror backup.

    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.

  • Table space containers that are 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 the LOGPATH and MIRRORLOGPATH configuration parameters are the values stored in memory. Changed values that are stored on disk, which are applicable only after a database restart, are not returned.
  • In a Db2® pureScale® environment, after a member is dropped, if the database is still maintaining the member’s contents, such as the metadata files under the member directory or transaction log files, then this is reflected in the output of the DBPATH and LOGPATH columns, which includes an entry for all existing members and previously dropped members.
  • If you use output from the SELECT * FROM SYSIBMADM.DBPATHS query to create a db2relocatedb command configuration file (a file containing the configuration information that is necessary for relocating a database), you must modify the DBPATH output appropriately before you can use it in the configuration file.
    For example, consider the following DBPATH output: 
    /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
  • The LOCAL_DB_DIRECTORY path might contain information belonging to multiple databases. Because each database that you create in the same directory does not have its own sqldbdir file, ensure that the target system to which you copy files does not have any databases already 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 you did not intend to split.
  • The DB_STORAGE_PATH type includes all storage paths from all defined storage groups. If a storage path is used by multiple storage groups or is specified multiple times in the same storage group, one record is returned for each occurrence of the path in a storage group.

Restriction

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.