Best Practices for Dynamic plan stability and pureQuery

What is pureQuery :

pureQuery is a database access platform that includes features of interest to both developers and DBAs. It provides a high-performance data access platform that makes it easier to develop, optimize, secure, and manage data access.

Components of pureQuery :

1. Application programming interfaces (APIs), built for ease of use and for simplifying the use of best practices for enhanced database performance when using Java

2. A runtime (pureQuery Client Optimizer), which provides optimized and secure database access                                                                                          

 The pureQuery Client Optimizer a java based product which works with the IBM DB2 JCC Driver and helps in the many challenges which the Database Administrators face in managing Dynamic Java applications. These applications usually have several components and tiers and in most cases generate the SQL statements from an ORM (Object Relational Mapping) Tool.

3. An Eclipse-based integrated database development environment (IDE) for enhancing development productivity, improving data access performance during development, and improving collaboration between developers and DBAs

4. Monitoring services, to provide developers and DBAs with previously unknown insights about performance of Java and CLI database applications

What is DPS :

Dynamic plan stability is new z/OS V12 feature which enhances DB2 to support the ability to stabilize and reuse query execution  or runtime  structures for dynamic SQL, extending the stability currently only available to statically bound SQL to stabilized dynamic SQL.

The runtime structure (for dynamic or static) is what DB2 executes today. Today for dynamic, each time a query enters the cache, a prepare occurs to generate that runtime structure. With DPS, after it is prepared the 1st time, later executions will load that runtime  structure from the catalog into the cache rather than issuing a new full prepare.

DPS merely saves the "runtime  structure" from the current execution, such that it can be used for subsequent executions of that SQL regardless of whether that SQL exits the dynamic statement cache. The access path is preserved and each time the query enters the cache, DB2 will load the query execution  structure instead of going through a new full prepare.

DPS is used to overcome these current issues when using Dynamic SQLs.

• Dynamic SQLs goes through full prepare/describe of SQL statement at every  dynamic  statement cache miss

• Dynamic SQLs are more susceptible to query performance regressions

• Dynamic SQLs are susceptible to changing optimization inputs

• RUNSTATS as data changes

• System parameter changes

• System maintenance

• Release migration

Working of DPS:

• Store dynamic SQL and cache structures in the DB2 catalog

• Load cache from catalog on cache miss, catalog hit

• DPS is 100% dependent on the cache.

Benefits of DPS :

Allow more stable, predictable query performance across

• Exit / re-entry to statement cache

• System recycle

• RUNSTATS

• DB2 maintenance

• Release migration

• Across members of data sharing group

Limitations of DPS:

1. Cached dynamic statements only

2. Concentrated  (CONCENTRATE WITH LITERALS ) statements currently excluded, DPS is 100% dependent on the cache. ( non-cached statements are not supported like Stmt with literals, CACHEDYN=NO, or if the SQL contains DGTT, or   REOPT(ALWAYS) )

3. Queries referencing objects enabled for temporal, transparent archive currently excluded

4. Support for REBIND with PLAN MANAGEMENT features that exist for static eg. BASIC/EXTENDED, APREUSE, APCOMPARE, REBIND SWITCH

5. Ability to pre-bind statements into the catalog like in Static SQL

6. Ability to grant authority to execute statements (and avoid granting authority to objects)

DPS v/s pureQuery:  (Other benefits of pureQuery )

1. There is no overlap of pureQuery with DPS, the only similarity is that they both fix/stabilizes an access path/runtime structure and because of that DPS is getting performance benefit like Static SQL   

The execution performance of the SQL does not change with DPS, the access path is preserved and each time the query enters the cache, DB2 will load the runtime structure instead of going through a new prepare. This is why DPS is similar to static, because each static execution, we load the saved runtime structure into the EDM pool, so we get around 90-95 % similar performance like Static SQLs but there are 5-10 % cache miss could impact the application’s performance.

2. pureQuery has features to change the authorization model from dynamic to static etc where as in DPS , there is no change in security/authorization model for authentication.

• The security model allows to determine the type of access groups of users will get, its of two types static security and dynamic security model.

  •  For DPS, the security model is exactly how dynamic queries are authenticated today because  the queries are authenticated on full prepare to get into the cache. What's externalized to the catalog is everything that used to be hidden in the cache to do authorization matching, and DPS use the same authorization matching to match in the catalog that we used in the cache.

  • For dynamic security model, the users need access to table-based privileges (The risk with granting table privileges is they do not limit users to executing specific SQL statements. Users could potentially execute any query or any update SQL statement on a table, once they have been granted read or update privileges on the table, Dynamic SQL injection is one form of malicious behavior that takes advantage of this fact.)

Where as in pureQuery changes the management from Dynamic to Static, the authorization model from Dynamic to Static, and has the ability to allowlist queries.  DPS addresses none of these areas

pureQuery uses static security model where  there is no need to have access to table-based privileges, package level privileges we can give to users , that is users can be given authorization only for a specific set of SQL statements. Users are only granted permission to execute packages containing SQL that matches their access level. With package level privileges, users cannot execute any SQL statements unless the statements are contained in the packages the users have been granted the privilege to execute.

3. In DPS there is no way to pre-bind statements into the repository.

4. pureQuery has end-to-end monitor and traceability features  (Extended insight ) where as in DPS there is no traceability like in Extended insight

 Extended Insight to monitor end-to-end database response time for Java™ and CLI applications. Extended Insight extends database monitoring across the database       client, the application server, and the network, giving DBAs immediate insight into where workloads, transactions, and SQL requests are spending their time. With   OPM EI, you can quickly detect trends like declining response times for applications or network congestion

5. The following features are  supported pureQuery client optimizer runtime component which is not supported by DPS

pureQuery client optimizer helps in the many challenges which the Database Administrators face in managing Dynamic Java applications. These applications   usually have several components and tiers and in most cases generate the SQL statements from an ORM (Object Relational Mapping) Tool.

Among the challenges which the DBA face are:

•            Identifying parts of the application which do not perform as expected.
•            Associate a poorly performing SQL Statement to the Application Source.
•            Improve security at the application level going beyond the security provided by the database ( as explained above as well)
•            Provide for consistent response times and capacity planning.

There are other benefits which can be obtained by Client Optimizing the application such as SQL Literal Substitution, SQL Replacement capability, running captured-only SQLs to prevent SQL-Injection attacks, provide trace backs to the application source to narrow down and isolate the problem easily.

6. pureQuery  can transform an existing JDBC dynamic application to Static SQL based where as In DPS, there is no way to transform an existing JDBC dynamic application to Static SQL based
 

pureQuery Client Optimizer provides a means to transform existing JDBC based Dynamic Java applications to a Static SQL based one, thereby providing  the benefits of a Static SQL execution model as opposed to a Dynamic SQL execution model. The process of Client Optimizing an existing application and in promoting the application from Dynamic to Static SQL does not require any modification to the application source code.

7. pureQuery  has application programming Interfaces (inline and annotated method) where as in DPS no such features available.

The pureQuery APIs ( inline SQL  and annotated  method style)  are built for ease of use, enhanced productivity,.These The pureQuery APIs allow developers to build applications with far less handwritten code yet still with greater control over database access than provided by many object-relational frameworks.

The pureQuery inline-style is an API for easily and quickly specifying and executing SQL statements in your application. The API is designed to allow you to quickly write code that specifies SQL statements, executes them, and gets the results in a useful format.

pureQuery provides an annotated-method programming style for executing SQL statements. Like the inline style, the annotated-method style makes it easy write applications that access databases. In addition, it allows you to use static SQL.

8. In DPS, there are no tooling support like in pureQuery

The pureQuery tooling from ODS (optim Data Studio) makes Java development with SQL simpler, faster and more productive than ever before. You can rapidly develop or customize the SQL inside Java applications to your needs using a sophisticated SQL editor integrated inside the Java editor. Furthermore, through a set of easy-to-use wizards, the tooling provides the ability to generate pureQuery Java code to access and manipulate data from tables, views and stored procedures.

Conclusion :

The goal of DPS is to achieve access path stability comparable to static SQL statements for repeating cached dynamic SQL statements. When you enable dynamic SQL plan stability, DB2 stores statement cache structures for specified dynamic SQL statements in the DB2 catalog. Whenever a stabilized dynamic SQL statement is not present in the dynamic statement cache when issued, DB2 can load the statement cache structures from the DB2 catalog and avoid the full prepare operation.

There is no overlap of pureQuery with DPS, the only similarity is that they both fix/stabilizes an access path/runtime structure and because of that DPS is getting performance benefit like Static SQL.