Did you ever wonder what is cursor blocking or row blocking in DB2?
Cursor or Row blocking in DB2 is a way to fetch number of rows in a single operation. This can improve performance of the applications.
There are 3 types of blocking which can be used for your cursors.
- ALL – Cursors specified with READ ONLY and FOR UPDATE OF clause. Blocking of rows will occur.
- NO – Blocking of rows will not occur
- UNAMBIGIOUS – Cursors specified with READ ONLY clause. Blocking of rows will occur.
You must have heard the term AMBIGUOUS CURSOR. What is it? An ambiguous cursor is the one where DB2 does not know if you want to do updates or not and hence it assumes that you are going to do the updates. DB2 treats ambiguous cursors as updatable.
Say for an example, this line of code
DECLARE c1 CURSOR FOR SELECT * FROM EMPLOYEE;
will open an ambiguous cursor and db2 assumes that you will update the rows. But if you say
DECLARE c1 CURSOR FOR SELECT * FROM EMPLOYEE FOR READ ONLY;
DB2 will open an unambiguous cursor for you.
The READ ONLY cursors are unambiguous as a rule of thumb. You may not say explicitly to DB2 to open a cursor as READ ONLY but DB2 may open a cursor as READ ONLY (unambiguous cursor) based upon your query if you are using SORT using full table scan etc.
The locks will be obtained for ambiguous cursors based upon isolation level in effect. If your application is not updating the rows, it is much better to DECLARE those cursors as unambiguous by adding FOR FETCH ONLY or FOR READ ONLY clause to improve concurrency.
But we deviated from our discussion of BLOCKING of cursors to explain about the difference between ambiguous and unambiguous cursors. So, you introduce BLOCKING so that DB2 can fetch the rows in bulk by setting BLOCKING option. This can be set when you pre-compile your embedded SQL program by specifying BLOCKING parameter to either ALL or NONE or UNAMBIGUOUS as explained above.
DB2 controls blocking (or buffering) of rows through 2 parameters.
ASLHEAPSIZE – For local applications (Running on server).
RQRIOBLK – For remote applications (Client connections).
Both the parameters are defined at the instance level. They affect all the databases and hence all applications as far as blocking or row buffering is concerned. You can control buffering by rebinding existing packages with a chosen BLOCKING parameter.
C:\>db2 get dbm cfg | grep -i asl
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
C:\>db2 get dbm cfg | grep -i rq
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
You might want to bump up above 2 parameters to increase the number of rows to be buffered. However, DB2 was not buffering BLOB/CLOB even though BLOCKING was in effect but this has changed starting DB2 9.5. DB2 now buffers BLOBS / CLOBS if BLOCKING is set to ALL or UNAMBIGUOUS.
BLOCKING caveats:
- Even though cursor blocking can improve performance for your embedded SQL programs, there are some dangers of cursor blocking if you use save points. For example, you insert some rows and they get buffered at the client and later on, you do rollback to a save point and FETCH can still get rows buffered at the client. So, consider using BLOCKING=NO if using SAVE POINTS.
- SQL PL procedures do not use BLOCKING and it is set to NO even if you set it to ALL using DB2_SQLROUTINE_PREPOPTS or SYSPROC.SET_ROUTINE_OPTS
A natural question comes into mind if BLOCKING is set to NO for SQL PL procedures, will they be slower than stored procedures using C/C++ or COBOL using embedded SQL? The answer is – No. Both will be very similar since SQL PL stored procedure code execution is integrated into engine runtime tighter than regular C/C++ or Java stored procedures.
What are the take away points?
- Blocking will help you since DB2 will buffer the rows at client or at server
- If you do not plan to use update, consider opening cursor with READ ONLY or FETCH ONLY to improve concurrency
- If you are using SAVE POINTS, consider setting BLOCKING=NO
- If you are using SQL PL Stored procedure, BLOCKING is already set to NO.