Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

Efficient Technique for Archiving Data Stored in Database Tables

IP.com Disclosure Number: IPCOM000022152D
Original Publication Date: 2004-Feb-27
Included in the Prior Art Database: 2004-Feb-27
Document File: 1 page(s) / 52K

Publishing Venue

IBM

Abstract

DB2 databases containing data considered to be inactive can be moved, or archived, to DB2 archive tables or to archive flat files. It is imperative that the archiving process be done efficiently and in a timely manner. The need to reduce the potential of data loss during the archiving process requires the time between the insert of the inactive data into the archive table and the removal from its source table be done as close together as possible as well as a single unit of work. To satisfy these requirements a cursor on the result set of a database query on the tables containing inactive data is used.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 100% of the total text.

Page 1 of 1

Efficient Technique for Archiving Data Stored in Database Tables

The technique, Figure 1, is to use a JDBC result set cursor on a database SELECT query and for each row of the result set use the column values of the row in both the INSERT and DELETE statement predicates. Once the entire result set is processed, the cursor is closed.

Figure 1 - Efficient technique for archiving inactive data

SELECT column1, column2 FROM table1 WHERE column1 > DATE

Close result set

FOR EACH ROW

INSERT INTO archive_table (column1, column2, ...,columnN) SELECT column1,column2, ...,columnN FROM table1 WHERE table1.column1 = [current row value for column1] AND table1.column2 = [current row value for column2]

DELETE FROM table1 WHERE table1.column1 = [current row value for column1] AND table1.column2 = [current row value for column2]

1

[This page contains 2 pictures or other non-text objects]