IBM Support

JR39462: SQL30020N/SQL0998N IS RETURNED AFTER ROLLBACK FOR WITH HOLD CURSOR WHEN CLIENT CONNECT TYPE 2

Subscribe

You can track all active APARs for this component.

 

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