Effects of the ALWCPYDTA parameter on database performance

Some complex queries can perform better by using a sort or hashing method to evaluate the query instead of using or creating an index.

By using the sort or hash, the database manager is able to separate the row selection from the ordering and grouping process. Bitmap processing can also be partially controlled through this parameter. This separation allows the use of the most efficient index for the selection. For example, consider the following SQL statement:

EXEC SQL
    DECLARE C1 CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT
      FROM CORPDATA.EMPLOYEE
      WHERE WORKDEPT = 'A00'
     ORDER BY LASTNAME
END-EXEC.

The above SQL statement can be written in the following way by using the OPNQRYF command:

OPNQRYF FILE(CORPDATA/EMPLOYEE)
        FORMAT(FORMAT1)
        QRYSLT(WORKDEPT *EQ ''AOO'')
        KEYFLD(LASTNAME)

In the preceding example, when ALWCPYDTA(*NO) or ALWCPYDTA(*YES) is specified, the database manager could try to create an index from the first index with a column named LASTNAME, if such an index exists. The rows in the table are scanned, using the index, to select only the rows matching the WHERE condition.

If ALWCPYDTA(*OPTIMIZE) is specified, the database manager uses an index with the first index column of WORKDEPT. It then makes a copy of all the rows that match the WHERE condition. Finally, it could sort the copied rows by the values in LASTNAME. This row selection processing is more efficient, because the index used immediately locates the rows to be selected.

ALWCPYDTA(*OPTIMIZE) optimizes the total time that is required to process the query. However, the time required to receive the first row could be increased because a copy of the data must be made before returning the first row of the result table. This initial change in response time could be important for applications that are presenting interactive displays or that retrieve only the first few rows of the query. The Db2® for i query optimizer can be influenced to avoid sorting by using the OPTIMIZE clause.

Queries that involve a join operation might also benefit from ALWCPYDTA(*OPTIMIZE) because the join order can be optimized regardless of the ORDER BY specification.