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
	Reference Partitioning	F1C706P0	SNAPSHOT_ID	Global Partitioned
		F1C706S1	BUS_OBJ_CD,BO_STATUS_CD,SNAPSHOT_ID	Global
		CM_ILM_F1C706L0	ILM_DT,ILM_ARCH_SW	Local
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	CHAR_TYPE_CD,CHAR_VAL	Global
F1_STATS_SNPSHT_LOG_PARM	Reference Partitioning	F1C709P0	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

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
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)	Global
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
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
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
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
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, BO_STATUS_CD, BUS_OBJ_CD, D1_TO_DTTM, D1_FROM_DTTM	Global Partitioned	RANGE (MEASR_COMP_ ID)
		CM_ILM_D1T304S4	ILM_DT, ILM_ARCH_SW, INIT_MSRMT_DATA_ID	Local
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
		D1T419S1	USG_EXT_ID, D1_USAGE_ID	Global Partitioned	HASH (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
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))

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
		CM_ILM_D1T434S1	ILM_DT, ILM_ARCH_SW, SP_SNAP_ID	Local
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
		CM_ILM_D1T438S1	ILM_DT, ILM_ARCH_SW, SP_UNR_USG_SNAP_ID	Local
D1_SP_USG_ SNAP_DL	RANGE(ILM_DT, SP_USG_SNAP_ ID)					D1_SP_USG_SNAP_DL.SNAPSHOT_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
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	Global
		CM_ILM_D1T440S1	ILM_DT, ILM_ARCH_SW, SP_VEE_EXCP_SNAP_ID	Local

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

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
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 Sample SQL for Enabling ILM with Sub Retention 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.

/*****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
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 Sample SQL for Enabling ILM with Sub Retention 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 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 maintenance object, follow the guidelines mentioned in the Module Specific ILM Implementation Details section.

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, BO_STATUS_CD, BUS_OBJ_CD, D1_TO_DTTM, D1_FROM_DTTM	Global Partitioned	RANGE (MEASR_COMP_ID)
		CM_ILM_D1T304S4	ILM_DT, RETENTION_PERIOD, ILM_ARCH_SW, INIT_MSRMT_DATA_ID	Local
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)

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 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 Sample SQL for Periodic Maintenance 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:

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