Reducing 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 could also require one open per subselect.

To minimize the number of opens, Db2® for i 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 optimizer could 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 does not have the entries required for a different host variable value.
  • 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.
    Note: Only overrides that affect the name of the table being referred to causes the ODP to be closed within a given program invocation.
  • The join is a complex join that requires temporary objects to contain the intermediate steps of the join.
  • Some cases involve a complex sort, where a temporary file is required, might 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 for i 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 for i 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) does not close the 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
  • the last program containing SQL statements on the call stack exits. Exception is for ODPs created for programs precompiled using CLOSQLCSR(*ENDJOB) or modules precompiled using CLOSQLCSR(*ENDACTGRP).
  • a CONNECT (Type 1) statement changes the application server for an activation group, all ODPs created for the activation group are closed.
  • a DISCONNECT statement ends a connection to the application server, all ODPs for that application server are closed.
  • a released connection is ended by a successful COMMIT, all ODPs for that application server are closed.
  • 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 closes any pseudo-closed cursors associated with the specified table.
  • an application has requested a close, but the data path was left open. The ODP can be forced closed for a specific file by using the ALCOBJ CL command. This close does not force the ODP to close if the application has not requested that the cursor be closed. The syntax for the command is: ALCOBJ OBJ((library/file *FILE *EXCL)) CONFLICT(*RQSRLS).
  • an MQT plan expired based on the timestamp.
  • an incompatible commitment control change occurred.
  • the table size changed beyond tolerance. The optimizer needs to reoptimize based on the new table size.
  • a new index or indexes were created. The optimizer can cost a plan created with the new indexes and compare its cost to the previous plan.
  • new statistics were created. The optimizer can take advantage of these new statistics to create a more efficient plan.
  • host variables are incompatible with a non-reusable MTI, an MQT, or a sparse index used to implement the query.
  • data is warm (in memory).
  • the OPTIMIZATION_GOAL *All IO or *First IO specified in query options file QAQQINI was changed.
  • a hard close was forced.

The optimizer does not recognize that query selectivity has changed due to host variable changes. It continues to use the existing open and access plan. Change of selectivity due to host variables is only evaluated at full open time unless the PSEUDO_OPEN_CHECK_HOST_VARS qaqqini option is altered.

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)

You can control whether the optimizer factors in host variable selectivity once in pseudo mode for queries with host variable that have considerable selectivity variability.

  • By specifying the PSEUDO_OPEN_CHECK_HOST_VARS parameter of the query options file (QAQQINI)

An open operation occurs for the first execution of each UPDATE WHERE CURRENT OF, when any SET clause expression 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).