""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
ARCHIVING FOR MAXIMUM SPEED AND MINIMUM SPACE
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
DATE : 3/87 NUMBER : 87-3-2
PRODUCT : R:BASE SYSTEM V VERSIONS : ALL
CATEGORY : DATA TRANSFER SUBCATEGORY : ARCHIVE/BACKUP
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
NEED:
""""
I have a very large database and I want to keep it as small as
possible to save on-line storage and improve application speed by
archiving old (or seldom accessed) information and storing it off-
line. What are some of the methods used for archiving?
SOLUTION:
""""""""
By archiving your data, you can keep your database smaller and you may
significantly improve speed.
"If we had infinite storage, and very fast computers, there would be
no need to archive information. Unfortunately, this is not the case,"
says Dennis W. Fleming, President of International Information
Services in Hawley, PA. "In any application where you have many to
one relationships, you may need to archive data to reduce the number
of rows in tables and improve overall system performance. This
migration of data from the current period, to an older period, to a
very old period, is a common practice. The value of information
diminishes as a function of time, but this does not mean we want to
delete data before saving it in a controlled fashion in order to allow
for easy recall."
Archival Methods
""""""""""""""""
You have a number of archiving options from which to choose. You can:
* TABLE TO TABLE: Move old data from a current table to an old table.
Here the database will continue to grow in size and all information
will be on-line if it is needed, but the most often accessed table
will be small.
* TABLE TO FILE: Move the old data into ASCII files, delete it from
the current database, and pack the database. If the data is needed
again, the ASCII files can be loaded back into the database.
* DATABASE TO DATABASE: Store one cumulative database off-line, on a
different computer, or on the same computer but in a different
subdirectory. Using this method, you would transfer the data from
the current database to the cumulative database.
* POST TO SUMMARY: Post transaction information to master records and
then empty all the transactions into ASCII files to be stored off-
line.
* DATABASE BACKUPS: Make a backup copy of the database at the end of
every month and then purge the old records from the current
database. With this method you could recreate the database the way
MICRORIM ONLINE April 1987 ---------------------------------- Page 1
it looked at the end of any particular month.
* COMBINATIONS: Combine several different methods to meet different
needs.
Choose the Data and Archival Methodology
""""""""""""""""""""""""""""""""""""""""
Before deciding which method to use, take a look at your database
design, your environment, and your corporate needs. Then decide what
data you will archive and when.
Look at all the tables that are on the many side of many to one
relationships, these tables are the most common candidates for
archival. Most people archive old records based either on a specific
cutoff date or based on saving the most recent record (or two) for
each customer (or whatever the many column is) and archiving the rest.
You might use a combination of these two methodologies by keeping the
latest record as long as it was not older than a selected cutoff date.
Decide When To Archive
""""""""""""""""""""""
Mr. Fleming suggests that you look at and analyze the following
factors when designing your archival system and setting up a schedule:
* What is your disk drive capacity and speed?
* How long is your corporate current period?
* What is the trade off between the immediate availability of data
and the processing time?
* Some tables may grow much faster than others and therefore may
require different archival periods.
Build Your Archival System
""""""""""""""""""""""""""
Establish a specific archival methodology for your application
including the names of the tables being archived, the archival method
(or combination method) for each table, the names of command files,
and the specific schedule for data migration from current, to old, to
very old.
Develop a command file that will carry out the archival process in two
stages. First, move information which is considered old but still
useful from the current table XXXX into a different table OLDXXXX.
Secondly, when data is no longer needed for reporting purposes, but
might be required at some future date, unload it from the OLDXXXX
table into an ASCII DOS file and append the file to a master DOS file
that has the same name as the table (OLDXXXX).
Using a command file to do the archival process ensures that all steps
in the process are completed. You might also want to include checks
and advisory messages in the command file to make sure it is not
accidentally run twice or to advise the operator about what is going
on. You might even want to keep a control table that is updated and
checked by the command file so that you can easily determine when the
last archival was done.
MICRORIM ONLINE April 1987 ---------------------------------- Page 2
Example
"""""""
For example, you may decide to move all part transactions from a TRANS
table to an OLDTRANS table at the beginning of each calendar year. By
having two reports that are identical except that one uses the TRANS
table and the other uses the OLDTRANS table, you can print the same
report for both tables.
Now, if you want to review the cost of parts for a specific vendor for
the past three years, you can print the transaction report for this
year by using the TRANS table, and then do the prior years using
OLDTRANS. If you have been moving data from OLDTRANS into an ASCII
DOS file OLDTRANS, you may need to load the "very old" transactions
back in.
MICRORIM ONLINE April 1987 ---------------------------------- Page 3