Fixes are available
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
APAR status
Closed as program error.
Error description
The problem can be happened against Federation Server V9.5 to V97fp4, including all releases between them. The client uses connect type 2 to connect to the federation database. It declares a WITH HOLD CURSOR for a nickname. After performing a rollback without explicitly close the cursor, it gets error SQL30020N/SQL0998N: ---------------------------------------------------------------- --------------------------------------- SQL30020N Execution of the command or SQL statement failed because of a syntax error in the communication data stream that will affect the successful execution of subsequent commands and SQL statements: Reason Code "0x124C"("011D")"". SQLSTATE=58009 ---------------------------------------------------------------- --------------------------------------- From the db2diag.log, you can find errors like below: ---------------------------------------------------------------- --------------------------------------- 2011-04-18-21.32.32.901698+480 I10175373E472 LEVEL: Error PID : 6902 TID : 47653924628800PROC : db2sysc INSTANCE: AUTHID NODE : 000 DB : DBNAME APPHDL : 0-18 APPID: *LOCAL.AUTHID.110418133212 AUTHID : AUTHID EDUID : 20 EDUNAME: db2agent (DBNAME) FUNCTION: DB2 UDB, Query Gateway, sqlqg_FMP_FedStart, probe:125 MESSAGE : ZRC=0xFFFFFFFA=-6 DATA #1 : String, 19 bytes xa_start fails with ........ 2011-04-18-21.32.32.911451+480 I10183029E838 LEVEL: Error PID : 6902 TID : 47653924628800PROC : db2sysc INSTANCE: AUTHID NODE : 000 DB : DBNAME APPHDL : 0-18 APPID: *LOCAL.AUTHID.110418133212 AUTHID : AUTHID EDUID : 20 EDUNAME: db2agent (DBNAME) FUNCTION: DB2 UDB, trace services, sqlt_logerr_data (secondary logging func, probe:0 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -998 sqlerrml: 2 sqlerrmc: 5 sqlerrp : SQLQ028e sqlerrd : (1) 0x8026006D (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 58005 2011-04-18-21.32.32.912030+480 I10185473E498 LEVEL: Error PID : 6902 TID : 47653924628800PROC : db2sysc INSTANCE: AUTHID NODE : 000 DB : DBNAME APPHDL : 0-18 APPID: *LOCAL.AUTHID.110418133212 AUTHID : AUTHID EDUID : 20 EDUNAME: db2agent (DBNAME) FUNCTION: DB2 UDB, Query Gateway, sqlqgResetWHCCnt, probe:101 DATA #1 : String, 40 bytes Server has still opened WITHHOLD cursors DATA #2 : String, 8 bytes servername 2011-04-18-21.32.32.912417+480 I10185972E446 LEVEL: Error PID : 6902 TID : 47653924628800PROC : db2sysc INSTANCE: AUTHID NODE : 000 DB : DBNAME APPHDL : 0-18 APPID: *LOCAL.AUTHID.110418133212 AUTHID : AUTHID EDUID : 20 EDUNAME: db2agent (DBNAME) FUNCTION: DB2 UDB, Query Gateway, FencedServer::find_connection, probe:10 MESSAGE : No existing connection found 2011-04-18-21.32.33.960268+480 I10186419E478 LEVEL: Error PID : 6902 TID : 47653924628800PROC : db2sysc INSTANCE: AUTHID NODE : 000 DB : DBNAME APPHDL : 0-18 APPID: *LOCAL.AUTHID.110418133212 AUTHID : AUTHID EDUID : 20 EDUNAME: db2agent (DBNAME) FUNCTION: DB2 UDB, Query Gateway, sqlqg_FMP_FedRollback, probe:160 MESSAGE : ZRC=0xFFFFFFFA=-6 DATA #1 : String, 22 bytes xa_rollback fails with ......... 2011-04-18-21.32.33.960674+480 I10188091E548 LEVEL: Error PID : 6902 TID : 47653924628800PROC : db2sysc INSTANCE: AUTHID NODE : 000 DB : DBNAME APPHDL : 0-18 APPID: *LOCAL.AUTHID.110418133212 AUTHID : AUTHID EDUID : 20 EDUNAME: db2agent (DBNAME) FUNCTION: DB2 UDB, Query Gateway, sqlqgFedRollback, probe:195 MESSAGE : ZRC=0x80260160=-2144992928=SQLQG_ERROR "Error constant for gateway." DATA #1 : <preformatted> Normal F2PC Rollback failed for server: servername ---------------------------------------------------------------- --------------------------------------- To hit the error, the following conditions all have to be satisfied: (1) Connect to database with connect type 2, auto commit set to false. (2) Declare a withhold cursor for a nickname. (3) Open the cursor and Fetch the cursor. (4) Rollback. At the above step (4), the expected result is rollback runs successfully and opened cursor is implicitly closed, but actually error SQL0998N/SQL30020N is returned.
Local fix
Explicitly close the cursor before rollback, that's to say you have to add a "close cursor" statement before a "rollback" statement.
Problem summary
**************************************************************** * USERS AFFECTED: * * The problem can be happened against Federation Server V9.5 * * to * * V97fp4, including all releases between them. * **************************************************************** * PROBLEM DESCRIPTION: * * The problem can be happened against Federation Server V9.5 * * to * * V97fp4, including all releases between them. The client uses * * connect type 2 to connect to the federation database. It * * declares a WITH HOLD CURSOR for a nickname. After performing * * a * * rollback without explicitly close the cursor, it gets error * * SQL30020N/SQL0998N: * * ------------------------------------------------------------ * * ---- * * --------------------------------------- * * SQL30020N Execution of the command or SQL statement failed * * because of a syntax error in the * * communication data stream that will affect the successful * * execution of subsequent commands and * * SQL statements: Reason Code "0x124C"("011D")"". * * SQLSTATE=58009 * * * * ------------------------------------------------------------ * * ---- * * --------------------------------------- * * From the db2diag.log, you can find errors like below: * * ------------------------------------------------------------ * * ---- * * --------------------------------------- * * 2011-04-18-21.32.32.901698+480 I10175373E472 LEVEL: * * Error * * PID : 6902 TID : 47653924628800PROC : * * db2sysc * * INSTANCE: AUTHID NODE : 000 DB : * * DBNAME * * APPHDL : 0-18 APPID: * * *LOCAL.AUTHID.110418133212 * * AUTHID : AUTHID * * EDUID : 20 EDUNAME: db2agent (DBNAME) * * FUNCTION: DB2 UDB, Query Gateway, sqlqg_FMP_FedStart, * * probe:125 * * MESSAGE : ZRC=0xFFFFFFFA=-6 * * DATA #1 : String, 19 bytes * * xa_start fails with * * * * ........ * * * * 2011-04-18-21.32.32.911451+480 I10183029E838 LEVEL: * * Error * * PID : 6902 TID : 47653924628800PROC : * * db2sysc * * INSTANCE: AUTHID NODE : 000 DB : * * DBNAME * * APPHDL : 0-18 APPID: * * *LOCAL.AUTHID.110418133212 * * AUTHID : AUTHID * * EDUID : 20 EDUNAME: db2agent (DBNAME) * * FUNCTION: DB2 UDB, trace services, sqlt_logerr_data * * (secondary * * logging func, probe:0 * * DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes * * sqlcaid : SQLCA sqlcabc: 136 sqlcode: -998 sqlerrml: 2 * * sqlerrmc: 5 * * sqlerrp : SQLQ028e * * sqlerrd : (1) 0x8026006D (2) 0x00000000 (3) * * 0x00000000 * * (4) 0x00000000 (5) 0x00000000 (6) * * 0x00000000 * * sqlwarn : (1) (2) (3) (4) (5) (6) * * (7) (8) (9) (10) (11) * * sqlstate: 58005 * * * * * * 2011-04-18-21.32.32.912030+480 I10185473E498 LEVEL: * * Error * * PID : 6902 TID : 47653924628800PROC : * * db2sysc * * INSTANCE: AUTHID NODE : 000 DB : * * DBNAME * * APPHDL : 0-18 APPID: * * *LOCAL.AUTHID.110418133212 * * AUTHID : AUTHID * * EDUID : 20 EDUNAME: db2agent (DBNAME) * * FUNCTION: DB2 UDB, Query Gateway, sqlqgResetWHCCnt, * * probe:101 * * DATA #1 : String, 40 bytes * * Server has still opened WITHHOLD cursors * * DATA #2 : String, 8 bytes * * servername * * * * 2011-04-18-21.32.32.912417+480 I10185972E446 LEVEL: * * Error * * PID : 6902 TID : 47653924628800PROC : * * db2sysc * * INSTANCE: AUTHID NODE : 000 DB : * * DBNAME * * APPHDL : 0-18 APPID: * * *LOCAL.AUTHID.110418133212 * * AUTHID : AUTHID * * EDUID : 20 EDUNAME: db2agent (DBNAME) * * FUNCTION: DB2 UDB, Query Gateway, * * FencedServer::find_connection, * * probe:10 * * MESSAGE : No existing connection found * * * * 2011-04-18-21.32.33.960268+480 I10186419E478 LEVEL: * * Error * * PID : 6902 TID : 47653924628800PROC : * * db2sysc * * INSTANCE: AUTHID NODE : 000 DB : * * DBNAME * * APPHDL : 0-18 APPID: * * *LOCAL.AUTHID.110418133212 * * AUTHID : AUTHID * * EDUID : 20 EDUNAME: db2agent (DBNAME) * * FUNCTION: DB2 UDB, Query Gateway, sqlqg_FMP_FedRollback, * * probe:160 * * MESSAGE : ZRC=0xFFFFFFFA=-6 * * DATA #1 : String, 22 bytes * * xa_rollback fails with * * * * ......... * * * * 2011-04-18-21.32.33.960674+480 I10188091E548 LEVEL: * * Error * * PID : 6902 TID : 47653924628800PROC : * * db2sysc * * INSTANCE: AUTHID NODE : 000 DB : * * DBNAME * * APPHDL : 0-18 APPID: * * *LOCAL.AUTHID.110418133212 * * AUTHID : AUTHID * * EDUID : 20 EDUNAME: db2agent (DBNAME) * * FUNCTION: DB2 UDB, Query Gateway, sqlqgFedRollback, * * probe:195 * * MESSAGE : ZRC=0x80260160=-2144992928=SQLQG_ERROR "Error * * constant * * for gateway." * * DATA #1 : <preformatted> * * Normal F2PC Rollback failed for server: servername * * ------------------------------------------------------------ * * ---- * * --------------------------------------- * * * * * * To hit the error, the following conditions all have to be * * satisfied: * * (1) Connect to database with connect type 2, auto commit set * * to * * false. * * (2) Declare a withhold cursor for a nickname. * * (3) Open the cursor and Fetch the cursor. * * (4) Rollback. * * * * At the above step (4), the expected result is rollback runs * * successfully and opened cursor is implicitly closed, but * * actually error SQL0998N/SQL30020N is returned. * **************************************************************** * RECOMMENDATION: * * Upgrade to v9.7 fp5 or later. * ****************************************************************
Problem conclusion
Fix in v9.7 fp5 or later.
Temporary fix
Comments
APAR Information
APAR number
JR39462
Reported component name
FEDERATION SERV
Reported component ID
5724N9700
Reported release
970
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2011-04-24
Closed date
2011-12-08
Last modified date
2011-12-08
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
FEDERATION SERV
Fixed component ID
5724N9700
Applicable component levels
R970 PSN
UP
R970 PSY
UP
[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCAVPY","label":"General Issues"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
08 December 2011