DB_PARTITIONS table function

The DB_PARTITIONS table function returns the contents of the db2nodes.cfg file in table format.

Note: This table function has been deprecated and replaced by the DB2_MEMBER and DB2_CF administrative views and DB2_GET_INSTANCE_INFO table function.

Syntax

The schema is SYSPROC.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

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

Table function parameters

The function has no input parameters.

Examples

Retrieve information from a 4 member partitioned database instance.

SELECT * FROM TABLE(DB_PARTITIONS()) as T

The following is an example of the output from this query:

PARTITION_NUMBER HOST_NAME PORT_NUMBER SWITCHNAME
---------------- --------- ----------- -------
               0 so1                 0 so1-ib0 
               1 so2                 0 so2-ib0 
               2 so3                 0 so3-ib0 
               3 so4                 0 so4-ib0

     4 record(s) selected.

In a Db2 pureScale environment, retrieve information from a 3 member and 1 cluster caching facility Db2® pureScale instance.

SELECT * FROM TABLE(DB_PARTITIONS()) as T

The following is an example of the output from this query:

PARTITION_NUMBER HOST_NAME  PORT_NUMBER SWITCHNAME
---------------- ---------- ----------- ----------
               0 so1                  0 so1-ib0
               0 so2                  0 so2-ib0
               0 so3                  0 so3-ib0

     3 record(s) selected.

Usage notes

For Db2 Enterprise Server Edition and in a partitioned database environment, the DB_PARTITIONS table function returns one row for each entry in the db2nodes.cfg file.

In a Db2 pureScale environment, the DB_PARTITIONS table function returns multiple rows, with the following information in the columns:

The PARTITION_NUMBER column always contains 0.
The remaining columns show information for the entry in the db2nodes.cfg file for the current member.

Information returned

Table 1. Information returned by the DB_PARTITIONS table function
Column name	Data type	Description
PARTITION_NUMBER	SMALLINT	partition_number - Partition Number monitor element
HOST_NAME	VARCHAR(256)	host_name - Host name monitor element
PORT_NUMBER	SMALLINT	The port number for the database partition server.
SWITCH_NAME	VARCHAR(128) Starting from version 11.5.6: VARCHAR(256)	The name of a high speed interconnect, or switch, for database partition communications.