I have two queries Q1 and Q2
Both when run on the same DB , same data give same result.

I want to check the performance of Q1 and Q2 and confirm which one of them give better performance supported by concrete measurements.

I tried db2batch command but i gives different timing which is not consistent each time query is run.


Can somebody tell me a reliable, accurate tool which will run a query and measure its performance?...so that I can compare and find if Q1 or Q2 is more efficient?

First, I would use the EXPLAIN tool to look at the access paths of both queries. This will allow you to see that it is accessing what you think should be the correct indexes, etc.

Then to confirm, set up a STATEMENT Event Monitor. Start it, then run your two queries. You will need to do this a few times and change the order of running the queries as the bufferpools may mask some inefficiencies. After each run, you need to analyze the output of the event monitor.

Andy

I am new to DB2
Can you tell me what steps are required for DB2 EXPAIN

DB2 9.1 on Windows

Also, something about STATEMENT event monitor

What DB2 version and OS?

Andy

DB2 9.1 on Windows

Please use the manuals:

Explain: DB2 Database for Linux, UNIX, and Windows

Event Monitor: DB2 Database for Linux, UNIX, and Windows

For more information, use the search feature...


Andy

There is a performance monitoring tool from Embarcadero called DBOptimizer.

Form their site I found: Embarcadero DB Optimizer maximizes database and application performance by enabling DBAs and developers to quickly discover, diagnose, and optimize poor-performing SQL. DB Optimizer eliminates performance bottlenecks by identifying data intensive or frequently executed queries, focusing on specific SQL statements through query statistics (CPU, I/O, wait times), and fine-tuning problematic statements.

This is where you can find more: Database Software for SQL Optimization | DB Optimizer

Catalin