Perhaps the most significant requirement for
coding successful Db2 application programs is having a reasonable set of test
data to use during the development process. Without data, there is no way to
test your code to make sure it runs. But there are many issues that must be
overcome in order to have a useful test data management process. Today we will
talk about this along with a key enabling component of such a process, BCV5 from UBS Hainer.
One of the first things that organizations try
is to make a copy of the production for testing. But this is easier said than
done. You cannot just stop your production databases to make a copy of them for
testing. But you still want a fast, consistent copy of the data. Consistent in
terms of the units of work and referential integrity. And maybe you just want
some of the data, not all of it. And we haven’t even talked about the potential
regulatory concerns if you are copying personally identifiable information.
When you initially go to build your test data
environment, the tools at your disposal are likely the utilities that came with
Db2. This means that you will start with solutions like unloading and loading
the data. But the LOAD and UNLOAD utilities are not known for their speed, so this
can take a long time to accomplish – both for the initial creation and for any
subsequent refreshing of the test data. This is important because test data must
be refreshed on a regular basis as application testing is performed. Without
the capability to refresh it is impossible to compare test runs and develop
your programs consistently.
So, what should you do? Well, the first step
is to create a consistent test bed either from scratch or, more likely, from
production. And you want to do this efficiently and without interrupting
production processing. This core bed of test data can be manipulated to reduce
its size and even to satisfy regulatory requirements. With a core set of data
you can then develop procedures to copy this data out to the various
development and QA environments. To succeed, you need a fast method of
populating multiple environments, on demand, from the approved test bed.
A key to achieving such an environment is an
efficient Db2 data copying tool like BCV5, which can be used to copy and
refresh Db2 data very rapidly. BCV5 copies Db2 table spaces and indexes within
the same Db2 subsystem or even between different Db2 subsystems much faster
than unloading and reloading the data. Using BCV5 you can deliver speedy copies
because it works directly at the VSAM level. As BCV5 copies at the VSAM level
it can replace Db2-internal OBIDs with the correct target values. This is
significantly more efficient than unloading and loading one row at a time. And
it takes away the complicated user-managed OBIDXLAT capability of DSN1COPY.
If you have used DSN1COPY in the past you know
that it can be difficult to use; this is not the case with BCV5. With DSN1COPY
you must specify a series of parameters that describe the input, such as the
PIECESIZE, NUMPARTS, DSSIZE, whether it is a LOB table space or not, and more. BCV5
determines all required values automatically, making things a lot easier and
less prone to failure.
And if you use LOB and XML data, and these
days who doesn’t, BCV5 handles this data like any other, copying it at the same
rate as regular table spaces.
BCV5 copies everything, not just the physical
Db2 data, but also all of the associated structures including databases, table spaces,
tables, indexes, and even views, triggers, aliases, synonyms, constraints, and so
on! And you don’t need to worry if objects already exist; BCV5 will check for
compatibility and keep the environment accurate. And all of the functionality
you’d expect is there, such as the ability to rename objects between
environments and to run the copy job either manually or via a job scheduler.
Furthermore, you can interact with BCV5 using either an ISPF or a GUI
interface.
Using BCV5, you can even use image copies as
the source for your test data. BCV5 can use the most recent image copy, or an
older image copy chosen by generation number, timestamp, or data set name
pattern. BCV5 can automatically identify the correct image copy data sets and
use them as the source for the data to be copied. You can even use BCV5 to
refresh indexes using image copies of indexes if they exist.
Keeping Db2 statistics accurate can be another
vexing test data issue. Generally speaking, you want to keep statistics up-to-date,
but in test you probably want test statistics to mirror production. BCV5 can
copy both RUNSTATS and RTS (Real Time Stats) directly from the source environment into the
target. There is no need for a separate RUNSTATS job or to do a REORG in order
to collect an RTS baseline.
And let’s not forget the most impressive
aspect of BCV5, its speed and efficiency. BCV5 runs tasks in parallel with automatic
workload balancing to further improve the performance of copying Db2 data. This
efficiency comes in three forms: less CPU consumption, less elapsed run time,
and a reduction in the management steps which can be automated instead of being
done manually.
A case in point, a large automobile
manufacturer uses BCV5 to manage its large Db2 test data environment consisting
of over 11,000 table space partitions, another 11,000+ index partitions, and 20
LOBs. Before deploying BCV5 the company required hundreds of jobs that took
almost 2 weeks to create, configure, and execute. After automating the process
with BCV5, the entire process requires only 6 jobs that can refresh the test
environments in 91 minutes. Impressive, no?
UBS Hainer markets other tools that augment
and assist BCV5. For example, its In-Flight Copy add-on can enable BCV5 to get
up-to-the-moment accurate data by gathering information from the Db2 log to
make consistent copies of table spaces and indexes. It also offers a Reduction
and Masking Data add-on to assist with enforcing privacy regulations in your
test data. And BCV4 can be used to duplicate an entire Db2 subsystem.
The bottom line is that setting up test data can
be difficult and time-consuming. Without a well-thought-out approach to
gathering and refreshing test beds, application developers and quality
assurance personnel will run into issues as they try to test Db2 code with
corrupted or improper data. If your organization has issues with effectively
managing test data for your Db2 for z/OS developers, take a look at UBS
Hainer’s BCV5 solution for quickly copying and refreshing
Db2 data.
