By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,226 Members | 1,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,226 IT Pros & Developers. It's quick & easy.

DB2 Stored Procedure

P: 3
I am having a problem with creating a stored procedure.

The premise underwhich we are operating is the following:
We are working with two tables. The first table simply stores an integer value representing the last used value. The second table lists ranges of integers which have already been allocated in a legacy system. This table has two columns representing the various starting and ending values for these ranges.

We wish to retrieve the single value from the first table and increment by 1, check that the number does not fall within any excluded ranges in the second table, and then update that new number into the first table. The new number is then returned to the application.

We have a stored procedure to perform this operation.

As the new number is meant to be unique, we need to ensure that simultaneous requests queue until each previous request has completed. We have had issues in the testing stage of the application where a few records have been assigned the same integer value. This would point to the fact that the stored procedure is being executed simultaneously.

The development environment is VB.NET 2005, Windows XP SP2, DB2 8.1 FP 13.

[STORED PROCEDURE]
CREATE PROCEDURE SDE.SP_GEN_KEY (OUT newUniqueKey INT)
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE lowerIdentifier INT;
DECLARE upperIdentifier INT;
DECLARE lastUniqueKey INT DEFAULT -9999;

-- get the LASTKEY value from the table
SET lastUniqueKey = (SELECT LASTKEY FROM LDR.AT_LAST_KEY);
-- increment the value
SET newUniqueKey = lastUniqueKey + 1;
-- check that the new value falls outside of the forbidden ranges
SET lowerIdentifier = (SELECT RANGE_START FROM LDR.AT_EXCLUSION WHERE RANGE_START <= newUniqueKey AND RANGE_END >= newUniqueKey);
SET upperIdentifier = (SELECT RANGE_END FROM LDR.AT_EXCLUSION WHERE RANGE_START <= newUniqueKey AND RANGE_END >= newUniqueKey);
IF NOT lowerIdentifier IS NULL THEN
IF lowerIdentifier = upperIdentifier THEN
SET newUniqueKey = -9999;
ELSE
SET newUniqueKey = upperIdentifier + 1;
END IF;
END IF;
IF newUniqueKey >= 0 THEN
UPDATE LDR.AT_LAST_KEY SET LASTKEY = newUniqueKey;
END IF;

END P1
[END STORED PROCEDURE]

Is there a way that we can lock the first table for reading until the update within the Stored Procedure has completed?

We were also looking into using a Sequence, but could not include the exclusion range table within that logic. We are worried that should we use a Sequence, it may take quite a long time to loop through the consecutive values until it cleared an excluded range. Each excluded range will occupy around 100000 - 200000 records.

Regards

Gregory
Oct 4 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.