Raja

• Aug 22nd, 2006

 

1. Make sure the SQL query is coded as simply and efficiently as possible.

Make sure that no unused columns are selected and that there is no

unneeded ORDER BY or GROUP BY.

 

2. Indexable predicates: Make sure all the predicates that you think should be indexable are coded so that they can be indexable.

 

3.      Does your query involve column functions?

 If your query involves column functions, make sure that they are coded as simply as possible; this increases the chances that they will be evaluated when the data is retrieved, rather than afterward. In general, a column function performs best when evaluated during data access and next best when evaluated during DB2 sort. Least preferable is to have a column function evaluated after the data has been retrieved.

For column functions to be evaluated during data retrieval, the following

conditions must be met for all column functions in the query:

 

o   There must be no sort needed for GROUP BY. Check this in the EXPLAIN

    output.

 

o   There must be no stage 2 (residual) predicates. Check this in your

    application.

 

There must be no distinct set functions such as COUNT(DISTINCT C1).

 

o   If the query is a join, all set functions must be on the last table

    joined. Check this by looking at the EXPLAIN output.

 

o   All column functions must be on single columns with no arithmetic

    expressions.

 

 

 

sethy.pradeep

 Profile Answers by sethy.pradeep 

• Nov 18th, 2008

 

For performance tuning for your COBOL/DB2 program you need to follow COBOL & DB2 standrads along with your organization standards.