Page 1 of 1
Improve performance for nvarchar(max)/LOB type in ODBC stage
Posted: Mon Jul 15, 2019 2:25 am
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?
Posted: Mon Jul 22, 2019 6:59 pm
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.
Posted: Tue Jul 23, 2019 2:00 am
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.