Commit utilization in SQL stored procedures

pts.
Tags:
AS/400
DB2 Universal Database
RPG
I need an explample of an SQL stored procedure running in an DB2 environment that uses commitment control. I don't want use RPG. I would like to call it from QSQLSRC via LXISCH (AS400 scheduler) and update over 100,000 records in 20,000 row increments. Any Suggestions? Thanks!

Answer Wiki

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

What you need, rather than an example, are the rules.

Commitment control can be implicit or explicit. If you don’t code WITH clauses into your statements, you get whatever commitment level you’re connected at. If you do code commitment level, you can use WITH NC (no commitment control) through WITH RR (with repeatable read commitment level). Scan the PDF manual for “WITH RR”, and you’ll see a write-up on the various levels.

In a cursor definition, you can add FOR READ ONLY or FOR UPDATE. This controls record locking, too.

When you’re done with your transaction, win or lose, code COMMIT or ROLLBACK to lock in your work or thow it away, respectively.

One more coding hint: If you’re going to log errors to a table, and then roll back, use WITH NC on your logging table. You must also use WITH NC on tables stored in QTEMP.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 1  Reply

 
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
  • TomLiotta
    I can't quite figure out what kind of example is wanted. An example of a stored procedure using commitment control? An example of executing SQL source to call a stored procedure (from a mostly unknown 3rd-party job scheduler)? An example of a stored procedure that handles one-fifth of a set of updates? Calling that stored procedure five times? Calling the stored procedure multiple times in parallel? ...serial? Logic to call or to avoid calling later stored procs if one of the first calls fails? All of the above? Tom
    125,585 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