How does Pivotal assigns record ids

If you’ve worked with Pivotal CRM you’re probably familiar with the RSys_Last_Id table. This table is located in the ED and stores the last assigned id (the primary key for a record) for every table in the system. When the Pivotal Business Server (PBS) needs to create a new record it will use this table to know what is the next id to assign to that record.

When you create a new table (using the Toolkit) and then do an Apply Customization Changes (ACC) using the Pivotal Administration Console (PAC) then a new record will be inserted into RSys_Last_Id, with two values: one for the table name and the other value for the id, which is initialized to 0x0000000000000000.

When the first record for the table is created the PBS will assign its id the value 0x0000000000000001, however if we look at the RSys_Last_Id table, the Last_Id_Value for the table is not 0x0000000000000001, but instead is 0x0000000000000010.

The reason why this occurs is due to the way the PBS works with these ids. The PBS has a cache of ids per table. When the PBS starts, this cache is empty, but immediately after creating the first record for a table the PBS creates a cache of 16 ids for such table. For the case of a new table, when the first record is created the PBS requests 16 Ids from the RSys_Last_Id table, and thus the Last_Id_Value is set to 0x0000000000000010 (these values are hex numbers: 10 hex = 16 decimal). The PBS assigns ids from the cache until the cache is empty again and no more ids are available for the table. When the 16th record is created the PBS will use the last id from the cache and the cache will be empty. When the 17th record is created the PBS realizes the cache is empty, so it requests another 16 ids from the RSys_Last_Id table, so the value of the Last_Id_Value for such table is set to 0x0000000000000020 (20 hex = 32 decimal).

Notice that if the PBS is shutdown the ids that are still in cache are lost. This explains why the record ids for a given table might have missing ids. As an example: suppose that 5 records have been created for a table, the PBS has used 5 ids out of the cache. At this point the PBS is shutdown, and the ids in the cache (16 – 5 = 11 ids) will be lost. When the PBS is restarted (it starts with an empty cache) and a new record for the table is requested (the 6th record, for this example) the PBS will ask again the RSys_Last_Id table for another 16 ids, but the RSys_Last_Id table had the Last_Id_Value set to 0x0000000000000010 (before the PBS was restarted), so this time it will provide 16 ids, ranging from 0x0000000000000010 to 0x0000000000000020. The 6th record will be created with the id 0x0000000000000010. At this point the table will have 6 records, with ids: 0x0000000000000001, 0x0000000000000002, 0x0000000000000003, 0x0000000000000004, 0x0000000000000005, 0x0000000000000010. Ids from 0x0000000000000006 to 0x000000000000000A were lost.