AS400 – DB2 concepts

895 pts.
Tags:
AS/400
IBM DB2
In AS400, suppose I have 4 files to update and system encounters failure while updating 4th file.....I such scenario will the transactions on first 3 files will rollback....

how can i write a sample program to test this...

i want to understand how 'Transaction' is defined in this scenario...

Is it - 1...Update all 4 files means one transaction?

OR 2....4 different files define their 4 different transactions...

I want to study Commit & Rollback concepts more precisely..is their any best study guide.....????

Thanks,

Nutan



Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.

You haven’t specified if you are using native I/O or SQL but the concepts are the same
1. Files must be journalled
2. Commitment control must be started prior to opening the files
Native – STRCMTCTL
3. Native each file to be updated must include COMMIT on the F spec
4. In your case an SQL or RPG Commit is issued after all four files are successfully updated
on failure
Your program should issue a rollback

Hope this helps – phil

————————
Right – the commit determines where the updates are committed and become irevoable. For your example that is after field 4 If you capture an error on any file issue the rollback and do not proceed to the other files in the set.

Phil
—————————
09/01/09
No.. no trigger on commit or rollback
– I recommend capture each event through trigger
– capture commit / rollback event through direct write to Log file.
Phil

————–
Wilson — that’s cool
Remember the trigger(s) will fire anytime that event occurs to the file(s) — other programs, sql, dfu or dbu.
If I remember correctly, this is one of those ugly flashbacks, you have to end the trigger before you can change the trigger program and they are all locked when the file is locked… and in my experience that was .. of course locked except during the maintenance window.

Wilson if there’s a mix of SQL and native … how does commitment control get started and commits/rollbacks get issued ???

////////////

First, the trigger “problem”…. The COMMIT keyword may have a parameter to activate it or not. You can change your trigger to activate the COMMIT only if it’s called by a program (there is many way to detect it) and that way when you use DFU, DBU, etc, no COMMIT will be done or controlled (remember that it’s necessary to activate commit control with the CL command). And you are right, to change a trigger program you must have exclusive access over the file.

Second, the SQL mixing… I’m not a SQL user but what I can remember is that it will work fine (we are talking about a mix in the same program right?). But as I said, I’m not an expert…

Wilson

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Nutangujar
    Thanks a ton Phil, In some programs i may use native I/O and in others SQL.. If I'm understanding you correctly...It really depends upon where exactly i place the Comit ... In above scenario if I place it after every file update...it may happen that the first 3 files are updated but 4th is rollbacked because of the failure... if the failure occurs while updating the 2nd file, it won't even attempt to update 3rd and 4th file...??? Thanks, Nutan
    895 pointsBadges:
    report
  • philpl1jb
    Right - the commit determines where the updates are committed and become irevoable. For your example that is after field 4 If you capture an error on any file issue the rollback and do not proceed to the other files in the set. Phil
    51,235 pointsBadges:
    report
  • Nutangujar
    Thanks that clears me on Comit opn ....I was stuck with a related topic.... Suppose in above scenario.....If I have triggers for every file fired after update operation......., when first 3 files are successfully updated, it will trigger the event and update triggerlog file.....however when 4th file fails and it is a rollback, now the triggerlog file will still show that the records are updated.... can I make triggerlog file to capture the image after i issue comit..... Thanks, Nutan.
    895 pointsBadges:
    report
  • WilsonAlano
    Hi Nutan If you use Rollback and the triggerlog file is under commit too, records written to it will be deleted during rollback. A Rollback operation will return each file under commit control to the exact point it was before any change. A "transaction" must define a logical operation that could be many update on files. In your example, you must use Commit operation after successfully updating file 4. If updating file 4 fails, just use Rollback and all files will be restored to their original state. Wilson
    2,555 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following