Cursor Blocking in DB2

Just like it is inefficient to be picking single rows off disk, to avoid which the database fetches data in pages/blocks, it can be equally inefficient to fetch rows from the server to the client one at a time. In Oracle, the array size can be configured to alter this behaviour and make the server send blocks of rows at a time. In DB2, the same is called cursor blocking and is influenced by either the RQRIOBLK size (if client is remote) or the ASLHEAPSZ (if client is local) parameters.

With cursor blocking, if a qualifying row is locked on the server, the cursor will block till it can get the row. In other words, if DB2 is trying to send a block of 1000 qualifying rows to the client and row number 900 is locked, DB2 does not instead send 899 rows and wait - it waits for that row to be unlocked before sending the whole block over. If one does not keep this in mind, it might appear that DB2 is doing page-level locking or something.

To disable cursor blocking, one can either explicitly bind with the blocking option set to NO or issue a Select For Update instead of a plain select (assuming the logic warrants it). This can be seen in action as follows:
In session 1, create a table T with 10 rows, commit and then update the 10th row.
In session 2, try a Select * From T and it will block without returning anything.

In session 3, try a Select * from T For Update and it will return 9 rows and then block.