Copyright © Cloud Software Group, Inc. All Rights Reserved
Copyright © Cloud Software Group, Inc. All Rights Reserved


Appendix C Database Stored Procedures : CASENUM_FIND_GAPS
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 is used to 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:
* 
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 case number 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.
•
It is recommended to have a good purge strategy to ensure that there are plenty of available case numbers for reuse.
•
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, it should be possible to predict the time CASENUM_FIND_GAPS will take for any given range with a reasonable amount of accuracy.
See Also
casenum_gaps
Copyright © Cloud Software Group, Inc. All Rights Reserved
Copyright © Cloud Software Group, Inc. All Rights Reserved