IC87848: DB2 MIGHT ABEND OR FAIL WITH SQL0901N (BAD PLAN; UNRESOLVED QNC FOUND) DURING BIND.

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• Under rare scenarios, DB2 might abend or give a SQL0901N (Bad
  Plan; Unresolved QNC found) error during bind.
  
  Stack Trace for trap:
  sqlnr_add_pushed_prd
  sqlnr_joinppd4sel_action
  sqlnr_comp
  qlnr_seq
  sqlnr_rcc
  sqlnr_magic_detail_action
  sqlnr_comp
  sqlnr_seq
  sqlnr_rcc
  sqlnr_endqtb_action
  
  For SQL0901N, users can see the db2diag.log entry like as below:
  2012-05-01-18.02.51.821690+540 E499790A773      LEVEL: Error
  PID     : 622766             TID  : 1544        PROC : db2sysc
  INSTANCE: db2inst1           NODE : 000         DB   : SAMPLE
  APPHDL  : 0-7                APPID: *LOCAL.db2inst1.120501090244
  AUTHID  : db2inst1
  EDUID   : 1544               EDUNAME: db2agent (SAMPLE) 0
  FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:10
  MESSAGE : ADM14005E  The following error occurred: "AppErr".
  First Occurrence
            Data Capture (FODC) has been invoked in the following
  mode:
            "Automatic".  Diagnostic information has been recorded
  in the
            directory named
            "/home/db2inst1/sqllib/db2dump/FODC_AppErr_2012-05-01-
            18.02.51.811325_622766_1544_000/".
  .
  2012-05-01-18.02.51.825744+540 I502438A856      LEVEL: Severe
  PID     : 622766             TID  : 1544        PROC : db2sysc
  INSTANCE: db2inst1           NODE : 000         DB   : SAMPLE
  APPHDL  : 0-7                APPID: *LOCAL.db2inst1.120501090244
  AUTHID  : db2inst1
  EDUID   : 1544               EDUNAME: db2agent (SAMPLE) 0
  FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc,
  probe:300
  DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 30
   sqlerrmc: Bad Plan; Unresolved QNC found
   sqlerrp : SQLNG006
   sqlerrd : (1) 0x00000000      (2) 0x00000000      (3)
  0x00000000
             (4) 0x00000000      (5) 0xFFFFFFCE      (6)
  0x00000000
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
  
             (7)      (8)      (9)      (10)        (11)
   sqlstate:
  
  The following db2diag.log entry also would be logged if
  DIAGLEVEL is set as 4.
  
  2012-05-01-18.02.51.929064+540 E544843A2106     LEVEL: Info
  (Origin)
  PID     : 622766             TID  : 1544        PROC : db2sysc
  INSTANCE: db2inst1           NODE : 000         DB   : SAMPLE
  APPHDL  : 0-7                APPID: *LOCAL.e97q5g.120501090244
  AUTHID  : db2inst1
  EDUID   : 1544               EDUNAME: db2agent (SAMPLE) 0
  FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
  MESSAGE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT
            "SQLCA has already been built"
  DATA #1 : String, 62 bytes
  An unexpected error was detected during statement compilation.
  DATA #2 : Boolean, 1 bytes
  true
  DATA #3 : Boolean, 1 bytes
  false
  DATA #4 : Boolean, 1 bytes
  false
  DATA #5 : Boolean, 1 bytes
  false
  DATA #6 : Hex integer, 4 bytes
  0x00000000
  DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
   sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 30
   sqlerrmc: Bad Plan; Unresolved QNC found
   sqlerrp : SQLNG006
   sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3)
  0x00000000
             (4) 0x00000000      (5) 0xFFFFFFCE      (6)
  0x00000000
   sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
  
             (7)      (8)      (9)      (10)        (11)
   sqlstate:
  DATA #8 : Hex integer, 4 bytes
  0x00000040
  DATA #9 : String, 822 bytes
  Compiler error stack for rc = -2145779603:
  sqlnn_cmpl[415]
  sqlng_main[3163]
  sqlng_main[1687]
  sqlng_build_thread[742]
  sqlng_process_return_op[1066]
  sqlng_process_pipe_op[1413]
  sqlng_process_mate_op[1990]
  sqlng_process_pipe_op[1413]
  sqlng_process_mate_op[1990]
  sqlng_process_nljn_op[11038]
  sqlng_build_thread[742]
  sqlng_scan_derived_table[3823]
  sqlng_build_thread[742]
  sqlng_process_sort_op[12515]
  sqlng_process_nljn_op[11217]
  sqlng_build_thread[742]
  sqlng_process_nljn_op[11038]
  sqlng_build_thread[742]
  sqlng_process_nljn_op[11460]
  sqlng_build_thread[742]
  sqlng_process_f_iscan[7020]
  sqlng_process_eao_plan[2293]
  sqlng_build_thread[742]
  sqlng_process_pipe_op[1413]
  sqlng_process_mate_op[1990]
  sqlng_process_f_iscan[7206]
  sqlng_build_TA_op[4323]
  sqlng_build_INDX_obj[5213]
  sqlng_build_KEY_obj[6205]
  sqlng_build_INDX_key[50]
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Please upgrade to DB2 version 101 fix pack 2                 *
  ****************************************************************
  

Problem conclusion

• The problem has been fixed in DB2 version 101 fix pack 2
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

  IC83144

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP

• RA10 PSY

     UP