A fix is available
APAR status
Closed as new function.
Error description
New Function
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 for zOS V12 users who receive * * SQLCODE -904 with reason code 00E70081 * * and resource type 1202 on DDL. * **************************************************************** * PROBLEM DESCRIPTION: * * When issuing DDL concurrently with * * cached dynamic SQL, the DDL can fail * * with SQLCODE -904 reason code 00E70081 * * and resource type 1202 because the SQL is still in use. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available * **************************************************************** A Db2 for z/OS DBA who needs to issue DDL often cannot do so because of concurrent dynamic SQL transactions. Large Db2 environments generally experience heavy dynamic SQL activity. DBAs who support these large environments often need to issue DDL to support the needs of the enterprise. For example, they might need to add a column in order to support a new business goal. However, when the DBA attempts to alter a table, if dynamic SQL statements have locked that table or its table space, the ALTER TABLE statement must wait for the locks to be released and sometimes fails with a timeout. In this case, the DBA must continuously reissue the ALTER TABLE statement until it runs successfully. The result is a hit to DB productivity and, more importantly, a delay in changing the table definition as required by the business. The DBA or application can experience various failures, which include but are not limited to: 1) DDL: SQLCODE -904, REASON 00E70081, TYPE 1202 2) DDL: SQLCODE -911, REASON 00C90088, TYPE 0210 3) DDL: SQLCODE -904, REASON 00C900EA, TYPE 0210 4) DML: SQLCODE -913, REASON 00C9008E, TYPE 0304 This APAR addresses failure condition #1, above.
Problem conclusion
Today, DDL statements find all cached dynamic SQL statements that are dependent on the object that is being modified. The DDL then removes the master copy of the SQL statement from the cache and invalidates the thread's local copy. If the SQL statement is still in use, the DDL waits for the SQL statement to complete and commit. It then moves to the next dependent SQL statement. The DDL's total wait time is the sum of the wait time for all the SQL statements that are in use. These SQL statements are not just the ones that are currently running when DDL is invoked; while the DDL waits, other dependent cached SQL statements can start executing, which can further extend the DDL's total wait time. DDL will timeout when it reaches the threshold specified by zparm IRLMRWT. This APAR introduces the following changes: 1) All dependent cached SQL statements that are still in use are identified up front. They will wait together in the same cycle. In other words, the DDL's total wait time is changed from the sum of all the wait times to the maximum of all the wait times. Example: Let's assume we have 3 threads (T1-T3) each running a different dependent SQL statement. Also assume IRLMRWT = 30. - T1 is executing when DDL is invoked. DDL will have to wait 20 seconds for T1 to commit. - T2 starts executing before T1 commits. DDL will have to wait 10 seconds for T2 to commit. - T3 starts executing before T2 commits. DDL will have to wait 15 seconds for T3 to commit. Before applying this APAR, DDL can wait up to 45 seconds for all the SQL statements to commit, which is the sum of the wait times. Because IRLMRWT = 30, DDL will timeout. After applying this APAR, DDL can wait up to 20 seconds for all the SQL statements to commit which is the longest wait time (T1). In this case, DDL will be successful. 2) All dependent cached SQL statements will be invalidated up front. This ensures dependent cached SQL statements cannot start while the DDL is in progress. Because the local copy is invalid, the SQL Statement must be reprepared. The prepare process requires access to the Db2 catalog. DDL holds a lock on the object in the catalog, so the prepare waits behind DDL. New SQL statements cannot be introduced. 3) If DDLTOX=1, the DDL's wait time is increased by a few seconds. In the case where there are multiple dependent cached SQL statements within a transaction, a deadlock situation can occur. For example, assume the unit of work has two dependent cached SQL statements, S1 and S2: S1 starts executing. The DDL starts executing. - The DDL invalidates S1 and S2 local copies. - The DDL waits for S1 to complete (use count = 0). S1 finishes, but does not decrement use count because it has not been committed yet. S2 starts, but cannot execute because it is invalid. S2 starts full PREPARE, but waits behind the DDL's lock. This situation exists today, but the likelihood increases with this APAR because invalidations are done up front. To address this issue, the DDL's wait time is increased so that it can outwait the PREPARE of the SQL statement. 4) If DDLTOX = 1 and IRLM APAR PH01865 is applied, the timeout threshold for PREPARE for cached dynamic SQL against the Db2 catalog is changed from IRLMRWT to MIN(IRLMRWT,30 seconds). The timeout threshold when accessing SYSIBM.SYSTABLES, SYSIBM.SYSTABLESPACE, and SYSIBM.SYSINDEXES is changed to use the Db2 default timeout threshold (30 seconds), unless the user specifies a shorter timeout period. BEHAVIOR CHANGES ------------------------------- - The likelihood of DDL failing with SQLCODE -904 REASON 00e70081 is reduced. However, allowing more DDL success while SQL statements are running comes with tradeoffs. - The likelihood of a SQL statement timing out when DDL is issued increases because the SQL statement must wait for the DDL to complete. - SQL throughput also drops when DDL is issued for the same reason. PREPARE might timeout sooner. Additional keywords: SQLCODE904 RC00E70081 SQLALTER SQLDYNSTMTCACHE
Temporary fix
Comments
×**** PE20/10/16 FIX IN ERROR. SEE APAR PH28557 FOR DESCRIPTION
APAR Information
APAR number
PH00637
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2018-07-17
Closed date
2018-10-03
Last modified date
2020-10-16
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI58875
Modules/Macros
DSNGEFSP DSNXOTL DSNXADQD DSNXIDMH DSNWVINT DSNGEFBK DSNGEGBK DSNKNXT2 DSNXEDS1 DSNXADMH DSNGEZPM DSNGEPDL DSNXEIST DSNGENER DSNKFTCH
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI58875
UP18/10/18 P F810
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.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0"}]
Document Information
Modified date:
19 October 2020