IBM Support

IC72160: DB2EXPLN FAILS WITH ERROR SQL1131 FOR SQL QUERIES HAVING DATA CHANGE TABLE REFERENCES ("SELECT ... FROM OLD/FINAL/NEW TABLE)

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When a db2expln is run against the query "db2expln -d new -f
    try.sql -z \; -t -i" such as:
    
    select ID_DAY, SID_SITE, SID_OFR   FROM old table ( delete from
    F_BRA_EXP_DAY_SIT_OFR WHERE (ID_DAY,SID_SITE) in (SELECT ID_DAY,
    SID_SITE FROM BRA_DAY_SIT_TRT_EXP) ) as T;
    
    It fails with error:
    
    The server portion of db2expln abnormally terminated.
    SQLCODE=-1131
    
    The db2diag.log shows the following messages:
    
    ===============================================
    2010-10-01-07.00.33.904990+120 E422515E3064        LEVEL: Severe
    
    PID     : 22845                TID  : 47210246957376PROC :
    db2wdog 0
    INSTANCE: bcuinst2             NODE : 000
    
    EDUID   : 2                    EDUNAME: db2wdog 0
    
    FUNCTION: DB2 UDB, routine_infrastructure, sqlerReturnFmpToPool,
    
    probe:910
    
    DATA #1 : String, 47 bytes
    
    Marking fmp as unstable during db2sysc cleanup:
    
    DATA #2 : sqlerFmpRow, PD_SQLER_TYPE_FMP_ROW, 496 bytes
    
     fmpPid: 15529
    
     fmpPoolList Ptr: 0x0000000000000000   fmpForcedList Ptr:
    
    0x0000000000000000
    
     nextFmpCB Ptr: 0x000000020053d920   prevFmpCB Ptr:
    0x000000020053c740
     fmpIPCList Ptr: 0x000000020053f460
    
     stateFlags: 0x00000150   numFmp32Attaches: 0
    
     numActiveThreads: 0   numPoolThreads: 0
    
     fmpCodePage: 0   fmpRowUseCount: 1
    
     active: 0x01    rowLoaderValidate: 0x00
    
     ipcLatch:
    
    0x000000020053E964 : 00CC AC01
    ....
     rowLatch:
    
    0x000000020053E968 : 00CC AD01
    ....
     fmpAgentList:
    
    0x000000020053E970 : 0000 0000 0000 0000 0000 0000 0000 0000
    
    ................
    
    
    
    
    
    2010-10-01-07.00.33.905163+120 E425580E397         LEVEL: Error
    
    PID     : 22845                TID  : 47210246957376PROC :
    db2wdog 0
    INSTANCE: bcuinst2             NODE : 000
    
    EDUID   : 2                    EDUNAME: db2wdog 0
    
    FUNCTION: DB2 UDB, base sys utilities, sqleChildCrashHandler,
    probe:15
    DATA #1 : <preformatted>
    
    A non-EDU child crashed. Process id: 15529, OSS term code:
    0x102,
    signal: 9
    
    
    
    2010-10-01-07.00.33.902041+120 I425978E7555        LEVEL: Error
    
    PID     : 22847                TID  : 47079023962432PROC :
    db2sysc 0
    INSTANCE: bcuinst2             NODE : 000          DB   : DWH
    
    APPHDL  : 0-10069              APPID: *N0.bcuinst2.101001050022
    
    AUTHID  : BCUINST2
    
    EDUID   : 789                  EDUNAME: db2agent (DWH) 0
    
    FUNCTION: DB2 UDB, routine_infrastructure,
    sqlerInvokeFencedRoutine,
    probe:88
    
    DATA #1 : String, 37 bytes
    
    Routine failed with termination error
    
    DATA #2 : Hexdump, 1392 bytes
    
    0x00002AD180035C88 : 5546 4F42 0000 0000 2815 0000 0000 0000
    
    UFOB....(.......
    
    
    ===============================================
    
    And the stack is:
    
    ================================================
    
    <StackTrace>
    -----Frame------ ------Address----- ------Function +
    Offset------
    0x00000FFFFFD82ED0 00000400066c1128
    _Z11printThreadR10RunOptionsP12sqlri_opparmlS2_ +
    0xfffffffffffc9630
      ( = offset 0x24128 in
    /home/dbguest4/sqllib/bin/routine/db2exsrv)
    0x00000FFFFFD82FA0 00000400066bed18
    _Z14processSectionR10RunOptions + 0xfffffffffffc72f8
      ( = offset 0x21D18 in
    /home/dbguest4/sqllib/bin/routine/db2exsrv)
    0x00000FFFFFD830B0 00000400066bab70
    _Z14processDynamicR10RunOptions + 0xfffffffffffc3228
      ( = offset 0x1DB70 in
    /home/dbguest4/sqllib/bin/routine/db2exsrv)
    0x00000FFFFFD847B0 00000400066b5450 _Z9doExplainR10RunOptions +
    0xfffffffffffbdc58
      ( = offset 0x18450 in
    /home/dbguest4/sqllib/bin/routine/db2exsrv)
    0x00000FFFFFD84C70 00000400066b2908 explain + 0xfffffffffffbb1b8
      ( = offset 0x15908 in
    /home/dbguest4/sqllib/bin/routine/db2exsrv)
    0x00000FFFFFD854A0 0000040000f944a4
      ( = offset 0xF5B4A4 in
    /home/dbguest4/sqllib/lib64/libdb2.so.1)
    0x00000FFFFFD85580 0000040000f93c6c sqlerDyload +
    0xfffffffffee0939c
      ( = offset 0xF5AC6C in
    /home/dbguest4/sqllib/lib64/libdb2.so.1)
    0x00000FFFFFD85690 0000040000f888c8 sqlerFmpListener +
    0xfffffffffedfe2e0
      ( = offset 0xF4F8C8 in
    /home/dbguest4/sqllib/lib64/libdb2.so.1)
    0x00000FFFFFD86090 0000000010002ce4 main + 0xfffffffffffedd24
      ( = offset 0x2CE4 in db2fmp ()
    0x00000FFFFFD865A0 0000040002bd0c6c
      ( = offset 0x4CC6C in /lib64/power5/libc.so.6)
    0x00000FFFFFD86870 0000040002bd0f08 __libc_start_main +
    0xffffffffffec8210
      ( = offset 0x4CF08 in /lib64/power5/libc.so.6)
    0x00000FFFFFD86920 (NULL pretcode)
    </StackTrace>
    ===================================================
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Customers using db2expln on statement containing data change *
    * table references.                                            *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * db2expln server portion crashes with -1131 error when used   *
    * to explain statement containing data change table            *
    * references.                                                  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply 97FP4 which has the fix.                               *
    ****************************************************************
    

Problem conclusion

  • Apply 97FP4 which has the fix.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC72160

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-10-25

  • Closed date

    2011-05-02

  • Last modified date

    2011-05-02

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

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

    IC72222 IC85309

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC72160

Modified date: 02 May 2011