Sample SQL for Enabling ILM in SGG (Existing Installation)

This section provides additional details related to supporting ILM in an existing installation. It includes the sample syntax for each step using the To Do Entry maintenance object as an example. Other maintenance object's implementations can follow a similar pattern.

1. Rename existing table CI_TD_ENTRY and primary key index as a backup. It is suggested to use an ILM_ prefix. The following are sample statements:

ALTER TABLE CI_TD_ENTRY RENAME TO ILM_TD_ENTRY;

ALTER INDEX XT039P0 RENAME TO ILM_XT039P0;

2. Generate DDL for the secondary index.

set heading off;

set echo off;

Set pages 999;

set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('INDEX','XT039S2','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','XT039S3','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','XT039S4','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','XT039S5','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','XT039S6','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','XT039S7','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','XT039S8','CISADM') from dual;

select dbms_metadata.get_ddl('INDEX','CM_ILM_XT039S8','CISADM') from dual;

spool off;

3. Drop secondary indexes.

DROP INDEX CISADM.XT039S2;

DROP INDEX CISADM.XT039S3;

DROP INDEX CISADM.XT039S4;

DROP INDEX CISADM.XT039S5;

DROP INDEX CISADM.XT039S6;

DROP INDEX CISADM.XT039S7;

DROP INDEX CISADM.XT039S8;

DROP INDEX CISADM.CM_ILM_XT039S8;

4. Create a partitioned table.

In the following example ILM_DT value is inserted from column CRE_DTTM. The degree setting of 'parallel' in the DDL can be adjusted according to the table’s data, its means and its size.

CREATE TABLE CI_TD_ENTRY (

TD_ENTRY_ID CHAR(14) NOT NULL ENABLE,

BATCH_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

BATCH_NBR NUMBER(10,0) DEFAULT 0 NOT NULL ENABLE,

MESSAGE_CAT_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

MESSAGE_NBR NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE,

ASSIGNED_TO CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

TD_TYPE_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

ROLE_ID CHAR(10) DEFAULT ' ' NOT NULL ENABLE,

ENTRY_STATUS_FLG CHAR(2) DEFAULT ' ' NOT NULL ENABLE,

VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE,

CRE_DTTM DATE,

ASSIGNED_DTTM DATE,

COMPLETE_DTTM DATE,

COMPLETE_USER_ID CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

COMMENTS VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE,

ASSIGNED_USER_ID CHAR(8) DEFAULT ' ' NOT NULL ENABLE,

TD_PRIORITY_FLG CHAR(4) DEFAULT ' ' NOT NULL ENABLE,

ILM_DT DATE,

ILM_ARCH_SW CHAR(1)

) NOLOGGING PARALLEL

ENABLE ROW MOVEMENT

PARTITION BY RANGE (ILM_DT)

SUBPARTITION BY RANGE (TD_ENTRY_ID) SUBPARTITION TEMPLATE

(

SUBPARTITION S01 VALUES LESS THAN ( '12499999999999' ),

SUBPARTITION S02 VALUES LESS THAN ( '24999999999999' ),

SUBPARTITION S03 VALUES LESS THAN ( '37499999999999' ),

SUBPARTITION S04 VALUES LESS THAN ( '49999999999999' ),

SUBPARTITION S05 VALUES LESS THAN ( '62499999999999' ),

SUBPARTITION S06 VALUES LESS THAN ( '74999999999999' ),

SUBPARTITION S07 VALUES LESS THAN ( '87499999999999' ),

SUBPARTITION SMAX VALUES LESS THAN ( MAXVALUE )

)

(

PARTITION "P2011JAN" VALUES LESS THAN (TO_DATE('2011-02-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011JAN,

PARTITION "P2011FEB" VALUES LESS THAN (TO_DATE('2011-03-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011FEB,

PARTITION "P2011MAR" VALUES LESS THAN (TO_DATE('2011-04-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011MAR,

PARTITION "P2011APR" VALUES LESS THAN (TO_DATE('2011-05-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011APR,

PARTITION "P2011MAY" VALUES LESS THAN (TO_DATE('2011-06-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011MAY,

PARTITION "P2011JUN" VALUES LESS THAN (TO_DATE('2011-07-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011JUN,

PARTITION "P2011JUL" VALUES LESS THAN (TO_DATE('2011-08-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011JUL,

PARTITION "P2011AUG" VALUES LESS THAN (TO_DATE('2011-09-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011AUG,

PARTITION "P2011SEP" VALUES LESS THAN (TO_DATE('2011-10-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011SEP,

PARTITION "P2011OCT" VALUES LESS THAN (TO_DATE('2011-11-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011OCT,

PARTITION "P2011NOV" VALUES LESS THAN (TO_DATE('2011-12-01 00:00:01', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLESPACE CM_XT039_P2011NOV,

PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)

TABLESPACE CM_XT039_PMAX

)as select /* PARALLEL */

TD_ENTRY_ID,

BATCH_CD,

BATCH_NBR,

MESSAGE_CAT_NBR,

MESSAGE_NBR,

ASSIGNED_TO,

TD_TYPE_CD,

ROLE_ID,

ENTRY_STATUS_FLG,

VERSION,

CRE_DTTM,

ASSIGNED_DTTM,

COMPLETE_DTTM,

COMPLETE_USER_ID,

COMMENTS,

ASSIGNED_USER_ID,

TD_PRIORITY_FLG,

CRE_DTTM as ILM_DT,

ILM_ARCH_SW

from ILM_TD_ENTRY

5. Enable logging option for table CI_TD_ENRY.

ALTER TABLE CI_TD_ENTRY NOPARALLEL LOGGING;

6. Create primary index for parent table CI_TD_ENTRY.

CREATE BIGFILE TABLESPACE CM_XT039_IND DATAFILE '+DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

CREATE UNIQUE INDEX XT039P0 ON CI_TD_ENTRY NOLOGGING PARALLEL (

TD_ENTRY_ID

)

PARTITION P1 VALUES LESS THAN ( '12499999999999' ),

PARTITION P2 VALUES LESS THAN ( '24999999999999' ),

PARTITION P3 VALUES LESS THAN ( '37499999999999' ),

PARTITION P4 VALUES LESS THAN ( '49999999999999' ),

PARTITION P5 VALUES LESS THAN ( '62499999999999' ),

PARTITION P6 VALUES LESS THAN ( '74999999999999' ),

PARTITION P7 VALUES LESS THAN ( '87499999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

) TABLESPACE CM_XT039_IND

ALTER INDEX XT039P0 LOGGING NOPARALLEL;

7. Add Primary Key for Parent table CI_TD_ENTRY

ALTER TABLE CI_TD_ENTRY ADD CONSTRAINT XT039P0 PRIMARY KEY(TD_ENTRY_ID) USING INDEX

8. Create Secondary Indexes for Parent table CI_TD_ENTRY.

CREATE UNIQUE INDEX CM_ILM_XT039S8 ON CI_TD_ENTRY ( ILM_DT, ILM_ARCH_SW, TD_ENTRY_ID ) LOCAL COMPRESS ADVANCED LOW

CREATE UNIQUE INDEX XT039S2 ON CI_TD_ENTRY ( ASSIGNED_TO, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE INDEX XT039S3 ON CI_TD_ENTRY ( ENTRY_STATUS_FLG, ASSIGNED_TO ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE INDEX XT039S4 ON CI_TD_ENTRY ( ROLE_ID, TD_TYPE_CD, ENTRY_STATUS_FLG, TD_PRIORITY_FLG, ASSIGNED_TO, CRE_DTTM ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE INDEX XT039S5 ON CI_TD_ENTRY ( BATCH_CD, BATCH_NBR, ENTRY_STATUS_FLG ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE UNIQUE INDEX XT039S6 ON CI_TD_ENTRY ( TD_ENTRY_ID, ASSIGNED_TO, ENTRY_STATUS_FLG )TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE UNIQUE INDEX XT039S7 ON CI_TD_ENTRY ( COMPLETE_USER_ID, COMPLETE_DTTM, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE INDEX XT039S8 ON CI_TD_ENTRY ( ENTRY_STATUS_FLG, TD_TYPE_CD, MESSAGE_CAT_NBR, MESSAGE_NBR ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW

CREATE UNIQUE INDEX CM_ILM_XT039S8 ON CI_TD_ENTRY ( ILM_DT, ILM_ARCH_SW, TD_ENTRY_ID ) LOCAL COMPRESS ADVANCED LOW;

9. After verification of the ILM based tables, user can drop the backup tables “ILM” renamed table.

10. Create all child Tables, Primary Key, Primary Indexes and Secondary Indexes as shown below.

Repeat the following steps for all child tables.

Create Child Table CI_TD_DRLKEY

CREATE TABLE CI_TD_DRLKEY

(

TD_ENTRY_ID NOT NULL ENABLE,

SEQ_NUM NOT NULL ENABLE,

KEY_VALUE DEFAULT ' ' NOT NULL ENABLE,

VERSION DEFAULT 1 NOT NULL ENABLE,

CONSTRAINT CI_TD_DRLKEY_FK FOREIGN KEY(TD_ENTRY_ID) REFERENCES CI_TD_ENTRY

ON DELETE CASCADE)

PARTITION BY REFERENCE (CI_TD_DRLKEY_FK)

ENABLE ROW MOVEMENT

AS SELECT /*+ PARALLEL */ * FROM ILM_CI_TD_DRLKEY;

Create Index

CREATE UNIQUE INDEX XT037P0 ON CI_TD_DRLKEY ( TD_ENTRY_ID, SEQ_NUM ) TABLESPACE CM_XT039_IND NOLOGGING PARALLEL

GLOBAL PARTITION BY RANGE (TD_ENTRY_ID)

(

PARTITION P1 VALUES LESS THAN ( '124999999999' ),

PARTITION P2 VALUES LESS THAN ( '249999999999' ),

PARTITION P3 VALUES LESS THAN ( '374999999999' ),

PARTITION P4 VALUES LESS THAN ( '499999999999' ),

PARTITION P5 VALUES LESS THAN ( '624999999999' ),

PARTITION P6 VALUES LESS THAN ( '749999999999' ),

PARTITION P7 VALUES LESS THAN ( '874999999999' ),

PARTITION P8 VALUES LESS THAN ( MAXVALUE )

)

COMPRESS ADVANCED LOW;

ALTER INDEX XT037P0 LOGGING NOPARALLEL;

ALTER TABLE CI_TD_DRLKEY ADD CONSTRAINT XT037P0 PRIMARY KEY(TD_ENTRY_ID, SEQ_NUM) USING INDEX;

CREATE INDEX XT037S1 ON CI_TD_DRLKEY ( KEY_VALUE, TD_ENTRY_ID ) TABLESPACE CM_XT039_IND COMPRESS ADVANCED LOW;