Troubleshooting
Problem
In purescale environment, SQL0171N error is returned if a value greater than zero (>0) is passed as second input parameter in table function ADMIN_GET_STORAGE_PATHS().
Symptom
SQL0171N The statement was not processed because the data type, length or
value of the argument for the parameter in position "2" of routine
"ADMIN_GET_STORAGE_PATHS" is incorrect. Parameter name: "DBPARTITIONNUM".
SQLSTATE=42815
Cause
In purescale environment, SQL0171N is received when following query is run where second input parameter in greater than 0 (zero) :
db2 "select * from TABLE( ADMIN_GET_STORAGE_PATHS ( '', 1 )) AS SNAP";
Diagnosing The Problem
Based on the following example Purescale configuration :
db2nodes.cfg :
0 node1 0 - - MEMBER
1 node1 1 - - MEMBER
2 node1 2 - - MEMBER
128 node1 0 - - CF
129 node1 0 - - CF
When the following query is executed :
db2 "select * from TABLE( ADMIN_GET_STORAGE_PATHS ( '', 1 )) AS SNAP"
The result is :
STORAGE_GROUP_NAME STORAGE_GROUP_ID DBPARTITIONNUM DB_STORAGE_PATH DB_STORAGE_PATH_WITH_DPE DB_STORAGE_PATH_STATE DB_STORAGE_PATH_ID FS_ID FS_TOTAL_SIZE FS_USED_SIZE STO_PATH_FREE_SIZE
-------------------------------------------------------------------------------------------------------------------------------- ---------------- -------------- ----------
SQL0171N The statement was not processed because the data type, length or
value of the argument for the parameter in position "2" of routine
"ADMIN_GET_STORAGE_PATHS" is incorrect. Parameter name: "DBPARTITIONNUM".
SQLSTATE=42815
According to the infocenter documentation of table function ADMIN_GET_STORAGE_PATHS() the second input parameter is a MEMBER :
Infocenter link :
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0055331.html
Syntax in infocenter :
>>-ADMIN_GET_STORAGE_PATHS--(--storage_group_name--,--member--)-><
The documentation refers to the second parameter of the table function as MEMBER, which leads to the impression that this is a PureScale member, while in reality it denotes a DPF data partition number (DBPARTITIONNUM). Hence it leads to SQL0171N error when a value greater than "0" is passed.
The infocenter specifies a general reference to second parameter as MEMBER but when it comes to purescale environment, the function logic explicitly checks to make
sure that a database partition number (DBPARTITIONNUM) value of "0" is entered and throws an error SQL0171N if a value greater than zero is entered.
Resolving The Problem
Please pass second input parameter value as "0" for DBPARTITIONNUM in table function ADMIN_GET_STORAGE_PATHS(), in purescale environments.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21986186