DSXchange

I recently had issues with the Long datatype as well. We ended up using CLOB instead because there are alot of restrictions with Long in oracle.

Ray, Yes, I have tested it with several data sets and it correctly processes them all. The destination column is a CLOB in oracle and the requirement was to put a CR/LF after each text component. I had some issues with the stage variables when I had several but I was able to simplify it to two. Then...

I used 2 stage variables, 1 to hold the concatenated values and 1 to determine when to clear it.

I then outputted to an Aggregator stage and used the Last derivation. It works and it's fast and NO HASH FILES!!

I thought about using the Hash File but I am hesitant since the volume is LARGE. I think the StageVariable approach will work by comparing the PrevPart to the current row and then concatenating. I'm just having difficulty with it.

Problem: Incoming file has 1:n rows that I want to process and produce a single row with a concatentated field (CLOB). Part PartText 123 This is line 1 of part 123 This is line 2 of part 456 This part has only 1 line ... Result: Part 123 This is line 1 of part:This is line 2 of part 456 This part ha...

We cannot get LoadPack to communicate from BW to DataStage. We can ping from BW to the DataStage server. Does anyone have any experience with this?

What are your LoadPack installation specifics? This sounds strangely like permissions.

You can do this through a shell script. I wrote one a few years ago which did this using the loginfo executable. This can also be automated into a Batch Job.

I concur. Let's keep the integrity of the DataStage development community in tact.

The clue is in your LD_LIBRARY path environment variable. Check your installation notes as it's most likely missing a path.

I wrote a VAL function a few years back.

It will convert things like "1.244E10", "$5767.88" and other non numeric fields to number.

Have you tried OCI which is a native connection?

Why are you writing to 3 separate dos files and then concatenating them??
I would have used a non-indexed hash file and then cleared it at the beginning of the process.

This sounds like a Memory Leak issue. You should open a case with support and they can walk you through the debugging process. If you're on unix you might want to do a ps -aux while the job is running and watch the process memory to see if it is causing the job to crash.

What you would need to do is a "screen shot" in Designer of each DataStage Job. The Documentation Tool will update it's repository tables of Job information but not the logic.

It's a good practice that anytime you do any complex SQL with a WHERE clause to create a view instead. This way if you change the condition of the WHERE clause you do not need to recompile your datastage jobs. In many installations I have been at, the DBAs enforce the creating of views for EVERY tab...