Programming techniques for database performance: Control database manager blocking

To improve performance, the SQL run-time attempts to retrieve and insert rows from the database manager a block at a time whenever possible.

You can control blocking, if desired. Use the SEQONLY parameter on the CL command Override Database File (OVRDBF) before calling the application program that contains the SQL statements. You can also specify the ALWBLK parameter on the CRTSQLxxx commands.

The database manager does not allow blocking in the following situations:

• The cursor is update or delete capable.
• The length of the row plus the feedback information is greater than 32767. The minimum size for the feedback information is 11 bytes. The feedback size is increased by the number of bytes in the key columns for the index used by the cursor and by the number of key columns, if any, that are null capable.
• COMMIT(*CS) is specified, and ALWBLK(*ALLREAD) is not specified.
• COMMIT(*ALL) is specified, and the following are true:
  • A SELECT INTO statement or a blocked FETCH statement is not used
  • The query does not use column functions or specify group by columns.
  • A temporary result table does not have to be created.
• COMMIT(*CHG) is specified, and ALWBLK(*ALLREAD) is not specified.
• The cursor contains at least one subquery and the outermost subselect provided a correlated reference for a subquery or the outermost subselect processed a subquery with an IN, = ANY, or < > ALL subquery predicate operator, which is treated as a correlated reference.

The SQL run-time automatically blocks rows with the database manager in the following cases:

• INSERT

  If an INSERT statement contains a select-statement, inserted rows are blocked and not actually inserted into the target table until the block is full. The SQL run-time automatically does blocking for blocked inserts.

  Note:

  If an INSERT with a VALUES clause is specified, the SQL run-time might not actually close the internal cursor that is used to perform the inserts until the program ends. If the same INSERT statement is run again, a full open is not necessary and the application runs much faster.

• OPEN

  Blocking is done under the OPEN statement when the rows are retrieved if all of the following conditions are true:

  • The cursor is only used for FETCH statements.
  • No EXECUTE or EXECUTE IMMEDIATE statements are in the program, or ALWBLK(*ALLREAD) was specified, or the cursor is declared with the FOR FETCH ONLY clause.
  • COMMIT(*CHG) and ALWBLK(*ALLREAD) are specified, COMMIT(*CS) and ALWBLK(*ALLREAD) are specified, or COMMIT(*NONE) is specified.