BLOB Data

nag0143 · Post by **nag0143** » Fri Aug 15, 2008 1:04 pm

Hi, I have to read a BLOB data from DB2 table and write to a DB2 table with out any transformations, just a direct dump. I have to do this datastage if there is a solution.

Please let me know how can i do this, I tried various options provided in the forum through search. But couldn't accomplish it.

Thanks

ArndW · Post by **ArndW** » Fri Aug 15, 2008 1:04 pm

What did you try? I use "VarBinary" or "LongVarBinary" with some success.

nag0143 · Post by **nag0143** » Fri Aug 15, 2008 1:12 pm

ArndW wrote:What did you try? I use "VarBinary" or "LongVarBinary" with some success. ...

I used "LongVarChar", but when I used "LongVarBinary" for some strange reason I am losing connection from designer while trying to view data..

So, From what I see, DS doesn't support BLOB datatypes ? even when not involving any transformations ??

toshea · Post by **toshea** » Fri Aug 15, 2008 1:22 pm

Use a connector such as the ODBC Connector. The connectors support LOBs whether passed inline or by reference. If the LOB is truly too large to pass inline, pass it by reference. The downstream connector will read the LOB from the upstream connector in chunks. When passing by reference, you definitely cannot do any transformations on the data in between, since the downstream connector reads the data directly from the upstream connector.

nag0143 · Post by **nag0143** » Fri Aug 15, 2008 1:29 pm

when you say ODBC connector, do you mean ODBC stage in datastage. Sorry I couldn't get you. Can you please eloborate a little.

toshea · Post by **toshea** » Fri Aug 15, 2008 1:48 pm

The ODBC Connector is a new stage in Information Server 8.

chulett · Post by **chulett** » Fri Aug 15, 2008 2:06 pm

Valid for Server jobs or just Parallel ones?

nag0143 · Post by **nag0143** » Fri Aug 15, 2008 2:32 pm

ODBC connector is only for parallel doesn't exist for Server jobs.

kduke · Post by **kduke** » Fri Aug 15, 2008 4:02 pm

The max length of an ODBC record is controlled also in the ODBC setup. There are several good posts about this.

nag0143 · Post by **nag0143** » Tue Aug 19, 2008 1:23 pm

I am able to unload and load the data into target DB2 table using "LongVarBinary". But How to verify whether I am loading the correct BLOB data. I know this is out of DS .anyone please explain how do i compare BLOB data check in DB2 UDB.

Thanks

chulett · Post by **chulett** » Tue Aug 19, 2008 1:57 pm

I personally don't know but don't you have a DBA you could ask?

nag0143 · Post by **nag0143** » Tue Aug 19, 2008 4:34 pm

I found third party tool to look at BLOB data, but there is a difference from source table to target table when i used "LongVarBinary" . I am wondering is there any easy way out with BLOB data or can any one suggest any other method. I have searched the forum and looks like most of them suggested to use VarBinary or LongVarBinary but this is somehow not working for me (though it is loading fine but not right data).
If I need to do in DS, Is there any other way?

Thanks

chulett · Post by **chulett** » Tue Aug 19, 2008 4:43 pm

And what did you find the difference to be? It should move it over untouched, unless perhaps it is getting truncated.

Still, the bottom line is it is unsupported in DataStage so your best answer may be to use a native tool to transport BLOB data.

nag0143 · Post by **nag0143** » Tue Aug 19, 2008 5:24 pm

Looks like data is getting truncated, is there any way to handle this ?

Thanks

eostic · Post by **eostic** » Tue Aug 19, 2008 9:52 pm

You might consider using JavaPack....several years ago I worked with a site where we implemented JavaPack to read and write ORACLE BLOBs.....in fact, they were .ppt's. DataStage would insert a row with the name and path of the .ppt, and then pass the key value and path to a Java class that would retrieve the document and update the Oracle BLOB field with the actual .ppt. Very interesting. I built the hooks for java into DS and they did the java coding to Oracle, so I can't help you with that piece, but it worked perfectly and ultimatlely only took about 2 or 3 hours to implement.

Ernie