DBA > Job Interview Questions > DB2 INTERVIEW QUESTIONS

How To perform Performance Tunning on COBOL-DB2

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

How To perform Performance Tunning on COBOL-DB2 program ?

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.

(Continued on next question...)

Other Job Interview Questions