Control database manager blocking

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

You can control blocking, if you want. 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 or use the QSY2.OVERRIDE_TABLE application service.

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 index key columns used by the cursor, and 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 need 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, and that subquery is not isolatable.

The SQL runtime 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 inserted into the target table until the block is full. The SQL runtime automatically does blocking for blocked inserts.
    Note: If an INSERT with VALUES is specified, the SQL runtime might not close the internal cursor 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 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.