CODEWITHSUNDEEP
javajdbcdb2
Ely
Ely

Reputation: 11162

fetchSize property on a DB2 database (JDBC)

I am looking into improving performance of a select query on a DB2 database with millions of rows. I do not have access yet unfortunately and cannot test it.
So I try to read through the documentation and there is a statement that confuses me. The statement is in bold and italic saying that the fetchSize property does not affect Statement objects that exist when fetchSize is set.

As far as I know I have to create a Statement object in order to set the fetchSize property and that is how I see and do it in the program code.
Also, that statement somehow contradicts its previous sentence saying that one can use Statement.setFetchSize method to set the fetch size.

Can anyone clarify or could share his/her experience with JDBC/fetchSize/DB2 ?

In the documentation of IBM (see link):

fetchSize

Specifies the default fetch size for ResultSet objects that are generated from Statement objects. The data type of this property is int.

The fetchSize default can be overridden by the Statement.setFetchSize method. The fetchSize property does not affect Statement objects that exist when fetchSize is set.

Possible values of fetchSize are:

0 or positive-integer

The default fetchSize value for newly created Statement objects. If the fetchSize property value is invalid, the IBM Data Server Driver for JDBC and SQLJ sets the default fetchSize value to 0.

DB2BaseDataSource.FETCHSIZE_NOT_SET (-1)

Indicates that the default fetchSize value for Statement objects is 0. This value is the property default. The fetchSize property differs from the queryDataSize property. fetchSize affects the number of rows that are returned, and queryDataSize affects the number of bytes that are returned.

Upvotes: 0

Views: 1404

Answers (1)

mustaccio
mustaccio

Reputation: 18945

What it means is that the fetchSize value set at the connection level is used by default for all future Statement objects, unless it's overridden by Statement.fetchSize(). Changing fetchSize at the connection level will have no effect on Statements created previously.

Upvotes: 1

Related Questions