Open topic with navigation

TIBCO iProcess® Engine 11.9.1

# CASENUM_FIND_GAPS

The `CASENUM_FIND_GAPS` stored procedure adds a list of free case number gaps to the `casenum_gaps` table.

If the case number or the subcase number generated from the `sequence` table reaches the maximum case number, 4294967295, then the following cases cannot be started. This stored procedure is used to scan a range of case numbers and create available blocks of free case numbers for reuse. It operates across a case range and only allocates free case numbers. The free case numbers are available either because the case numbers have never been used or from the original cases that have been purged.

The `casenum_gaps` table holds the free case number gaps that are created by the `CASENUM_FIND_GAPS` stored procedure. See casenum_gaps for more information.

## Syntax

    CASENUM_FIND_GAPS (
    	v_casenum_min	IN NUMBER,
    	v_casenum_max	IN NUMBER,
    	v_gap_size	IN NUMBER)

where:

| • | `v_casenum_min` specifies the minimum case number of the range.

| • | `v_casenum_max` specifies the maximum case number of the range.

| • | `v_gap_size` specifies the minimum size of a gap that contains only free case numbers.

## How to Reuse Free Case Numbers

Perform the following steps to reuse the free case numbers:

Note: TIBCO recommends that you shut down iProcess Engine before running `CASENUM_FIND_GAPS`. If you want to run the procedure against a running system, you must ensure that the case range supplied does not overlap with the ranges currently being used, as there is the possibility of overlapping gaps with duplicate case numbers being created.

| 1. | Shut down TIBCO iProcess Engine.

| 2. | Periodically run the `CASENUM_FIND_GAPS` stored procedure as the database administrator.

To do this, you can create a SQL script as shown in the following example, and use SQL*Plus to run the script.

    call swpro.casenum_find_gaps(1, 26, 1);

In the example, `CASENUM_FIND_GAPS (100, 500, 20)` looks for the gaps of at least 20 free case numbers from 100 to 500. If the range has three gaps: 130 - 140, 240 - 270, and 430 - 480, only the last two gaps will be listed in the `casenum_gaps` table for iProcess Engine to allocate case numbers.

| 3. | Restart TIBCO iProcess Engine.

When TIBCO iProcess Engine wants to cache a new batch of sequences, it will first use the case numbers in the `casenum_gaps` table that are listed by the `CASENUM_FIND_GAPS` stored procedure and then allocate the unused new case numbers when the case numbers in the table are used up.

## Notes

Before running the stored procedure, note that:

| • | Running the `CASENUM_FIND_GAPS` stored procedure may take a long time. It is only of benefit in the areas where the density of the occupied case numbers is low enough to have many gaps in between. This is typically in the lower range of case numbers, as these are older cases and more likely to have been closed and purged. The area close to the most recently started cases is likely to be densely populated, because all these cases are new and less likely to be closed and purged.

| • | Ensure that there are enough available case numbers for reuse by creating a sound purge strategy.

| • | TIBCO recommends that you do not run `CASENUM_FIND_GAPS` repeatedly on the same case number range. Check the values in the `casenum_gaps` table for the listed gaps and run the procedure on a range outside of the highest and lowest figures in the table.

| • | The performance of `CASENUM_FIND_GAPS` is proportional not to the size of the range, but to the number of actual cases in the range. For instance, when running it on a range from 0 to 100 million, if there are only 5000 cases in that range, it will be very fast and might only take a few seconds. While running it on a range from 100 million to 105 million, if there are close to 5 million cases in that range, it will take considerably longer. To find how many cases are in the intended range, run the following SQL:

| • | `SELECT COUNT(*) FROM CASE_INFORMATION WHERE CASENUM > v_casenum_min AND CASENUM < v_casenum_max`

| • | Based on previous runs and recorded timings, you might be able to predict the time `CASENUM_FIND_GAPS` will take for any given range with a reasonable amount of accuracy.

## See Also

| • | casenum_gaps

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO iProcess® Engine