DB2 Tuning

DB2 tuning :

Click Here to Search This Site

DB2 tables are the major resources in the mainframe platform, at the same time they are costlier too. So DB2 tables should be accessed efficiently. There are some tips given below to access the data from DB2 table efficiently.

If a particular SQL query is CPU intensive, then there are few things that should be considered for tuning.

The number SQL calls should be reduced-

1. The number of SQL calls made to the query should be calculated. If it is very high then the number of calls should be minimized. If the query is being executed for the duplicate data obtained from input file, then we can have a previous IF check to avoid duplicate calls for same input data. Here the data retrieved from DB2 table should be moved to working-storage variable for further use of the data when duplicate input comes. By doing so, the SQL calls could be greatly minimized and CPU consumption will also be reduced.

2. If DB2 table is referred to retrieve same data again and again, or If the number of rows in a DB2 table is low and it is highly browsed through a COBOL program, we can have internal tables to load the data retrieved from DB2 table at the very first time and for further data reference, internal table could be used.This would also reduce the number of SQL calls, there by reducing switches between DB2 and cobol address space.

3. If number of FETCH calls to a SQL query is too high, we can't have a previous check as well as internal table with data loaded in it. In this case MULTIROW FETCHING is a better way to reduce the DB2 calls and DB2 CPU. Here in a single fetch, maximum of 10,000 rows could be retrieved from DB2 table. For this purpose the host variable in which the data is retrieved should be an occurs clause variable (Internal table variable). This will greatly reduce the number of DB2 calls while fetching the data obtained for a particular SQL query.

Tuning the SQL query:

If the number of SQL calls is low and the DB2 CPU consumed by the query is too high, the SQL query should be tuned to reduced the CPU consumption

1. Try to have where clause conditions using keys of the table. This will locate the required row immediately with minimum number of DB2 page process.

2. If multiple DB2 tables are used in a CPU intensive query, the query could be modified as a NESTED query. ie) data from each table would be fetched from a separate query and the result would be used in the where clause of another query.

Example for NESTED query:
lets say there are three tables -

NAME-TBL - contains NAME , ROLLNO , and MARK
CLASS-TBL - contains NAME , CLASS and AGE

Query before tuning -

SELECT NAME,
ROLLNO,
FROM NAME-TBLE NT ,
CLASS-TBL CT
WHERE CT.NAME=NT.NAME
AND CT.AGE>10

Nested query after tuning-

SELECT NAME , ROLLNO
FROM NAME-TBLE,
WHERE NAME IN (SELECT NAME
FROM CLASS-TBL
WHERE AGE>10)

The nested query would be more efficient than a single query processing multiple DB2 tables.

3. Try to have where clause conditions using indexed columns of the DB2 table. This will help a lot to locate the DB2 row appropriately.

4. If the where clause condition is not using KEY column or indexed column, it is better to include the column in index set of the DB2 table.