5.2.1.15 Partitioning IPE Tables

Partitioning Inline processing Engine (IPE) tables is done to prevent the IPE batch from continuously running and thus help with performance. Since IPE tables add up data quickly, the batches run continuously.

To partition IPE tables, follow these steps:

  1. Execute the following statements to drop and recreate (with partition) the 3 IPE results tables:

    Drop Table RTI_ASSMNT_EVAL_RESULT; CREATE TABLE RTI_ASSMNT_EVAL_RESULT ( N_RUN_IDNUMBER(22) , N_BATCH_IDNUMBER(22) ,

    N_TASK_IDVARCHAR2(100 CHAR) , N_START_TIMETIMESTAMP , N_ASSMNT_EVAL_RESULT_ID VARCHAR2(3800 CHAR) , N_ASSMNT_RESULT_ID NUMBER(22) ,

    N_EVAL_IDNUMBER(22) ,

    N_EVAL_VERSIONNUMBER(22) DEFAULT 0 , N_EVAL_SCORENUMBER(22, 2) , V_EVAL_FLAGVARCHAR2(100 CHAR ), D_EVAL_TMTIMESTAMP , N_ENTITY_SEQ_ID VARCHAR2(3500 CHAR) , N_ACTIVITY_BUS_IDNUMBER(22) , N_ASSMT_IDNUMBER(22)

    ,

    V_THRESHOLDVARCHAR2(100 CHAR),

    V_INFODOMVARCHAR2(100 CHAR ) , V_BATCH_RUN_IDVARCHAR2(200 CHAR ) , V_BATCH_ASSMNT_RES_IDVARCHAR2(4000 CHAR ), N_ASSMT_RES_EXT_REF_ID NUMBER(22),

    V_APP_ID VARCHAR2 (20 CHAR) DEFAULT 'OFS_IPE' NOT NULL

    )PARTITION BY LIST (V_APP_ID) SUBPARTITION BY LIST (V_BATCH_RUN_ID) ( PARTITION DEFAULT_PART VALUES (DEFAULT) (

    SUBPARTITION DEFAULT_SUBPART VALUES (DEFAULT)

    )

    );

    Drop Table RTI_ASSMNT_RESULT; CREATE TABLE RTI_ASSMNT_RESULT ( N_RUN_IDNUMBER(22) , N_BATCH_IDNUMBER(22) ,

    N_TASK_IDVARCHAR2(100 CHAR) , N_START_TIMETIMESTAMP , N_ASSMNT_RESULT_ID NUMBER(22) ,

    N_ASSMT_IDNUMBER(22) NOT NULL , N_ASSMNT_VERSIONNUMBER(22) DEFAULT 0 , N_ASSMNT_SCORENUMBER(22, 2) , N_ENTITY_SEQ_IDVARCHAR2(3500 CHAR) , D_ASSMNT_EXEC_TMTIMESTAMP , V_ERROR_CODEVARCHAR2(10 CHAR) , V_ERROR_MSG VARCHAR2(500 CHAR) , N_ACTIVITY_BUS_IDNUMBER(22) , V_ASSMNT_EXEC_MODE VARCHAR2(10 CHAR) , V_ASSMNT_EXEC_RESULTVARCHAR2(10 CHAR) , N_ALERT_ID NUMBER(22) ,

    V_THRESHOLDVARCHAR2(100 CHAR),

    V_INFODOMVARCHAR2(100 CHAR ) , V_BATCH_RUN_IDVARCHAR2(200 CHAR ) , V_BATCH_ASSMNT_RES_IDVARCHAR2(4000 CHAR ), N_ASSMT_RES_EXT_REF_ID NUMBER(22),

    V_APP_ID VARCHAR2 (20 CHAR) DEFAULT 'OFS_IPE' NOT NULL

    )PARTITION BY LIST (V_APP_ID) SUBPARTITION BY LIST (V_BATCH_RUN_ID) ( PARTITION DEFAULT_PART VALUES (DEFAULT) (

    SUBPARTITION DEFAULT_SUBPART VALUES (DEFAULT)

    )

    );

    Drop Table RTI_ASSMNT_EVAL_EXPORT_DATA; CREATE TABLE RTI_ASSMNT_EVAL_EXPORT_DATA (

    N_RUN_ID NUMBER(22,0), N_BATCH_ID NUMBER(22,0), N_TASK_ID VARCHAR2(100 CHAR), N_EVAL_ID NUMBER(22,0),

    N_EVAL_VERSION NUMBER(22,0) DEFAULT 0, N_ENTITY_SEQ_ID VARCHAR2(3500 CHAR), N_ACTIVITY_BUS_ID NUMBER(22,0), N_ASSMT_ID NUMBER(22,0),

    V_INFODOM VARCHAR2(100 CHAR), V_BATCH_RUN_ID VARCHAR2(200 CHAR), V_APP_ID VARCHAR2(20 CHAR) DEFAULT 'OFS_IPE' NOT NULL ,

    v_export_DATA clob

    PARTITION BY LIST (V_APP_ID) SUBPARTITION BY LIST (V_BATCH_RUN_ID) ( PARTITION DEFAULT_PART VALUES (DEFAULT) (

    SUBPARTITION DEFAULT_SUBPART VALUES (DEFAULT)

    )

    );

  2. To create and drop partition tasks as part of the Regular Processing Batch, follow these steps:
    1. Open the IPEKYCRun run in edit mode, click Selector drop-down, and select Job.
    2. On the Left Hand Side (LHS) of the pop-up, look for KYC_IPE_TAB_CRT_PARTITION_DLY under Processes and move that component to Right Hand Side (RHS).
    3. Select the KYC_IPE_TAB_CRT_PARTITION_DLY component check box in the RHS and move it up to make it the first task.
    4. On the LHS of the pop-up, look for KYC_IPE_DROP_PARTITION_DLY under Processes and move that component to RHS.
    5. Select the KYC_IPE_DROP_PARTITION_DLY component check box in the RHS and move it down to make it the last task.
    6. Click OK to close the pop-up.
    7. Click Save.
    8. Click Run.
  3. To Create and Drop partition tasks as part of the Deployment Initiation Batch, follow these steps:
    1. Open the IPEKYCRunDI run in edit mode, click Selector drop-down, and select Job.
    2. On the LHS of the pop-up, look for KYC_IPE_TABLE_CREATE_PARTITION under Processes and move that component to RHS.
    3. Select the KYC_IPE_TABLE_CREATE_PARTITION component metadata in the RHS and move it up to make it the first task.
    4. On the LHS of the pop-up, look for KYC_IPE_DROP_PARTITION under Processes and move that component to RHS.
    5. Select the KYC_IPE_DROP_PARTITION component check box in the RHS and move it down to make it the last task.
    6. Click OK to close the pop-up.
    7. Click Save.
    8. Click Run.