Understanding SQL Replacement with pureQuery's Client Optimization
IBM_Optim 27000269HS Visits (2717)
Optim pureQuery Runtime, and I want to start using this blog to help address questions that I get from people as they learn about or use pureQuery capabilities. In this first blog, I’ll discuss the new SQL replacement capability.
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 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:
Nevertheless, there are quite a number of useful changes that you could make that would not violate the restrictions. You can:
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.
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