• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9362
  • Last Modified:

SELECT FOR UPDATE WITH RS

1) We are using DB2 8.0.1 Express on Red Hat Linux 9
2) SELECT MAX(POINT_SEQ) + 1 FROM SFUCHECK WHERE POINT_DATE = CURRENT DATE FOR UPDATE WITH RS; is not working due to one of the many SELECT FOR UPDATE restrictions. Even just SELECT MAX(POINT_SEQ) is not allowed.
3) So, I used SELECT POINT_SEQ + 1 FROM SFUCHECK WHERE POINT_SEQ = (SELECT MAX(POINT_SEQ) FROM SFUCHECK WHERE POINT_DATE = CURRENT DATE) FOR UPDATE WITH RS;
This works fine till the point that succeeding waiting transactions read same MAX value & hence ultimately same value after exclusive read lock is released in remnant windows.
4) FOR UPDATE WITH RS is not allowed in inner query and so not MAX aggregate function in direct WHERE clause itself. Please suggest a work around.
0
k_murli_krishna
Asked:
k_murli_krishna
  • 6
  • 3
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi k,

I'm assuming that this line of code is one of several lines that are executed "in series", probably a stored procedure.  I'm  also assuming that your application must ensure that MAX(POINT_SEQ) doesn't change until to you a COMMIT.

You can redefine POINT_SEQ to be an identity column and let DB2 increment it for you.

You can also write a single INSERT statement that doesn't have so many locking issues.

INSERT INTO <mytable> (point_seq, .....)
WITH t (next_seq) AS
(
  SELECT max(point_seq) + 1
  FROM sfucheck
  WHERE point_date = current_date
)
SELECT next_seq, ....
FROM table_list, t;


Kent
0
 
k_murli_krishnaAuthor Commented:
I forgot to mention, sorry, that a design change to add ID columns and so many sequences for each and every POINT_DATE is not permissible.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi K,

That's fine.  Can you comment on my assumptions?  That way we'll have a better understanding of the problem.

Thanks,
Kent
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
k_murli_krishnaAuthor Commented:
Sorry, Kent, did not see the insert with WITH clause. Looks good. Sure, will try it & get back.
0
 
k_murli_krishnaAuthor Commented:
Same problems. SELECT MAX(POINT_SEQ) + 1 FROM SFUCHECK WHERE POINT_DATE = <date-val> FOR UPDATE WITH RS throws error:
SQL0511N  The FOR UPDATE clause is not allowed because the table specified by
the cursor cannot be modified.  SQLSTATE=42829
I put this on outer query & it percolates into inner one. With INSERT INTO either plain or using WITH clause, works fine but different transactions started before current one is commited all pick up MAX(POINT_SEQ) + 1 and not incremental values like in a sequence object.

WITH RS is not allowed in inner query like what you supplied or a proper inner query. Again putting on outer query works but increment not like a sequence object in successive transactions before first one is commited. Please suggest with SELECT then insert or INSERT INTO SELECT FROM. Using ID column/sequence I am aware of.

Is all or some of this since at offshore we have DB2 8.0.1 on intel x8632 (32 bit) Red Hat Linux where as at onsite we have correct one as DB2 9.1.0 on AMD x8664 (64 bit)
0
 
Kent OlsenData Warehouse Architect / DBACommented:

What if you were to create a separate sequence object and just define NEXT_SEQ as the value that you get from the sequence?  It's very simple, very fast, and doesn't require anychanges to the existing tables.


Kent
0
 
k_murli_krishnaAuthor Commented:
It is based on a set of same date values that POINT_SEQ has to be incremented and inserted using CURRENT DATE. How many sequences will have to be created. There are more than 600 reports against iseries AS/400 DB2 database and we need to migrate to Linux as well. Client said to keep database schema changes to abs minimum. If you have run out of options, let me know, I will award the question to you.
0
 
k_murli_krishnaAuthor Commented:
And as new dates are added we require new sequences for them and how do we know when to create a sequence and where/when/how to code its usage in queries/inserts.
0
 
k_murli_krishnaAuthor Commented:
I got it Kent. It is:
SELECT point_seq + 1 FROM sfucheck WHERE point_date = <date-val> ORDER BY point_seq DESC FETCH FIRST 1 ROWS ONLY FOR UPDATE WITH RS;
Thanks for all the help.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now