XtSoft - Presentation produit

XTSOFT Team provides free service to optimize your DB2 statements

Optimize the response time

Performance problems, which are relatively frequent in the environment of relational databases, are mostly due to the actual writing of the SQL orders.
In order to optimize response time, it is important almost systematically to modify the input.

To write top quality requests, the programmer must ask three questions :

WHO ?
Who uses the resources of the machine ?
Which SQL order, of which program gives an unsatisfactory response time ?
The SQL-TUNE response : Statistics concerning the request.
WHY ?
Why this response time, since I obtain the expected result and there are indexes on this table ?
The SQL-TUNE response : Display of the access path used by SQL
HOW ?
How to optimize time ?
The SQL-TUNE response : Possible to modify 'On-Line' the SQL request and to simulate the new access path.

SQL-TUNE provides the right answers to these three questions !

WHO?

Which SQL request gives an unsatisfactory response time ?

SQL-TUNE gives statistics concerning each request (static or dynamic orders). This allows you to identify quickly the SQL order which has an unsatisfactory response time.

SQL-TUNE displays for each query :

The name of the program
The date and time of execution
The number of STMT
The total time STMT for LUW
The number of LUW STMT executions in LUW
The response time of STMT

This query can be displayed (using function key PF5) and then processed 'On-Line'.

SQL-TUNE has given an accurate reply to the question WHO.

Location of queries.

The most expensive queries are quickly localized
and displayed using the PF5 function key :

WHY?

Why is the response time to the query unsatisfactory ?

The SQL-TUNE response is the display of the access path used by SQL.
If you want to know exactly why SQL has chosen this access path, position the cursor on the accessed table and you will see all the characteristics of this table :

The list of columns
The list of indexes
The list of synonyms

SQL-TUNE has given a precise answer to the question WHY.

Display of Access Path :

In this example, SQL-TUNE indicates
that the query has induced a scan on a
table of several thousand rows,
which explains the response time.

HOW?

How to improve this order ?

The SQL-TUNE response is a simulation.
In fact, the programmer can return to the screen displaying the order, modify it and ask SQL-TUNE "What would be the access path if I wrote my order like this".

The initial order can be modified :

Then display of new access path :

Thus, after successive attempts, SQL-TUNE
is able to write a query which
has optimum access.