DSXchange

Don't do what you're doing. Design a job to write the data to an intermediate file and then turn around and load that. You're going to run into a lot of data consistency issues (rollback segments, etc).

No amount of hash file cache tuning will help. If your CPUs are pegged, then you only have two choices: 1. Reduce the amount of logic being applied (usually not an option) 2. Determine if your CPUs are spinning thru inefficient hash files. This means pre-allocating hash files to make sure your hash ...

You say things are slow, but you haven't defined slow. Are you watching cpu utilization? What is your hardware configuration, how many cpus, memory, and disk? Is Performance Monitor being used in your analysis? Why do you think you have a hash file issue? What analysis have you done regarding the ha...

Not to be argumentative, but discretion has to be used when designing hash file usage for your ETL processes. I would argue that in most cases, an entire large reference table is not required to process the source data. For example, on a given day, only a small percentage of a source customer table ...

DS uses a paging technique, so therefore there is some effort to pmove data in and out. You probably know this, but for the newer users reading this post, you should only preload to memory for really one reason: A row will be accessed multiple times. If each row is only accessed once, then it is a w...

Please search this forum. Kill should be your last resort. You must know the necessary components to cleanup a job should you do what you did. Search the forum for "cleanup resources" and "clear status file". Please fully understand how DataStage jobs work with the Director clients and how they feed...

Columns, during aggregation, either are group-by columns or have an aggregate function. If your data looks like this: x 1 2 3 4 x 1 2 3 4 x 1 2 3 4 And you group by the first column, sum the second, and pass thru the third and fourth, you are in effect using the LAST value for the third and fourth. ...

Your job is way too big. You're doing too much in it, probably have no modularity. Trust me on this.

It's usually best to prepare a hash lookup to contain cleaned up data, that way any job that uses that hash file as a reference does not have to reapply data correction rules (you know, trims, upcases, etc). Best to prepare the hash file once and let every reference benefit. As for the stream values...

The CHANGE function or the CONVERT statement can change one character for another. In your case, simply do:



Now your pipe delimited strings are dynamic arrays!

Use the Director client and look at the Monitor information for that job. If the monitor doesn't show rows being seeing by the reference lookup, then it means that the reference hash key columns are not matching up to the data stream. Make sure your hash file primary key is defined correctly, make s...

You must convert the array from being PIPE delimited to @AM delimited. Then, use the LOCATE statement, which will find whole value only matches in the array cells. INDEX is a literal substring match. Search the forum, we recently had a lively discussion about when to use INDEX and when to use LOCATE.

You may want to change these lines (<<<<<<) * Use StringDecodeX(LookupValue, LookupString, ReturnString, DefaultValue) * e.g: StringDecodeX(InField, "A|B|C|D", "1|2|3|4", "Nothing") Equate RoutineName To 'StringDecodeX' Common /SDKStringDecode/ Initializ...

Could you please go back and edit your routine and highlight the code on your post and press the "Code" format button so it's easier to read and preserves the tabs/spacing?

Go to your Start button and descend to the Ascential client software folder and then go to Online Documentation. Everything you could ever need is there.