Moving Data from RO to ARC

In this method, the decision making for archival of the record is based on the PDATE column in each of the main table in NVDB. This method does not require any additional validation to move or delete data from NVDB.

In this method, the decision making for archival of the record is based on the Temenos Transact archival rules for each of the NVDB table defined. A Temenos Transact routine confirms the eligibility of each PDATE column for archival after which the system archives or deletes the associated data in the partition as per the user configuration. This method requires business validation before moving or deleting the record from NVDB.

To enable data archiving in Oracle, you need to do the following:

1. Get the DL_SeparationTemplate pack from Distribution.
2. Execute the RO to ARC configuration scripts available in DL_SeparationTemplate\Scripts\TafjArtefacts\DLMFull\templates\oracle\RoToArc in the following order:
   • TableAndFunctionCreation
   • ROARCPartitionStatusProcedure
   • CloneTablePromotedColumnProcedure
   • ROARCExchangePartitionProcedure

To enable data archiving in Microsoft SQL (MSSQL), you need to do the following:

1. Get the DL_SeparationTemplate pack from Distribution.
2. Execute the RO to ARC configuration scripts available in DL_SeparationTemplate\Scripts\TafjArtefacts\DLMFull\templates\mssql\RoToArc in the following order:
   • TableCreation.sql
   • ValidatePartitionBoundariesProcedure.sql
   • ROARCPartitionStatusProcedure.sql
   • CloneTableProcedure.sql
   • CreateStagingTableProcedure.sql
   • RO-ScriptPromotedColumnFunctionArc.sql
   • CloneTablePromotedColumnProcedure.sql
   • CreateStagingTablePromotedColumnProcedure.sql
   • CheckPartitionEmptyProcedure.sql
   • ArchiveTablePartitionProcedure.sql
   • ArchivePartitionProcedure.sql
   • SlideWindowControlProcedure.sql
   • SlideWindowProcedure.sql
   • SlideWindowWrapperProcedure.sql

Based on the user-defined field values in DLM.RO.ARC.CONFIG, the system relates the same information with the table architectural information stored in the database metadata tables and verifies the boundaries of the appropriate tables configured.

• If the boundaries are in alignment with the current date, the system does nothing.
• If the boundaries are lesser than the current date, the system adds subsequent monthly partition until current date.

Based on the current partition boundaries and retention period, the data is stored in the RO_ARC_PARTITION_STATUS table.

The following screen capture displays the RO_ARC_PARTITION_STATUS table output as per the configuration in DLM.RO.ARC.CONFIG.

The system executes this procedure only on the successful completion of the previous schedule. Else, the system displays appropriate message and logs an entry in the RO_ARC_STATUS_REPORT table. The logging tables are created and this procedure is compiled in NVDB under respective schema in which the NVDB table is created.

The RO_ARC_PARTITION_STATUS table has partition wise records. The primary key for this table is the table name and partition name. The records from this table flagged as PARTITION_IDENTIFIED and ARCHIVAL_STATUS=’N’ are considered further to get all RECID, which fall under the respective partitions. Such records are stored in the F_RO_ARC_KEYLIST table.

The following screen capture displays the key list generated using the Trickle Feed method.

For efficient processing, a maximum of 100 RECID are stored in one row mapped against every partition. If there are more than 100 RECID, the system groups and stores them in the subsequent rows in a sequential manner.

Finally, you need to execute the stored procedure by passing the @ROTableSchema parameter, which indicates the schema name of the RO table (if known). The control table should be updated as COMPLETED after successful completion of the status procedure.

The DLM.VALIDATE.PARTITION service validates the records in F_RO_ARC_KEYLIST and decides whether the partition is eligible for movement. The eligible records in RO.ARC.PARTITION.STATUS are marked as ARCHIVAL.IDENTIFIED and ARCHIVAL.STATUS is set to Y. The RO.ARC.CONTROL table status is set to COMPLETED.

The following screen capture displays the F_RO_ARC_CONTROL table status after the service execution.

This procedure populates the partition scheme mapped to every table and logs them in the RO.SLIDE_WINDOW_CONTROL table.

The following table lists the parameters required for this procedure.

This procedure is executed only if the status of ROARCPartitionStatusProcedure.sql and DLM.VALIDATE.PARTITION service execution is marked as COMPLETED in the control table.

Data Movement

This activity uses the sliding window mechanism. There are twelve pre-defined procedural steps interlinked with each other and the SlideWindowWrapper main procedure invokes these steps. This procedure acts as a wrapper, which triggers the process to do the following:

• Switch partition between main and work table
• Purge or archive data from the work table
• Prepare the filegroup to accept new boundaries
• Split the right most partition based on a new boundary
• Merge the old partition with the new boundary

The SlidingWindowWrapper procedure moves forward the partitions by a month and archives all the records in the oldest partition of every table in the partition scheme classified as ARCHIVAL_IDENTIFIED and ARCHIVAL_STATUS=’Y’. This further loops all the identified tables or partition schemes and requires no additional parameters. The EXECUTE [RO] SlideWindowWrapper command executes this procedure. This command pre-validates the F_RO_ARC_CONTROL table for records with COMPLETED status.

The sliding window procedure creates #ARC table including the promoted columns, if not existing already and moves or deletes the data as per the definition in DLM.RO.ARC.CONFIG.

The following screen captures display the results of data movement.

• Data count after the procedure execution in #ARC.

• Count in #RO table all the records moved to #ARC.

• Data count after the procedure execution in #ARC.

• Data deleted from #RO as per the configuration in DLM.RO.ARC.CONFIG.