|
When developing DB2 applications it is critical that SQL statements within the application are as efficient as possible to ensure good database and application performance. Inefficient SQL can lead to poor application response times and high resource use and CPU costs within the DB2 system in which it runs.
It is much more cost effective to develop efficient SQL from the outset –during development, rather than to work to identify and fix inefficient SQL running in a live application. Sometimes, the original design of the SQL is not entirely under your control. You may be implementing Enterprise Resource Planning (ERP) applications using dynamic SQL and packaged solutions on your systems. It is still important to ensure the SQL within these applications is efficient. Tuning SQL in ERP applications can yield substantial performance gains and cost savings.
Coding efficient SQL is not always easy since there are often a number of ways to obtain the same result set. SQL should be written to filter data effectively while returning the minimum number of rows and columns to the application. Data filtering should be as efficient as possible using correctly coded and ordered predicates. Some SQL functions such as sorting should be avoided if possible. If sorting is necessary, you should ensure your database design supports indexes to facilitate the sort ordering.
Developing efficient SQL initially and subsequent tuning of existing SQL in your application is very hard to do manually. CA provides two products to assist you with SQL development and tuning, namely CA SQL-Ease® for DB2 for z/OS and CA Plan Analyzer® for DB2 for z/OS.
CA SQL-Ease® for DB2 for z/OS
CA SQL-Ease® for DB2 for z/OS (CA SQL-Ease) is designed to reduce the time and expertise required to develop highly efficient SQL statements for DB2 applications. It saves time by allowing developers to generate, test, analyze, and tune SQL online, within their ISPF editing session, without needing to compile, link, bind and execute the program. Developer productivity is improved as much of the manual coding effort is automated. CA SQL-Ease improves SQL performance by analyzing SQL and using an expert rule system offers SQL performance improvement recommendations as part of the development process so that SQL is highly tuned before it is ever moved to production.***
When developing a new application, the programmer can use the GEN function to automatically generate SELECT, INSERT, UPDATE or DELETE statements for a given table. The programmer can choose which columns will be automatically included in the SQL statement thus saving them having to type the column names. Program host variable memory structures and DECLARE TABLE constructs can also be generated automatically. CA SQL-Ease supports C, COBOL, assembler and PL/1 programming languages. The GEN function will also include suggested filters in the WHERE clause based on indexes defined on the tables and example join predicates for joined tables based on indexes and referential relationships. The programmer can also use the NOTES function as an online SQL reference manual. Once the programmer has finished formatting the SQL statement the SYNTAX function can be used to check that everything is syntactically correct. The STAND function can be used to convert the SQL statement into a standard format for easy viewing, understanding and documentation purposes.
The PRED function allows the programmer to check how efficient the predicates coded for the SQL statement are. It shows whether DB2 can use an index to evaluate the predicate and whether it will be evaluated as stage 1 or stage 2.
The EXPLAIN function can be used to determine the access path that will be used by DB2 for the SQL statement. It shows how the data will be accessed, whether by tablespace scan or index access, whether prefetch is used and shows the level of DB2 locking. Estimated costs are shown in ms, service units and TIMERONS.
CA SQL-Ease also provides an Enhanced Explain function offering enhanced access path information and uses an expert system to provide recommendations to show how the efficiency of the SQL could be improved. The expert system provides recommendations in 3 categories: SQL coding guidelines to improve the efficiency of the SQL statement, predicate coding guidelines offering efficiency improvements for predicate filters and physical object guidelines offering improvements to the object which will improve the SQL access to it. CA SQL-Ease allows the programmer to manipulate the DB2 statistics for the object to allow ‘what-if’ scenarios to be played to see what effect different volumes of user data will have on the efficiency of the SQL they are developing.
CA SQL-Ease also allows the programmer to execute the SQL statement to ensure the expected result set is returned.
Enhanced Explain also offers eight different reports to fully document the SQL using summary report, access path, cost estimate, predicate analysis, object dependency, RI dependency, tree diagram and object statistics.
|
