0

I need to be able to purge rows, based on user criteria (e.g. older than 3 years) from our DB2 tables. However, for those rows I am purging - I need to be able to archive the data such that it can be "deciphered" for audit purposes (not reload/recover the data into its table).

Of course, the deleted DB2 logs have all the data - but if we had to look at them later (a year or two down the road, say for audit purposes) they would not be a format that we/users could easily decipher. We wouldn't want to reload since the table and business rules have changed...maybe even the DB2 version has changed.

So does anybody know if there is a technique or utility to provide a solution for this type of problem? Not looking for anything fancy - maybe there is a "generic" trick for tying the data to it's column name and saving it out to a comma delimited file - or something like that.

Thanks.

user278458
  • 201
  • 1
  • 2
  • 5

1 Answers1

0

If your purging frequency is low, use a temp table to capture the user identity you want to action. Perform an export joining all your tables (to make in essence a flat table), save your old data to a CSV. Then when the export is done, perform a row delete. Make sure you execute each seperately to ensure data integrity.

CREATE GLOBAL TEMPORARY TABLE OLDUSERS
AS (SELECT USERID FROM USERTABLE) DEFINITION ONLY
ON COMMIT PRESERVE ROWS;

INSERT INTO OLDUSERS
SELECT USERID
FROM USERTABLE
JOIN USERINFOTABLE ON USERTABLE.USERID=USERINFOTABLE.USERID
WHERE USERINFOTABLE.LASTLOGING < '2011-01-01 00:00:00.0';

EXPORT TO filename OF DEL
SELECT USERID, USERNAME, ...
FROM USERTABLE
JOIN USERINFOTABLE ON USERTABLE.USERID=USERINFOTABLE.USERID
WHERE USERID IN (SELECT USERID FROM OLDUSERS);

DELETE FROM USERINFOTABLE WHERE USERID IN 
(SELECT USERID FROM OLDUSERS);

DELETE FROM USERTABLE WHERE USERID IN 
(SELECT USERID FROM OLDUSERS);

If youR frequency to do this is more demanding, you may want to create a stored procedure which will take the cut off date and perform the above actions.

GeekyDaddy
  • 384
  • 2
  • 12