IBM Support

IC79679: QUERY REFERENCING A TABLE CONTAINING A GENERATED COLUMN MIGHT PRODUCE A -901

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • Under rare scenarios, DB2 might produce a SQL0901N if the
    following conditions are true:
    1. The query references a table with one or more generated
    columns
    2. The same table is part of one or more MQTs
    3. The generated column(s) are not referenced in the MQT
    
    In the db2diag.log, the issue appears as
    
    2011-08-22-14.59.17.413828-240 I107409736E862      LEVEL: Severe
    PID     : 25891                TID  : 192766011744 PROC :
    db2sysc 1
    INSTANCE: nypxt280             NODE : 001          DB   : IMRSDM
    APPHDL  : 1-5889               APPID:
    10.184.115.183.24068.1108221859
    AUTHID  : IMRSDB
    EDUID   : 93402                EDUNAME: db2agent (IMRSDM) 1
    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 : SQLNG032
     sqlerrd : (1) 0x00000000      (2) 0x00000000      (3)
    0x00000000
               (4) 0x000000B9      (5) 0xFFFFFC1E      (6)
    0x00000001
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
    
    
    <dumping into FODC>
    
    2011-08-22-14.59.17.659189-240 E107459750E1259     LEVEL: Info
    (Origin)
    PID     : 25891                TID  : 192766011744 PROC :
    db2sysc 1
    INSTANCE: nypxt280             NODE : 001          DB   : IMRSDM
    APPHDL  : 1-5889               APPID:
    10.184.115.183.24068.1108221859
    AUTHID  : IMRSDB
    EDUID   : 93402                EDUNAME: db2agent (IMRSDM) 1
    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, 723 bytes
    Compiler error stack for rc = -2145779603:
    sqlnn_cmpl[420]
    sqlng_main[2790]
    sqlng_main[1656]
    sqlng_build_thread[10]
    sqlng_process_return_op[50]
    sqlng_process_pipe_op[30]
    sqlng_process_mate_op[1735]
    sqlng_process_TQ_op[30]
    sqlng_build_thread[40]
    sqlng_scan_derived_table[40]
    sqlng_build_thread[10]
    sqlng_process_sort_op[20]
    sqlng_process_nljn_op[50]
    sqlng_build_thread[10]
    sqlng_process_hsjn_op[60]
    sqlng_build_thread[10]
    sqlng_process_nljn_op[50]
    sqlng_build_thread[10]
    sqlng_process_TQ_op[30]
    sqlng_build_thread[40]
    sqlng_process_nljn_op[140]
    sqlng_build_thread[10]
    sqlng_process_f_iscan[110]
    sqlng_build_TA_op[110]
    sqlng_build_thread[100]
    sqlng_walk_BF_chain[25]
    sqlng_process_BF_node[20]
    sqlng_process_parse_tree_n[994]
    
    <followed by ADM5570W>
    

Local fix

  • Create another MQT which references the generated column in the
    select list.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7 Fixpack 6                                *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC79679

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-11-08

  • Closed date

    2012-07-20

  • Last modified date

    2012-07-20

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

    IC79077

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

Fix information

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC79679

Modified date: 20 July 2012