Data Archive for DB2 OS/390

Susan C. Loria

Data Archive for DB2 OS/390
Hello,

We are running DB2 V6.1 for OS/390 and have a need to review Data Archiving
processing as our data continues to grow and the application has no data
archival procedures in place.

Through research of the archives and vendor website, I was wondering if
anyone has made use of the following:

* From past places I've worked, usually the application had an unload,
followed by some in-house routine that selected data and archived other data
writing out new load files which is then followed by a load file. The team
has indicated they don't have time to do this method.

* Using the DISCARDs parameter within the Reorganization and building a
DISCARDs file and then using this file as a load file into a LOAD RESUME
should the end user need to get that data back? I haven't tested this yet
as I still researching what can be various methods, but again was wondering
if anyone has implemented this method in their shop.

* IBM's product solution of DB2 Row Archive Manager - has anyone used this?


* Princeton Softech's Archive for DB2 - has anyone used this and are there
any problems or scenarios to watch out for? Did this product meet your
expectations?

And of course we're looking at costs as well as if we were to upgrade to a
newer release of DB2 and we have old archival data, the solution will need
to support getting the data back regardless of what DB2 release we are
running. Our company sometimes has a need to use old 8+ years of data and
may need to restore it for some purpose. We do not keep data in a
month/year format so rolling partitions by date criteria is not an option.

Also, one last question within your organization, who normally executes the
removal/restoration of the data - the DBA, the end-user, or a person
identified as the "data owner" or other? I'm trying to define roles in
this process too. I feel the DBA should administer whatever process is put
into place, but the "data owner" should be able to archive/restore the data
should it be necessary.

Thanks for any information you can provide,
Susan Loria
Nielsen Media Research
[login to unmask email]



Ken McDonald

Re: Data Archive for DB2 OS/390
(in response to Susan C. Loria)
Hi Susan,

Truthfully, I am not familiar with the functionality of the two archival
tools you referenced... But, how about using a log processing tool for
creation of archive files. You can create archive files from the logged
deletes when removing aged rows. These files can be merged and stored on
less expensive media and recalled/restored for use when need arises.

BMC's Log Master for DB2 provides a 'Logical Log' format which can be
reprocessed later to produce any of its other outputs (UNDO SQL to reinsert
the deleted rows, LOAD output, reports, etc.). The 'Logical Log' contains
descriptions of the tables as they existed at the time the file was created,
so if your table definitions have changed in the 8+ years you are archiving,
the data can still be interpreted. CA's Log Analyzer, I believe, has a LOAD
output which may also be reprocessed. IBM also has an entry in the log
processing arena.

Just an idea,
Ken McDonald

-----Original Message-----
From: Loria, Susan [mailto:[login to unmask email]
Sent: Wednesday, January 10, 2001 7:33 AM
To: [login to unmask email]
Subject: Data Archive for DB2 OS/390


Hello,

We are running DB2 V6.1 for OS/390 and have a need to review Data Archiving
processing as our data continues to grow and the application has no data
archival procedures in place.

Through research of the archives and vendor website, I was wondering if
anyone has made use of the following:

* From past places I've worked, usually the application had an unload,
followed by some in-house routine that selected data and archived other data
writing out new load files which is then followed by a load file. The team
has indicated they don't have time to do this method.

* Using the DISCARDs parameter within the Reorganization and building a
DISCARDs file and then using this file as a load file into a LOAD RESUME
should the end user need to get that data back? I haven't tested this yet
as I still researching what can be various methods, but again was wondering
if anyone has implemented this method in their shop.

* IBM's product solution of DB2 Row Archive Manager - has anyone used this?


* Princeton Softech's Archive for DB2 - has anyone used this and are there
any problems or scenarios to watch out for? Did this product meet your
expectations?

And of course we're looking at costs as well as if we were to upgrade to a
newer release of DB2 and we have old archival data, the solution will need
to support getting the data back regardless of what DB2 release we are
running. Our company sometimes has a need to use old 8+ years of data and
may need to restore it for some purpose. We do not keep data in a
month/year format so rolling partitions by date criteria is not an option.

Also, one last question within your organization, who normally executes the
removal/restoration of the data - the DBA, the end-user, or a person
identified as the "data owner" or other? I'm trying to define roles in
this process too. I feel the DBA should administer whatever process is put
into place, but the "data owner" should be able to archive/restore the data
should it be necessary.

Thanks for any information you can provide,
Susan Loria
Nielsen Media Research
[login to unmask email]








Rob Crane

Re: Data Archive for DB2 OS/390
(in response to Ken McDonald)
CA's RC/Extract also has the functionality you are looking for. Most of
these types of tools allow you to use utilities or standard SQL
processing. In my experience utilities were the way to go but not all
application folks have the ability to run utilizes so sometimes the SQL
option is nice to have.

At a previous shop we decided to write our own process that was not
dependent on any vendor product since tying your archive process to a
vendor implies a long term contract with that vendor to meet you archive
needs. In this user written process we also unloaded the DDL of the
objects being archived so the archive data could be isolated and loaded
back into a separate table that had the correct DDL. Had a control
table that kept track of the data being archived (local and remote), the
DDL file name (local and remote), date of extraction, counts of data
extracted, etc.

-----Original Message-----
From: Loria, Susan [mailto:[login to unmask email]
Sent: Wednesday, January 10, 2001 7:33 AM
To: [login to unmask email]
Subject: Data Archive for DB2 OS/390


Hello,

We are running DB2 V6.1 for OS/390 and have a need to review Data
Archiving
processing as our data continues to grow and the application has no data
archival procedures in place.

Through research of the archives and vendor website, I was wondering if
anyone has made use of the following:

* From past places I've worked, usually the application had an unload,
followed by some in-house routine that selected data and archived other
data
writing out new load files which is then followed by a load file. The
team
has indicated they don't have time to do this method.

* Using the DISCARDs parameter within the Reorganization and building a
DISCARDs file and then using this file as a load file into a LOAD RESUME
should the end user need to get that data back? I haven't tested this
yet
as I still researching what can be various methods, but again was
wondering
if anyone has implemented this method in their shop.

* IBM's product solution of DB2 Row Archive Manager - has anyone used
this?


* Princeton Softech's Archive for DB2 - has anyone used this and are
there
any problems or scenarios to watch out for? Did this product meet your
expectations?

And of course we're looking at costs as well as if we were to upgrade to
a
newer release of DB2 and we have old archival data, the solution will
need
to support getting the data back regardless of what DB2 release we are
running. Our company sometimes has a need to use old 8+ years of data
and
may need to restore it for some purpose. We do not keep data in a
month/year format so rolling partitions by date criteria is not an
option.

Also, one last question within your organization, who normally executes
the
removal/restoration of the data - the DBA, the end-user, or a person
identified as the "data owner" or other? I'm trying to define roles in
this process too. I feel the DBA should administer whatever process is
put
into place, but the "data owner" should be able to archive/restore the
data
should it be necessary.

Thanks for any information you can provide,
Susan Loria
Nielsen Media Research
[login to unmask email]



the
DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list can
be