Conditions for statement sharing

If a prepared version of an identical SQL statement already exists in the dynamic statement cache, certain conditions must still be met before Db2 can reuse that prepared statement.

Suppose that S1 and S2 are source statements, and P1 is the prepared version of S1. P1 is in the dynamic statement cache.

The following conditions must be met before Db2 can use statement P1 instead of preparing statement S2:

  • S1 and S2 must be identical. The statements must pass a character by character comparison and must be the same length. If the PREPARE statement for either statement contains an ATTRIBUTES clause, Db2 concatenates the values in the ATTRIBUTES clause to the statement string before comparing the strings. That is, if A1 is the set of attributes for S1 and A2 is the set of attributes for S2, Db2 compares S1||A1 to S2||A2. S1 and S2 must be identical if the PREPARE ATTRIBUTES clause CONCENTRATE STATEMENTS WITH LITERALS is not used to request literal constant replacement in S1 and S2.

    If the statement strings are not identical, Db2 cannot use the statement in the cache.

    For example, assume that S1 and S2 are specified as follows: 
    'UPDATE EMP SET SALARY=SALARY+50'
    In this case, Db2 can use P1 instead of preparing S2.
    However, assume that S1 is specified as follows: 
    'UPDATE EMP SET SALARY=SALARY+50'
    Assume also that S2 is specified as follows: 
    'UPDATE EMP SET SALARY=SALARY+50 '
    In this case, Db2 cannot use P1 for S2. Db2 prepares S2 and saves the prepared version of S2 in the cache.
  • The authorization ID or role that was used to prepare S1 must be used to prepare S2:
    • When a plan or package has run behavior, the authorization ID is the current SQLID value.
      For secondary authorization IDs:
      • The application process that searches the cache must have the same secondary authorization ID list as the process that inserted the entry into the cache or must have a superset of that list.
      • If the process that originally prepared the statement and inserted it into the cache used one of the privileges held by the primary authorization ID to accomplish the prepare, that ID must either be part of the secondary authorization ID list of the process searching the cache, or it must be the primary authorization ID of that process.
    • When a plan or package has bind behavior, the authorization ID is the plan owner's ID. For a DDF server thread, the authorization ID is the package owner's ID.
    • When a package has define behavior, then the authorization ID is the user-defined function or stored procedure owner.
    • When a package has invoke behavior, then the authorization ID is the authorization ID under which the statement that invoked the user-defined function or stored procedure executed.
    • If the application process has a role associated with it, Db2 uses the role to search the cache instead of the authorization IDs. If the trusted context that associated the role with the application process is defined with the WITH ROLE AS OBJECT OWNER clause, the role value is used as the default for the CURRENT SCHEMA special register and the SQL path.
  • When the plan or package that contains S2 is bound, the values of these bind options must be the same as when the plan or package that contains S1 was bound:
    • CURRENTDATA
    • DYNAMICRULES
    • ISOLATION
    • SQLRULES
    • QUALIFIER
    • EXTENDEDINDICATOR
  • When S2 is prepared, the values of the following special registers must be the same as when S1 was prepared:
    • CURRENT DECFLOAT ROUNDING MODE
    • CURRENT DEGREE
    • CURRENT RULES
    • CURRENT PRECISION
    • CURRENT REFRESH AGE
    • CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
    • CURRENT LOCALE LC_CTYPE
  • Two statements must be identical, except for literals. When the PREPARE ATTRIBUTES clause CONCENTRATE STATEMENTS WITH LITERALS is specified and the statements qualify for literal constant replacement, the cached statement (where the literals were already replaced) and the new statement must be identical, except for the literals. To be considered for literal constant replacement, the dynamic SQL statement must not include parameter markers ('?').

    If the first search of the cache does not find an exact match using the original statement text and CONCENTRATE STATEMENTS WITH LITERALS is specified in the ATTRIBUTES clause, the CONCENTRATE STATEMENTS behavior goes into effect. Db2 substitutes the ampersand character ('&') for literal constants in the SQL statement text and continues the cache prepare process, using this new version of the statement text that contains '&'. Db2 searches the cache again to find a matching cached statement that also has '&' substituted for the literal constants. For this second search, the new statement and the cached statement must again pass a character by character statement text comparison, with both statements having '&' for the literals. If that statement text comparison is successful, Db2 determines if the literal reusability criteria between the two statements allows for the new statement to share the cached statement.

    For literal reusability, the reusability criteria includes, but is not limited to, the immediate usage context, the literal data type, and the data type size of both the new literal instance and the cached literal instance. If Db2 determines that the new instance of a literal can be reused in place of the cached literal instance, a cached statement that was prepared with the CONCENTRATE STATEMENTS WITH LITERALS clause can be shared by the same SQL statement with a different instance of a literal value. However, that same SQL statement must meet all of the other conditions for sharing the cached statement.

    If Db2 determines that the statement with the new literal instance cannot share the cached statement because of incompatible literal reusability criteria, Db2 inserts into the cache a new statement that has both '&' substitution and a different set of literal reusability criteria. This new statement is different from the cached statement, even though both statements have the same statement text with ampersand characters ('&'). Now, both statements are in the cache, but each has different literal reusability criteria that makes these two cached statements unique.

    Example 1
    Original SQL statement:
    SELECT X, Y, Z FROM TABLE1 WHERE X < 123 (no cache match)

    After the literals are replaced with '&', the cached statement is as follows:

    SELECT X, Y, Z FROM TABLE1 WHERE X < &
    Example 2
    Original SQL statement:
    INSERT INTO TABLE1 (X, Y, Z) VALUES (8,109,29) (no cache match)

    After the literals are replaced with '&', the cached statement is as follows:

    INSERT INTO TABLE1 (X, Y, Z) VALUES (&,&,&)
    Example 3
    As an example of the literal reusability criteria, assume that the SELECT statement from example 1 is cached as follows, where column X has data type decimal:
    SELECT X, Y, Z FROM TABLE1 WHERE X < 123 (no cache match)

    After the literals are replaced with '&', the cached statement is as follows:

    SELECT X, Y, Z FROM TABLE1 WHERE X < & (+ lit 123 reuse info)

    Assume that the following new instance of that statement is now being prepared:

    SELECT X, Y, Z FROM TABLE1 WHERE X < 1E2

    According to the literal reusability criteria that Db2 uses for literal replacement, the literal value 1E2 in the new version of the SELECT statement is not reusable in place of the literal value 123 in the original cached '&' SELECT statement, because the literal value 1E2 does not match the literal data type reusability of the cached statement. Therefore, Db2 does a full cache prepare for this SELECT statement with literal 1E2 and inserts another instance of this '&' SELECT statement into the cache as follows:

    SELECT X, Y, Z FROM TABLE1 WHERE X < & (+ lit 1E2 reuse info)

    The new literal reusability information that was used as part of the cache match criteria is also be cached with this instance of the '&' SELECT statement. This literal reusability information is specific to the literal 1E2, making it a new unique entry in the cache even though it is another instance of the same '&' SELECT statement that is cached.

Now, given the two '&' SELECT statements that are cached, let's attempt to prepare the same SELECT statement again but with a different literal value instance from the first two cases as follows:

SELECT X, Y, Z FROM TABLE1 WHERE X < 9

The Db2 cache behavior, for this scenario with CONCENTRATE STATEMENTS WITH LITERALS in effect, is as follows:

  1. Db2 searches the cache, attempting to find an exact match for the new SELECT statement with literal '9' (along with the usual required conditions for a cache match or sharing). No cache match is found.
  2. Db2 replaces literal '9' in the SELECT statement with '&' and does a second search of the cache using the new SELECT statement text that has the '&' instead of '9'. Db2 finds two qualifying cached '&' SELECT statements that match. The one for original literal 123 and the second for original literal 1E2.
  3. Given the two qualifying '&' SELECT statement cache matches that were found, Db2 continues the cache matching evaluation by using the literal reusability criteria to determine which of the two cache matches is reusable with literal value '9'. In this case, both cached statements are reusable with literal value '9', therefore, simply by order of statement insertion into the cache, cached statement for literal 123 is the first cached statement found that satisfies the literal reusability criteria for the new literal value '9'.
  4. Db2 does a short prepare for the SELECT statement with literal '9', using the executable statement structures that are cached for the cached '&' SELECT statement for literal 123.