Special Character to Sequential File

endyl13 · Post by **endyl13** » Mon Jan 20, 2003 6:17 am

Dear Experts,

We're having a big problem here everytime we want to write a sequential file with the data coming from a SQL Server 2000. The thing is,the operational application stores the data as rows in the SQL Server with BLOB datatype. And we have to extract the data first from the BLOB table and write it to the text. We're using a conversion : ICONV(ICONV('blob.data',"MXOC"),"MXOC") to convert the BLOB data to the text file.

The job run smoothly if the data doesn't have any Thai character. But we always failed to write the sequential file if the BLOB data has Thai character inside.

Our Datastage is NLS enabled, and PC874 is our default NLS. We have no problem reading a text file with Thai Character inside, or reading from a table with Thai Character as the values. But we always have problem writing THAI Character files to sequential files with data coming from the SQL Tables.
Btw, we are writing the target files to a Sun Solaris machine.

Can you help us? We really need help with this, since almost 90% of the data source are in BLOB and have Thai Character.

Thanks in advance...
Endy

ray.wurlod · Post by **ray.wurlod** » Mon Jan 20, 2003 4:23 pm

DataStage makes no claim to be able to handle binary data, such as the BLOB data type.
Is it possible to use SQL Server utilites (such as CAST) to convert the BLOB into a form that DataStage can process, such as VARCHAR or NVARCHAR?
Can you try "MU0C" rather than "MX0C" in your conversions?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

endyl13 · Post by **endyl13** » Mon Jan 20, 2003 10:03 pm

Hi Ray,

Actually, the MXOC Conversion works OKAY with BLOB data.
I found out that before the source file can be put into the BLOB, that file has to be converted into UTF8 format.

The funny thing is that, if we converted directly all of the source files into UTF8 before inserting it into the BLOB, it won't work (I've tried with MX0C and MU0C).
But, if we open the source file first with Excel, then save it as Text Delimeted (*.TXT), and then convert that .TXT into UTF8, before inserting it into the BLOB it will work.

Do you have any idea on this?
On the job to read the BLOB and write to sequential file, we use UTF8 as the NLS.

Thanks again...
Endy

Regards

ND

endyl13 · Post by **endyl13** » Tue Jan 21, 2003 4:06 am

Hi Ray,

I just found out that the problem is in the source file. They are spool files without any delimeter, and the length of each row is 1001 character, so when we put them into the BLOB the size will be huge and the LongVarBinary datatype of DataStage cannot write the data to the seq. file because they are too long.

The solution is to manipulate the files before they are inserted into the BLOB.

Thanks again...

Regards

ND