ACCOUNT_ADJUSTMENTS SET STATUS_IND = : ACCT-ADJ-STATUS-IND , INVOICE_ID = : ACCT-ADJ-INVOICE-ID , LAST_ID = : ACCT-ADJ-LAST-ID WHERE ADJUSTMENT_ID = : ACCT-ADJ-ADJUSTMENT-ID END-EXEC Is this standard practice, or am I missing an (obvious) other way to do it? STATUS_IND = 'O' END-EXEC After fetching a row we may need to update the following columns: ADJ. ACCOUNT_ADJUSTMENTS SET STATUS_IND = : ACCT-ADJ-STATUS-IND , INVOICE_ID = : ACCT-ADJ-INVOICE-ID , LAST_ID = : ACCT-ADJ-LAST-ID WHERE CURRENT OF ALL-ADJSTMTS-CSR END-EXEC You can't use the FOR UPDATE clause in the DECLARE, however, if the SELECT joins two tables (and other reasons, of course). So instead we just do a read-only cursor and update, when necessary, like this: EXEC SQL UPDATE ACCOUNTS.

--------------------------------------------------------------- Statement: UPDATE ACCOUNTS. ACCOUNT_ADJUSTMENTS AS ADJ SET STATUS_IND =: H9_0, INVOICE_ID =: H9_1, LAST_ID =: H9_2 WHERE ADJUSTMENT_ID =: H9_3 Section Code Page = 1252 Estimated Cost = 21.316250 Estimated Cardinality = 1.000000 Access Table Name = ACCOUNTS.

The second uses the RID_BIT() function to do a 'direct read' of the record.

Is it absolutely essential to use SELECT FOR UPDATE for doing updates in an environment where the same data can be updated by multiple people ?

What are the repercussions if we don't use SELECT FOR UPDATE for updating/deleting the data ?

We have a CURSOR declared thusly: EXEC SQL DECLARE ALL-ADJSTMTS-CSR CURSOR FOR SELECT ACCT.

I'm just using it to fetch the record quicker during a batch update process. Am I missing some reason that I should not do this?

Sometimes I may even select the cursor WITH UR if I know it will not create a data integrity problem (I know that no other programs are updating the columns in the predicate). I guess I am wondering, if another process attempt to access a table that I have declared a cursor on, and that other process wants to update it, what will the other process see if I have SHARED locks on my cursor, and what will he see if I have UPDATE locks on my cursor? I guess I am wondering, if another process attempt to access a table that I have declared a cursor on, and that other process wants to update it, what will the other process see if I have SHARED locks on my cursor, and what will he see if I have UPDATE locks on my cursor?

I declare the cursor as READ ONLY, and use the WITH HOLD option (so that the cursor does not close upon commit, and then issue a separate update or delete statement (followed by frequent commits). "Share: Limits concurrent application processes to read-only operations on the data." How is WITH RS USE AND KEEP SHARED LOCKS different than just WITH RS and no lock-request-clause and how is it different then an UPDATE lock? "Share: Limits concurrent application processes to read-only operations on the data." How is WITH RS USE AND KEEP SHARED LOCKS different than just WITH RS and no lock-request-clause and how is it different then an UPDATE lock?

I often do a separate update/delete instead of "where current of cursor" even when there is only one table involved, simply because I want to have maximum concurrency (minimize locking issues).

Thanks, Frank Yes this is common practice, but whether it is "standard" depends on what you are trying accomplish and what level of locking and concurrent access you need.

ACCOUNT_ADJUSTMENTS SET STATUS_IND = : ACCT-ADJ-STATUS-IND , INVOICE_ID = : ACCT-ADJ-INVOICE-ID , LAST_ID = : ACCT-ADJ-LAST-ID WHERE ADJUSTMENT_ID = : ACCT-ADJ-ADJUSTMENT-ID END-EXEC Is this standard practice, or am I missing an (obvious) other way to do it? We have a CURSOR declared thusly: EXEC SQL DECLARE ALL-ADJSTMTS-CSR CURSOR FOR SELECT ACCT. ACCOUNT_ADJUSTMENTS ID = 9,775 --------------------------------------------------------------- The second is obviously "twice as efficient" as the first.