ChannelDB2

How to use DBPARTITIONNUM against nickname?

Problem
"n1" is a nickname for DB2 LUW datasource:

db2 "select * from n1 where DBPARTITIONNUM(c1) = current node"
SQL0391N Invalid use of the row based function "SYSIBM.DBPARTITIONNUM".
SQLSTATE=42881

Explanation
DBPARTITIONNUM don't support nickname column as argument.
Check the document:
It says the argument should be table column

Workaround
You can create a function template and a function mapping for DBPARTITIONNUM so when you use it in a predicate with the nickname.

CREATE FUNCTION DBPARTITIONNUM( INTEGER ) RETURNS INTEGER AS TEMPLATE NO EXTERNAL ACTION NOT DETERMINISTIC
DB20000I The SQL command completed successfully.

CREATE FUNCTION MAPPING nodemap FOR DBPARTITIONNUM(INTEGER) SERVER serv OPTIONS (REMOTE_NAME 'SYSIBM.DBPARTITIONNUM(:1P)')
DB20000I The SQL command completed successfully.

select * from n1 where <schema>.DBPARTITIONNUM(c1) = current node

C1 C2 C3
----------- ----------- -----------
1 1 1
2 2 2

Notice:
1. You need to create function template for each type of nickname columns.
2. Since the function template has same name as "DBPARTITIONNUM". You need to explicitly specify schema when using it. Otherwise, the default SYSIBM.DBPARTITIONNUM will be searched too.
3. The function template can be used only in SQL statements that reference a nickname under DRDA SERVER and only if the optimizer decides that the function can be pushed down to the data source. If you try to use the function template somewhere else (or the optimizer cannot push it down), you will get the following error: SQL0142N The SQL statement is not supported.
4. DBPARTITIONNUM was named as NODENUMBER which is used by Datastage(as far as we know) when it works with Federation Server

Views: 234

Tags: DB2, DBPARTITIONNUM, Information, Integration, NODENUMBER, datastageSQL0391N, function, template

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Try BLU Acceleration on Cloud

© 2014   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service