By using client optimization, an administrator can modify the SQL from a captured application. The enhanced tooling to support this capability is described in Sonali's article, What's new and cool in Optim Development Studio 2.2. The intended usage of this feature is to let a DBA make a change to an SQL statement without the need to edit and recompile an application. This could be useful, for example, in late night or weekend emergencies when an application can't easily be changed. It is also useful in cases where a third party application embeds or generates sub-optimal SQL and a change to the application is not possible without contacting the vendor. In any of these cases, you should aim to change the application directly at the first practical opportunity to use the improved SQL.
When I talk about this capability to people, there are two questions that frequently come up:
- What is the extent of the change I can make to the SQL?
- Isn't there a security risk to allow editing of the SQL? How can we control access?
What can I change in the captured SQL?
There are restrictions on what you can change when creating the replacement SQL. The Optim Development Studio pdqxml editor will prevent many of the restricted changes, which is why it is strongly recommend that you use this editor to create the replacement SQL. The primary restrictions on the replacement SQL are:
- You may not change the SQL statement type. For example, you can’t change a SELECT to an INSERT.
- The number and types of any input parameters or output result columns must be unchanged. For example, if your SELECT statement is expecting two columns of CHAR and INT as result, your changed SELECT statement must also expect a result of two columns of CHAR and INT.
Nevertheless, there are quite a number of useful changes that you could make that would not violate the restrictions. You can:
- Influence access path / index usage:
- Add an ORDER BY clause
- Add OPTIMIZE FOR 1 ROW
- Other "tricks" to influence the DB2 Optimizer (like adding OR 0 =1 to a predicate)
- For Oracle - add a comment hint to end of the statement
- Influence fetch size for distributed queries:
- Add FETCH FIRST n ROWS ONLY clause
- Add an OPTIMIZE FOR n ROWS clause
- Add FOR FETCH ONLY or FOR UPDATE clause
- Add a predicate that narrows the data returned - just be sure it uses literals and not parameters
- Change the locking behavior:
- Add WITH ISOLATION clause
- Add SKIP LOCKED DATA clause
- Add FOR UPDATE clause
- Directly manage the schema name for object references:
- Add or change the schema qualifier on a table or other object reference.
- Help manage EXPLAIN DATA:
- Add the QUERYNO clause
Some people have expressed concern that there is a security risk involved with the ability to change captured SQL. While there is some potential for abuse, there are means for controlling changes. I’ll discuss some control points within the context of how this feature can be used with either static or dynamic SQL.
Static SQL:An important point here is that all the basic building blocks of security remain in place. That is, SQL privileges are necessary to execute the bound packages or the dynamic statements. But even with that being true, additional care must be taken. To prevent unexpected changes to the file you must control write access to the file. It can be locked down on the executing server by making it read-only. It is also important to control the updateability of execution-time properties that can affect the application’s execution. The capture file, and any application properties files need to be thought of, along with any executables, as a collection of related resources, all of which need protection.
Client optimization is most frequently used to convert dynamic SQL execution to static execution. To use SQL replacement here, you must modify the SQL before performing the bind operation. Any changes made to the SQL in the capture file after the bind are ignored. So control over the capture file contents needs to be managed across the capture/configure/bind process. In many instances, this would be done by the same administrator. Ultimately the bind is performed by an administrator with all the authority to perform all the SQL contained in the file. This is not very different than a traditional 3GL program deployment. And any bound SQL is then visible for inspection in the DB2 catalog.
There are scenarios where client optimization is used (for example, to gather performance metrics) even when the eventual execution mode is dynamic SQL. In these cases, the capture file is examined at execution time for the existence of replacement SQL. If present, that SQL is used in the prepare and execute. But you can disable that execution-time replacement. A pureQuery configuration property, enableDynamicSQLReplacement, controls whether this is allowed. The default is false, so you have to do something to turn on the execution time replacement. The gives control at a datasource or application level..
I hope you’ve found this useful. Let me know if there are other questions you have about pureQuery, and I’ll do my best to answer them.
-- Bill Bireley