Larry, Craig,
interesting point - thanks I'm exploring your suggestions now. The Row Buffering isn't available in 5.x therefore my spontainious thought was - is it on by default in 5.x? .. well, perhaps not.
Hoping it will inspire you to give me even more hints I'll answer your questions:
Truncate: I only have one (input) stream to the Tables I'm truncating
Hashfile: I always do a "clear file" and "delete file before create" - The logic in my jobs requires the hashfiles to be re-loaded every time.
Latest simple error does not apprear in hash file context
Insert update order: I have two inputs on the target table, one for update, one for insert. The job where tonights error (54 rows) apperd i do just this: one insert and one update. - this is actually the most troublesome of all the jobs - it's almost exclusivly here the errors (i've found) appear. The update ALWAYS performed (incoming rows) - the insert is performed if a incoming row is a modified version of an existing one.
Restore and re-run: this is done manually - I delete rows from tables but I'm assuming that DS deletes/empties the HASH file each time I re-run the job?? (the "clear file" option). I know that I always get rows into the hashfile - I've heard that if a query returns zero rows then the existing hashfile isn't flushed and old values are reused dispite the "clear file" option (in 5.x at least).
option 4: larry - I'm really looking into something along those lines, thanks for putting it in words
Details if it helps: I'm working on a User Life Cycle table and in the most troublesom job I pretty much do as follows:
- load incoming users (with relevant changed info)
- allocate dimension keys (Transform stage one)
- for each incoming user, find if he is registered alread in the User Life Cycle table (Transform stage two)
- Insert & Update table ST2_USER_LIFE_CYCLE (ORAOCI8 stage):
* Update incoming user info with dimension keys (always)
* Insert the current record from the DWH_USER_LIFE_CYCLE into ST2_USER_LIFE_CYCLE (when the user already exists)
The idea is to later compare the two rows and register all changes to the user in separate rows with a specific transaction_type_id to track all changes to the users - one job for each transaction_type (e.g. Gross-add, churn, msisdn change, tariff change, address change etc.)
In this very job there are two types of "errors" I get:
1) I do a lookup on the existing user using the source-system unique user key: I get no row in return (although I should) -> the user is registered as NEW with a new dwh internal id -> Gross-adds will increase by one more than they should.
2) I do a lookup on the existing user using the source-system unique user key: I get the wrong row in return (for a different existing user) => a MSISDN change, churn or Address change is registered on the wrong user.
Sounds familiar?
Additional thoughts appreciated - I'll continue with Larrys suggestion or something very similar - feels like an DS7.1 upgrade is approaching much faster than anticipated
Thanks guys
Tobbi