"Paul M" <nospam@nospam.com> wrote in message
news:HT73g.702$fx.123217@news20.bellglobal.com...
> Hi All,
>
> I've been tasked with writing a z/OS C program to read and update a table
> (DB/2 v8). This Table contains a single row with a single column of data.
> This program (which will run as a Stored Proc) needs to implement a
> strategy
> for concurrency as the Stored Proc can be called concurrently by several
> different users.
>
> The code essentially does the following:
>
> 1) Read the current value (eg. "ABCD")
> 2) Increment the value to the next value (eg. "ABCE")
> 3) Write the incremented value back to the table
>
> In my code, I'm doing the following:
>
> EXEC SQL
> DECLARE C2 SENSITIVE STATIC SCROLL CURSOR FOR
> SELECT NEXT_MAILBOX_ID FROM BTMFE_MB_NEXT_AVL
> FOR UPDATE OF NEXT_MAILBOX_ID;
>
> EXEC SQL
> OPEN C2;
>
> EXEC SQL
> FETCH FROM C2
> INTO :OLD_MAILBOXID;
>
> // the next value is calculated here and stored in :NEW_MAILBOXID
> // the new value is then written back to the table
>
> EXEC SQL
> UPDATE BTMFE_MB_NEXT_AVL
> SET NEXT_MAILBOX_ID = :NEW_MAILBOXID
> WHERE CURRENT OF C2;
>
> EXEC SQL
> CLOSE C2;
>
> It seems a little overkill to use a Cursor for something so simple, but
> our
> DBA recommended we use one.
>
> Everything works as expected, but I'm not sure if concurrency has been
> addressesed.
>
> - Do I need to use a SCROLL cursor when the table only has a single record
> with a single column?
> - How do I ensure that the row is locked during the above activity?
>
> Sorry for the basic questions, but I'm not a database guy.
>
> Thanks in advance.
>

The rows remain locked after an update until you do a commit. Whether you
need a cursor depends partly on how many rows you are updating, and whether
you should do intermediate commits every few hundred rows or so. If there
are a lot of rows, you can use the "with hold option" and do intermediate
commits without automatically closing the cursor.