DB2 queryDataSize above 32767 is ignored on HP-UX machine

Question

Hi I have been using DB2 10.5 LUW deployed on HP-UX machine, the driver is db2jcc I think the version is 4.19.66 in combination with Spring Data. I have two configurations of my Datasource in both places I try to increase the queryDataSize jdbc attribute because I need to stream large tables. My data source configuration looks the following way:

Configuration 1:

DB2SimpleDataSource source = new DB2SimpleDataSource();
source.setQueryDataSize(98303);
source.setFetchSize(100000);
source.setDriverType(4);
source.setServerName("somename");
source.setPortNumber(56010);
source.setDatabaseName("DATABASE");
source.setReadOnly(true);

Configuration 2 via Hikari

HikariConfig config = new HikariConfig();
        config.setJdbcUrl( "myDB2JdbcUrl;queryDataSize=98303;" );
        config.setUsername( "user" );
        config.setPassword( "password" );     
        config.addDataSourceProperty( "cachePrepStmts" , "true" );
        config.addDataSourceProperty( "prepStmtCacheSize" , "250" );
        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );
        config.addDataSourceProperty( "maximumPoolSize" , 50 );
        config.addDataSourceProperty( "readOnly" , true );
        config.addDataSourceProperty( "transactionIsolation" , Connection.TRANSACTION_READ_UNCOMMITTED);
        config.addDataSourceProperty( "useServerPrepStmts " , true);

In both configurations when I profile the application via network monitoring tool. The buffer size is the default 32767 when it should have been 65535.

What am I doing wrong ? How can I activate the queryDataSize ?

UPDATE: IT apears setting the value lower tha 32k is accepted , but any valid value above is reset to 32K I have been placing so far only valid values.

Maybe this value is not a valid one, could you try with e.g `4096` or `98303` ? — Arnaud, Apr 01 '19 at 12:41
Edit your question to add the jdbc driver name and *version*, and also the Db2-server operating sytem (i-series, z/os, linux/unix/windows). — mao, Apr 01 '19 at 12:41
I need to point out that the java version is 11. Not sure if this has any meaning to the problem. — Alexander Petrov, Apr 01 '19 at 12:46

score 1 · Answer 1 · edited Apr 01 '19 at 13:31

1

According to the documentation, the valid for queryDataSize values are:

4096-32767, 98303, 131071, 163839, 196607, 229375, 262143

Note:

If you specify a value between the minimum and maximum value that is not a valid value, the IBM Data Server Driver for JDBC and SQLJ sets queryDataSize to the nearest valid value.

edited Apr 01 '19 at 13:31

Mark Rotteveel

100,966
191
140
197

answered Apr 01 '19 at 12:56

mao

11,321
2
13
29

I set it to 98303 which is a valid value. No difference :( – Alexander Petrov Apr 01 '19 at 14:31
Take a jdbc trace (see documentation for instructions) and check in the trace output if there are indications, in particular if something is adjusting the value after the initial setting. (This assumes of course that your server and network is capable of delivering a packet of said size). – mao Apr 01 '19 at 14:36
thanks for cominng back. I will check the trace. I know for sure that it is readigg the value, because if I set it to inproper value it complains. But is it possible that this propertu is negotiated in some way with the DB2. Do you know if it is somehow configurable on the server as well. Why would the property be reset ? Also about the network. Any ideas how can I check if it supports the packet size ? – Alexander Petrov Apr 01 '19 at 15:25
One detail more. The DB2 is deployed on HP-UX machines. – Alexander Petrov Apr 01 '19 at 16:38

DB2 queryDataSize above 32767 is ignored on HP-UX machine

1 Answers1