ILM Database Administrator’s Tasks

For a database administrator, there are two key phases involved with managing your data using ILM.

• Preparation Phase - This phase covers the database level configuration that needs to be done before the ILM solution runs in a production environment.

• On-going Maintenance Phase - This phase covers the ongoing maintenance tasks.

Preparation Phase

Note: In order to successfully implement ILM as described here, the following DB Version and Patch are pre-requisites: database version 19.3.0.0 or newer.

The steps needed to enable ILM functionality differ depending on whether ILM is enabled as part of the initial implementation of the product or enabled ILM on an existing implementation where data already exists in the respective tables.

• Initial Install – For an initial installation, the section Module Specific ILM Implementation Details outlines the additional steps to be performed on base delivered ILM Enabled Tables to conform to ILM requirements. In addition, Appendix E: Sample SQL for Enabling ILM in C2M for MDM (Initial Install) provides sample reference DDLs using two maintenance objects as examples.

• Transform NON-ILM implementation to ILM Enabled Implementation: The following steps provide a high level overview of steps that must be performed to implement ILM on enabled maintenance objects for an existing implementation. The Appendix F: Sample SQL For Enabling ILM in C2M for MDM (Existing Installation) provides detailed information using To Do Entry as an example. Also refer to Appendix C: Sample SQL for Enabling ILM with Sub Retention in MDM (Existing Installation) or detailed information using D1_INIT_MSRMT_DATA as an example.

1. Rename the existing tables (Parent table followed by child table), and primary key index associated with ILM enabled maintenance objects by renaming the tables.

2. Save the DDLs for the secondary indexes as you will need to recreate them later.

3. Drop secondary indexes on the renamed tables.

4. Create Partitioned table with no secondary indexes for ILM enabled maintenance objects using a CTAS operation (Create Table as Select), which will also load the data into the partitioned table structure.

Functional Note: ILM enabled maintenance objects should have the ILM date (ILM_DT) populated when data is moved into the new partitioned table. Please refer to the Module Specific ILM Implementation Details section below for initial load details on which date column to use as the basis for populating the ILM date. Often it is based on Create Date (CRE_DTTM). ILM_ARCH_SW should initially be set to ‘N’.

Note: Certain ILM enabled maintenance objects, specifically IMD, Device Event, and Activity, support more than one retention period also known as sub retention periods. For these maintenance objects the table will be sub-partitioned based on the retention period. Furthermore, a more detailed approach will be required to set both the ILM date (ILM_DT) and the retention period (<field name>). If your implementation does not wish to leverage the ability to define multiple retention periods for these maintenance objects, this note can be ignored and the general guidelines for ILM enablement can be followed. If your implementation wishes to leverage the multiple retention period capability then please refer to the section Module Specific ILM Implementation Details For Sub Retention below.

5. Enable logging option.

6. Create Primary Key index.

7. Create Primary Key Constraint of parent table.

8. Create secondary indexes for the newly-created partitioned tables. This includes creating an index used specifically to benefit the ILM Crawler batch. The recommendation for this index name is to prefix it with “ILM”.

Note: This can be created specifying parallel index create; remember to turn off parallelism after the index is created.

9. Follow a similar operation for all child tables for this maintenance object, such as rename child table, and primary key index, generate DDL for secondary index, drop secondary index etc. Sample DDL for child tables their partitioning and indexes can be found in Appendix F: Sample SQL For Enabling ILM in C2M for MDM (Existing Installation). If sub retention is supported, sample DDL for child tables can be found in Appendix G: Sample SQL for ILM in C2M with Sub Retention (Existing Installation). Please note that child table should be partitioned using reference partitioning of the parent table’s partitioning key.

10. Drop the original, renamed tables after verifying the newly created partitioned tables.

11. If sub-retention is not supported, create the ILM specific indexes from section Module Specific ILM Implementation Details.

Table Name	Index Name
CI_TD_ENTRY	CM_ILM_XT039S8
D1_ACTIVITY	CM_ILM_D1T319S1
D1_COMM_IN	CM_ILM_D1T386S1
D1_COMM_OUT	CM_ILM_D1T380S1
D1_COMPL_EVT	CM_ILM_D1T340S1
D1_DVC_EVT	CM_ILM_D1T400S4
D1_INIT_MSRMT_DATA	CM_ILM_D1T304S4
D1_USAGE	CM_ILM_D1T281S2
D1_USAGE_EXCP	CM_ILM_D1T443S1
D1_VEE_EXCP	CM_ILM_D1T308S2
D1_SP_SNAP_DL	CM_ILM_D1T434S1
D1_SP_UNR_USG_SNAP_DL	CM_ILM_D1T438S1
D1_SP_USG_SNAP_DL	CM_ILM_D1T436S1
D1_SP_VEE_EXCP_SNAP_DL	CM_ILM_D1T440S1
F1_BUS_FLG	CM_ILM_F1T681S2
F1_ERASURE_SCHED	CM_ILM_F1T756S1
F1_OBJ_REV	CM_ILM_FT035S6
F1_OUTMSG	CM_ILM_FT010S2
F1_PROC_STORE	CM_ILM_F1T747S1
F1_REMOTE_MSG	CM_ILM_F1T735S7
F1_STATS_SNPSHT	CM_ILM_F1C706S2
F1_SVC_TASK	CM_ILM_F1C474S3
F1_SYNC_REQ	CM_ILM_F1T014S4
F1_SYNC_REQ_IN	CM_ILM_F1T191S3

12. If sub-retention is supported, create the following ILM specific indexes from the Module Specific ILM Implementation Details section:

Table Name	Index Name
CI_TD_ENTRY	CM_ILM_XT039S8
D1_COMM_IN	CM_ILM_D1T386S1
D1_COMM_OUT	CM_ILM_D1T380S1
D1_COMPL_EVT	CM_ILM_D1T340S1
D1_USAGE	CM_ILM_D1T281S2
D1_USAGE_EXCP	CM_ILM_D1T443S1
D1_VEE_EXCP	CM_ILM_D1T308S2
D1_SP_SNAP_DL	CM_ILM_D1T434S1
D1_SP_UNR_USG_SNAP_DL	CM_ILM_D1T438S1
D1_SP_USG_SNAP_DL	CM_ILM_D1T436S1
D1_SP_VEE_EXCP_SNAP_DL	CM_ILM_D1T440S1
F1_BUS_FLG	CM_ILM_F1T681S2
F1_ERASURE_SCHED	CM_ILM_F1T756S1
F1_OBJ_REV	CM_ILM_FT035S6
F1_OUTMSG	CM_ILM_FT010S2
F1_PROC_STORE	CM_ILM_F1T747S1
F1_REMOTE_MSG	CM_ILM_F1T735S7
F1_STATS_SNPSHT	CM_ILM_F1C706S2
F1_SVC_TASK	CM_ILM_F1C474S3
F1_SYNC_REQ	CM_ILM_F1T014S4
F1_SYNC_REQ_IN	CM_ILM_F1T191S3

and the ILM subretention specific indexes from the Module Specific ILM Implementation Details For Sub Retention section:

Table Name	Index Name
D1_ACTIVITY	CM_ILM_D1T319S1
D1_DVC_EVT	CM_ILM_D1T400S4
D1_INIT_MSRMT_DATA	CM_ILM_D1T304S4

Module Specific ILM Implementation Details

This section outlines each maintenance object that has been configured to support ILM. The parent table is noted. Other tables are child tables of the parent unless otherwise noted. In each case, the partitioning strategy is indicated.

All indexes are listed with a recommendation whether the index should be global or local and whether the index should be partitioned. In addition to the base delivered indexes, each parent table includes a recommended ILM specific local index to build with the ILM_DT, ILM_ARCH_SW and the primary key of the table. The recommended column that should be used to populate the ILM_DT is also shown.

This section details the following maintenance objects:

• To Do Entry

• Sync Request (Outbound)

• Inbound Sync Request

• Outbound Message

• Service Task

• Object Revision

• Business Flag

• Remote Message

• Statistics Snapshot

• Object Erasure

• Process Flow

• Activity

• Communication In

• Communication Out

• Device Event

• Completion Event

• Initial Measurement Data

• Usage Transaction

• Usage Transaction Exception

• VEE Exception

• Snapshot Tables

To Do Entry

This table describes the To Do Entry maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
CI_TD_ENTRY (Parent)	RANGE (ILM_DT, TD_ENTRY_ ID)					CI_TD_ENTRY. CRE_DTTM
		XT039P0	TD_ENTRY_ID	Global Partitioned	RANGE (TD_ENTRY_ID)
		XT039S2	ASSIGNED_TO, TD_ENTRY_ID	Global
		XT039S3	ENTRY_STATUS_FLG, ASSIGNED_TO	Global
		XT039S4	ROLE_ID, TD_TYPE_CD, ENTRY_STATUS_FLG, TD_PRIORITY_FLG, ASSIGNED_TO, CRE_DTTM	Global
		XT039S5	BATCH_CD, BATCH_NBR, ENTRY_STATUS_FLG	Global
		XT039S6	TD_ENTRY_ID, ASSIGNED_TO, ENTRY_STATUS_FLG	Global
		XT039S7	COMPLETE_USER_ID, COMPLETE_DTTM, TD_ENTRY_ID	Global
		CM_ILM_XT039S8	ILM_DT, ILM_ARCH_SW, TD_ENTRY_ID	Local Partitioned
CI_TD_ENTRY_CHA	Reference Partitioning	XT701P0	TD_ENTRY_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		XT701S1	SRCH_CHAR_VAL, CHAR_TYPE_CD, TD_ENTRY_ID	Global
		XT701S2	CHAR_VAL_FK1	Global
CI_TD_DRLKEY	Reference Partitioning	XT037P0	TD_ENTRY_ID, SEQ_NUM	Global Partitioned
		XT037S1	KEY_VALUE, TD_ENTRY_ID	Global
CI_TD_LOG	Reference Partitioning	XT721P0	TD_ENTRY_ID, SEQ_NUM	Global Partitioned
		XT721S1	LOG_DTTM,USER_ID, LOG_TYPE_FLG, TD_ENTRY_ID	Global
CI_TD_MSG_ PARM (Child table of CI_TD_LOG)	Reference Partitioning	XT040P0	TD_ENTRY_ID, SEQ_NUM	Global Partitioned
CI_TD_SRTKEY	Reference Partitioning	XT041P0	TD_ENTRY_ID, SEQ_NUM	Global Partitioned
		XT041S1	KEY_VALUE, TD_ENTRY_ID	Global

Sync Request (Outbound)

This table describes the Sync Request (Outbound) maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_SYNC_REQ (Parent)	RANGE (ILM_DT, F1_SYNC_REQ_ ID)				RANGE (F1_SYNC_REQ_ID)	F1_SYNC_REQ.CRE_DTTM
		F1T014P0	F1_SYNC_REQ_ID	Global Partitioned
		F1T014S1	BO_STATUS_CD, BUS_OBJ_CD, F1_SYNC_REQ_ID	Global
		F1T014S2	BO_STATUS_ REASON_CD	Global
		F1T014S3	MAINT_OBJ_CD, PK_VALUE1, PK_VALUE2, F1_SYNC_REQ_ID	Global
		CM_ILM_F1T014S4	ILM_DT, ILM_ARC_SW, F1_SYNC_REQ_ID	Local Partitioned
F1_SYNC_REQ_CHAR	Reference Partitioning	F1T017P0	F1_SYNC_REQ_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		F1T017S1	SRCH_CHAR_VAL	Global
F1_SYNC_REQ_EXTRACT	Reference Partitioning	F1T019P0	F1_SYNC_REQ_ID, SEQ_NUM	Global Partitioned
F1_SYNC_REQ_LOG	Reference Partitioning	F1T015P0	F1_SYNC_REQ_ID, SEQNO	Global Partitioned
		F1T015S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global
		F1T015S2	CHAR_TYPE_CD, CHAR_VAL	Global
		F1T015S3	BO_STATUS_REASON_CD	Global
F1_SYNC_REQ_LOG_PARM (Child Table of F1_SYNC_REQ_LOG_PARM)	Reference Partitioning	F1T016P0	F1_SYNC_REQ_ID, SEQNO, PARM_SEQ	Global Partitioned

Note: It is recommended that data retention policies and rules for this object match the policies and rules implemented for the Inbound Sync Request on the target system to avoid data inconsistencies when auditing.

Inbound Sync Request

This table describes the Inbound Sync Request maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_SYNC_REQ_IN (Parent)	RANGE(ILM_DT, F1_SYNC_REQ_IN_ID)				RANGE (F1_SYNC_REQ_IN_ID)	F1_SYNC_REQ_IN.CRE_DTTM
		F1T191P0	F1_SYNC_REQ_ IN_ID	Global Partitioned
		F1T191S1	BO_STATUS_CD, BUS_OBJ_CD, F1_SYNC_REQ_ IN_ID	Global
		F1T191S2	MAINT_OBJ_CD, EXT_PK_VALUE1, NT_XID_CD, PK_VALUE1	Global
		CM_ILM_F1T191S3	ILM_DT, ILM_ARCH_SW, F1_SYNC_REQ_IN_ID	Local Partitioned
F1_SYNC_REQ_ IN_CHAR	Reference Partitioning	F1T193P0	F1_SYNC_REQ_IN_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		F1T193S1	SRCH_CHAR_VAL	Global
F1_SYNC_REQ_ IN_EXCP	Reference Partitioning	F1T197P0	F1_SYNC_REQ_IN_ID, SEQNO	Global Partitioned
F1_SYNC_REQ_ IN_EXCP_PARM (Child Table of F1_SYNC_REQ_IN_EXCP)	Reference Partitioning	F1T198P0	F1_SYNC_REQ_IN_ID, SEQNO, PARM_SEQ	Global Partitioned
F1_SYNC_REQ_ IN_LOG	Reference Partitioning	F1T194P0	F1_SYNC_REQ_IN_ID, SEQNO	Global Partitioned
		F1T194S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global
		F1T194S2	CHAR_TYPE_CD, CHAR_VAL	Global
F1_SYNC_REQ_IN_LOG_PARM (Child Table of F1_SYNC_REQ_IN_LOG)	Reference Partitioning	F1T195P0	F1_SYNC_REQ_IN_ID, SEQNO, PARM_SEQ	Global Partitioned
F1_SYNC_REQ_IN_REL_OBJ	Reference Partitioning	F1T192P0	F1_SYNC_REQ_IN_ID, MAINT_OBJ_CD, REL_OBJ_TYPE_FLG	Global Partitioned

Note: It is recommended that data retention policies and rules for this object match the policies and rules implemented for the Outbound Sync Request on the source system to avoid data inconsistencies when auditing.

Outbound Message

This table describes the Outbound Message maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_OUTMSG (Parent)	RANGE (ILM_DT, OUTMSG_ID)				RANGE (OUMSG_ID)	F1_OUTMSG. CRE_DTTM
		FT010P0	OUTMSG_ID	Global Partitioned
		FT010S1	OUTMSG_STATUS_FLG, OUTMSG_TYPE_CD	Global
		CM_ILM_FT010S2	ILM_DT, ILM_ARC_SW, OUTMSG_ID	Local Partitioned
F1_OUTMSG_ ERRPARM	Reference Partitioning	FT011P0	OUTMSG_ID, PARM_SEQ	Global Partitioned

Service Task

This table describes the Service Task maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_SVC_TASK (Parent)	RANGE (ILM_DT, F1_SVC_TASK_ID)				RANGE (F1_SVC_TASK_ ID_)	F1_SVC_TASK. CRE_DTTM
		F1C474P0	F1_SVC_TASK_ID	Global Partitioned
		F1C474S1	F1_STASK_TYPE_CD	Global
		F1C474S2	BUS_OBJ_CD	Global
		CM_ILM_F1C474S2	ILM_DT, ILM_ARC_SW, F1_SVC_TASK_ID	Local Partitioned
F1_SVC_TASK_ CHAR	Reference Partitioning	F1C476P0	F1_SVC_TASK_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		F1C476S1	SRCH_CHAR_VAL	Global
F1_SVC_TASK_ LOG	Reference Partitioning	F1C477P0	F1_SVC_TASK_ID, SEQNO	Global Partitioned
		F1C477S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global
		F1C477S2	CHAR_TYPE_CD, CHAR_VAL	Global
F1_SVC_TASK_ LOG_PARM (Child Table of F1_SVC_TASK_ LOG)	Reference Partitioning	F1C478P0	F1_SVC_TASK_ID, SEQNO, PARM_SEQ	Global Partitioned
F1_SVC_TASK_ REL_OBJ	Reference Partitioning	F1C479P0	F1_SVC_TASK_ID, MAINT_OBJ_CD, SEQ_NUM	Global Partitioned
		F1C479S1	MAINT_OBJ_CD, PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5	Global

Object Revision

This table describes the Object Revision maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_OBJ_REV (Parent)	RANGE (ILM_DT, REV_ID)				RANGE (REV_ID)	F1_OBJ_REV. STATUS_UPD_DTTM
		FT035P0	REV_ID	Global Partitioned
		FT035S1	BO_STATUS_CD, BUS_OBJ_CD, REV_ID	Global
		FT035S2	MAINT_OBJ_CD, PK_VALUE1	Global
		FT035S3	EXT_REFERENCE_ID, MAINT_OBJ_CD	Global
		FT035S4	USER_ID, MAINT_OBJ_CD	Global
		FT035S5	PK_VALUE1	Global
		CM_ILM_ FT035S6	ILM_DT, ILM_ARC_SW, REV_ID	Local Partitioned
F1_OBJ_REV_ CHAR	Reference Partitioning	FT037P0	REV_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		FT037S1	SRCH_CHAR_VAL	Global
F1_OBJ_REV_ LOG	Reference Partitioning	FT039P0	REV_ID, SEQNO	Global Partitioned
F1_OBJ_REV_ LOG_PARM (Child Table of F1_OBJ_REV_ LOG)	Reference Partitioning	FT040P0	REV_ID, SEQNO, PARM_SEQ	Global Partitioned

Note: This maintenance object is enabled for ILM, however it is not used in a production environment. It is typically used in a development or configuration environment. Your implementation should review its use of this functionality and consider whether or not it is a candidate for ILM and in which region.

Business Flag

This table describes the Business Flag maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_BUS_FLG (Parent)	RANGE (ILM_DT,BUS_FLG_ID)				RANGE(BUS_FLG_ID)	F1_BUS_FLG. CRE_DTTM
		F1T681P0	BUS_FLG_ID	Global Partitioned
		F1T681S1	BUS_OBJ_CD, BO_STATUS_CD, BUS_FLG_ID	Global
		CM_ILM_ F1T681S2	ILM_DT, ILM_ARCH_SW, BUS_FLG_ID	Local Partitioned
F1_BUS_FLG_ CHAR	Reference Partitioning	F1T684P0	BUS_FLG_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		F1T684S0	SRCH_CHAR_VAL	Global
F1_BUS_FLG_ LOG	Reference Partitioning	F1T685P0	BUS_FLG_ID, SEQNO	Global Partitioned
		F1T685S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global
		F1T685S2	CHAR_TYPE_CD, CHAR_VAL	Global
F1_BUS_FLG_ LOG_PARM	Reference Partitioning	F1T686P0	BUS_FLG_ID, SEQNO, PARM_SEQ	Global Partitioned
F1_BUS_FLG_ REL	Reference Partitioning	F1T682P0	BUS_FLG_ID, BUS_FLG_REL_ TYPE_FLG, SEQ_NUM	Global Partitioned
F1_BUS_FLG_ REL_OBJ	Reference Partitioning	F1T683P0	BUS_FLG_ID, BUS_FLG_REL_ OBJ_TYPE_FLG, SEQ_NUM	Global Partitioned

Remote Message

This table describes the Remote Message maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_REMOTE_ MSG (Parent)	RANGE (ILM_DT,F1_REMOTE_MSG_ID)				RANGE(F1_REMOTE_MSG_ID)	F1_REMOTE_MSG.CRE_DTTM
		F1T735P0	F1_REMOTE_MSG _ID	Global Partitioned
		F1T735S1	CRE_DTTM	Global
		F1T735S2	F1_MDT_ID	Global
		F1T735S3	MAINT_OBJ_CD	Global
		F1T735S4	PK_VALUE1	Global
		F1T735S5	F1_DEVICE_MSG_ID	Global
		F1T735S6	F1_MDT_ID, F1_MSG_CLASS_ FLG, F1_DELIVERY_ STATE_FLG	Global
		CM_ILM_ F1T735S7	ILM_DT, ILM_ARCH_SW, F1_REMOTE_MSG_ID	Local Partitioned
F1_REMOTE_MSG_CHAR	Reference Partitioning	F1T736P0	F1_REMOTE_MSG_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		F1T736S1	SRCH_CHAR_VAL	Global
F1_REMOTE_ MSG_LOG	Reference Partitioning	F1T737P0	F1_REMOTE_MSG_ID, SEQNO	Global Partitioned
		F1T737S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global
		F1T737S2	CHAR_TYPE_CD, CHAR_VAL	Global
F1_REMOTE_ MSG_LOG_ PARM	Reference Partitioning	F1T738P0	F1_REMOTE_MSG_ID, SEQNO, PARM_SEQ	Global Partitioned

Statistics Snapshot

This table describes the Statistics Snapshot maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_STATS_ SNPSHT( Parent)	RANGE (ILM_DT, SNAPSHOT_ID)				RANGE (SNAPSHOT_ID)	F1_STATS_ SNPSHT. CRE_DTTM
		F1C706P0	SNAPSHOT_ID	Global Partitioned
		F1C706S1	BUS_OBJ_CD, BO_STATUS_CD, SNAPSHOT_ID	Global
		CM_ILM_ F1C706S2	ILM_DT, ILM_ARCH_SW, SNAPSHOT_ID	Local Partitioned
F1_STATS_ SNPSHT_CHAR	Reference Partitioning	F1C707P0	SNAPSHOT_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
		F1C707S1	SRCH_CHAR_VAL	Global
F1_STATS_ SNPSHT_LOG	Reference Partitioning	F1C708P0	SNAPSHOT_ID, SEQNO	Global Partitioned
		F1C708S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global
		F1C708S2	SNAPSHOT_ID, SEQNO, PARM_SEQ	Global Partitioned
F1_STATS_ SNPSHT_REL_ OBJ	Reference Partitioning	F1C710P0	SNAPSHOT_ID, STATS_SNPSHT_ REL_OBJ_TYPE_ FLG, SEQ_NUM	Global Partitioned

Object Erasure

This table describes the Object Erasure maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_ERASURE_SCHED (Parent)	RANGE (ILM_DT, ERASURE_SCHED_ID)					F1_ERASURE_ SCHED.STATUS_UPD_DTTM
		F1T756P0	ERASURE_SCHED_ID	GLOBAL Partitioned	RANGE (ERASURE_SCHED_ID)
		CM_ILM_ F1T756S1	ILM_DT, ILM_ARCH_SW, ERASURE_SCHED_ID	LOCAL Partitioned
F1_ERASURE_SCHED_LOG	Reference partitioning	F1T757P0	ERASURE_SCHED_ID, SEQNO	GLOBAL Partitioned
F1_ERASURE_SCHED_LOG_PARM	Reference partitioning	F1T758P0	ERASURE_SCHED_ID, SEQNO, PARM_SEQ	GLOBAL Partitioned

Process Flow

This table describes the Process Flow maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
F1_PROC_STORE (Parent)	RANGE(ILM_ DT, PROC_ STORE_ID)					F1_PROC_STORE.STATUS_UPD_DTTM
		F1T747P0	PROC_STORE_ID	GLOBAL Partitioned	RANGE(PROC_ STORE_ID)
		CM_ILM_ F1T747S1	ILM_DT, ILM_ARCH_SW, PROC_STORE_ID	LOCAL Partitioned
F1_PROC_STORE_DTL_ELEMENTS	Reference partitioning	F1T748P0	PROC_STORE_ID, CHAR_TYPE_CD, SEQ_NUM	GLOBAL Partitioned
F1_PROC_ STORE_LOG	Reference partitioning	F1T749P0	PROC_STORE_ID, SEQNO	GLOBAL Partitioned
F1_PROC_STORE_LOG_PARM	Reference partitioning	F1T750P0	PROC_STORE_ID SEQNO, PARM_SEQ	GLOBAL Partitioned

Activity

If sub retention periods will be defined for this MO, then please follow the guidelines set forth in section Module Specific ILM Implementation Details For Sub Retention.

This table describes the Activity maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_ACTIVITY (Parent)	RANGE (ILM_DT, D1_ACTIVITY_ID) Note: Default is to use sub-retention or use RANGE (ILM_DT, D1_ACTIVITY_ID) if not using sub-retention.					D1_ACTIVITY. CRE_DTTM
		D1T319P0	D1_ACTIVITY_ID	Global Partitioned	RANGE (D1_ACTIVITY_ID)
		D1T319S0	BUS_OBJ_CD, BO_STATUS_CD, D1_ACTIVITY_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, D1_ACTIVITY_ID)
		CM_ILM_D1T319S1	ILM_DT, ILM_ARCH_SW, D1_ACTIVITY_ID	Local Partitioned
D1_ACTIVITY_ CHAR	REFERENCE (D1_ACTIVITY_ CHAR_FK)
		D1T320P0	D1_ACTIVITY_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T320S0	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_ACTIVITY_ IDENTIFIER	REFERENCE (D1_ACTIVITY_IDENTIFIER_FK)
		D1T330P0	D1_ACTIVITY_ID, ACTIVITY_ID_TYPE_ FLG	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T330S0	ACTIVITY_ID_TYPE_ FLG, ID_VALUE	Global Partitioned	HASH(ACTIVITY_ID_TYPE_FLG, ID_VALUE)
		D1T330S1	ACTIVITY_ID_TYPE_FLG, UPPER(ID_VALUE)
D1_ACTIVITY_ LOG	REFERENCE (D1_ACTIVITY_ LOG_FK)
		D1T321P0	D1_ACTIVITY_ID, SEQNO	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T321S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_TYPE_CD, CHAR_VAL_FK1)
		D1T321S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_TYPE_CD, CHAR_VAL)
D1_ACTIVITY_ LOG_PARM	REFERENCE (D1_ACTIVITY_ LOG_PARM_FK)
		D1T322P0	D1_ACTIVITY_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
D1_ACTIVITY_ REL	REFERENCE (D1_ACTIVITY_ REL_FK)
		D1T323P0	D1_ACTIVITY_ID, ACTIVITY_REL_TYPE_ FLG	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T323S0	REL_ACTIVITY_ID	Global Partitioned	HASH(REL_ ACTIVITY_ID)
D1_ACTIVITY_ REL_OBJ	REFERENCE (D1_ACTIVITY_ REL_OBJ_FK)
		D1T324P0	D1_ACTIVITY_ID, MAINT_OBJ_CD, ACTIVITY_REL_OBJ_ TYPE_FLG	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T324S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1,PK_VALUE2, PK_VALUE3, PK_VALUE4)

Communication In

This table describes the Communication In maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_COMM_IN (Parent)	RANGE(ILM_DT, D1_COMM_ID)					D1_COMM_IN. CRE_DTTM
		D1T386P0	D1_COMM_ID	Global Partitioned	RANGE (D1_COMM_ID)
		D1T386S1	BUS_OBJ_CD, BO_STATUS_CD, D1_COMM_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, D1_COMM_ID)
		CM_ILM_D1T386S1	ILM_DT, ILM_ARCH_SW, D1_COMM_ID	Local Partitioned
D1_COMM_IN_ CHAR	REFERENCE (D1_COMM_IN_ CHAR_FK)
		D1T387P0	D1_COMM_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE (D1_COMM_ID)
		D1T387S0	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_COMM_IN_ IDENTIFIER	REFERENCE (D1_COMM_IN_ IDENTIFIER_FK)
		D1T391P0	D1_COMM_ID, COMM_ID_TYPE_FLG	Global Partitioned	RANGE(D1_COMM_ID)
		D1T391S0	COMM_ID_TYPE_FLG, ID_VALUE	Global Partitioned	HASH(COMM_ID_TYPE_FLG, ID_VALUE)
		D1T391S1	COMM_ID_TYPE_FLG, UPPER(ID_VALUE)
D1_COMM_IN_ LOG	REFERENCE (D1_COMM_IN_ LOG_FK)
		D1T388P0	D1_COMM_ID, SEQNO	Global Partitioned	RANGE(D1_COMM_ID)
		D1T388S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL_FK1)
		D1T388S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL)
D1_COMM_IN_ LOG_PARM	REFERENCE (D1_COMM_IN_ LOG_PARM_FK)
		D1T389P0	D1_COMM_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(D1_COMM_ID)
D1_COMM_IN_ REL_OBJ	REFERENCE (D1_COMM_IN_ REL_OBJ_FK)
		D1T390P0	D1_COMM_ID, MAINT_OBJ_CD, COMM_REL_OBJ_TYPE_FLG	Global Partitioned	RANGE(D1_COMM_ID)
		D1T390S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4)

Communication Out

This table describes the Communication Out maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_COMM_OUT (Parent)	RANGE(ILM_DT,D1_COMM_ID)					D1_COMM_ OUT. CRE_DTTM
		D1T380P0	D1_COMM_ID	Global Partitioned	RANGE (D1_COMM_ID)
		D1T380S1	BUS_OBJ_CD, BO_STATUS_CD, D1_COMM_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, D1_COMM_ID)
		CM_ILM_D1T380S1	ILM_DT, ILM_ARCH_SW, D1_COMM_ID	Local Partitioned
D1_COMM_OUT_CHAR	REFERENCE (D1_COMM_OUT_CHAR_FK)
		D1T381P0	D1_COMM_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE (D1_COMM_ID)
		D1T381S0	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_COMM_OUT_IDENTIFIER	REFERENCE (D1_COMM_OUT_IDENTIFIER_ FK)
		D1T385P0	D1_COMM_ID, COMM_ID_TYPE_FLG	Global Partitioned	RANGE(D1_COMM_ID)
		D1T385S0	COMM_ID_TYPE_FLG, ID_VALUE	Global Partitioned	HASH(COMM_ID_TYPE_FLG, ID_VALUE)
		D1T385S1	COMM_ID_TYPE_FLG, UPPER(ID_VALUE)
D1_COMM_ OUT_LOG	REFERENCE (D1_COMM_OUT_LOG_FK)
		D1T382P0	D1_COMM_ID, SEQNO	Global Partitioned	RANGE(D1_COMM_ID)
		D1T382S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL_FK1)
		D1T382S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL)
D1_COMM_OUT_LOG_PARM	REFERENCE (D1_COMM_OUT_LOG_PARM_FK)
		D1T383P0	D1_COMM_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(D1_COMM_ID)
D1_COMM_OUT_REL_OBJ	REFERENCE (D1_COMM_OUT_REL_OBJ_FK)
		D1T384P0	D1_COMM_ID, MAINT_OBJ_CD, COMM_REL_OBJ_TYPE_FLG	Global Partitioned	RANGE(D1_COMM_ID)
		D1T384S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4)

Device Event

If sub retention periods will be defined for this MO, then please follow the guidelines set forth in section Module Specific ILM Implementation Details For Sub Retention.

This table describes the Device Event maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_DVC_EVT (Parent)	RANGE(ILM_DT, DVC_EVT_ID) Note: Default is to use sub-retention or use RANGE (ILM_DT,DVC_EVT_ID) if not using sub-retention.					D1_DVC_EVT. CRE_DTTM
		D1T400P0	DVC_EVT_ID	Global Partitioned	RANGE (DVC_EVT_ID)
		D1T400S1	BUS_OBJ_CD, BO_STATUS_CD, DVC_EVT_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, DVC_EVT_ID)
		D1T400S2	D1_DEVICE_ID, DVC_EVT_DTTM	Global Partitioned	HASH(D1_DEVICE_ID, DVC_EVT_DTTM)
		D1T400S3	BUS_OBJ_CD, BO_STATUS_CD, D1_DEVICE_ID, DVC_EVT_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, D1_DEVICE_ID, DVC_EVT_ID)
		CM_ILM_D1T400S4	ILM_DT, ILM_ARCH_SW, DVC_EVT_ID	Local Partitioned
D1_DVC_EVT_ CHAR	REFERENCE (D1_DVC_EVT_ CHAR_FK)
		D1T401P0	DVC_EVT_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T401S0	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_DVC_EVT_ IDENTIFIER	REFERENCE (D1_DVC_EVT_ IDENTIFIER_FK)
		D1T405P0	DVC_EVT_ID, DVC_EVT_ID_TYPE_FLG	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T405S0	DVC_EVT_ID_TYPE_FLG, ID_VALUE	Global Partitioned	HASH(DVC_EVT_ID_TYPE_FLG, ID_VALUE)
		D1T405S1	DVC_EVT_ID_TYPE_FLG, UPPER(ID_VALUE)
D1_DVC_EVT_ LOG	REFERENCE (D1_DVC_EVT_ LOG_FK)
		D1T402P0	DVC_EVT_ID, SEQNO	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T402S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL_FK1)
		D1T402S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL)
D1_DVC_EVT_ LOG_PARM	REFERENCE (D1_DVC_EVT_ LOG_PARM_FK)
		D1T403P0	DVC_EVT_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(DVC_EVT_ID)
D1_DVC_EVT_ REL_OBJ	REFERENCE (D1_DVC_EVT_ REL_OBJ_FK)
		D1T404P0	DVC_EVT_ID, MAINT_OBJ_CD, DVC_EVT_REL_OBJ_TYPE_FLG	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T404S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4)

Completion Event

This table describes the Completion Event maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_COMPL_EVT (Parent)	RANGE(ILM_DT,COMPL_EVT_ID)					D1_COMPL_ EVT. CRE_DTTM
		D1T340P0	COMPL_EVT_ID	Global Partitioned	RANGE (COMPL_EVT_ID)
		D1T340S0	D1_ACTIVITY_ID	Global Partitioned	HASH(D1_ ACTIVITY_ID)
		CM_ILM_D1T340S1	ILM_DT, ILM_ARCH_SW, DVC_EVT_ID	Local Partitioned
D1_COMPL_EVT_CHAR	REFERENCE (D1_COMPL_ EVT_CHAR_FK)
		D1T341P0	COMPL_EVT_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(COMPL_ EVT_ID)
		D1T341S1	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_COMPL_EVT_LOG	REFERENCE (D1_COMPL_EVT_LOG_FK)
		D1T342P0	COMPL_EVT_ID, SEQNO	Global Partitioned	RANGE(COMPL_ EVT_ID)
		D1T342S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL_FK1)
		D1T342S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL)
D1_COMPL_EVT_LOG_PARM	REFERENCE (D1_COMPL_EVT_LOG_PARM_FK)
		D1T343P0	COMPL_EVT_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(COMPL_ EVT_ID)
D1_COMPL_EVT_REL_OBJ	REFERENCE (D1_COMPL_EVT_REL_OBJ_FK)
		D1T344P0	COMPL_EVT_ID, MAINT_OBJ_CD, COMPL_EVT_REL_OBJ_TYP_FLG	Global Partitioned	RANGE(COMPL_ EVT_ID)
		D1T344S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4)

Initial Measurement Data

If sub retention periods will be defined for this MO, then please follow the guidelines set forth in section Module Specific ILM Implementation Details For Sub Retention.

This table describes the Initial Measurement Data maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_INIT_MSRMT_DATA (Parent)	RANGE (ILM_DT,MEASR_COMP_ID) Note: Default is to use sub-retention or use RANGE (ILM_DT,MEASR_COMP_ID) if not using sub-retention.					D1_INIT_MSRMT_DATA. CRE_DTTM
		D1T304P0	INIT_MSRMT_DATA_ID	Global Partitioned	RANGE (INIT_MSRMT_DATA_ID)
		D1T304S1	MEASR_COMP_ID, D1_TO_DTTM	Global Partitioned	RANGE (MEASR_COMP_ ID)
		CM_ILM_D1T304S4	ILM_DT, ILM_ARCH_SW, INIT_MSRMT_DATA_ID	Local Partitioned
D1_INIT_MSRMT_DATA_CHAR	REFERENCE (D1_INIT_MSRMT_DATA_CHAR_FK)
		D1T305P0	INIT_MSRMT_DATA_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(INIT_MSRMT_DATA_ID)
		D1T305S1	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_INIT_MSRMT_DATA_LOG	REFERENCE (D1_INIT_MSRMT_DATA_LOG_FK)
		D1T306P0	INIT_MSRMT_DATA_ID, SEQNO	Global Partitioned	RANGE (INIT_MSRMT_DATA_ID)
D1_INIT_MSRMT_DATA_LOG_ PARM	REFERENCE (D1_INIT_MSRMT_DATA_LOG_PARM_FK)
		D1T307P0	INIT_MSRMT_DATA_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE (INIT_MSRMT_DATA_ID)

Usage Transaction

This table describes the Usage Transaction maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_USAGE (Parent)	RANGE(ILM_DT, D1_USAGE_ID)					D1_USAGE. CRE_DTTM
		D1T281P0	D1_USAGE_ID	Global Partitioned	RANGE (D1_USAGE_ID)
		D1T281S0	US_ID, START_DTTM	Global Partitioned	RANGE (US_ID)
		D1T281S1	BUS_OBJ_CD, BO_STATUS_CD, D1_USAGE_ID	Global Partitioned	HASH(BUS_OBJ_ CD, BO_STATUS_CD, D1_USAGE_ID)
		CM_ILM_D1T281S2	ILM_DT, ILM_ARCH_SW, D1_USAGE_ID	Local Partitioned
		D1T419S1	USG_EXT_ID, D1_USAGE_ID	Global Partitioned	RANGE (USG_EXT_ID)
D1_USAGE_ CHAR	REFERENCE (D1_USAGE_ CHAR_FK)
		D1T285P0	D1_USAGE_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(D1_ USAGE_ID)
		D1T285S1	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_USAGE_LOG	REFERENCE (D1_USAGE_LOG_FK)
		D1T286P0	D1_USAGE_ID, SEQNO	Global Partitioned	RANGE(D1_USAGE_ID)
		D1T286S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL_FK1)
		D1T286S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL)
D1_USAGE_LOG_PARM	REFERENCE(D1_USAGE_LOG_ PARM_FK)
		D1T287P0	D1_USAGE_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE (D1_USAGE_ID)
D1_USAGE_ PERIOD	REFERENCE(D1_USAGE_PERIOD_FK)
		D1T283P0	D1_USAGE_ID, PERIOD_SEQ_NUM	Global Partitioned	RANGE(D1_ USAGE_ID)
D1_USAGE_ PERIOD_ITEM_DET	REFERENCE(D1_USAGE_PERIOD_ITEM_DET_FK)
		D1T431P0	D1_USAGE_ID, PERIOD_SEQ_NUM, ITEM_SEQ_NUM	Global Partitioned	RANGE(D1_USAGE_ID)
D1_USAGE_ PERIOD_SQ	REFERENCE(D1_USAGE_PERIOD_SQ_FK)
		D1T284P0	D1_USAGE_ID, PERIOD_SEQ_NUM, SQ_SEQ_NUM	Global Partitioned	RANGE(D1_ USAGE_ID)
D1_USAGE_PERIOD_SQ_DATA	REFERENCE(D1_USAGE_PERIOD_SQ_DATA_FK)
		D1T497P0	D1_USAGE_ID, PERIOD_SEQ_NUM, SQ_SEQ_NUM, SQ_DATA_DTTM	Global Partitioned	RANGE(D1_ USAGE_ID)
D1_USAGE_REL	REFERENCE (D1_USAGE_REL_FK)
		D1T316P0	D1_USAGE_ID, USAGE_REL_TYPE_FLG	Global Partitioned	RANGE(D1_ USAGE_ID)
		D1T316S0	REL_USAGE_ID, USAGE_REL_TYPE_FLG, D1_USAGE_ID	Global Partitioned	HASH(REL_USAGE_ID, USAGE_REL_TYPE_FLG, D1_USAGE_ID)
D1_USAGE_ SCALAR_DTL	REFERENCE(D1_USAGE_SCALAR_DTL_FK)
		D1T282P0	D1_USAGE_ID, D1_SP_ID, SEQ_NUM	Global Partitioned	RANGE(D1_ USAGE_ID)

Usage Transaction Exception

This table describes the Usage Transaction Exception maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_USAGE_ EXCP (Parent)	RANGE (ILM_DT, USAGE_EXCP_ ID)					D1_USAGE_ EXCP. CRE_DTTM
		D1T443P0	USAGE_EXCP_ID	Global Partitioned	RANGE (USAGE_EXCP_ ID)
		CM_ILM_D1T443S1	ILM_DT, ILM_ARCH_SW, USAGE_EXCP_ID	Local Partitioned
D1_USAGE_ EXCP_CHAR	REFERENCE (D1_USAGE_ EXCP_CHAR_ FK)	D1T446P0	USAGE_EXCP_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE (USAGE_EXCP_ ID)
		D1T446S1	SRCH_CHAR_VAL	Global Partitioned	HASH (SRCH_CHAR_ VAL)
D1_USAGE_ EXCP_PARM	REFERENCE (D1_USAGE_ EXCP_PARM_ FK)	D1T445P0	USAGE_EXCP_ID, PARM_SEQ	Global Partitioned	RANGE (USAGE_EXCP_ ID)

VEE Exception

This table describes the VEE Exception maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_VEE_EXCP (Parent)	RANGE(ILM_DT, VEE_EXCP_ID)					D1_VEE_EXCP. CRE_DTTM
		D1T308P0	VEE_EXCP_ID	Global Partitioned	RANGE (VEE_EXCP_ID)
		D1T308S1	INIT_MSRMT_DATA_ID	Global Partitioned	HASH(INIT_ MSRMT_DATA_ID)
		CM_ILM_D1T308S2	ILM_DT, ILM_ARCH_SW, VEE_EXCP_ID	Local Partitioned
D1_VEE_EXCP_CHAR	REFERENCE (D1_VEE_EXCP_ CHAR_FK)
		D1T310P0	VEE_EXCP_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(VEE_ EXCP_ID)
		D1T310S1	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_VEE_EXCP_PARM	REFERENCE (D1_VEE_EXCP_ PARM_FK)
		D1T309P0	VEE_EXCP_ID, PARM_SEQ	Global Partitioned	RANGE(VEE_ EXCP_ID))

Module Specific ILM Implementation Details For Sub Retention

This section outlines each maintenance object that has been configured to support ILM as well as sub retention periods. This differs from the standard ILM enabled tables in that the partitioning strategy is inclusive of an additional column that defines the retention period for each record. In each case, the recommendation of the initial load of the ILM_DT and the <field name for retention period> for existing records is noted. The CTAS operation for these tables includes an extra step of generating a temporary mapping table that will allow the select for the ILM_DT to also identify the appropriate <retention period field name> for each record.

This section details the following maintenance objects that support ILM as well as sub retention periods:

• Activity

• Device Event

• Initial Measurement Data

Activity

If sub retention periods will not be defined for this MO, then please follow the guidelines set forth in section Module Specific ILM Implementation Details.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_ACTIVITY (Parent)	RANGE (ILM_DT, RETENTION_ PERIOD)					D1_ACTIVITY. CRE_DTTM
		D1T319P0	D1_ACTIVITY_ID	Global Partitioned	RANGE (D1_ACTIVITY_ID)
		D1T319S0	BUS_OBJ_CD, BO_STATUS_CD, D1_ACTIVITY_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, D1_ACTIVITY_ID)
		CM_ILM_D1T319S1	ILM_DT, RETENTION_PERIOD, ILM_ARCH_SW, D1_ACTIVITY_ID	Local Partitioned
D1_ACTIVITY_ CHAR	REFERENCE (D1_ACTIVITY_ CHAR_FK)
		D1T320P0	D1_ACTIVITY_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T320S0	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_ACTIVITY_ IDENTIFIER	REFERENCE (D1_ACTIVITY_IDENTIFIER_FK)
		D1T330P0	D1_ACTIVITY_ID, ACTIVITY_ID_TYPE_ FLG	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T330S0	ACTIVITY_ID_TYPE_ FLG, ID_VALUE	Global Partitioned	HASH(ACTIVITY_ID_TYPE_FLG, ID_VALUE)
		D1T330S1	ACTIVITY_ID_TYPE_FLG, UPPER(ID_VALUE)
D1_ACTIVITY_ LOG	REFERENCE (D1_ACTIVITY_ LOG_FK)
		D1T321P0	D1_ACTIVITY_ID, SEQNO	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T321S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_TYPE_CD, CHAR_VAL_FK1)
		D1T321S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_TYPE_CD, CHAR_VAL)
D1_ACTIVITY_ LOG_PARM	REFERENCE (D1_ACTIVITY_ LOG_PARM_FK)
		D1T322P0	D1_ACTIVITY_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
D1_ACTIVITY_ REL	REFERENCE (D1_ACTIVITY_ REL_FK)
		D1T323P0	D1_ACTIVITY_ID, ACTIVITY_REL_TYPE_ FLG	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T323S0	REL_ACTIVITY_ID	Global Partitioned	HASH(REL_ ACTIVITY_ID)
D1_ACTIVITY_ REL_OBJ	REFERENCE (D1_ACTIVITY_ REL_OBJ_FK)
		D1T324P0	D1_ACTIVITY_ID, MAINT_OBJ_CD, ACTIVITY_REL_OBJ_ TYPE_FLG	Global Partitioned	RANGE(D1_ ACTIVITY_ID)
		D1T324S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1,PK_VALUE2, PK_VALUE3, PK_VALUE4)

Query for Setting the Retention Period

The following query should be used to create a temporary table to create a mapping table that will identify the retention period for each measuring component type. This table will then be used during in the CTAS operation for Activity to identify the retention period for each record.

Please refer to Appendix G: Sample SQL for ILM in C2M with Sub Retention (Existing Installation) for detailed information using Initial Measurement Data as an example.

Note: A pre-requisite to executing this query is configuring the appropriate retention periods in the ILM master configuration in the Oracle Utilities Meter Data Management application.

/*****ACTIVITY*****/

CREATE TABLE ILM_ACTIVITY_RETENTION_TMP

select acty.activity_type_cd

/*retrieve the retention period for Activity Types in this order of precedence:

1. The category based retention period from the MDM master configuration

2. The MO level retention period from the MO options

3. The installation level retention period from the FW master configuration

, CAST(coalesce(catMap.retPeriod --Category level

, (select maint_obj_opt_val

from ci_md_mo_opt mmo

where maint_obj_cd = 'D1-ACTIVITY'

and maint_obj_opt_flg ='FLRP'

and seq_num =

(select max(seq_num)

from ci_md_mo_opt mmo

where maint_obj_cd = 'D1-ACTIVITY'

and maint_obj_opt_flg ='FLRP')) --MO level

, extractvalue( xmlparse(content fw_mcfg.mst_config_data)

,'generalMasterConfiguration/defaultRetentionPeriod') --Install level

) as NUMBER(5)) retPeriod

from d1_activity_type acty

, (select extractvalue(value(p),

'activityTypeCategoryRetentionPeriodList/activityTypeCategory'

)ACTIVITY_TYPE_CAT_FLG

, extractvalue(value(p),

'activityTypeCategoryRetentionPeriodList/retentionPeriod'

)retPeriod

from f1_mst_config mdm_mcfg ,

table(xmlsequence(extract(xmlparse(content mdm_mcfg.mst_config_data),

'activityRetentionPeriod/activityTypeCategoryRetentionPeriods/activityTypeCategoryRetentionPeriodList'

))) p

where mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig')catMap

, f1_mst_config fw_mcfg

where fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig'

and acty.ACTIVITY_TYPE_CAT_FLG = catMap.ACTIVITY_TYPE_CAT_FLG (+)

order by 1;

Device Event

Note: If sub retention periods will not be defined for this MO, then please follow the guidelines set forth in section Module Specific ILM Implementation Details.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_DVC_EVT (Parent)	RANGE(ILM_DT, RETENTION_ PERIOD)					D1_DVC_EVT. CRE_DTTM
		D1T400P0	DVC_EVT_ID	Global Partitioned	RANGE (DVC_EVT_ID)
		D1T400S1	BUS_OBJ_CD, BO_STATUS_CD, DVC_EVT_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, DVC_EVT_ID)
		D1T400S2	D1_DEVICE_ID, DVC_EVT_DTTM	Global Partitioned	HASH(D1_DEVICE_ID, DVC_EVT_DTTM)
		D1T400S3	BUS_OBJ_CD, BO_STATUS_CD, D1_DEVICE_ID, DVC_EVT_ID	Global Partitioned	HASH(BUS_OBJ_CD, BO_STATUS_CD, D1_DEVICE_ID, DVC_EVT_ID)
		CM_ILM_D1T400S4	ILM_DT, RETENTION_PERIOD, ILM_ARCH_SW, DVC_EVT_ID	Local Partitioned
D1_DVC_EVT_ CHAR	REFERENCE (D1_DVC_EVT_ CHAR_FK)
		D1T401P0	DVC_EVT_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T401S0	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_DVC_EVT_ IDENTIFIER	REFERENCE (D1_DVC_EVT_ IDENTIFIER_FK)
		D1T405P0	DVC_EVT_ID, DVC_EVT_ID_TYPE_FLG	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T405S0	DVC_EVT_ID_TYPE_FLG, ID_VALUE	Global Partitioned	HASH(DVC_EVT_ID_TYPE_FLG, ID_VALUE)
		D1T405S1	DVC_EVT_ID_TYPE_FLG, UPPER(ID_VALUE)
D1_DVC_EVT_ LOG	REFERENCE (D1_DVC_EVT_ LOG_FK)
		D1T402P0	DVC_EVT_ID, SEQNO	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T402S1	CHAR_TYPE_CD, CHAR_VAL_FK1	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL_FK1)
		D1T402S2	CHAR_TYPE_CD, CHAR_VAL	Global Partitioned	HASH(CHAR_ TYPE_CD, CHAR_VAL)
D1_DVC_EVT_ LOG_PARM	REFERENCE (D1_DVC_EVT_ LOG_PARM_FK)
		D1T403P0	DVC_EVT_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE(DVC_EVT_ID)
D1_DVC_EVT_ REL_OBJ	REFERENCE (D1_DVC_EVT_ REL_OBJ_FK)
		D1T404P0	DVC_EVT_ID, MAINT_OBJ_CD, DVC_EVT_REL_OBJ_TYPE_FLG	Global Partitioned	RANGE(DVC_EVT_ID)
		D1T404S0	PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4, PK_VALUE5, MAINT_OBJ_CD	Global Partitioned	HASH(PK_VALUE1, PK_VALUE2, PK_VALUE3, PK_VALUE4)

Query for Setting the Retention Period

The following query should be used to create a temporary table to create a mapping table that will identify the retention period for each measuring component type. This table will then be used during in the CTAS operation for Device Event to identify the retention period for each record.

Please refer to Appendix G: Sample SQL for ILM in C2M with Sub Retention (Existing Installation) for detailed information using Initial Measurement Data as an example.

Note: A pre-requisite to executing this query is configuring the appropriate retention periods in the ILM master configuration in the Oracle Utilities Meter Data Management application.

CREATE TABLE ILM_DVC_EVT_RETENTION_TMP

select det.dvc_evt_type_cd

/*retrieve the retention period for Device Event Types in this order of precedence:

1. The category based retention period from the MDM master configuration

2. The MO level retention period from the MO options

3. The installation level retention period from the FW master configuration

, CAST(coalesce(catMap.retPeriod --Category level

, (select maint_obj_opt_val

from ci_md_mo_opt mmo

where maint_obj_cd = 'D1-DVCEVENT'

and maint_obj_opt_flg ='FLRP'

and seq_num = (select max(seq_num)

from ci_md_mo_opt mmo

where maint_obj_cd = 'D1-DVCEVENT'

and maint_obj_opt_flg ='FLRP')) --MO level

, extractvalue( xmlparse(content

fw_mcfg.mst_config_data),

'generalMasterConfiguration/defaultRetentionPeriod') --Install level

) as NUMBER(5)) retPeriod

from d1_dvc_evt_type det

, (select extractvalue(value(p),

'deviceEventCategoryRetentionPeriodList/deviceEventCategory') dvc_evt_cat_flg

, extractvalue(value(p),

'deviceEventCategoryRetentionPeriodList/retentionPeriod') retPeriod

from f1_mst_config mdm_mcfg ,

table(xmlsequence(extract(xmlparse(content

mdm_mcfg.mst_config_data),

'deviceEventRetentionPeriod/deviceEventCategoryRetentionPeriods/deviceEventCategoryRetentionPeriodList'

))) p

where mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig')catMap

, f1_mst_config fw_mcfg

where fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig'

and det.dvc_evt_cat_flg = catMap.dvc_evt_cat_flg (+)

order by 1;

Initial Measurement Data

If sub retention periods will not be defined for this MO, then please follow the guidelines set forth in section Module Specific ILM Implementation Details.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_INIT_MSRMT_DATA (Parent)	RANGE (ILM_DT, RETENTION_ PERIOD)					D1_INIT_MSRMT_DATA. CRE_DTTM
		D1T304P0	INIT_MSRMT_DATA_ID	Global Partitioned	RANGE (INIT_MSRMT_ DATA_ID)
		D1T304S1	MEASR_COMP_ID, D1_TO_DTTM	Global Partitioned	RANGE (MEASR_COMP_ ID)
		CM_ILM_D1T304S4	ILM_DT, RETENTION_PERIOD, ILM_ARCH_SW, INIT_MSRMT_DATA_ID	Local Partitioned
D1_INIT_MSRMT_DATA_CHAR	REFERENCE (D1_INIT_MSRMT_DATA_CHAR_FK)
		D1T305P0	INIT_MSRMT_DATA_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned	RANGE(INIT_MSRMT_DATA_ID)
		D1T305S1	SRCH_CHAR_VAL	Global Partitioned	HASH(SRCH_CHAR_VAL)
D1_INIT_MSRMT_DATA_LOG	REFERENCE (D1_INIT_MSRMT_DATA_LOG_FK)
		D1T306P0	INIT_MSRMT_DATA_ID, SEQNO	Global Partitioned	RANGE (INIT_MSRMT_DATA_ID)
D1_INIT_MSRMT_DATA_LOG_ PARM	REFERENCE (D1_INIT_MSRMT_DATA_LOG_PARM_FK)
		D1T307P0	INIT_MSRMT_DATA_ID, SEQNO PARM_SEQ	Global Partitioned	RANGE (INIT_MSRMT_DATA_ID)

Initial Measurement Snapshot

This table below describes the Initial Measurement Snapshot maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_INIT_MSRMT_SNAP (Parent)	Range(ILM_DT, INIT_MSRMT_ SNAP_ID)	D1T622P0	INIT_MSRMT_SNAP_ID	Global Partitioned	Range(INIT_ MSRMT_SNAP_ID)	D1_INIT_MSRMT_SNAP.CRE_ DTTM
		CM_ILM_D1T622L0	ILM_DT, ILM_ARCH_SW, INIT_MSRMT_SNAP_ID	Local Partitioned
D1_INIT_MSRMT_SNAP_CHAR	Reference Partitioning	D1T624P0	INIT_MSRMT_SNAP_ID, CHAR_TYPE_CD, SEQ_NUM	Global Partitioned
D1_INIT_MSRMT_SNAP _LOG	Reference Partitioning	D1T625P0	INIT_MSRMT_SNAP_ID, SEQNO	Global Partitioned
D1_INIT_MSRMT_SNAP _LOG_PARM	Reference Partitioning	D1T626P0	INIT_MSRMT_SNAP_ID, SEQNO, PARM_SEQ	Global Partitioned

Measurement Data Snapshot Interval

This table below describes the Measurement Data Snapshot Interval maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_MDSI (Parent)	Range(ILM_DT, D1_MDSI_ID)	D1T565P0	D1_MDSI_ID	Global Partitioned	Range(D1_MDSI_ ID)	D1_MDSI_ START_DTTM
		CM_ILM_D1T565L0	ILM_DT, ILM_ARCH_SW, D1_MDSI_ID	Local Partitioned

Measurement Data Snapshot Scalar

This table below describes the Measurement Data Snapshot Scalar maintenance object.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_MDSS (Parent)	Range(ILM_DT, D1_MDSS_ID)	D1T594P0	D1_MDSS_ID	Global Partitioned	Range(D1_MDSS_ID	D1_MDSS_ START_DTTM
		CM_ILM_D1T594L0	ILM_DT, ILM_ARCH_SW, D1_MDSS_ID	Local Partitioned

Snapshot Tables

This table below describes the snapshot tables.

Table Name	Table Partitioning Type (Partitioning, Sub-Partitioning Key)	Index Name	Index Columns	Index Type Global or Local	Index Partitioning Sub-Partitioning Key	ILM_DT Initial Load
D1_SP_SNAP_DL	RANGE(ILM_DT, SP_SNAP_ID)					D1_SP_SNAP_DL.SNAPSHOT_DTTM
		D1T434P0	SP_SNAP_ID	Global Partitioned	RANGE(SP_SNAP_ID)
		D1T434S0	D1_SP_ID, SNAPSHOT_DTTM, SNAPSHOT_TYPE_FLG	Global Partitioned
		D1T434S1	CM_ILM_ILM_DT, ILM_ARCH_SW, SP_SNAP_ID	Local Partitioned
D1_SP_UNR_ USG_SNAP_DL	RANGE(ILM_DT, SP_UNR_USG_ SNAP_ID)					D1_SP_UNR_USG_SNAP_DL.SNAPSHOT_DTTM
		D1T438P0	SP_UNR_USG_SNAP_ID	Global Partitioned	RANGE(SP_UNR_USG_SNAP_ID)
		D1T438S0	D1_SP_ID, SNAPSHOT_DTTM, UNR_USG_SNAPSHOT_ TYPE_FLG, SNAPSHOT_TYPE_FLG	Global Partitioned
		CM_ILM_D1T438S1	ILM_DT, ILM_ARCH_SW, SP_UNR_USG_SNAP_ID	Local Partitioned
D1_SP_USG_ SNAP_DL	D1_SP_USG_ SNAP_DL					D1_SP_USG_SN AP_DL.SNAPSH OT_DTTM
		D1T436P0	SP_USG_SNAP_ID	Global Partitioned	RANGE(SP_USG_ SNAP_ID)
		D1T436S0	D1_SP_ID, SNAPSHOT_DTTM, MEASR_COMP_ID, USG_SNAPSHOT_TYPE_FLG, D1_TOU_CD, MSRMT_COND_FLG, SNAPSHOT_TYPE_FLG	Global
		CM_ILM_D1T436S1	ILM_DT, ILM_ARCH_SW, SP_USG_SNAP_ID	Local Partitioned
D1_SP_VEE_EXCP_SNAP_DL	RANGE(ILM_DT, SP_VEE_EXCP_ SNAP_ID)					D1_SP_VEE_ EXCP_SNAP_ DL.SNAPSHOT_ DTTM
		D1T440P0	SP_VEE_EXCP_SNAP_ID	Global Partitioned	RANGE(SP_VEE_EXCP_SNAP_ID)
		D1T440S0	D1_SP_ID, SNAPSHOT_DTTM, MEASR_COMP_ID, EXCP_TYPE_CD, D1_IMD_TYPE_FLG, EXCP_SEVERITY_FLG, VEE_GRP_CD, VEE_RULE_CD, SNAPSHOT_TYPE_FLG
		CM_ILM_ D1T440S1	ILM_DT, ILM_ARCH_SW, SP_VEE_EXCP_SNAP_ID	Local Partitioned

Query for Setting the Retention Period

The following query should be used to create a temporary table to create a mapping table that will identify the retention period for each measuring component type. This table will then be used during in the CTAS operation for Initial Measurement Data to identify the retention period for each record.

Please refer to Appendix B: Sample SQL for Enabling ILM in MDM (Existing Installation) for detailed information using Initial Measurement Data as an example.

Note: A pre-requisite to executing this query is configuring the appropriate retention periods in the ILM master configuration in the Oracle Utilities Meter Data Management application.

CREATE TABLE ILM_IMD_RETENTION_TMP

select mct.measr_comp_type_cd

/*retrieve the retention period for MC Types in this order of precedence:

1. The UOM based retention period from the MDM master configuration

2. The interval IMD retention period from the MDM master configuration

3. The MO level retention period from the MO options

4. The installation level retention period from the FW master configuration

, CAST(coalesce( (select retPeriod

from (select 'D1IN' interval_scalar_flg

, extractvalue(value(p),'uomRetentionPeriodList/uom') D1_UOM_CD

, extractvalue(value(p),'uomRetentionPeriodList/retentionPeriod') retPeriod

from f1_mst_config mdm_mcfg

, table(xmlsequence(extract(xmlparse(content

mdm_mcfg.mst_config_data),

'imdRetentionPeriod/intervalImdRetentionPeriods/uomRetentionPeriods/uomRetentionPeriodList'))) p

where mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig'

union

select 'D1SC' INTERVAL_SCALAR_FLG

, extractvalue(value(p),'uomRetentionPeriodList/uom') D1_UOM_CD

, extractvalue(value(p),'uomRetentionPeriodList/retentionPeriod') retPeriod

from f1_mst_config mdm_mcfg

, table(xmlsequence(extract(xmlparse(content

mdm_mcfg.mst_config_data),

'imdRetentionPeriod/scalarImdRetentionPeriods/uomRetentionPeriods/uomRetentionPeriodList'))) p

where mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig') uomMap

where uomMap.interval_scalar_flg = mct.interval_scalar_flg

and trim(mctvi.d1_uom_cd) = trim(uomMap.d1_uom_cd))--UOM

, DECODE(mct.interval_scalar_flg

,'D1IN'

,extractvalue( xmlparse(content mdm_mcfg.mst_config_data),

'imdRetentionPeriod/intervalImdRetentionPeriods/intervalRetentionPeriod') --interval IMD

,extractvalue( xmlparse(content mdm_mcfg.mst_config_data),

'imdRetentionPeriod/scalarImdRetentionPeriods/scalarRetentionPeriod') --scalar IMD

)

, (select maint_obj_opt_val

from ci_md_mo_opt mmo

where maint_obj_cd = 'D1-IMD'

and maint_obj_opt_flg ='FLRP'

and seq_num = (select max(seq_num)

from ci_md_mo_opt mmo

where maint_obj_cd = 'D1-IMD'

and maint_obj_opt_flg ='FLRP')) --IMD

, extractvalue( xmlparse(content fw_mcfg.mst_config_data),

'generalMasterConfiguration/defaultRetentionPeriod') --Install

) as NUMBER(5)) retPeriod

from d1_measr_comp_type mct

, d1_mc_type_value_identifier mctvi

, f1_mst_config fw_mcfg

, f1_mst_config mdm_mcfg

where mct.measr_comp_type_cd = mctvi.measr_comp_type_cd

and mctvi.value_id_type_flg = 'D1MS'

and fw_mcfg.bus_obj_cd = 'F1-ILMMSConfig'

and mdm_mcfg.bus_obj_cd = 'D1-ILMMSConfig'

order by 1;

On-going Maintenance Phase

The following steps provide a high level overview of what needs to be done for on-going maintenance for ILM on enabled MOs.

Please refer to the Appendix H: Sample SQL for Periodic Maintenance for MDM Data for detailed information using To Do Entry(Without LOB), F1_SYNC_REC_IN(With LOB-Tablespace per Partition), Initial Measurement Data (With LOB-Tablespace per Subpartition), and the D1_MSRMT table (Partition Compression) as examples.

1. Add the partition.

a. Create Tablespace to be used for the new parent table partition.

b. Since, we define MAXVALUE Partition; new partition can only be created using “SPLIT” operation. Identify and use next HIGH_VALUE Partition for the split operation.

c. All the child table(s) partition(s)\LOB(s) must be altered to use the same tablespace as that of the parent table’s partition.

d. Enable advanced compression on all child table(s).

e. Copy partition level statistics from the previous partition.

2. Archive the partition/subpartition.

a. Make the tablespace that will be archived READ ONLY.

b. Check that no records have ILM_ARCH_SW = ‘N’.

• If record count is zero, then proceed for further steps.

• If record count is not zero, then change the tablespace back to READ WRITE MODE as Archive is not Feasible at the time.

c. Create an archive tablespace for the partition/subpartition that needs to be archived.

d. Create staging tables using the new archive tablespace. Load data for all child tables first.

e. Create staging table using the new archive tablespace and load data for the parent table.

f. Export tablespace using TRANSPORT_TABLESPACES method.

Make Sure Tablespace datafile required for further import is preserved.

g. Drop the partition, partition the tablespace and archive the tablespace (as it is already exported).

3. Restore the partition:

a. Create a new tablespace to restore the partition/subpartition.

b. Add partition using split operation on next greater high value partition.

If the table contains LOBS, there will an additional statement in split partition DDL indicating tablespace where the LOBs will be stored.

c. Enable advanced compression on all child table(s).

d. Import Tablespace using TRANSPORT_TABLESPACES method.

e. Load data into the parent table first from the staging table.

f. Load data into the child table from the staging table.

g. Drop the archive tablespace after import and data loading is successful.

4. Compress D1_MSRMT table Partition:

a. Create new compressed tablespace.

b. Create a table using CTAS for each subpartition of the partition being compressed in the new compressed tablespace.

c. Create a unique primary index for each subpartition of the partition being compressed in the new compressed tablespace. Then alter table to create the primary key.

d. Exchange the subpartition of the D1_MSRMT table with the newly created table for each subpartition.

e. Drop the original uncompressed tablespace.

f. Alter the partition level metadata to reflect the new compressed tablespace.

g. Rename the new compressed tablespace to the original tablespace name.

5. Move Data between different storage tiers:

The ILM facilities can be used within the database to implement storage savings, as follows:

• Use ILM Assistant to define the data groups to be used for the individual objects. Assign those data groups to partitions and storage devices to implement the storage savings. Remember to assign transportable tablespaces for the archive/dormant data stage to allow for safe removal of the data.

• Use ILM assistant to generate the necessary commands to implement the data changes manually or use Automatic Storage Management (ASM) to automate the data storage policies.

• Optionally, use Automatic Data Optimization to provide further optimizations.

For more information about ILM refer to the following:

• Oracle Database VLDB and Partitioning Guide (19c) available at:

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/manage-data-db-ilm.html

• Oracle Enterprise Manager 13.4 Lifecycle Management available at:

https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/lifecycle.html