IBM Support

IT28460: QUERY FAILS WITH -901 FOUND ZERO CLOB.RESET OR -973

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • DB2 query may produce SQL0901N "Found zero CLOB.reset" or
    SQL0973N when the following conditions are met:
    1) The query has an outer join
    2) There is an inner join on the Outer Join that references
    column(s) from the table(s) used inside outer join
    3) The column(s) used in predicate(s) from the table used in the
    top inner join on outer join are columns in unique index.
    
    db2diag.log will show the following stack on
    sqlng_build_IU_CLOB_obj
    
    sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 21
    sqlerrmc: Found zero CLOB.reset
    sqlerrp : SQLNG09A
    sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 0x00000000
               (4) 0x001E856B      (5) 0xFFFFF3A5      (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 with size, 402 bytes
    <query>
    DATA #10: String, 520 bytes
    Compiler error stack for rc = -2145779603:
    sqlnn_cmpl[415]
    sqlng_main[3030]
    sqlng_main[1713]
    sqlng_build_thread[890]
    sqlngProcessLolepop[550]
    sqlng_process_return_op[1652]
    sqlngProcessLolepop[550]
    sqlng_process_pipe_op[889]
    sqlngProcessLolepop[550]
    sqlng_process_mate_op[1504]
    sqlngProcessLolepop[550]
    sqlng_scan_derived_table[3442]
    sqlng_build_thread[890]
    sqlngProcessLolepop[550]
    sqlng_process_sort_op[7718]
    sqlngProcessLolepop[550]
    sqlng_process_TQ_op[1065]
    sqlng_build_rev_TQB_op[2658]
    sqlng_build_IU_CLOB_obj[3163]
    
    Another symptom of this same issue will be the query failing
    with -973
    
    Obtaining a stack of the failure with db2pdcfg -catch -973 will
    show stack on sqlnr_qncdereqc
    
    FUNCTION: DB2 UDB, SW- query graph, sqlnq_select_ofn_sm,
    probe:10
    DATA #1 : <preformatted>
    Caught sqlcode -973.  Dumping stack trace.
    CALLSTCK: (Static functions may not be resolved correctly, as
    they are resolved to the nearest symbol)
      [0] 0x090000016B05799C pdLogPrintf + 0x28
      [1] 0x09000001676932DC pdLogPrintf@glue154 + 0x44
      [2] 0x0900000169E2471C pdInvokeCatchInterface + 0x134
      [3] 0xFFFFFFFFFFFFFFFC ?unknown + 0xFFFFFFFF
      [4] 0x0900000169DC9434
    sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc + 0x1E4
      [5] 0x090000016921E3BC sqlnn_erds__FiN41e + 0x134
      [6] 0x0900000169BD753C
    sqlnq_select_ofn_sm__FP9sqlnq_qtb17sqlnq_requestTypePUcP3loc +
    0x90
      [7] 0xFFFFFFFFFFFFFFFC ?unknown + 0xFFFFFFFF
      [8] 0x09000001692EF854 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x7C
      [9] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [10] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [11] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [12] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [13] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [14] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [15] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [16] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [17] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [18] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [19] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [20] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [21] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [22] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [23] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [24] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [25] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [26] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [27] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [28] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [29] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [30] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [31] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [32] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [33] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [34] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [35] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [36] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [37] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [38] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
      [39] 0x09000001692EF910 sqlnr_qncdereqc__FP9sqlnq_qncP3loc +
    0x138
    
    Note that issue was introduced in 10.5FP8
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 Version 11.1 M4FP5                            *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Db2 V11.1 M4FP5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT28460

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2019-03-14

  • Closed date

    2021-03-15

  • Last modified date

    2021-03-15

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

    IT23784

  • 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

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
03 May 2022