DataStage 8.5 (Px), SQL Server 8, Large Text

jygarcia · Post by **jygarcia** » Fri Feb 24, 2012 3:43 pm

Here is some background:
First time using DataStage on SQL Server 2008, have always used DB2, Oracle or Terradata. First time using DataStage on a windows platform, so there's that too.

I have a ASCII text pipe delimited file, one of the fields is defined as a CLOB from the source -- the source is an ORACLE database, but I'm not pulling directly from that database -- my target is a SQL Server 2008 DB.

When the CLOB field is ignored, I can load to the target pretty quickly (performance ain't that great to begin with) using The ODBC Connector Stage. The BCP Load Stage, MSSQL Load Stage haven't been installed, don't know why, but that's something else I'm chasing down. And even if I could use those, they don't seem to work with with CLOB/Large Text fields in DataStage.

When I include the CLOB field in my field definitions, then it really crawls to about 1 record every 2 seconds.

I came across this post and I recognize there are some synergies:
viewtopic.php?t=142469&highlight=sql+server+++clob

Can anyone shed some light on their experiences with dealing with CLOB/Large Text using SQL Server? Because, quite honestly, there is not a lot of info out there that I can find.

jygarcia · Post by **jygarcia** » Fri Feb 24, 2012 4:54 pm

"The BCP Load Stage, MSSQL Load Stage haven't been installed, don't know why, but that's something else I'm chasing down."

Duh! Only available in server edition.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 24, 2012 6:50 pm

You can put it into a server Shared Container and use that in a parallel job. Details in Chapter 2 of Parallel Job Developer's Guide.