moving text data type from one table to another.

[dstucke]

I'm trying to convert one table to another table, both containing a column of text data type, which Datastage interprets as longvarchar(2147483647) in the metadata. The data can be rather large, and could contain any variety of characters entered in the data. This should be a straight mapping.

Datastage is having problems reading from the source table for certain rows. Actually, it just kills the job with a less than informative message: Abnormal termination of stage exConvertMessage..stgxMessage detected. That's it.

I'm able to view these problematic rows without any problems in MSSQL Query Analyzer. However, attempting to view them from the ODBC stage in Datastage lock the job up.

I'm assuming that there's a character in the data that Datastage is having problems with. I don't readily see it.

Does anyone have any suggestions on dealing with these large data types?

[rwierdsm]

Just came across this thread yesterday on how to remove non visible characters.

Maybe it will spark some ideas.

viewtopic.php?t=98175

Rob W

[chulett]

I doubt there are any problem characters in these fields, it's more about the fact that these kinds of large TEXT fields (or LONGs or CLOBs) aren't really appropriate for processing by an ETL tool. However, that's not to say it can't be done, I've done it for CLOBs in Oracle, for instance.

First thing to check would be to make sure you do not set your array size to anything other than 1. That being said, do you have idea what size ranges you are dealing with in this field - min, max, average?

Also curious, why not just use whatever tools exist in MSSQL 2000 for this? Export/import perhaps?

[ray.wurlod]

The ODBC stage is limited in the maximum row size it can work with. This is configurable via MAXBUFFSIZE in the uvodbc.config file (iirc). Search the Forum for uvodbc.config to be sure. It's out there somewhere. (Yes, I'm in an airport lounge, away from DataStage again.)

[dstucke]

Thank you all for your replies. I know the size of most of these image files are over 1MB. In the meantime, I've gotten things to work using a MSSQL DTS job called from a command stage within DS 7.5.1. However, I'll look into adjusting the MAXBUFFSIZE to 2MB+ and try that. I'll also check for goofy characters in my text.

Thanks, again.