Archiving the CREOLECASEDETERMINATIONDATA table

Merative ™ Social Program Management is now Cúram ™ by Merative™

Archiving guidance for the CREOLECASEDETERMINATIONDATA table.

Question & Answer

Question

Is there specific guidance for archiving the CREOLECASEDETERMINATIONDATA table?

Answer

The CREOLECaseDeterminationData table contains information relevant to determinations that have been performed. The table, and some information on reducing the rate at which the table increases in size, can be found on the Creole Case Determination Data page on the documentation.

Please be aware that data from this table can also be used for troubleshooting purposes.

The Merative ™ Social Program Management recommendation is that only snapshot data for non-current determinations be archived.

Data in the CreoleCaseDeterminationData table (and specifically the creoleSnapshotData column) is compressed and stored in blobs of up to 1000000 bytes long. If the data exceeds this size, then the excess is stored in another record which is referenced by the overflowID field, and so on in a linked list fashion. So each record in the CreoleCaseDeterminationData table is referenced by either:

• A record in the CreoleCaseDetermination table or
• Another record in the CreoleCaseDeterminationData table.

If it is decided to archive the snapshot data, the following example can be used as a basis for a site's specific approach.

This process describes how to save storage space in the database by blanking out the contents of column creoleSnapshotData for some records in table CREOLECaseDeterminationData. This does not delete any rows from the table, it only erases the contents of a 100000 byte Blob, having first copied them onto a backup table (CCDD_BAK in this example).
This is because:

(a) these records continue to be referenced by foreign key from the CreoleCaseDetermination and CreoleCaseDeterminationData table
(b) it enables the archived data to be reloaded from the backup table if needed, by ensuring that the record unique identifiers are preserved.

1) Merative Social Program Management recommends customers have an up to date backup taken.

2) In this example, a replica table named CCDD_BAK is used to contain the archived data, purely to demonstrate how the 'SELECT * FROM CreoleCaseDeterminationData...' clause should be used to find the appropriate records to copy. How the data is archived is a site specific concern.

# Create a table capable of holding records from the CreoleCaseDeterminationData table:
# NB this table will duplicate a large proportion of data from the source table, but is not used by the application therefore should not effect system performance.
CREATE TABLE ccdd_bak( CREOLECASEDETERMINATIONDATAID NUMBER(19,0) not null, CREOLESNAPSHOTDATA BLOB not null, OVERFLOWID NUMBER(19,0) );
ALTER TABLE ccdd_bak ADD CONSTRAINT ccdd_bak PRIMARY KEY(CREOLECASEDETERMINATIONDATAID);
3) Archive and blank the first record of each non-current determinations.

In this SQL, we deal with all records which are directly referred to by a superseded (non-current) CreoleCaseDetermination record.
If any of these records span into an overflow row, the overflow rows will be dealt with in a subsequent pass described in step 4 below.

# a) archive the FIRST entry of the snapshot Blobs for determinations which are not 'Current'.
# NB this is a large operation; it will copy one record per CreoleCaseDetermination record
INSERT INTO ccdd_bak
SELECT * FROM CreoleCaseDeterminationData ccdd WHERE ccdd.creoleCaseDeterminationDataID IN (
SELECT ruleObjectSnapshotDataID FROM CreoleCaseDetermination WHERE AssessmentStatus !='CDAS1');

# b) blank the snapshot data for the FIRST record in the chain (NB remember it needs to be saved to archive first)
UPDATE CreoleCaseDeterminationData ccdd SET creoleSnapshotData = '0' WHERE ccdd.creoleCaseDeterminationDataID IN (
SELECT ruleObjectSnapshotDataID FROM CreoleCaseDetermination WHERE AssessmentStatus !='CDAS1');

4) Archive and blank overflow records
These statements deal with the next position of the linked lists of CreoleCaseDeterminationData records and need to be run repeatedly until all the lists have been processed. i.e. the first time they are run they process all the second entries, the next time they are run they process all the third entries, and so on. Therefore the steps below need to be repeated until there are no more records to act upon.

# a) get the next record in each chain, and . Identify it by the fact that it is being pointed at by one which we nullified in the previous step:
# if this statement affects no records, then the archival process is complete.
INSERT INTO ccdd_bak
SELECT * FROM CreoleCaseDeterminationData ccdd WHERE ccdd.creoleSnapshotData != '0' AND ccdd.creoleCaseDeterminationDataID IN (
SELECT overflowID FROM CreoleCaseDeterminationData WHERE creoleSnapshotData = '0' );

# b) blank the next record in each chain.
UPDATE CreoleCaseDeterminationData ccdd SET creoleSnapshotData = '0' WHERE ccdd.creoleSnapshotData != '0' AND ccdd.creoleCaseDeterminationDataID IN (
SELECT overflowID FROM CreoleCaseDeterminationData WHERE creoleSnapshotData = '0' );


Notes:

It is valid for more than one CreoleCaseDetermination record to reference the same CreoleCaseDeterminationData record, so there may be fewer CreoleCaseDeterminationData rows.
It is possible to archive the full CreoleCaseDeterminationData snapshot row, but it is a little more complex. First the value of CreoleCaseDetermination.ruleObjectSnapshotDataID would need to be archived and then set to zero before archiving the full CreoleCaseDeterminationData record. This is because field CreoleCaseDetermination.ruleObjectSnapshotDataID is a foreign key which references CreoleCaseDeterminationData and therefore prevents the actual CreoleCaseDeterminationData record from being deleted.