Use live data

 

The term live data refers to the type of access that the database manager uses when it retrieves data without making a copy of the data. Using this type of access, the data, which is returned to the program, always reflects the current values of the data in the database. The programmer can control whether the database manager uses a copy of the data or retrieves the data directly. This is done by specifying the allow copy data (ALWCPYDTA) parameter on the precompiler commands or on the Start SQL (STRSQL) command.

Specifying ALWCPYDTA(*NO) instructs the database manager to always use live data. In most cases, forcing live data access is a detriment to performance as it severely limits the possible plan choices that the optimizer may use to implement the query. Consequently, in most cases it should be avoided. However, in specialized cases involving a simple query, live data access can be used as a performance advantage because the cursor does not need be closed and opened again to refresh the data being retrieved. An example application demonstrating this advantage is one that produces a list on a display. If the display screen can only show 20 elements of the list at a time, then, after the initial 20 elements are displayed, the application programmer can request that the next 20 rows be displayed. A typical SQL application designed for an operating system other than the i5/OS^® operating system, might be structured as follows:

EXEC SQL     DECLARE C1 CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT       FROM CORPDATA.EMPLOYEE      ORDER BY EMPNO END-EXEC.
 
EXEC SQL     OPEN C1
END-EXEC.
 
*    PERFORM FETCH-C1-PARA  20 TIMES.
 
     MOVE EMPNO to LAST-EMPNO.
 
EXEC SQL     CLOSE C1
END-EXEC.
 
*    Show the display and wait for the user to indicate that *    the next 20 rows should be displayed.
 
EXEC SQL     DECLARE C2 CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT       FROM CORPDATA.EMPLOYEE     WHERE EMPNO > :LAST-EMPNO     ORDER BY EMPNO END-EXEC.
 
EXEC SQL     OPEN C2
END-EXEC.
 
*    PERFORM FETCH-C21-PARA  20 TIMES.
 
*    Show the display with these 20 rows of data.
 
EXEC SQL     CLOSE C2
END-EXEC.

In the above example, notice that an additional cursor had to be opened to continue the list and to get current data. This can result in creating an additional ODP that increases the processing time on the iSeries™ server. In place of the above example, the programmer can design the application specifying ALWCPYDTA(*NO) with the following SQL statements:

EXEC SQL     DECLARE C1 CURSOR FOR
    SELECT EMPNO, LASTNAME, WORKDEPT       FROM CORPDATA.EMPLOYEE      ORDER BY EMPNO END-EXEC.
 
EXEC SQL     OPEN C1
END-EXEC.
 
*    Display the screen with these 20 rows of data.
 
*    PERFORM FETCH-C1-PARA  20 TIMES.
 
*    Show the display and wait for the user to indicate that *    the next 20 rows should be displayed.
 
*    PERFORM FETCH-C1-PARA  20 TIMES.
 
EXEC SQL     CLOSE C1
END-EXEC.

In the above example, the query might perform better if the FOR 20 ROWS clause was used on the multiple-row FETCH statement. Then, the 20 rows are retrieved in one operation.

 

Parent topic:

Application design tips for database performance

 

Related information

Start SQL (STRSQL) command