Reduce the number of open operations

 

The SQL data manipulation language statements must do database open operations in order to create an open data path (ODP) to the data. An open data path is the path through which all input/output operations for the table are performed. In a sense, it connects the SQL application to a table. The number of open operations in a program can significantly affect performance.

A database open operation occurs on:

• An OPEN statement

• SELECT INTO statement

• An INSERT statement with a VALUES clause

• An UPDATE statement with a WHERE condition

• An UPDATE statement with a WHERE CURRENT OF cursor and SET clauses that refer to operators or functions

• SET statement that contains an expression

• VALUES INTO statement that contains an expression

• A DELETE statement with a WHERE condition

An INSERT statement with a select-statement requires two open operations. Certain forms of subqueries may also require one open per subselect.

To minimize the number of opens, DB2 Universal Database™ for iSeries™ leaves the open data path (ODP) open and reuses the ODP if the statement is run again, unless:

• The ODP used a host variable to build a subset temporary index. The i5/OS^® database support may choose to build a temporary index with entries for only the rows that match the row selection specified in the SQL statement. If a host variable was used in the row selection, the temporary index will not have the entries required for a different value contained in the host variable.

• Ordering was specified on a host variable value.

• An Override Database File (OVRDBF) or Delete Override (DLTOVR) CL command has been issued since the ODP was opened, which affects the SQL statement execution. The ODPs opened by DB2 Universal Database for iSeries

  Only overrides that affect the name of the table being referred to will cause the ODP to be closed within a given program invocation.

• The join is a complex join that requires temporaries to contain the intermediate steps of the join.

• Some cases involve a complex sort, where a temporary file is required, may not be reusable.

• A change to the library list since the last open has occurred, which changes the table selected by an unqualified referral in system naming mode.

• The join was implemented by the CQE optimizer using hash join.

For embedded static SQL, DB2 Universal Database for iSeries only reuses ODPs opened by the same statement. An identical statement coded later in the program does not reuse an ODP from any other statement. If the identical statement must be run in the program many times, code it once in a subroutine and call the subroutine to run the statement.

The ODPs opened by DB2 Universal Database for iSeries are closed when any of the following occurs:

• A CLOSE, INSERT, UPDATE, DELETE, or SELECT INTO statement completes and the ODP required a temporary result that was not reusable or a subset temporary index.

• The Reclaim Resources (RCLRSC) command is issued. A Reclaim Resources (RCLRSC) is issued when the first COBOL program on the call stack ends or when a COBOL program issues the STOP RUN COBOL statement. Reclaim Resources (RCLRSC) will not close ODPs created for programs precompiled using CLOSQLCSR(*ENDJOB). For interaction of Reclaim Resources (RCLRSC) with non-default activation groups, see the following books:

  • WebSphere^® Development Studio: ILE C/C++ Programmer's Guide

  • WebSphere Development Studio: ILE COBOL Programmer's Guide

  • WebSphere Development Studio: ILE RPG Programmer's Guide

• When the last program that contains SQL statements on the call stack exits, except for ODPs created for programs precompiled using CLOSQLCSR(*ENDJOB) or modules precompiled using CLOSQLCSR(*ENDACTGRP).

• When a CONNECT (Type 1) statement changes the application server for an activation group, all ODPs created for the activation group are closed.

• When a DISCONNECT statement ends a connection to the application server, all ODPs for that application server are closed.

• When a released connection is ended by a successful COMMIT, all ODPs for that application server are closed.

• When the threshold for open cursors specified by the query options file (QAQQINI) parameter OPEN_CURSOR_THRESHOLD is reached.

• The SQL LOCK TABLE or CL ALCOBJ OBJ((filename *FILE *EXCL))  CONFLICT(*RQSRLS) command will close any pseudo-closed cursors associated with the specified table.

• Open data paths left open by DB2 Universal Database when the application has requested a close can be forced to close for a specific file by using the ALCOBJ CL command. This will not force the ODP to be closed if the application has not requested the cursor be closed. The syntax for the command is: ALCOBJ OBJ((library/file *FILE *EXCL)) CONFLICT(*RQSRLS).

You can control whether the system keeps the ODPs open in the following ways:

• Design the application so a program that issues an SQL statement is always on the call stack

• Use the CLOSQLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) parameter

• By specifying the OPEN_CURSOR_THRESHOLD and OPEN_CURSOR_CLOSE_COUNT parameters of the query options file (QAQQINI)

The system does an open operation for the first execution of each UPDATE WHERE CURRENT OF when any expression in the SET clause contains an operator or function. The open can be avoided by coding the function or operation in the host language code. For example, the following UPDATE causes the system to do an open operation:

EXEC SQL  FETCH EMPT INTO :SALARY END-EXEC.
 
EXEC SQL  UPDATE CORPDATA.EMPLOYEE    SET SALARY = :SALARY + 1000
   WHERE CURRENT OF EMPT END-EXEC.
 

Instead, use the following coding technique to avoid opens:

EXEC SQL  FETCH EMPT  INTO  :SALARY END EXEC.
 
ADD 1000 TO SALARY.
 
EXEC SQL  UPDATE CORPDATA.EMPLOYEE    SET SALARY = :SALARY    WHERE CURRENT OF EMPT END-EXEC.

You can determine whether SQL statements result in full opens in several ways. The preferred methods are to use the Database Monitor or by looking at the messages issued while debug is active. You can also use the CL commands Trace Job (TRCJOB) or Display Journal (DSPJRN).

 

Parent topic:

Application design tips for database performance

 

Related information

Reclaim Resources (RCLRSC) command
Trace Job (TRCJOB) command
Display Journal (DSPJRN) command
ILE RPG
ILE COBOL
C and C++