Memory use for IBM Data Server Driver for IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for z/OS

In general, applications that use IBM® Data Server Driver for JDBC and SQLJ type 2 connectivity require more memory than applications that use IBM Data Server Driver for JDBC and SQLJ type 4 connectivity.

With IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, an application receives data from the DB2® database server in network packets, and receives only the data that is contained in a particular row and column of a table.

Applications that run under IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on DB2 for z/OS® generally require more memory. IBM Data Server Driver for JDBC and SQLJ type 2 connectivity has a direct, native interface to DB2 for z/OS. For IBM Data Server Driver for JDBC and SQLJ type 2 connectivity, the driver must provide memory in which DB2 for z/OS writes data. Because the amount of data that is needed can vary from row to row, and the driver has no information about how much memory is needed for each row, the driver must allocate the maximum amount of memory that any row might need. This value is determined from DESCRIBE information on the SELECT statement that generates the result table. For example, when an application that uses IBM Data Server Driver for JDBC and SQLJ type 2 connectivity selects a column that is defined as VARCHAR(32000), the driver must allocate 32000 bytes for each row of the result table.

The extra memory requirements can be particularly great for retrieval of LOB columns, which can be defined with lengths of up to 2 GB, or for CAST expressions that cast values to LOB types with large length attributes.

In general, even when you use a 64-bit JVM, all native connectivity to DB2 for z/OS is below the bar, with 32-bit addressing limits. Although the maximum size of any row is defined as approximately 2 GB, the practical maximum amount of available memory for use by IBM Data Server Driver for JDBC and SQLJ type 2 connectivity is generally significantly less. However, if the IBM Data Server Driver for JDBC and SQLJ can use limited block fetch to retrieve the data for a query, the data can be passed to the driver using full 64-bit addressing.

Two ways to alleviate excess memory use for LOB retrieval and manipulation are to use progressive streaming or LOB locators. You enable progressive streaming or LOB locator use by setting the progressiveStreaming property or the fullyMaterializeLobData property.