Using a cursor for Update

[Paul M]

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.

[Serge Rielau]

Paul M wrote:

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?

The row is locked by virtue of the cursor being declared FOR UPDATE.
But you don't need a scrollable cursor for that. Any cursor will do.

In a future version DB2 for zOS may support:
SELECT mailboxid INTO :newmailboxid
FROM FINAL TABLE(UPDATE BTMFE_MB_NEXT_AVL
SET mailboxid = <blurp>(mailboxid))

That's denser
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Mark A]

"Paul M" <no****@nospam.com> wrote in message
news:HT*****************@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.

[Paul M]

Thanks, Mark.

Is a Commit done automatically when I close the Cursor or do I have to
explicitly issue a commit? My table has a single row with a single column.
The stored value is essentially an incrementing counter that I don't want
read or altered by concurrent requests.

Thanks again.

"Mark A" <no****@nowhere.com> wrote in message
news:1r******************************@comcast.com. ..

"Paul M" <no****@nospam.com> wrote in message
news:HT*****************@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.

[Knut Stolze]

Paul M wrote:

Thanks, Mark.

Is a Commit done automatically when I close the Cursor or do I have to
explicitly issue a commit? My table has a single row with a single
column. The stored value is essentially an incrementing counter that I
don't want read or altered by concurrent requests.

The end of a transaction (commit or rollback) is completely independent of
cursors. So you have to use your own explicit COMMIT statement (or the
respective API call). Where a connection does exist is that all cursors
are closed at ROLLBACK and non-holdable cursors are closed at COMMIT
(holdable cursors are kept open after the commit).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Mark A]

"Paul M" <no****@nospam.com> wrote in message
news:sN******************@news20.bellglobal.com...

Thanks, Mark.

Is a Commit done automatically when I close the Cursor or do I have to
explicitly issue a commit? My table has a single row with a single
column.
The stored value is essentially an incrementing counter that I don't want
read or altered by concurrent requests.

Thanks again.

This is a common situation. The following is the best solution and will
avoid deadlocks:

-- increment the sequence number and hold exclusive lock on the row

UPDATE sequence_table
SET seq_number = seq_number + 1
WHERE seq_key = ?;
-- to retrieve the value you just incremented above

SELECT seq_number
FROM sequence_table
WHERE seq_key = ?;

COMMIT;