IBM Support

IT33973: APPLICATION IN UOW EXECUTING USING HASH JOINS MAY HANG AND COULDNOT BE FORCED OFF

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • The conditions which cause this issue are as follows:
    
    1. SMP environment
    2. Automatic setting of SORTHEAP
    3. Query using HSJOIN
    4. Extremely memory constrained environment
    
    Application may stuck in
    'Application status' = UOW Executing
    Trying to force this application does not work. It keeps
    running.
    The stack of this coordinator agent may show:
    
    sqlkdInterrupt
    sqlriecc
    sqlrr_process_close_request
    sqlrr_fetch_error
    sqlrr_open
    sqljs_ddm_opnqry
    sqljsParseRdbAccessed
    ...
    
    All its smp subagents are in sqloXlatchConflict and at least one
    will be in sqlri_hsjnFlushBlocks:
    
    sqloXlatchConflict
    sqlri_hsjnFlushBlocks
    sqlri_hsjnPartitionFull
    sqlri_hsjnProcessBuildTuple
    sqlrihsjnBuildPD
    sqldDataFetch
    ...
    
    
    Other stack should have sqlri_hsjnHeapReduction and
    sqlri_hsjnFlushBlocks like so:
    
    sqloXlatchConflict
    sqlri_hsjnFlushBlocks
    sqlri_hsjnHeapReduction
    sqlri_hsjnGetTupleBlock
    sqlri_hsjnPartitionFull
    sqlri_hsjnProcessBuildTuple
    sqlrihsjnBuildPD
    
    db2diag.log may be flushed with messages from STMM trying to
    reduce SORTHEAP memory.
    
    This fix will cause error SQL0955N to be returned when this
    happens and force off the application and not cause any hangs.
    

Local fix

  • To get out of the hang situation, kill the process executing the
    application
    and
    to prevent it:
    
    Apply degree=1 on per query basis.
    
    /* <OPTGUIDELINES> <DEGREE VALUE="1"/> </OPTGUIDELINES> */
    
    for this query.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1.4.6 or higher                            *
    ****************************************************************
    

Problem conclusion

  • Upgrade to Db2 11.1.4.6 or higher
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT33973

  • 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

    2020-08-21

  • Closed date

    2021-03-31

  • Last modified date

    2021-03-31

  • 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

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1"}]

Document Information

Modified date:
01 April 2021