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

dwh_user · Posted: Mon Jul 15, 2019 2:25 am

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 · Posted: Mon Jul 22, 2019 6:59 pm

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_F ...

What's this?

dwh_user · Posted: Tue Jul 23, 2019 2:00 am

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.