IBM Support

PM35207: ALLOW DYNAMIC SQL STARTING WITH SQL BRACKETED COMMENTS /* */ TO BE CACHED IN THE DB2 10 DYNAMIC STATEMENT CACHE

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • In DB2 10, allow dynamic SQL with bracketed comments /* */ at
    the start of the statement text to be cached in the DB2
    Dynamic Statement Cache.
    
    The DB2 Dynamic Statement Cache (DSC) does not support dynamic
    SQL that contains characters in front of the SQL statement
    text.  For such cases, DB2 performs a non-cache PREPARE for
    the dynamic SQL and the statement is not cached.
    This includes dyn SQL that have bracketed comments (/*  */)
    preceding the SQL statement text. This DSC behavior is as
    expected & designed and is not an error.
    
    However, with this apar DB2 10 is changed so that DSC will now
    cache dynamic SQL that have a bracketed /*  */ comment string in
    front of the SQL statement text.
    

Local fix

  • n/a
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 10 users of Dynamic Statement        *
    *                 Caching and dynamic SQL with SQL bracketed   *
    *                 comments                                     *
    ****************************************************************
    * PROBLEM DESCRIPTION: PM35207 is a DB2 10 'new function'      *
    *                      CPU performance-related  APAR to allow  *
    *                      dynamic SQL starting with SQL bracketed *
    *                      comments (slash+asterisk followed by    *
    *                      asterisk+slash) to be cached in the DB2 *
    *                      Dynamic Statement Cache (DSC), so that  *
    *                      PREPAREs of such statements can benefit *
    *                      from the CPU reduction of sharing       *
    *                      prepared dynamic statements in the DB2  *
    *                      DSC.                                    *
    *                                                            . *
    *                      Note that this would only be applicable *
    *                      if the SQL bracketed comments are not   *
    *                      removed from the dynamic SQL by a tool/ *
    *                      product 'before' DB2 even sees the SQL  *
    *                      statement text. This can occur when     *
    *                      a tool/product is being used to submit  *
    *                      dynamic SQL to DB2 on behalf of user    *
    *                      instead of user application issuing     *
    *                      the SQL PREPARE itself.                 *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    The DB2 Dynamic Statement Cache (DSC) does not support dynamic
    SQL that contains characters in front of the SQL statement text.
    This includes dynamic SQL that have SQL bracketed comments
    (slash+asterisk followed by asterisk+slash) preceding the SQL
    statement text. For such dynamic SQL cases, DB2 does not cache
    these dynamic SQL statements to be shared, instead, DB2 does
    a non-cache full PREPARE for them each time the application
    issues a PREPARE for the statement. This DSC behavior is as
    expected and designed, and is not an error.
                                                                   .
    However, as a result, for these dynamic statement cases a noted
    negative difference in CPU performance can be observed when
    compared with executing dynamic SQL statements that are cached.
                                                                   .
    Note that if a tool/product is being used to submit dynamic SQL
    to DB2 on behalf of the user (i.e., the tool/product issues
    the PREPARE) instead of the user's application issuing
    the SQL PREPARE, the tool/product may remove SQL bracketed
    comments from the SQL text before submitting the SQL statement
    to DB2. The user may not be aware that it is the tool that is
    removing the comments before DB2 even sees the statement.
    This results in the dynamic SQL statement being cached in
    the DSC, despite from the user's perspective that the SQL
    statement starts with SQL bracketed comments.
                                                                   .
    With this 'new function' CPU performance related APAR PM35207,
    DB2 10 DSC is enhanced to now cache dynamic SQL statements that
    have SQL bracketed comment strings in front of the SQL statement
    text, if those comment strings have not been removed before DB2
    sees the statement. However, the leading SQL bracketed comments
    must be unnested (i.e., the bracketed comment cannot contain
    another bracketed comment) and no single leading bracketed
    comment can exceed 258 bytes in length. Statements with multiple
    leading bracketed comments are eligible for the DSC.
    This enhancement can result in CPU reduction when executing
    these dynamic SQL statements.
                                                                   .
    Note that dynamic SQL that have SQL bracketed comments 'within'
    the SQL statement text (i.e., not 'preceding' the statement
    text) are already eligible to be cached in the DSC prior to this
    APAR without restriction, and continue to be eligible.
                                                                   .
    The DB2 10 Application Programming and SQL Guide discussion of
    of 'Dynamic SQL statements that DB2 can cache', topic
    'Eligible statements:' is updated for PM35207 as follows:
                                                                   .
       The following types of SQL statement text with SQL bracketed
       comments can be saved in the dynamic statement cache:
        - SQL statement text that begins with SQL bracketed comments
          that are unnested. No single SQL bracketed comment that
          begins the statement can be greater than 258 bytes.
        - SQL statement text with unnested or nested SQL bracketed
          comments within the text.
                                                                   .
       Bracketed comments that are in SQL statement source code are
       saved with the statement text when the SQL statements are
       placed in the dynamic statement cache, unless other tools
       remove the bracketed comments before DB2 processes the SQL
       statement.
                                                                   .
       SQL statement text that is preceded by SQL simple comment
       (--) or any other characters besides unnested, bracketed
       comments is not eligible to be saved in the dynamic statement
       cache.
                                                                   .
    

Problem conclusion

Temporary fix

  • AM35207
    

Comments

  • DB2 10 Dynamic Statement Caching has been changed as previously
    described for dynamic SQL that start with SQL bracketed comment
    strings in front of the SQL statement text.
                                                                   .
    Additional search keywords: SQLDYNSTMTCACHE PERFM
    

APAR Information

  • APAR number

    PM35207

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-03-21

  • Closed date

    2011-11-14

  • Last modified date

    2011-12-02

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK73841

Modules/Macros

  • DSNXEDSC
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK73841

       UP11/11/30 P F111

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":"10.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":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
02 December 2011