Hi,
Q1)I am working with Flat Files which can have duplicate rows.
So,what I am doing is I am writing the record to the hash file and to the Oracle at the same time.
In Oracle I am doing Commit after all the records in the file are processed, what happened is my job failed after processing 5602 records in a file because its broken some how.
so , It didn't write any records to the database but , it did to the Hash File.
Next time when I did the full load it didn't write some records because they are already in Hash File.
Is there any way to prevent this(except committing after each record)?
I mean I wanted to write the records that are written to the database only.
Q2)
I want to delete a file once after it has been processed by datastage(once the job is finished),is it possible in DataStage?
nsm.
Writing to Hash file
Moderators: chulett, rschirm, roy
Hash file writes are immediate, unless you've got Write Caching turned on when it may take a moment or two to actually make it to disk. In any case, there is no concept analagous to "commit levels" with hash files.
I don't really understand how you are using this hash. When you said:
I don't really understand how you are using this hash. When you said:
Does this mean you are trying to use the information in the hash for restarting the job stream from where it left off? This doesn't really seem to mix well with an all-or-nothing commit strategy. If it fails, start over from the beginning. If you want to do intermediate commits, use something like a MOD function in a constraint so that you only write a record out when a commit happens, not for every row.Next time when I did the full load it didn't write some records because they are already in Hash File.
No, you don't. Trust me. Rename it, move it to another directory, archive it in some fashion but do not remove it. Use an after job subroutine of ExecSH to call the command directly or write a shell script and run it from there.I want to delete a file once after it has been processed by datastage(once the job is finished)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Write Cache will not overwrite a hash record so the first record in is the final record only on duplicate keys. Why not use the hash file to update Oracle? That way you do not update duplicate.
You can have the hash file deleted and recreated at the start of the job. There is a check box to do that. It is easier to do that than clear it.
You can have the hash file deleted and recreated at the start of the job. There is a check box to do that. It is easier to do that than clear it.
Mamu Kim
Going back in time a little, but this came up in conversation this morning...kduke wrote:Write Cache will not overwrite a hash record so the first record in is the final record only on duplicate keys.
This statement concerned me, so I wrote a little test case to see if it was really the case that overwrites don't happen with write caching turned on. It does still happen, meaning regardless of the cache setting I always get 'last in' survivorship.
Is this old behaviour that perhaps was 'fixed' or changed in more recent releases? I did my testing under 7.0.1, for what it's worth.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Back in DS 4 days there was a bug that surfaced on large hash files using write delayed caching if there were duplicates of the primary key. Under what appeared to be random occurrences the last-in row was not the row found in the hash file. I called in this bug and it took a lot of validation before engineering reproduced it. I believe it was fixed under 4 and has gone away on subsequent releases.chulett wrote:Going back in time a little, but this came up in conversation this morning...kduke wrote:Write Cache will not overwrite a hash record so the first record in is the final record only on duplicate keys.
This statement concerned me, so I wrote a little test case to see if it was really the case that overwrites don't happen with write caching turned on. It does still happen, meaning regardless of the cache setting I always get 'last in' survivorship.
Is this old behaviour that perhaps was 'fixed' or changed in more recent releases? I did my testing under 7.0.1, for what it's worth.
Other than that, the expected functionality is for the last-in row for a given primary key, no matter the caching, should be the final row in the hash file.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle