Archiving data in a partitioned environment

You can retrieve data from a production database in a partitioned environment for archival.

In the partitioned database environment, as a best practice, consider archiving the previous day's data everyday. The process requires you to retrieve the old data from each table and then copy it to the archive table. The following queries can be used to retrieve yesterday's data from each table: 

SELECT * FROM EVENTS WHERE DATE_TIME >= ‘2009/09/04’ ANDDATE_TIME <
 '2009/09/05';
 SELECT * FROM CD_STATS_LOG WHERE LOG_DATE_TIME >= ‘2009/09/04’AND 
LOG_DATE_TIME < '2009/09/05';
 SELECT * FROM CE_STATS_LOG WHERE LOG_DATE_TIME >= ‘2009/09/04’AND 
LOG_DATE_TIME < '2009/09/05';
 SELECT * FROM EVENT_COMMENTS WHERE DATE_TIME >= ‘2009/09/04’ ANDDATE_TIME 
< '2009/09/05'; 
SELECT * FROM AUDIT_LOG WHERE LOG_DATE_TIME >= ‘2009/09/04’
 ANDLOG_DATE_TIME < '2009/09/05';
 SELECT * FROM EVENTS WHERE DATE_TIME >= ‘2009/09/04’ ANDDATE_TIME < 
'2009/09/05'; 
SELECT * FROM CD_STATS_LOG WHERE LOG_DATE_TIME >= ‘2009/09/04’AND LOG_DATE_TIME <
 '2009/09/05'; 
SELECT * FROM CE_STATS_LOG WHERE LOG_DATE_TIME >= ‘2009/09/04’AND
 LOG_DATE_TIME < '2009/09/05';
 SELECT * FROM EVENT_COMMENTS WHERE DATE_TIME >= ‘2009/09/04’ ANDDATE_TIME
 < '2009/09/05'; 
SELECT * FROM AUDIT_LOG WHERE LOG_DATE_TIME >= ‘2009/09/04’ 
ANDLOG_DATE_TIME < '2009/09/05';
SELECT * FROM  CX_STATS_LOG  WHERE LOG_DATE_TIME >= '2009/09/04' AND
    LOG_DATE_TIME < '2009/09/05';
SELECT * FROM  QF_STATS_LOG WHERE LOG_DATE_TIME >= '2009/09/04' AND
    LOG_DATE_TIME < '2009/09/05';
SELECT * FROM MQ_STATS_LOG WHERE LOG_DATE_TIME >= '2009/09/04' AND
    LOG_DATE_TIME < '2009/09/05';
SELECT * FROM  AF_STATS_LOG  WHERE LOG_DATE_TIME >= '2009/09/04' AND
    LOG_DATE_TIME < '2009/09/05';
SELECT * FROM BP_STATS_LOG  WHERE LOG_DATE_TIME >= '2009/09/04 'AND
    LOG_DATE_TIME < '2009/09/05';
SELECT * FROM  FG_STATS_LOG  WHERE LOG_DATE_TIME >= '2009/09/04' AND
    LOG_DATE_TIME < '2009/09/05';
SELECT * FROM  CC_ALERT  WHERE DATE_TIME >= '2009/09/04' AND  DATE_TIME <
    '2009/09/05';

The query assumes that the current date is ‘2009/09/05’ and retrieves all the data from yesterday (2009/09/04). This is a generic approach to retrieve data regardless of the database type. There are other alternative steps to retrieve the data for archival, and the process varies for each database type. Refer to one of the following sections for more information on your database type.

Oracle table partitioning

In Oracle, IBM® Control Center names the table partitions by using the following pattern:

For the EVENTS table, the partitions are named as EVyyyymmdd where EV is the prefix, yyyy is the year, mm is the month and dd is the date of data. For the CD_STATS_LOG table, the partitions are named as CDyyyymmdd where CD is the prefix, yyyy is the year, mm is the month, and dd is the date of data.

For the CE_STATS_LOG table, the partitions are named as CEyyyymmdd where CE is the prefix, yyyy is the year, mm is the month, and dd is the date of data.

For the EVENT_COMMENTS table, the partitions are named as ECyyyymmdd where EC is the prefix, yyyy is the year, mm is the month, and dd is the date of data. For the AUDIT_LOG table, the partitions are named as ALyyyymmdd where AL is the prefix, yyyy is the year, mm is the month, and dd is the date of data. If today’s date is 2009/09/05, to retrieve yesterday’s partition, use the following commands:

DROP TABLE TMP_ARC_EVENTS;
ALTER TABLE EVENTS EXCHANGE PARTITION EV20090904 WITH TABLE
TMP_ARC_EVENTS;

The data from table TMP_ARC_EVENTS can then be copied or moved to the archive table. Repeat the same two commands for the other tables.

Microsoft SQL server (MSSQL) table partitioning

In MSSQL, a specific partition’s data can be retrieved by using a partition function. IBM Control Center uses a different partition function for each table in MSSQL.
  • The EVENTS table uses EV_PART_FUNC.
  • The CD_STATS_LOG table uses CD_PART_FUNC.
  • The CE_STATS_LOG table uses CE_PART_FUNC.
  • The EVENT_COMMENTS table uses EC_PART_FUNC.
  • The AUDIT_LOG table uses AL_PART_FUNC.
  • CX_STATS_LOG table uses CX_PART_FUNC
  • QF_STATS_LOG table uses QF_PART_FUNC
  • MQ_STATS_LOG table uses MQ_PART_FUNC
  • AF_STATS_LOG table uses AF_PART_FUNC
  • BP_STATS_LOG table uses BP_PART_FUNC
  • FG_STATS_LOG table uses FG_PART_FUNC
  • CC_ALERT table uses CA_PART_FUNC

To retrieve and archive data from a specific partition on the EVENTS table, use the following commands:

DROP TABLE TMP_ARC_EVENTS;

ALTER TABLE EVENTS SWITCH PARTITION $PARTITION.EV_PART_FUNC

('2009/09/04') TO TMP_ARC_EVENTS

PARTITION$PARTITION.EV_PART_FUNC ('2009/09/04');

Copy and move the data from the TEMP_ARC_EVENTS table to the archive table. The other tables can be archived in the same way.

IBM DB2 – Linux, UNIX, and Windows table partitioning

In DB2, IBM Control Center names the table partitions by using the following pattern:

  • For the EVENTS table, the partitions are named as EVyyyymmdd where EV is the prefix, yyyy is the year, mm is the month, and dd is the date of data.
  • For the CD_STATS_LOG table, the partitions are named as CDyyyymmdd where CD is the prefix, yyyy is the year, mm is the month, and dd is the date of data.
  • For the CE_STATS_LOG table, the partitions are named as CEyyyymmdd where CE is the prefix, yyyy is the year, mm is the month, and dd is the date of data.
  • For the EVENT_COMMENTS table, the partitions are named asECyyyymmdd where EC is the prefix, yyyy is the year, mm is the month, and dd is the date of data.
  • For the AUDIT_LOG table, the partitions are named as ALyyyymmdd where AL is the prefix, yyyy is the year, mm is the month, and dd is the date of data. If today’s date is 2009/09/05, to retrieve yesterday’s partition, use the following commands:
DROP TABLE TMP_ARC_EVENTS;
ALTER TABLE EVENTS DETACH PARTITION EV20090904 INTO
TMP_ARC_EVENTS;

The data from the table TMP_ARC_EVENTS can then be copied or moved to the archive table. The other tables can be archived in the same way.

IBM DB2 z/OS table partitioning

In DB2 z/OS, the UNLOAD utility can be used to copy the data between tables.

MySQL table partitioning

In MySQL, the SELECT … INTO OUTFILE SQL command can be used to dump a specific range of data to a file, and then the LOAD DATA INFILE command can be used to load data into an archive table.