UID generation in DMS
As known below are the UID or Sequences numbers that gets generated and used in DMS. All
the below numbers are generated by a common UID API’s either from DMS or Web service
UIDs in DMS Comments
DOCNO Main key that integrates all the DMS
INDOCNO The key used for Incoming numbers
OUTDOCNO The key used for Outgoing numbers
PERSON_ID The key used for Person record
RECORD_ID The key used for Person Link record
ATTACH_DOCNO The key used for Attachment record
Below are the tables involved in UID generation by UID API’s
Tables used by DMS UID APIs Comments
Before any of the below Counter and Failure tables are used a
OAEFILE_LOCKTABLES record with the table name is inserted and kept in this table as
a lock on it
The counters for most of the DMS UIDs are maintained in this
OAEFILE_UID_MAIN table
The counters for Incoming and Outgoing numbers (UID) are
OAEFILE_CABINET maintained in this table
This table keeps record of any failed DOCNO so it can be
OAEFILE_FAILURE_SYS_DOCNOS used in next call
This table keeps record of any failed Incoming and Outgoing
OAEFILE_FAILURE_DOCNOS numbers (UID) so it can be used in next call
As known since sometime we are facing either duplicates or holes in the above UID sequences
and most concerning is INDOCNO and OUTDOCNO.
Malik did implement new Database Sequences introduced in SQL 2012 for DOCNO,
PERSON_ID and RECORD_ID (based on key). But still the most important UIDs INDOCNO and
OUTDOCNO are still generated by UID APIs. Hence it is highly suggested that the DMS
Developer have a through and deep look at this UID generation and resolve this serious issue
at the soonest.
Below are the briefs on Database Sequences introduced in SQL 2012. It is an object created to
give Sequence Numbers across the current database. Below creates basic Sequence with name
XYZ on current database
CREATE SEQUENCE XYZ
START WITH 1
INCREMENT BY 1;
Below is how you access above sequence created
SELECT NEXT VALUE FOR XYZ;
The big problem here I noticed is that the above Database Sequences does not run in
transaction and hence gets incremented on every call by a caller who is in transaction and fails
for some reason, hence a hole in sequences can happen. Consider the below statement which
in spite in transaction and rolled back still the Database Sequences XYZ gets incremented
BEGIN TRAN
SELECT NEXT VALUE FOR XYZ;
ROLLBACK
Hence I suggest that we get the current value of the Database Sequences XYZ by below
statement and only after successful confirmation of big complex DB transaction like Incoming
Form that we increase the Database Sequences XYZ
--Get the current_value of Database Sequences XYZ
SELECT current_value FROM sys.sequences WHERE name = 'XYZ';
--Increase Database Sequences XYZ only if caller like Incoming is successful by below statement
SELECT NEXT VALUE FOR XYZ;
When look closely we see that we get Sequence numbers than an UID which in our case
happens to be Sequence. For example for systems columns like DOCNO, ATTACH_NO,
PERSON_ID and PERSON_RECORD_ID we need just unique numbers and not necessarily a
Sequenced unique numbers as these are system numbers that binds DMS and not seen
visually in DMS. The Sequence numbering is needed only for two columns INDOCNO and
OUTDOCNO and they should not have duplicates or holes. Below I tried pictorially explain in
analogy of banking service token numbers. Notice that if a Person after taking a token# 123
but for some reason do not use it and go away from bank than that token number 123 goes in
vain and hence a hole in sequence of serving. Check below the pictorial explanation of the UID
concept.
DMS Requesting UID UID Generating APIs
Generating APIs (Transaction B?)
(Transaction A?)
Create Incoming
Create Outgoing with Persons
Create Incoming with
Attachments
Reserve Incoming
