A fix is available
APAR status
Closed as program error.
Error description
DB2DDF DDFL09 DB2INACTIVE defects pk55838 dpk55838 A distributed thread using sequence objects will not go inactive after it commits if the fix for PK32436 is installed. This can result in the thread getting timed out for exceeding the idle thread timeout specification in zparm IDTHTOIN. 00D3003B *********************************************** Additional symptoms and keywords: MSGDSNL027I DSNL027I 00D3003B RC00D3003B Idle Thread Time-out Timeout Missing reduced fewer IFCID 3 0003 IFCID3 IFCID0003 accounting records SEQUENCE SEQUENCES PREVVAL PREVAL ACESQACT SeQuences ACTive SQLCODE -845 SQLCODE845 SQL845N SQL0845N DSN6FAC CMTSTAT INACTIVE DSNTIPR DDF THREADS INACTIVE
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All Distributed Data Facility (DDF) users. * * Specifically those that exploit the use * * of SQL Sequences, and those where DB2 is * * configured with DDF THREADS=INACTIVE * * specified (DSN6FAC CMTSTAT INACTIVE) or * * those with client systems (such as * * DB2 UDB/LUW) that pool/re-use connections * * among numerous client applications. * **************************************************************** * PROBLEM DESCRIPTION: IFCID 3 accounting records are * * not written and/or a high rate of * * unexpected "Idle Thread Time-out" * * (reason code 00D3003B) detections * * may occur because a DDF server thread * * is not allowed to become inactive * * due to the use of SQL Sequences. * * Client "connection concentrator" * * environments may also be adversely * * affected as a result of an inability * * to pool connections on a transaction * * (commit/rollback) boundary. * **************************************************************** * RECOMMENDATION: * **************************************************************** To correct remote client application incorrect output conditions related to the use of Sequences PREVVAL after a Commit or Rollback, and due to the complexity and risk associated with implementing a save and restore mechanism to maintain the Sequences environment across transaction boundaries, APAR PK32436 made a change to prevent DB2 z/OS threads and connections from being pooled when SQL Sequences are involved. That is, the solution corrected the Sequences incorrect output condition at the expense of preventing DB2 z/OS threads and connections from being pooled. This solution imposes too much adverse operational impact to application environments that use Sequences, especially since most of these environments are not dependent on PREVVAL values spanning transaction boundaries. Symptoms may include: . Increased number of concurrent Data Base Access Threads (DBATs) are required in order to service work because existing DBATs cannot be pooled to service the work. . Increased number of concurrent remote connections are required by remote client systems since existing connections cannot be pooled and re-used. . Increased occurrences of Idle Thread Time-out (00D3003B) detection conditions. Negative performance and throughput characteristics may also be observed as a result of the PK32436-induced inability to have a limited number of threads and connections that can be used to service a large number of remote applications. This may also lead to higher consumption of storage and network resources which may also impact performance.
Problem conclusion
The ability to save and restore SQL Sequences environments is too complex and risky for an APAR. However, DDF thread and connection pooling is more desirable than the rare exposure to erroneous Sequences PREVVAL behavior. As a result, changes will be made to effectively reverse the PK32436-induced impact to DDF thread and connection pooling behavior. This means that the Sequences PREVVAL value will not be able to span transaction boundaries as far as remote applications are concerned. As a result, when a thread or connection is pooled, DB2 will ensure that the existing, if any, Sequences environment is destroyed in order to prevent an erroneous Sequences environment from being inherited by new users of the thread or connection. Hence, if a remote application attempts to resume their use of the Sequences PREVVAL value in a new transaction, a -845 SQLCODE may now be returned. The ability to save and restore the SQL Sequences environment, and hence allow pooling *and* the ability for Sequences PREVVAL references to be made across transaction boundaries, will be addressed in a future release of DB2. Until that time, in cases where applications desire the ability to reference PREVVAL across transaction boundaries, the DB2 z/OS SQL Reference manual will be changed to describe an application programming technique where a cursor can be defined WITH HOLD. The WITH HOLD nature of the cursor will prevent DDF thread and connection pooling and hence allow the desired PREVVAL behavior for remote applications.
Temporary fix
Comments
APAR Information
APAR number
PK55838
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
810
Status
CLOSED PER
PE
YesPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2007-11-01
Closed date
2007-11-28
Last modified date
2008-01-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK31712 UK31713
Modules/Macros
DSNDACE DSNLQDIS DSNLQINA DSNLTCM1 DSNLTDSR DSNLTRB1 DSNTACT
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 January 2008