Resolve a restriction that CACHE DYN STABILITY function can not use with CONCENTRATE WITH LITERALS option.

Customer environment

Db2 V12　<- DRDA -> Db2connect/JDBC(IBM Data Server Driver for JDBC and SQLJ type 2)

Server side.(JDBC Type2 is used)

Windows server:

- WebSphere BASE 9.0.0　（Windows2016）

- Db2client / IBM Data Server Runtime Client V11.1 Mod2 FixPack2

- JDBC 4.0 driver / IBM Data Server Driver for JDBC and SQLJ 4.23.42

- DB2 Connect Unlimited Edition for System z V11.1 Mod 2 Fix Pack 2

WAS------------------------- --------------------- ----------- --------

| Db2client-JDBC driver|-|DB2 CONNECT | --/-- |DB2 s/OS|---|IDAA|

------------------------------ --------------------- ----------- --------

　 Requester Server

Customer is very sensitive about accesspath changes and want to implement V12's new function:Dynamic SQL plan stability.

Their JDBC driver use the CONCENTRATE WITH LITERALS to improve a performance since they upgraded Db2 to v9.

The definition is as follows;

https://www.ibm.com/docs/ja/db2/11.1?topic=pdsdjs-common-data-server-driver-jdbc-sqlj-properties-db2-servers

Common IBM Data Server Driver for JDBC and SQLJ properties for Db2 servers

statementConcentrator

DB2BaseDataSource.STATEMENT_CONCENTRATOR_WITH_LITERALS (2)

The IBM Data Server Driver for JDBC and SQLJ uses the data source's statement concentrator functionality.

On the other hand,during a study of implementation of Dynamic SQL plan stability,we found there are some restriction .

Db2 V12's command manual has the following explanation.

https://www.ibm.com/docs/en/SSEPEK_12.0.0/pdf/db2z_12_comrefbook.pdf

Chapter 76. -START DYNQUERYCAPTURE (Db2)

The Db2 command START DYNQUERYCAPTURE stabilizes access paths for qualified cached dynamic queries. This command can also optionally start monitoring of cached dynamic queries that qualify for a scope but have not met the specified execution threshold for stabilization.

All statements in the dynamic statement cache are qualified for capture, with the following exceptions:

• Queries that were prepared with the REOPT(AUTO) bind option

• Queries that were prepared with the CONCENTRATE STATEMENT WITH LITERALS bind option

• Queries that were transformed because they reference system temporal, application temporal, or archived transparency tables and one or more of the following settings uses a non-default value:

– CURRENT SYSTEM TEMPORAL TIME special register

– CURRENT BUSINESS TEMPORAL TIME special register

– GET_ARCHIVE global variable

Abbreviation: -STA DYNQUERY

Since customer's JDBC driver will not change this CONCENTRATOR_WITH_LITERALS(2) option,we can not use this Db2 V12's new function:DYNQUERYCAPTURE at moment..

We want to resolve this restriction as soon as possible because of the following reason.

1)We have a performance problem of dynamic SQL after migrating Db2 V12 FL503(&z15),because Db2 V12 choose different(bad) accesspath compare with V11's one(Its investigation is still continued in TS005587226).

2)As a workaround of 1), we were advised to use OPTHINT, then it appeared OPTHINT is unable to work well along with CONCENTRATE WITH LITERALS option so that APAR:PH37458 has been opened to fix this issue.(TS005696694).

But even if PH37458 will be available soon,to implement OPTHINT,we have to change an application SQL coding(add QUERYNO),and server side setting also need to change(allow the "SET CURRENT OPTIMIZATION" option).

Customer does not want these user-program related changes.

If we can use a stabilized dynamic SQL statements function,it is a best workaround for both 1) and 2). We really want to stabilize specific access path for a specific SQL under Db2 v12.