Improve performance for nvarchar(max)/LOB type in ODBC stage

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dwh_user
Premium Member
Premium Member
Posts: 14
Joined: Thu Oct 18, 2018 8:08 pm
Location: Sydney

Improve performance for nvarchar(max)/LOB type in ODBC stage

Post by dwh_user »

The nvarchar(max) field of sql server is read as LOB by datastage ODBC stage ([IBM (DataDirect OEM)][ODBC SQL Server Wire Protocol driver]). In order to read the data it suggests to change the array size to 1 for LOB and this change highly impacts the performance.

Is there any solution/workaround for reading nvarchar(max) fields without impacting the performance?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You may be able to increase array size by one or two, but are quickly likely to run into the maximum number of bytes that the ODBC driver can handle.

That figure can be tuned by including MAX_FETCH_BUFF in the uvodbc.config entry for the DSN; but tune it too big and you will hit other memory limits and either start using scratch disk or taking out of memory errors.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dwh_user
Premium Member
Premium Member
Posts: 14
Joined: Thu Oct 18, 2018 8:08 pm
Location: Sydney

Post by dwh_user »

Hi ray.wurlod

Thanks for the details. I updated the array size to 2 and the ODBC stage still fails. It fails for any sizes other than 1.

I will try to configure MAX_FETCH_BUFF in windows and see if it works.
Appreciate your help with this.
Post Reply