Archive Execution Process

To execute the archival process, follow the steps:

1. Before executing the archive jobs, run the SQL query:
   • io_msg_stats_monthly_v2.sql passing parameter as PREARCHIVE
2. Count the messages for the month you will be archiving by running the following script in both CISADM schema and ARCADM schema:

select sysdate from dual;

set time on;

select name from v$database;

SET SERVEROUTPUT ON;

SET DEFINE OFF;

set timing on;

SET SQLBLANKLINES ON

select bo_status_cd, count(*) cnt from cisadm.C1_INBOUND_MESSAGE WHERE trunc(CRE_DTTM) between to_date('2021-07-01','yyyy-mm-dd') and to_date('2021-07-31','yyyy-mm-dd') group by bo_status_cd;

3. Take screenshots of the output.
4. Execute the following tracker jobs to identify messages for archive:
   • ILM_RUN_INB_MSG_TRACKER_JOB
   • ILM_RUN_OUT_MSG_TRACKER_JOB
5. Run the SQL query:
   • io_msg_stats_monthly_v2.sql passing parameter as IDENTIFY
6. Execute the following migrate jobs to migrate the identified jobs:
   • ILM_RUN_INB_MSG_MIGRATE_JOB
   • ILM_RUN_OUT_MSG_MIGRATE_JOB
7. Run the SQL query:
   • io_msg_stats_monthly_v2.sql passing parameter as MIGRATE
8. Execute the following delete jobs to delete the identified jobs:
   • ILM_RUN_INB_MSG_DELETE_JOB
   • ILM_RUN_OUT_MSG_DELETE_JOB
9. Run the SQL query:
   • io_msg_stats_monthly_v2.sql passing parameter as DELETE
10. After executing the archive jobs, count the messages for the month you will be archiving by running the following script in both CISADM schema and ARCADM schema:

select sysdate from dual;

set time on;

select name from v$database;

SET SERVEROUTPUT ON;

SET DEFINE OFF;

set timing on;

SET SQLBLANKLINES ON

select bo_status_cd, count(*) cnt from cisadm.C1_INBOUND_MESSAGE WHERE trunc(CRE_DTTM) between to_date('2021-07-01','yyyy-mm-dd') and to_date('2021-07-31','yyyy-mm-dd') group by bo_status_cd

11. Take screenshots of the output.
12. Compare with the previous screenshot. The counts listed in the screenshot should match with the output of io_msg_stats_monthly_v2.sql.