| View previous topic :: View next topic |
| Author |
Message |
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
 Posted: Mon Mar 23, 2009 9:29 am Post subject: Unique Key on the Table for a particular Day |
 |
|
Hi All,
In one of the tables that we have, the unique key is "Date(PIC X(10))" and "Record Sequence Number(PIC S9(6) COMP-3)".
While inserting a new row into this table, the key is generated as:
- Date is the current date when the record is being processesd.
- For the "Record Sequence Number": Fetch the current timestamp and take the last six digits of the timestamp as the Record Sequence. If a duplicate is found, we have a retry logic for eight times.
But now, we are having problem as even after the eight iteration, the program sometimes find duplicate on the table and skips inserting into the table.
For example, for today, Date field will be 032309 and for record sequence: 2009-03-23-18.14.41.012499, we take 012499.
So, Key will be 032309 - 012499.
If duplicate is found, we fetch the timesamp again and if it is 2009-03-23-18.14.41.513764, we take 513764.
So, the retry logic will try to insert the record as 032309 - 513764.
Can you please suggest a short method to generate unique numbers for a particular day without changing the table. This short algorithm can be built into the program.
This program is called multiple times and in multiple instances per day.
Thanks for your time!!
Thanks!! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 23, 2009 10:22 am Post subject: |
|
|
THRIVIKRAM,
Why cant it be a simple insert of
Current date + max(record seq num for current date) + 1
| Code: |
DATECOL RECSEQ
========== ======
2009-03-23 1
2009-03-23 2
2009-03-23 3
2009-03-23 4
..
|
If you don't have a entry for the current date then it would start with a 1.
This is how your insert statement would look like(untested)
| Code: |
INSERT INTO your-table(DATECOL,RECSEQ)
(SELECT CURRENT DATE, VALUE(MAX(RECSEQ),0) + 1
FROM your-table
WHERE DATECOL = CURRENT DATE)
;
|
If there are other applications which insert into the same table, you can check for -803 on insert and then re-issue the statement. |
|
| Back to top |
|
|
THRIVIKRAM
Beginner
Joined: 03 Oct 2005
Posts: 70
Topics: 34
|
| Posted: Mon Mar 23, 2009 1:51 pm Post subject: |
|
|
Thanks much for your answer Kolusu. One thing that I am curious to know is how would the performance of the program be impacted if I use a column function to get the maximum of the Rec_seq for every insert. I just checked the statistics and this program does approximately 400Thousand inserts in a day and we retain about 2 weeks worth of data.
Thanks!! |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Mar 23, 2009 3:36 pm Post subject: |
|
|
THRIVIKRAM,
I don't think it would have an impact , since you are always picking max sequence number for a given date. If there is an index on the Date column defined it would help. IMHO as long as you don't go for a Table space scan you should be fine.
Why don't you run an explain on that statement and see for yourself about the cost and index usage.
Kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
