DSXchange

Hi,

I have a requirement where I need to split a free text column (which mostly exceeds 255 chars) coming in the input row and insert them as multiple rows in the output with each having splitted chunk from the free text column.

So, I have used Loop Condition in Transformer to split the columns with the following Loop Varaibles:
stgTrim -> Col1[stgStart+1,132]
stgStart -> stgStart+132 (stgStart initialized to 0)
stgCnt -> @Iteration

Loop While -> stgTrim<>''

The logic works fine with sample records.
But when I run this job with the actual file, DS hangs and the server crashes.

After analysis, it was found that there was a big peak in the memory usage every time I had run that job which killed the server.

So I wish to know an alternative to implement this or How can I restrict the memory usage of this job.

Thanks and Regards,
Abhishek

Sounds like a bug, something you should report to your official support provider and see if it is a known issue with a patch or other resolution available.

What is the longest possible length of the incoming free text column?

Have you tried lowering the "loop iteration warning threshold" in the Loop Variable tab of the Stage Properties window from the default of 10000?

As per the docs covering loop variables, it is possible to enter an infinite loop. I would guess if that happens, it could eat up your memory before hitting the default threshold of 10000. It's not really a scenario I want to recreate to test it. :D

Another possible approach you could test, still using a loop condition, is to first set a stage variable to the length of the incoming free text column, such as svLen. Then use a new loop variable, such as lvStartPos as the while condition, where the new loop variable would change in each iteration.

So on the first pass, loop while: lvStartPos + 1 <= svLen (lvStartPos initializes to 0). In the loop, increment lvStartPos by 132, or your chunk size.

Thanks Craig,

Thanks Eric,
I like your suggestion. But just I am afraid of attempting it as we have a delivery this weekend. I have incorporated the same logic in Stored Procedure in DB2. Just waiting till Friday to test the same.
Will post the results of my tests.

REgards,
Abhishek