Any good SQL optimization tool

[nandakumar.jayaraman@gmail.com]

Hi

Does anybody use a good SQL tool other than Navigator for i?

I am looking for a tool which not only advises indexes but also recommend new sql

Example :

If I use Like clause in my SQL, tool should be smart enough to give an alternative SQL.

Thank You in advance.

Regards
Nanda

[Jim_IT]

Nanda,

There really isn't such a thing. This is what I've done for more than the last 25 years. The best thing is to read every bit of information you can google. I would estimate I've read over 100,000 pages of documentation during that time. Seeing that this is your 1st post here, there is a lot to learn. How much of the DB2 for i SQL Reference Guide are you familiar with? Have you read the IBM i Database SQL programming documentation?

IBM Lab services has a very good training session for SQL Performance but even then you have to have a very good understanding about the operational abilities of SQL to be able to grasp the depths of the optimizer. Mike Cain, leader of the DB2 for i Lab Services has a blog which explains what it takes to be a good DBE. A particularly good post is How to Become an IBM i Database Engineer. He lists what a DBE needs: Awareness, Knowledge, Practice, Practice, Practice.

The SQE query optimizer is extremely complex and does an incredible amount of analysis to make plan decisions. IMHO, those guys are geniuses, but some things they really cannot do. The DB2 for i optimizer makes index advice but that advice cannot be blindly followed.

Think about the LIKE predicate. The LIKE predicate must look through the entire bytes in string for a certain pattern. What improvement to the database (index) could tell you that and at what expense?

Don't believe that there is any product which can make a decisions which a DBE can.

SQL is just another programming language. There have been times which I've worked on a single statement for a month or two, testing and verifying the output. Likewise, I would estimate I've run over a million SQL statements in 25 years comparing results and statement optimization.

Jim