SQL1229N in DPF environment

Body

I am sharing one experience where my client moved DPF environment from 2 physical servers - 16 logical nodes to 1 physical server - 16 logical nodes . They had taken an offline backup of their database before this migration. They managed to restore the backup from old setup to new setup. However any command against this restored database in new setup were failing with SQL1229N error - The current transaction has been rolled back because of a system error.

The db2diag.log shows entries as follows:

2017-01-01-18.46.04.749624-360 I7829901A496       LEVEL: Error 
PID     : 11206842             TID  : 41638       PROC : db2sysc 0 
INSTANCE: db2inst1              NODE : 000         DB   : SAMPLE 
APPHDL  : 0-133                APPID: *N0.db2inst1.170101004315 
AUTHID  : db2inst1 
EDUID   : 41638                EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, buffer dist serv, sqlkdInterrupt, probe:18 
RETCODE : ZRC=0x81580016=-2124939242=SQLKD_NODE_FAILURE 
       "Mapping for SQLKF_NODE_FAILED" 
 
2017-01-01-18.46.04.749814-360 I7830398A450       LEVEL: Warning 
PID     : 11206842             TID  : 41638       PROC : db2sysc 0 
INSTANCE: db2inst1              NODE : 000         DB   : SAMPLE 
APPHDL  : 0-133                APPID: *N0.db2inst1.170101004315 
AUTHID  : db2inst1 
EDUID   : 41638                EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, base sys utilities, sqlePdbRemap, probe:100 
MESSAGE : ZRC 81580016 remapped to SQLCODE -1229 
 
2017-01-01-18.46.04.749917-360 I7830849A468       LEVEL: Error 
PID     : 11206842             TID  : 41638       PROC : db2sysc 0 
INSTANCE: db2inst1              NODE : 000         DB   : SAMPLE 
APPHDL  : 0-133                APPID: *N0.db2inst1.170101004315 
AUTHID  : db2inst1 
EDUID   : 41638                EDUNAME: db2agent (SAMPLE) 0 
FUNCTION: DB2 UDB, buffer dist serv, sqlkdInterrupt, probe:62 
MESSAGE : Error reply for intrpt. rqst. with rqst. type = -2147483624 

The current server hosting 16 logical nodes were part of original DPF setup consisting of 2 servers with 8 logical nodes each.

So the error SQL1229N is due to the fact that the FCM communication was broken between new logical nodes. With DPF, the logical nodes communicate via FCM. And for this inter-node communication, FCM ports are reserved in /etc/services file. When customer moved DPF from two physical servers to single physical server, they allocated new 8 FCM ports for newly added 8 logical nodes. They kept original 8 ports as it is in /etc/services. These existing entries were cached in system memory and were still linked to original services running on system. In other words, the ports linked to old instance on current server were never released. Hence it can not be used for newly created instance.

In order to resolve this problem, we have two possible solutions:

1. Reboot the server so that cached FCM ports are released correctly.

or

2. Allocate new FCM ports for newly created DPF instance. This solution does not require system reboot.

For solution #2 : 

a. Stop db2 instance completely.

b. edit /etc/services so as to remove existing FCM ports and keep new port entries in it.

c. Start db2 instance, activate the database and start executing any command. The command should run fine now.

*Please make a copy of original /etc/services file before doing any changes. It may be useful to revert the changes if something is failing after doing the changes suggested below. 

Original FCM Ports:

DB2_db2inst1     60000/tcp           # First FCM port for db2inst1 
DB2_db2inst1_1   60001/tcp 
DB2_db2inst1_2   60002/tcp 
DB2_db2inst1_3   60003/tcp 
DB2_db2inst1_4   60004/tcp 
DB2_db2inst1_5   60005/tcp 
DB2_db2inst1_6   60006/tcp 
DB2_db2inst1_7   60007/tcp 
DB2_db2inst1_8   60008/tcp 
DB2_db2inst1_9   60009/tcp 
DB2_db2inst1_10   60010/tcp 
DB2_db2inst1_11   60011/tcp 
DB2_db2inst1_12   60012/tcp 
DB2_db2inst1_13   60013/tcp 
DB2_db2inst1_14   60014/tcp 
DB2_db2inst1_END  60015/tcp       # Last  FCM port for db2inst1  (max 16 logical partitions per server) 

New FCM Ports:

DB2_db2inst1     60100/tcp           # First FCM port for db2inst1 
DB2_db2inst1_1   60101/tcp 
DB2_db2inst1_2   60102/tcp 
DB2_db2inst1_3   60103/tcp 
DB2_db2inst1_4   60104/tcp 
DB2_db2inst1_5   60105/tcp 
DB2_db2inst1_6   60106/tcp 
DB2_db2inst1_7   60107/tcp 
DB2_db2inst1_8   60108/tcp 
DB2_db2inst1_9   60109/tcp 
DB2_db2inst1_10   60110/tcp 
DB2_db2inst1_11   60111/tcp 
DB2_db2inst1_12   60112/tcp 
DB2_db2inst1_13   60113/tcp 
DB2_db2inst1_14   60114/tcp 
DB2_db2inst1_END  60115/tcp       # Last  FCM port for db2inst1  (max 16 logical partitions per server) 

Hope this helps you!