IBM Support

PH00637: NEW FUNCTION

A fix is available

Subscribe

You can track all active APARs for this component.

 

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