Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I am looking to change the I/O size returned from the DB2 server for query results. (As per below instruction from SAP BODS 4.X performance optimization guide)
Increase the size of each I/O from the database server to match the OS read-ahead I/O size.
Where do I actually change the parameters for the database ? Is it a driver configuration parameter or at server level? Also I have no clue where to check the OS read ahead block size(AIX 6.1, DB2 9.7 LUW)
The communication buffer size for remote clients is determined by the RQRIOBLK database configuration parameter (more info in the manual).
However, the statement you quote is overly simplistic and does not make much sense for DB2 configuration. Firstly, DB2 will perform direct disk I/O, so the OS read-ahead setting, if such thing even exists, will not have any effect on DB2. Secondly, DB2 will determine prefetch size, which is what you might call the DB2's answer to read-ahead, dynamically during each query compilation, so the number of pages read with each I/O will be different for different tables and even different queries against the same table. Thirdly, if your query has a WHERE clause, not all that is read from disk will be returned to the client, obviously, so the prefetch size will hardly matter at all.
You'll be better off, instead of trying to follow some very vague and generic advice, determine the actual bottleneck in your system and address that.
