Database application design tips: 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
• 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 UDB for AS/400 leaves the open data path (ODP) open and reuses the ODP if the statement is run again, unless:

• The GROUP BY contains columns from more than one table, and index grouping was used.
• The ODP used a host variable to build a subset temporary index. The OS/400 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.
• A host variable is used to specify the pattern of a LIKE predicate. The host variable value has either underscores (_) or involves more than one search pattern: for '%ABC%DEF', two patterns are involved, ABC and DEF.
• An Override Database File (OVRDBF) or Delete Override (DLTOVR) CL command has been issued since the ODP was opened, which would affect the SQL statement execution.

  Note:

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

• A change to the library list since the last open has occurred, which would change the table selected by an unqualified referral in system naming mode.
• The table being queried is a join logical file and its join type (JDFTVAL) does not match the join type specified in the query.
• The format specified for a logical file references more than one table.
• The table is a complex SQL view, common table expression, or nested table that requires a temporary table to contain the results of the SQL view.
• The join was implemented using hash join.
• The join is a complex join that requires temporaries to contain the intermediate steps of the join.

DB2 UDB for AS/400 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 UDB for AS/400 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 or a subset temporary index.
• The Reclaim Resources (RCLRSC) command is issued. A RCLRSC is issued when the first COBOL program on the call stack ends or when a COBOL program issues the STOP RUN COBOL statement. RCLRSC will not close ODPs created for programs precompiled using CLOSQLCSR(*ENDJOB). For interaction of RCLRSC with non-default activation groups, see the following books:
  • ILE C for AS/400 Programmer's Guide
  • ILE COBOL for AS/400 Programmer's Guide
  • ILE RPG for AS/400 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.

You can control whether DB2 UDB for AS/400 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

DB2 UDB for AS/400 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 DB2 UDB for AS/400 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 use the CL commands Trace Job (TRCJOB) or Display Journal (DSPJRN) to determine the number of opens that are being performed by an SQL statement.