Making a copy of a database for query\reporting

Ken McDonald

Making a copy of a database for query\reporting
Hi George,

There are several 'off the shelf' products and techniques available for
moving data. Some questions to be answered before choosing would be how
current does the copy need to be, how much latency can be tolerated between
updates to the primary database and the copy, and how frequently does the
copy need to be updated? You mentioned that this will be a duplicate copy,
but, if it is not an exact duplicate, how much data transformation or
removal of columns must occur?

If there is a low need for currency and the copy can be refreshed on a
relatively infrequent bases, there are bulk data move products and solutions
which may fit your needs, similar to the UNLOAD/LOAD process you discussed.

Some of the transation propagation products available 'Capture' changes from
the DB2 log via an IFI log exit allowing for closer to real time
propagation. This type of product include IBM's DPROPR and BMC's Change
Data Move. These products usually require that tables are defined with Data
Capture Changes to ensure that full rows are logged on update transactions.

Other products process the DB2 Log via a batch job to extract changes and
produce SQL which will need to be applied at the target site. One
difference between these and the log exit 'Captures' is that tables can be
defined with Data Capture None, and these products can 'complete' partially
logged update rows from available resources (TS, Image Copy, and additional
log). The batch jobs can be scheduled to run to meet an individual site's
latency needs. These products include BMC's Log Master for DB2 and
CA/Platinum's Log Analyzer. Log Master for DB2 also has an EXEC SQL
compenent which operates about 2.5 times faster than dynamic SQL processors
such as SPUFI. BMC also has a new fast SQL exectuion product, called Apply
Plus, which can greatly speed the 'Apply' portion of transaciton

I apologize for not including more available product names, but suggest you
visit the Web sites for the various vendors to see available offerings...
keeping in mind that an individual vendor may have multiple solutions to
address the differing data propagation needs.

Good Luck,
Ken McDonald
BMC Software
'My opinions...'

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Monday, January 03, 2000 7:49 AM
To: [login to unmask email]
Subject: Making a copy of a database for query\reporting


I'm interested in anyone's experiences in creating a duplicate copy of a

We currently use unload\load for smaller databases. But, I am now asked to
the same for a larger DB.

o Does anyone have experiences they can share regarding IBM's Data

o How about BMC's Change Data Capture or others?

What other products are out there?

Is there a solution that relies on off the shelf products and techniques?

I'll appreciate all replies.

Thanks, George White.