IBM Utilities vs. Third Party Tools for DB2 - Page 5

SQLExpert -Tool for Development DBA

A development DBA does not have much choice, when code optimization comes into play. Quest Central has a very good module for optimization and development, but Lecco SQLExpert for DB2 UDB is one of the coolest tools for the development DBA.

Find a query, start query optimization and let artificial intelligence, together with rewrite technology lead you to the light. It can generate every possible semantically SQL alternative and unique access plan as well as provide several scenarios which can later be executed as a run test to obtain the execution times for comparison. All run tests are tested without an actual physical execution. SQL tuning procedure is fully automatic, and the DBA can become an SQL optimization expert very quickly. With SQLExpert, the DBA gets a better understanding about the internal behavior of the database optimizer.

SQL Expert is suitable environment for development and for application code testing. The tool helps the development DBA in development tasks or in testing new or modified application SQL code such as COBOL, Java, C/C++, Perl, HTML and more. The DBA can scan a source code (static or dynamic) and DB2 event monitor files for problematic and inefficient code. Problematic SQL code can be identified and access plans can be compared between bound and current.

During the tuning session, the tool will provide a new index list and new index simulation options. SQLExpert will offer for SQL optimization a list of new indexes. When you agree with the suggested new indexes, SQLExpert will start the query simulation using the new indexes without physically creating them. Complete SQL syntax is shown on the screen, and the DBA has just to click and watch.

Cons: From time to time, it is good to check that the AI is giving a true picture. Bad statistics or a combination of indexes can lead to a erroneous conclusion. In such cases, and the actual run time of the tested SQL may be much longer than indicated. For the DBA who would like to assess the software functionality, Lecco will provide trial serial keys. After the trial period expires, you have to unlock it with the real license keys. There are no options to extend the trial period. If this is the tool of choice, the DBA will need additional SQL Scanner and SQL Inspector modules, which are part of the very expensive Professional Edition.

Conclusion

The database is at the core of today's critical business applications and the DBA, as a data architect, programmer and administrator needs To be armed with tools that enable him to easily meet the expectations of the end users.

DBAs still dream about one super tool capable of making:

• Space calculation for allocated and used tablespace storage
• Database housekeeping for automatic report and removal of unused views and indexes from DB2 catalog
• Partition number optimization for installed database on a given hardware
• Timeout/deadlock reporting with detailed info about transactions that have timed out

» See All Articles by Columnist Marin Komadina