Keeping it in the Family: Batch Movement of Data Between DB2 Databases and/or Subsystems

Posted by Frank Fillmore on May 15, 2013 under DB2 for Linux Unix Windows, DB2 for z/OS. Tags: , .

A few weeks ago a customer was confronted with a common challenge.  They had to move terabytes of data – billions of rows – from DB2 for LUW to DB2 for z/OS.  I suggested a “cursor-based load” (aka the “cross-loader”).  This DB2 for z/OS DBA team is top-notch, with centuries of collective experience, but I mostly was met with blank stares.  So here’s the brief refresher (or introduction) I gave them on the cross-loader.

The common method for moving data from DB2 for LUW tables to DB2 for z/OS could be a serial process where the data is

  1. extracted from DB2 for LUW
  2. transported to the z/OS platform
  3. loaded into DB2 for z/OS

This can be done as three discrete steps in a variety of ways.  The problem with three discrete steps is the I/O overhead of landing the data to disk three times: to the local extract file, FTPed to z/OS, loaded into DB2.  In addition, the steps are serial (e.g. the extract must complete before the FTP can begin).

 An alternative is using a cursor-based LOAD (aka the “cross-loader”).  The cross-loader has been available in DB2 for z/OS since v7.

 Steps to enable the cross-loader:

  1. Set up DRDA definitions in the DB2 Communications Database (CDB) in the DB2 for z/OS subsystem that will be running the loads (i.e. the “target”).
  2. Create DB2 for z/OS nicknames for the tables to be loaded from the DB2 for LUW source.

In the example below the table PAOLOR7.DEPT is a nickname.  The LOAD utility jobs can be scheduled to minimize the impact on production workloads.

This would be relatively easy to set up and test in a development environment.   This is the fastest, easiest, simplest way to move the data to z/OS.  This method will also use the least machine resources.


References for cursor-based load

DB2 10 for z/OS Information Center “Loading data by using the cross-loader function

Redbook “Moving Data Across the DB2 Family

Read More…