When will query data be blocked for better performance?

 

The query data will be blocked if none of these conditions are true.

• The cursor is updatable (see note 1).

• The cursor is potentially updatable (see note 2).

• The BLOCKING NO precompiler or bind option was used on SQLPREP or SQLBIND.

Unless you force single-row protocol with the BLOCKING NO precompile/bind option, blocking will occur in both of the following cases:

• The cursor is read-only (see note 3).

• All of the following are true:

  • There is no FOR UPDATE OF clause in the SELECT, and

  • There are no UPDATE or DELETE WHERE CURRENT OF statements against the cursor in the program, and

  • Either the program does not contain dynamic SQL statements or BLOCKING ALL was used.

Notes:

1. A cursor is updatable if it is not read-only (see note 3), and one of the following items is true:

   • The select statement contained the FOR UPDATE OF clause, or

   • There exists in the program an UPDATE or DELETE WHERE CURRENT OF against the cursor.

2. A cursor is potentially updatable if it is not read-only (see note 3), and if the program includes any dynamic statement, and the BLOCKING UNAMBIG precompile or bind option was used on SQLPREP or SQLBIND.

3. A cursor is read-only if one or more of the following conditions is true:

   • The DECLARE CURSOR statement specified an ORDER BY clause but did not specify a FOR UPDATE OF clause.

   • The DECLARE CURSOR statement specified a FOR FETCH ONLY clause.

   • One or more of the following conditions are true for the cursor or a view or logical file referenced in the outer subselect to which the cursor refers:

     • The outer subselect contains a DISTINCT keyword, GROUP BY clause, HAVING clause, or a column function in the outer subselect.

     • The select contains a join function.

     • The select contains a UNION operator.

     • The select contains a subquery that refers to the same table as the table of the outer-most subselect.

     • The select contains a complex logical file that had to be copied to a temporary file.

     • All of the selected columns are expressions, scalar functions, or constants.

     • All of the columns of a referenced logical file are input only.

 

Parent topic:

User FAQs