View previous topic :: View next topic
|
Author |
Message |
eHorizon.Andrew
New User
Joined: 18 Jan 2007 Posts: 28 Location: Bank of communications
|
|
|
|
I have a question about db2 table design, would anyone help me on this? Any advice will be appreciated.
Here is the issue:
The requirement is that I need set up a table, and both online and batch program would read this and get the necessary reference number, let¡¯s take account for example, there is a field in table called ACT-NBR-GNT, which stores the account number already been generated and other programs should read this for creating a new account (using this as a reference number in certain position of account field.) and update this field after account created (by increase 1 value to this field)
The question is when online program is doing this process, it takes a long time, and lock the table ( or specified row) to prevent other batch programs to go further, always time out error, so could any help me on this?
I have thought about separating the online and batch programs to read different rows in table, but how can I control the update process for this field as the max number should be unique, otherwise it will cause the same account number if the update process not finished through online program and bath one get the old value.
Thanks a lot! |
|
Back to top |
|
|
Stefan
Active User
Joined: 12 Jan 2006 Posts: 110 Location: Germany
|
|
|
|
Instead of fiddling around with a numeric key within your application programs, I recommend the use of a sequence number.
With this DB2 feature it is the DB2 server itself who cares about a unique key and the increment logic.
You could define a sequence number by DDL, for example as follows:
Code: |
SET CURRENT SQLID = 'MYOWNER';
DROP SEQUENCE MY_SEQUENCE_1;
COMMIT;
CREATE SEQUENCE MY_SEQUENCE_1
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CYCLE
CACHE 256
ORDER;
|
Then you have to generate a unique key by modifying the INSERT statement. Instead of retrieving the existing key value, increasing it by +1, and using the new value as an argument in the INSERT statement, you now specify the new value with the phrase "next value for <sequence number>". Then DB2 generates a new value and ensures data integrity and smoothless concurrent access by all address spaces, e.g batch or CICS. This INSERT statement could then be included in a SELECT statement to retrieve the newly generated key as follows:
Code: |
SELECT MY_KEY
FROM FINAL TABLE
(INSERT INTO MYOWNER.MYTABLE
(MY_KEY ,
MY_TIMESTAMP ,
MY_NUMBER)'
VALUES (NEXT VALUE FOR MYOWNER.MY_SEQUENCE_1 ,
CURRENT TIMESTAMP ,
1)
)
FOR FETCH ONLY
|
Hope this helps |
|
Back to top |
|
|
eHorizon.Andrew
New User
Joined: 18 Jan 2007 Posts: 28 Location: Bank of communications
|
|
|
|
Thanks, Stefan, I would try this and reply on this later . |
|
Back to top |
|
|
eHorizon.Andrew
New User
Joined: 18 Jan 2007 Posts: 28 Location: Bank of communications
|
|
|
|
In your second code section, if the update process can be finished durning the select clause concurrently, that would work for my program, how could this happen in db select clause, Steafan? Thanks very much for your help. |
|
Back to top |
|
|
Stefan
Active User
Joined: 12 Jan 2006 Posts: 110 Location: Germany
|
|
Back to top |
|
|
eHorizon.Andrew
New User
Joined: 18 Jan 2007 Posts: 28 Location: Bank of communications
|
|
|
|
That is so wonderful, thanks a lot again. |
|
Back to top |
|
|
|