DATE      :  3/87                    NUMBER       :  87-3-2
    PRODUCT   :  R:BASE SYSTEM V         VERSIONS     :  ALL
    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?
    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-
    * 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
    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
    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