|
|
|
|
Алгоритм создания ROW_ID
Introduction
Whenever a new record is created in Siebel Sales Enterprise an internal
unique identifier is created and associated with the record. For example,
when a user creates a new Account the User Interface will create a unique
identifier and place the value in the column ROW_ID of the table S_ORG_EXT
(the Account table). This TAM tool describes how these unique identifiers
(known as Row Id's) are created and gives an example of how to create them
manually.
Disclaimer
It is not generally recommended that Row Id's be created in the manor
outlined below. Wherever possible use either the Siebel Sales Enterprise
user interface or Interface Manager to create new records (and therefore Row
Id's). In cases where this cannot be accomplished then the following will
work but should be followed with great caution.
What is a Row Id and Why are They Important?
The unique identifier associated with every record in the Siebel Sales
Enterprise database is known as a Row Id. The column in which this value is
found is ROW_ID and is present on every table. The Row Id for a record is
guaranteed to be unique across the entire database (not just within a
specific table), including mobile user databases. That is, a record created
by a mobile user against Watcom will never be assigned the same Row Id as a
record created by another mobile or connected user against the same server
database.
Row Id's are used extensively throughout Siebel Sales Enterprise to access
specific records. Although users access records by a User Primary Key (such
as Opportunity Name) it is more efficient for Siebel Sales Enterprise to
store and access related data via the Row Id. For example, if an Account has
a Row Id of '1-1234' and we want to look up the related contacts then we
only need to do a lookup in the Contact table against the Row Id for the
Account to which the Contact is related rather than the Account Name. This
is particularly important when User Primary Keys may involve multiple
columns, such as in the case of a Contact (Last Name, First Name, Middle
Name) or where the User Primary Key can be long (such as in the case of
Opportunity Name).
As an example illustration of the database space savings consider the
following scenario:
1000 Opportunities with an average name of 30 characters each
5 Contacts per Opportunity
If we where to store the Opportunity Name against each Contact association
then we would require 150,000 bytes. With the use of Row Id's this would
require at most 75,000 but more typically 7*5*1000 = 35,000 (7 as this is a
typical width of a Row Id value). Obviously, across a large database, the
savings in space can become dramatic given the many relationships.
How are Row Id's Generated?
The format of the Row Id is one of the following:
CP-NS For records created by the Siebel Sales Enterprise user interface
CP+NP+NS For records created by Interface Manager
where: CP = Corporate Prefix
NP = Next Prefix
NS = Next Suffix
It is important to understand the procedure for creating a mobile user
database and how these definitions differ between connected and mobile
users.
Each Siebel Sales Enterprise server database contains the following table:
Table Name Column
S_SSA_ID CORPORATE_PREFIX
NEXT_PREFIX
...
This table only ever contains a single record. An example could be:
CORPORATE_PREFIX NEXT_PREFIX
1 50
The Corporate Prefix and Next Prefix are set up as part of the Siebel Sales
Enterprise database server setup procedure. Once the DBXTRCT program is run
to create a new mobile user database the NEXT_PREFIX value will be used as
the CORPORATE_PREFIX for that new database and the NEXT_PREFIX on the server
database is incremented by 1. For example, if we extract a new user then the
entry in S_SSA_ID for the above example on the server will now be:
CORPORATE_PREFIX NEXT_PREFIX
1 51
The table S_SSA_ID will also be created on the mobile (Watcom) database and
will be as follows:
CORPORATE_PREFIX NEXT_PREFIX
50 1
If a second DBXTRCT was run (this includes reinitializing an existing mobile
user) then the table S_SSA_ID for the server would now appear as:
CORPORATE_PREFIX NEXT_PREFIX
1 52
And for the new or reinitialized mobile client would appear as:
CORPORATE_PREFIX NEXT_PREFIX
52 1
Note how the Corporate Prefix will always be unique for any database; the
server maintains its original value and all mobile databases created against
that particular server database are always assigned a new unique value.
Generation of Row Id's in the User Interface
The Siebel Sales Enterprise user interface generates Row Id's in the format:
CP-NS
When a new record is created through the User Interface, Siebel Sales
Enterprise will go to the table S_SSA_ID and use the Corporate Prefix. It
will also use the current Next Suffix and increment the Next Suffix. In
fact, to improve performance, Siebel Sales Enterprise will update the Next
Suffix value by 25; this allows the user interface to create 25 new records,
alleviating the need to update S_SSA_ID for each new record created. When
this 'cache' is emptied then Siebel Sales Enterprise will fetch the next set
of Row Id's and update the Next Suffix accordingly. Should Siebel Sales
Enterprise be closed before all of these 'cached' values are used then the
remaining values in the current set will be lost. However, as will be seen
later it is possible to create upwards of 78 billion Next Suffixes for any
single database so the potential for running out is literally non-existent
(see the explanation below titled 'How Many Unique Row Id's can be Created?'
for an explanation of these values are calculated).
Generation of Row Id's in Interface Manager
Interface Manager generates Row Id's with the following format:
CP+NP+NS
When Interface Manager is executed it will select the current values for the
Corporate Prefix and Next Prefix from S_SSA_ID plus increment the Next
Prefix by one. It will then generates it's own Next Suffix for all records
created. This approach means that Interface Manager need only access
S_SSA_ID once per run but still guarantee that all generated Row Id's are
unique.
How Many Unique Row Id's can be Created?
All ROW_ID columns are defined as VARCHAR(15); that is, they can contain up
to 15 characters. The maximum number of Row Id's that can be generated is
dependent on the length of the Corporate Prefix and Next Prefix (in the case
of Row Id's generated by Interface Manager). For example, if the corporate
prefix is 1 character and the Next Suffix is 5 characters (which is not
unreasonable for an environment of 5000 users with regular Interface Manager
runs) then 7 characters remain for the Next Suffix (remember that Interface
Manager inserts two section separators into the Row Id (+). Siebel Sales
Enterprise generates base 36 values for the Next Prefix which results in up
to 36 to the power of 7 values. That is, over 78 billion values! Thus, with
a Next Prefix width of 5 characters it is possible to generate 5 to the
power of 36 times 78 billion Row Id's (2.8 trillion).
|
|
|