Error updating XML column in DB2

[saraswati]

Friends,

I have a Parallel Job which is reading data from few tables, creating XML Hierarchy and updating the XML column of a DB2 table on the basis of a key column.

I have set the data type of the XML column as LongVarChar(99999) in the XML Output Stage and in DB2 UDB API Stage.

With this setting, my Job is running successfully and updating the column correctly.

However I want to keep the length as unbounded because the XML can grow beyond the length specified (99999).

When I leave the length as unbounded I get a Fatal error:
1 XML DOCUMENT LENGTH IS ZERO

Can anyone please advise.

regards

[saraswati]

Friends:

Any idea what I should do to resolve this?

Thanks

[chulett]

Not really. Is "99999" the largest value you are allowed to set?

[saraswati]

Actually there are multiple xml columns in the table. Hence if I set each to more than 99999 I will have to increase the Block size to a very large number than the present 128 kb which I want to avoid.

Hence I tried to keep it unbounded but then the DB2 Stage is throwing this error.

Friends: Have any one of you faced similar situation where you encounter large xmls to be feed into xml columns. If yes, what do you advise?

The other stages do not seem to bother about the unbounded column however the DB2 Stage is not allowing this.

Thanks.

[India2000]

I think you need to increase the block size by setting env variable to accomodate very large object

[saraswati]

Yes, I am aware that can be a change.

However, I was looking if we can handle this using unbounded longvarchars.

[chulett]

Without a corresponding change to the buffer size? Not possible.

[saraswati]

Craig-

If I have to set the block size explicitly to a safe "high" number then I need to increase the length of the xml columns in my job to a high number as well.

The xml columns support 2 GB of XML data and the sum of all those XML columns will be a very big number.

As a last resort I might have to do that, but I would like to know why unbounded columns does not work with XML data in my case and if there is something I can do to resolve this.

[chulett]

I would like to know why unbounded columns does not work with XML data

To me that's a support question, I for one have no clue. Is that type even officially supported? Not asking whether it 'works' or not... is it supported?

[eostic]

I have seen very little call for this feature since its inception, but several years ago the Connectors provided DataStage with the ability to handle extremely long strings in a "pass by reference" fashion. I haven't tried it in years, but take a look at this DeveloperWorks article that talks about xml columns in DB2 and the ability to read and write them in DataStage even if they are extremely long.

http://www.ibm.com/developerworks/data/ ... index.html

The LOB (large object) capability is supported via the LongNVarChar datatype within the link, and then via (when writing) the "orchestrate.[column reference] within the applied SQL.

Let us know how it goes. My guess is that the capability depends on the edition of DB2 as well as the release of DataStage.

Ernie

[chulett]

Ah... passing by reference sounds like the answer to both of the size concerns. If it works out.

[saraswati]

What do I have to do to pass XMLs by reference across stages?

[chulett]

What Ernie said.