Clarity sequencing IDs not in order by created date

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

This applies to all Clarity tables with sequencing on IDs, such as BPM_RUN_PROCESSES, CMN_AUDITS, CMN_SCH_JOBS, CMN_SCH_JOB_RUNS and many others

Example use cases: 

• You are trying to use CMN_SCH_JOBS.ID field to filter on sequential/chronological jobs in your custom code.
  It does not return the jobs in the time order they are run, You see gaps in IDs, you see the last running jobs using older IDs
• You have a custom interface written on the max logic using IDs in CMN_AUDITS. You are seeing unexpected ID sequencing. The latest created record doesn't have the maximum ID. For example, the record created at 2023-03-02 23:58:37.067 timestamp has 16715517, however the record created at 2023-03-02 23:59:10.42 which is the latest one has 16715502.  

Environment

Release: Any

Cause

Working as Designed.

• Clarity APP and BG servers will cache IDs in 1000s increments and depending on which node a given user is at it will use the IDs it will pick from the cache.
• This will be valid even if you only have 1 APP or 1 BG 
• Some of the IDs will be used by both APP and BG service
• It is not expected and guaranteed that Clarity sequenced IDs will always be in chronological order - what is guaranteed is that the IDs will be unique.

Resolution

The behavior you are facing is expected.

• The ID in CMN_SCH_JOBS, BPM_RUN_PROCESSES and other sequenced tables will not be chronological and contiguous, as it depends on sequences cache, which is different for each APP/BG service running it.
• It's expected to have gaps, or older ID used when a newer is already used for previous job instances.
• Example: If you have 2 BG services, each will use their own app cache of a specific amount IDs, and they will not overlap. Say BG 1 will have 500001 to 5000050, and BG2 5000050 to 5000100 and they will assign them as needed, depending on the service that's getting the request. If a BG is restarted, the unused IDs will be dropped. So the IDs are really not meant to be used in chronological order.
• The same functionality design is valid for Clarity APP service 

Suggested way to adjust your custom code to suit your needs:

• Use your custom code to create your own chronological ID to use. This would be the best possible solution as sometimes the Clarity IDs may not behave as you would want it or they may be change in functionality
• If you cannot create an ID in your custom code, then you can use the start date, created_date or a similar date field to get the results formatted as you need.