How to increase the row fetch size for an IBM DB2 data source in TIBCO Data Virtualization?

How to increase the row fetch size for an IBM DB2 data source in TIBCO Data Virtualization?

book

Article ID: KB0073930

calendar_today

Updated On:

Products

TIBCO Data Virtualization

Description

In TIBCO Data Virtualization (TDV), an IBM DB2 data source query would fetch the default ~32KB in every packet. This article provides details to increase the row retrieval in every packet. 

Issue/Introduction

How to increase the row fetch size for an IBM DB2 data source in TIBCO Data Virtualization?

Environment

All supported Operating Systems

Resolution

To set the fetch size for IBM DB2 data source, from TDV Studio >  open DB2 data source panel > navigate to Advanced tab > JDBC connection properties > Add the following property   

    Property: queryDataSize
    Value: {mention any of this value based on the requirement-> 32767, 65535, 98303, 131071, 163839, 196607, 229375, 2621431}

Screenshot for reference:
User-added image

Details on the property: queryDataSize
Specifies a hint that is used to control the amount of query data, in bytes, that is returned from the data source on each fetch operation. This value can be used to optimize the application by controlling the number of trips to the data source that are required to retrieve data. Use of a larger value for queryDataSize can result in less network traffic, which can result in better performance. For example, if the result set size is 50 KB, and the value of queryDataSize is 32767 (32 KB), two trips to the database server are required to retrieve the result set. However, if queryDataSize is set to 65535 (64 KB), only one trip to the data source is required to retrieve the result set.

Additional Information

Article from IBM knowledge center for reference: https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/java/src/tpc/imjcc_r0052038.html