Primary Key - Expert Opinion Requested

November 19th, 2004, 2:13 am

I am looking for an expert opinion on the following:

Our development environment is Windows 2000 with 8.1.5 DB2UDB Database and Production will be on AIX Machine.

We have around 15 tables which should be with Primary key with fixed length and prefixed with Date. The primary key is a combination of (Date + Sequence for the Day). Again, the sequence has to start from 1 every day.

Primary Keys Expected:
================

For Date:
------------
2004-11-19


Primary Keys:
-----------------
2004111900000001
2004111900000002
2004111900000003
2004111900000004
2004111900000005
2004111900000006
2004111900000007
2004111900000008
2004111900000009
2004111900000010
2004111900000011


Date:
-------
2004-11-20


Primary Keys:
-----------------
2004112000000001
2004112000000002
2004112000000003
2004112000000004
2004112000000005
2004112000000006
2004112000000007
2004112000000008
2004112000000009
2004112000000010
2004112000000011

Your suggestions and advises are requested.

Thanks

Best Regards,
Varma Jampana

November 19th, 2004, 5:34 am

Hello,
OK I try to answer despite of my poor english.
it's depending on what you're gonna do with primary key field.
In fact sequence is enough for the primary key.
what do you want to add the date as a prefixe for?
will you use both the informations of the field?In a standard way you should split the information:
- the primary key with the sequence.
-a date field

that's just the job of the sequence to provide an unique identifiant number.
You will use the same sequence from the beginning and then all the days and it causes no problem.


In your solution, INSERT opérations may be more painfull, and I don't see any interest.

November 19th, 2004, 8:34 am

by the way,
I never worked on DB2 so far.

there is no "auto-incrément" (sorry ) data type?
In that case maybe you can emulate one with a sequence (created in the database) + "before insert" trigger.
That's what we can do on Oracle database.

November 19th, 2004, 7:53 pm

I would be a bit concerned with the code required to generate the key. How will the script know what number is truly next? The program will need to determine the last number that was generated. How will it do that? It will either have to get the last record and increment the key, or keep track of the number of records added that day. It will also need to check whether the day has changed since the last record was added. In a high volume, multi-user environment, it would be difficult to ensure that duplicates are not generated from simultaneous sessions especially when considering network latency.

Doing the above is rather inefficient from a scripting point of view. I would consider using hours-minutes-seconds-milliseconds rather than the propsed sequence. The first key would then be: 2004.11.19.00.23.52.23

I am using decimals for ease of reading and understanding the example. I am not suggesting that the key should include the decimals.

It is highly unlikely that 2 records could be added within the same one hundreth of a second.

November 20th, 2004, 3:23 am

hello longcall,
that's just one of the reasons I talk about when I said "more painfull".

Quote:

I would consider using hours-minutes-seconds-milliseconds rather than the propsed sequence

??????
the sequence is a database object, it's impossible to get 2 same values.
The SEQUENCE is the latest function implemented by DB2 UDB for generating unique values and it needs no extra-code, one INSERT->one generated value.


exemples:

Code: [ Download ] [ Select ]

CREATE SEQUENCE MY_SEQUENCE
AS BIGINT
START WITH 1
INCREMENT BY 1

1. CREATE SEQUENCE MY_SEQUENCE
2. AS BIGINT
3. START WITH 1
4. INCREMENT BY 1

Code: [ Download ] [ Select ]

CREATE TABLE MY_TABLE ( MY_KEY BIGINT NOT NULL,
   FIELD1  DATATYPE,
   FIELD2  DATATYPE,
   ...
   PRIMARY KEY (MY_KEY))

1. CREATE TABLE MY_TABLE ( MY_KEY BIGINT NOT NULL,
2.    FIELD1  DATATYPE,
3.    FIELD2  DATATYPE,
4.    ...
5.    PRIMARY KEY (MY_KEY))

You will need A sequence for each key in your database of course

when sequence is created, all you have to do is:

Code: [ Download ] [ Select ]

INSERT INTO MY_TABLE ( MY_KEY, FIELD1, FIELD2,...)
VALUES(NEXTVAL FOR MY_SEQUENCE, 'value1', 'value2', ...)

1. INSERT INTO MY_TABLE ( MY_KEY, FIELD1, FIELD2,...)
2. VALUES(NEXTVAL FOR MY_SEQUENCE, 'value1', 'value2', ...)

You can even create a BEFORE iNSERT trigger if you want it to act like an auto-increment datatype like autonumber in ACCESS or "autoincrément" in MYSQL.(you therefore won't even mention the key field in INSERT requests, no need to call nextval, the trigger will do it.)(->just ask me if you're interested in the trigger script)

TO answer to your question, how to get the last key(for example f you need it in your programm script): you can use "PREVVAL FOR MY_SEQUENCE" to get the most recently generated sequence value.
The amazing is that's the returned one is the last one of your own session even if there are many connection session at the same time, doing INSERT opérations
In this chronolgical order:
user1:

Code: [ Download ] [ Select ]

INSERT INTO MY_TABLE ( MY_KEY, FIELD1, FIELD2,...)
VALUES(NEXTVAL FOR MY_SEQUENCE, 'valuea1', 'valuea2', ...)

1. INSERT INTO MY_TABLE ( MY_KEY, FIELD1, FIELD2,...)
2. VALUES(NEXTVAL FOR MY_SEQUENCE, 'valuea1', 'valuea2', ...)

user1 you get 7 from the sequence for MY_KEY value


user2

Code: [ Download ] [ Select ]

INSERT INTO MY_TABLE ( MY_KEY, FIELD1, FIELD2,...)
VALUES(NEXTVAL FOR MY_SEQUENCE, 'valueb1', 'valueb2', ...)

1. INSERT INTO MY_TABLE ( MY_KEY, FIELD1, FIELD2,...)
2. VALUES(NEXTVAL FOR MY_SEQUENCE, 'valueb1', 'valueb2', ...)

User2 get 8 from the sequence for MY_KEY value
user1 again:

Code: [ Download ] [ Select ]

SELECT PREVVAL FOR MY_SEQUENCE FROM MY_TABLE

this will return 7 and not 8 to user1 .
then you can do a SELECT * from MY_TABLE WHERE MY_KEY=(SELECT PREVVAL FOR MY_SEQUENCE FROM MY_TABLE) to get your last record inserted (but not the last of any user).
or SELECT * FROM MY_TABLE
WHERE MY_KEY=(SELECT max(MY_KEY) FROM MY_TABLE) to get the very last record.(depending on access, lock, update ryules of course)

The PREVVAL and NEXTVAL values are not impacted by the transaction rollback. when a value in used in an INSERT opération, it won't be generated again even after a rollback.
to me it's a good thing.

A last thing: You can update the way in wich the key is generated at any moment, with ALTER SEQUENCE (new start, increment).

November 20th, 2004, 9:01 am

gisele,

I see now, and I like it. It emulates the autonumber function perfectly.