Thanks for the thought-provoking input.
I guess the cool thing about using bigint would be that it mimics the relative record number concept that we're used to being spoiled with on IBM i if we've worked on DB2 long enough 😊 Plus it can be auto-incrementing. And the secondary extended value can be used for merging or other purposes where the true key such as customer# or systemid if important to have.
If the ID isn't needed for secondary indexes (like customer# ) and joining, then if the data is moved to another system the ID doesn't really matter (because it's essentially like a RRN) if I were to move the data, correct ? I would be relying on the secondary keys for my meaningful joins.
Just want to make sure I’m thinking of this correctly. And database indexes (on other platform databases) are essentially replacing my logical files.
BTW: I think your AppServer4RPG might be the perfect medicine for customers implementing MariaDB and Postgres on IBMi. Allowing them to use the data from RPG. I plan to create a few samples soon.
Happy holidays.
Regards,
Richard Schoen
Web:
http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx
------------------------------
message: 3
date: Mon, 14 Dec 2020 09:32:09 +0100
from: "D*B" <dieter.bender@xxxxxxxxxxxx>
subject: RE: The age-old database design question - Integers or
GUID/UUID for primary keys ? What are your thoughts ?
@UUID:
The maria paper you mentioned says: "The probability of a collision is almost none."
A few years ago DB2/400 stated: "UUIDs are most likely unique"
The reason for this is, that a part of the UUID depends on timestamp. This could be solved by a function (provided by the datatbase, mentioned from Birgitta; or by a UDF) or has to be solved in the application layer in the insert process. Uniqueness is guaranteed anyway by the primary key.
@merging data:
This is done for UUIDs by a part of the key is dependend on the box it is generated from. Same could be done for the number keys: just add another key column for the customer or the box the record is inserted from.
@your indexes:
The ID is not needed as a part of your CustNumber_Key and CustName_Key. The ID is only needed for the elementary join operations and normaly ensured by your referential constraints
@how to decide:
It depends on your needs! For heavy transactional workload of the insert and delete (!!!) processes, I would do extensive workload tests first. In a datawarehouse project (it was the biggest real world database on as400 in germany of those days) the decision was to use bigint keys, generated in the application layer (blocking in the process outperformed autoincrement numbers - maybe dependend on the current implementation).
@some thoughts:
- index size is very important!
-- indexes could get bigger than the tables
-- delete performance of reorg processes depends on index size
- bulk operations could be speeded up by parallelism and are influenced by your index decision
@my preference:
Bigint keys optionally extended by a key for the source-system
D*B
midrange.com
