|
Problem IC78143
|
Status: Closed |
AN APPLICATION MAY APPEAR TO BE STUCK IN "UOW EXECUTING" STATE WHEN EXECUTING A PLAN CONTAINING AN INDEX AND OPERATION. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
An application may appear to be stuck in "UOW Executing" state
when executing query with an execution plan that includes an
index AND operation. It will be characterised by high CPU usage
for one or more EDUs processing the query on behalf of the
application.
Index ANDing represents the logical AND of results from multiple
index scans on the same source object to satisfy the filtering
criteria or to merge the results of semi-joins used in star
joins.
Examination of the stack for an EDU involved in this processing
will be similar to the following:
==========================================
sqlri_BFBandP
sqliScanLeaf2NoLocking
sqlifnxt
sqlirdk
sqldIndexFetch
sqldIndexFetch
sqldRowFetch
......
==========================================
The application isn't truly hung but may take an unrealistic
amount of time to complete and may be forced using the FORCE
APPLICATION command. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of version 9.7 on Linux, Unix and Windows *
* platforms. *
****************************************************************
* PROBLEM DESCRIPTION: *
* An application may appear to be stuck in "UOW Executing" *
* state when executing query with an execution plan that *
* includes an index AND operation. It will be characterised by *
* high CPU usage for one or more EDUs processing the query on *
* behalf of the application. *
* *
* Index ANDing represents the logical AND of results from *
* multiple index scans on the same source object to satisfy *
* the filtering criteria or to merge the results of semi-joins *
* used in star joins. *
* *
* Examination of the stack for an EDU involved in this *
* processing will be similar to the following: *
* ========================================== *
* sqlri_BFBandP *
* sqliScanLeaf2NoLocking *
* sqlifnxt *
* sqlirdk *
* sqldIndexFetch *
* sqldIndexFetch *
* sqldRowFetch *
* ...... *
* ========================================== *
* *
* The application isn't truly hung but may take an unrealistic *
* amount of time to complete and may be forced using the FORCE *
* APPLICATION command. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 UDB Version 9.7 FixPak 5 or higher levels. *
**************************************************************** |
| Local Fix: |
Use the query explain tools to check the access plan for the
executing SQL and create necessary indices to avoid the index
AND operations. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 UDB Version 9.7 FixPak 5. |
| Workaround |
Use the query explain tools to check the access plan for the
executing SQL and create necessary indices to avoid the index
AND operations. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.08.2011
02.01.2012
02.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
