XML data getting truncated while loading into CLOB column

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
nyk1989
Participant
Posts: 22
Joined: Mon Dec 14, 2015 9:32 am

XML data getting truncated while loading into CLOB column

Post by nyk1989 »

Hello,

I have a job design like below -

MQ Connector -> Transformer -> DTS
Here in DS, I have given the column datatype as LongVarchar with no length provided.

I am reading a XML file coming from MQ and loading into a Oracle table with column datatype as CLOB.

The job completes successfully and I see the XML message getting added into the table.

But when I retrieve the data from the CLOB column, I see only partial XML message.
Here for retrieving, in the DS job, the column datatype is LongVarchar without any length and the Array size is 1.

Can someone suggest how this data is getting truncated?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Have you verified where this data is being 'truncated'? Meaning, if you check the COLB outside of DataStage, say with Toad or something similar, is it whole in the database. Also a bit curious why it's not being stored as an XMLTYPE...
-craig

"You can never have too many knives" -- Logan Nine Fingers
eostic
Premium Member
Premium Member
Posts: 3838
Joined: Mon Oct 17, 2005 9:34 am

Post by eostic »

Do some searches in the forum here.....you might be running into defailt size limits for what can go out on a link......there are several APT vars to consider.....they are something like APT_DEFAULT_TRANSPORT_BLOCKSIZE and used to be too small for large MQ msgs. make it bigger.....

Ernie
Ernie Ostic

blogit!
<a href="https://dsrealtime.wordpress.com/2015/0 ... ere/">Open IGC is Here!</a>
nyk1989
Participant
Posts: 22
Joined: Mon Dec 14, 2015 9:32 am

Post by nyk1989 »

I checked the data in Toad and found that the xml data is truncated. So, it's not loading completely into database but it doesn't throw any size error or anything else. In my ETL the APT_DEFAULT_TRANSPORT_BLOCK_SIZE is set to 9999999. Still this issue happens.
nyk1989
Participant
Posts: 22
Joined: Mon Dec 14, 2015 9:32 am

Post by nyk1989 »

I tried loading the CLOB column with array size as 1 and I see that the entire xml is getting loaded into the field.

This works properly if I use an Oracle Connector to load the data whereas if I use DTS stage - the job aborts.

Error I get is - player terminated unexpectedly.
Post Reply