Technical Blog Post
Abstract
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!
UID
ibm13286599