DSXchange

Depends on what you want. Increment per row and then use Remove Duplicates to keep only the last row, perhaps. Or just use an Aggregator.

Oracle target is a DATE or TIMESTAMP but you don't want any time portion in it? And I'm kind of surprised they have 'midnight' at the end of a 12AM time; what do they do for 12PM, add 'noon'? :? Regardless, it looks like the only thing that requires any transformation is your date field and Ray has ...

Clarify your source data types... strings? And the target - two fields or one? What are their data type(s)? And is there truly no space between the month and year in your input?

You're trying to do that in the source? See if the chr() function helps, give it the decimal value of the problem character. Use it rather than your hex notation in the trim.

You need to check it in a hex editor or do an 'octal dump' on the UNIX side and determine what the actual hexadecimal code(s) is.

So... you've got three people with varying answers and interpretations of your post. Out of curiousity what was the actual resolution to your question?

FWIW, I've been in your shoes. Sort in the source, assert that it is already sorted in the job and everything tests out fine, volume testing in UAT/QAT works well too. It spends a short time in Production and then blam! falls over dead... something comes in that we'd never seen before in a sort fiel...

Have you checked the actual values around that record to see the nature of the issue? Perhaps Netezza is performing a string sort and DataStage is expecting a numeric one. You really need to see the sequence of sorted values to figure out what's going on, even if it is bajillions of records.

That's toned down from his normal reply, which usually involves some sort of bat - baseball, cricket, whatever.

I'm marking this as Resolved but feel free to come back and continue the conversation if needed.

Exactly. I wager 400 Quatloos on Oracle being the sole 'bottleneck' here.

Done. But keep in mind the fact that you can too - as long as a post hasn't been replied to yet, the original poster can delete it. FYI.

Only if you have a valid license.

You don't need stage variables, just a simple derivation in the new column to add the other three together. And this is not something you can do in the Aggregator, it does sums across rows not within.

Not that I'm aware of. Make them bother.

I don't see either column headers or a schema file being possible here. I base that on the assumption that they mean the file can contain columns in any order record by record in a single file. This rather than a fixed but 'random' order file by file.

Vamsi, can you clarify please?