Configuring DB2 for z/OS Continuous Block Fetch

You can configure support for DB2 for z/OS Continuous Block Fetch (CBF) using DRDA for high performance.

About this task

This task applies only to IBM DB2 for z/OS using DRDA.

Procedure

Configure the AVZSIN00 member.

Set the DRDA configuration for DB2.

In the DRDA Define, the default for the QRBLKSZ parameter is set to 128K. Modify QRBLKSZ if a larger block if needed. Recommendation is to keep the default. As an example, to add 512K:

"DEFINE DATABASE TYPE(MEMBER)"              ,
           "NAME(DB3A)"                      ,          
           "LOCATION(ZOS3DB3A)"              ,
           "DDFSTATUS(ENABLE)"               ,           
           "DOMAIN(MYHOST)"        , 
           "PORT(3740)"                      ,          
           "CCSID(37)"                       ,          
           "QRBLKSZ(524288)                  ,
           "IDLETIME(160)"

Add the DRDAMAXBLKEXT parameter. Start with value 8:
```
"MODIFY PARM NAME(DRDAMAXBLKEXT)        VALUE(8)"
```
In the SQL query, estimate the number of rows in the RESULT SET and use it in the SQL query as follows:
1. Assuming the SQL query is SELECT * FROM CBFTABLE, and there are 50000000 rows.
2. Append the following to the end: OPTIMIZE FOR 50000000 ROWS FOR FETCH ONLY
  For example:
```
SELECT * FROM CBFTABLE OPTIMIZE FOR 50000000 ROWS FOR FETCH ONLY
```

To verify the functionality, turn on the following TRACE BROWSE parameter:

TRACE DRDA CODEPOINT READ/WRITE/FLOW YES
TRACE DRDA CODEPOINT WRITE BUFFER YES

The trace should look like the following example. The number of corresponding “CodePoint(READ)” equates to the value of DRDAMAXBLKEXT set in the AVZSIN00:

18:56:43 0301869847                LEN=02A7,CPT=241B,ELEN=00
18:56:43 0301869848 DSNHLI INTERNAL     OPEN-CURSOR - DSNT400I 
                                   SQLCODE = 000,  SUCC
18:56:43 0301869849                LEN=0221,CPT=241B,ELEN=00
18:56:43 0301869850 DSNHLI BLOCK FETCH  (41490) - RC 0 REASON 
                                   00000000 SQLCODE 0
18:56:44 0301869851                LEN=01A0,CPT=241B,ELEN=00
18:56:44 0301869852                LEN=007C,CPT=241B,ELEN=00