19.20 Case Purge Utility

This section describes the Case Purge Utility.

Case Purge utility is meant for purging of cases, case-related entity tables, events inside the case and evented table data for those events. The input criteria for purging is Case IDs, Case Created Date Range, Case Last Updated Date Range, and Case Status.

To use case purge utility, perform the following steps:
  1. Defining Input Criteria

    The input criteria is defined in FCC_PURGE_CASE_INPUT in Atomic Schema. The criteria can be given in the following manner:

    • Particular Case IDs: If multiple, then give multiple rows
    • Created Date From and To
    • Last Updated Date From and To
    • Created Date From and To and Last Updated From and To

    Table 19-16 PURGE_CASE_INPUT Table

    Criteria Column Name
    Particular Case ID V_CASE_INTRL_ID
    Created Date From and To D_CREATED_DT_FRM, D_CREATED_DT_TO
    Last Updated Date From and To D_LAST_UPDATED_DT_FRM, D_LAST_UPDATED_DT_TO

    There are some columns in FCC_PURGE_CASE_INPUT which are only informational:

    • N_CASE_PUREG_SEQ_ID: Normal Numeric sequence for the filter/Criteria. Example:1 or 2
    • F_PURGE_SUCCESS_FLAG: This indicates the status of the Purge. It should be null or N for the filter or criteria to be picked up. After the purge is successfully completed it will automatically be updated to Y. If any error occurs during the purge it will be updated to E.
    • D_REQUESTED_DATE: This can be filled as the date when the filter criteria was inserted. It just kept for future use for informational purpose
    • V_USER_ID: This can be filled as the userid running the purge. It is just kept for future use for informational purposes.
    • D_PRCSNG_BATCH_DATE: This is updated by the case purge utility. It will be updated with the date the purge was run.

    The STATUS filter for the purge is captured in the CasePurgeConfig.cfg. The details are mentioned under the Configuration File section below.

  2. Configuration File (CasePurgeConfig.cfg)

    This file is available in the #FIC_HOME#/ficdb/conf folder. This contains the list of tables that has to be purged in the following format:

    ActualTableName:PurgeTableName:deleteType:deleteKey

    Eg: KDD_CASES:KDD_CASES_PE:directDelete:case_intrl_id

    The table-wise Hint and Global Hint Configurations are introduced to improve Insert/Delete Queries. If table-wise configuration is available, it will be used; else, Global Hint configuration will be used.

    • Table wise Hint Configuration
      • To optimize the Insert and Delete queries, the above format will additionally support the optimizer hint for insert and delete operations separated by semicolon (:).You can configure the required hint as per your optimizer:

        ActualTableName:PurgeTableName:deleteType:deleteKey:optimizerHintInsert:optimizerHintDelete

        E.g.: KDD_CASES:KDD_CASES_PE:directDelete:case_intrl_id:/*+ parallel(8) */:/*+ parallel(8) */

      • To optimize only for insert scripts, use the below format:

        ActualTableName:PurgeTableName:deleteType:deleteKey:optimizerHintInsert

        E.g.: KDD_CASES:KDD_CASES_PE:directDelete:case_intrl_id:/*+ parallel(8) */

      • To optimize only for delete scripts, use the below format:

        ActualTableName:PurgeTableName:deleteType:deleteKey::optimizerHintDelete

        E.g.: KDD_CASES:KDD_CASES_PE:directDelete:case_intrl_id::/*+ parallel(8) */

    • Global Hint Configuration

      The following configurations must be used to apply any global optimizer hint. You can set up the required hint as per your optimizer.

      • For Delete operation

        OptimizerHintDelGlobal: - To apply the optimizer hint to all the tables provided in cfg files for delete scripts.

        E.g. OptimizerHintDelGlobal:/*+ parallel(4) */

      • For Insert operation

        OptimizerHintInsGlobal:- To apply the optimizer hint to all the table’s information provided in cfg files for insert scripts

        E.g. OptimizerHintInsGlobal:/*+ parallel(4) */

        OptimizerHintUpdtCaseTemp:-To optimize insertion in the temp table FCC_PURGE_CASE_UPDTCNTINP_TEMP which is not in the cfg file but handled internally as part of the temp table population.

        E.g. OptimizerHintUpdtCaseTemp:/*+ parallel(4) */

    Following delete types are supported:

    • directDelete: Any table where data can be deleted directly (basically which has either case_intrl_id or n_event_correlation_skey or n_event_skey). For case_intrl_id since it appears with different names in different tables we have handled column names like case_intrl_id, v_case_id, parent_case_id . You can directly configure Direct Delete if on any of these columns mentioned for any new tables provided the new table and its purge table exists in the schema.
    • logicalDelete: Any table which requires join to find the case_intrl_id. For example: KDD_CASE_ACTION_NOTE. As of now we support logical deletion based on action_seq_id or note_id. In logical Delete, clients can configure delete for any new tables which is based on action_seq_id or note_id provided the new table and its purge table exists in the schema.
    • eventedEntityDelete: Any evented table which requires join on basis of mis_date,data_origin,entityskey,entitytype. The config should be given in following format ActualTableName:PurgeTableName:eventedEntityDelete:misdatecolumnname,dataorigincolumnname,skeycolumnname,entitytype.
      • Example: FCC_ACCT_EVNT:FCC_ACCT_EVNT_PE:eventedEntityDelete:mis_date,data_origin,account_skey,ACCOUNT .
      • Evented Entity Delete can be configured directly by the customer for any new tables provided the new table and its purge table exists in the schema.

    The configuration file also captures the STATUS filter to decide which case statuses should be picked. Statuses should be given in single quotes and multiple statuses should be comma-separated. For example: STATUS:'NW','INV'.

    The configuration file has the following additional configurations:

    • TESTMODE: If false, the temp tables created during purge will be dropped and the purge action will be committed. If true, the temp tables won’t be dropped and the purge won’t be committed. OOB value will be false. This flag is useful when analysis or debugging needs to be done by QA on which cases and events got picked for purge.
    • CheckPurgeTablesDM: To check the Data Model of CM and CM_PE tables to bring it in sync, we call "compare_tables" proc for this. The list of tables compared is picked from the TEMP_COMPARE_TABLES table in the atomic schema. This supports only alter that is creation of the column if it’s not present in the PE(Purge) table. It doesn’t support modification to existing columns or drop of columns. Also, it doesn’t support the creation of purge tables. OOB value will be true. If true it checks the data models. If false it doesn’t check.
    • BatchSize: This is used to do periodic batch executions based on the size. OOB value is 200.
  3. Important Temp tables created during Purge.

    Below temp tables are created during purge. These tables won’t be dropped when testmode is true and helps in analyzing which cases and events got picked up during purge.

    1. FCC_PURGE_CASE_INPUT_TEMP

      This table contains the eligible caseids for purge. The eligibility for delete is denoted by the f_delete_case column as Y. It also shows the number of events inside the case and also the event correlation skey.

    2. FCC_PURGE_CASE_EVNT_INPUT_TEMP

      This table contains the eligible eventids for purge. The eligibility for delete is denoted by f_delete_event column as Y.

    3. FCC_PURGE_CASE_EVNT_ENT_TEMP

      This table contains the eligible evented entities for purge.

  4. Execution

    After doing the configuration as mentioned above, trigger the <<ficdb>>/bin/CasePurge.sh.

    Logs are generated in <<ficdb>>/log/CasePurge/CasePurge.log. Insert and Delete Scripts for reference and informational purpose is generated with timestamp at <<ficdb>>/CasePurge.