Reading SQL Server XML data type

[ecclesr]

On a DataStage Windows platform, a team memeber recently developed two jobs
1) Extracts xml files and via XML Hierarchical Data stage load into into a SQL server table where one of the target colums is defined as datatype XML in SQL Server (write from Datastage as SQL type LongNVarChar)

2) Read data from the SQl Server table, xml columns is read as LongNVarChar and witten to a SQL server table as LongNVarChar

Job was running without issue on the windows platform

The person has now migrated the two job to a new Linux DataStage installation

when the second job (Read and writes to SQL server via odbc) is run in linux environment we get the following warning

terminate called after throwing an instance of 'APT_BadAlloc'

If I remove the column associated with the data type XML - the job run without issues

Has anyone had this issue in a Linux/SQL Server installation and resolved the problem

Thanking you all in advance

[ecclesr]

Since I raised this issue, I have had no feed back.

With no initial feedback, we raised a PMR. The PMR to date has not resulted in a solution.

Have any users had such a problem working with the XML Hierarchical Data stage


Thanking you all in advance

[chulett]

So... no indication from IBM of a bug or possible bug in that version of the stage? Are they at least still working it with you?

[eostic]

Does any other datatype work? That would be my only guess. Try LongVarChar.

Ernie

[boxtoby]

I have a job which reads an xml column from an SQL Server table successfully on Red Hat Linux.

However, I had to add the following environment variables to the job:

$APT_DEFAULT_TRANSPORT_BLOCK_SIZE
$APT_PHYSICAL_DATASET_BLOCK_SIZE

and set them both to 3000000 (3,000,000)

Hope that helps.

Bob.

[ecclesr]

I have tried
$APT_DEFAULT_TRANSPORT_BLOCK_SIZE
$APT_PHYSICAL_DATASET_BLOCK_SIZE

This did not resolve the issue.

[ecclesr]

Work Around

I have found a work around

I changed the SQL server datatype from XML to varchar(max). The odbc is read ad SQL Type LongNVarChar length 3741823

I will continue continue testing this work around

Thanking you all for you input

[pbttbis]

I managed to get the SQL server type varchar(max) to be read into DataStage LongNVarchar by making use of the following ODBC driver:

Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMsqls00.so
Description=DataDirect SQL Server Native Wire Protocol

I was using:

Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMmsss00.so
Description=DataDirect SQL Server Wire Protocol driver

Where I was having same/similar issues as above. Hope this helps.