Client/Server Performance, Part 1: Blocking

It is no secret that read performance can generally be improved by the age-old “blocking” mechanism whereby many rows are fetched at once in preparation for a program to process them. So how is blocking done in the client/server world? Assuming that the good old IBM i will be the server, the following code samples (C#, Java, VBA, and DRDA) show an example of how to increase the amount of data that can be transferred from the server to the client.

C# Using ADO.NET and the IBM DB2 UDB for iSeries .NET Provider

For applications that need to buffer large amounts of data, the iDB2Connection object has properties BlockSize and LobBlockSize (both specified in KB), which can be used to control the buffer size when data is requested. The larger the allocated buffer, the more rows that can be buffered and transferred at once to the client. This is advantageous for performance because it minimizes the number of trips the client needs to make to the server.

The BlockSize property is used for “normal” data transfers from view and table data while LobBlockSize is used to control the separate buffer storage area that is used for large object data transfers (BLOBs, CLOBs and DBCLOBs.)

The sample connection string below shows the BlockSize property being set to 512KB.

iDB2Connection conn = new iDB2Connection(
"DataSource=as400;UserID=MyUser;Password=MyPwd;Naming=System;
DefaultCollection=ADVWORKS;BlockSize=512");

For the V6R1 System i Access provider, the BlockSize property defaults to zero (system determined buffer), and the LobBlockSize defaults to 512.

There is also a DataCompression property that can be used to increase transfer speeds. This property is set to True by default.

The IBM-supplied ADO.NET provider does not offer an option to block a specific number of rows during a fetch.

Java Using JDBC and the IBM Toolbox for Java (JTOpen) JDBC Driver

The IBM Toolbox for Java JDBC Driver has many properties related to performance. A few of them relevant to this discussion are listed in Table 1 below (from IBM):