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

<snippet from Redbook pages 60 – 61>

Declaring a cursor for use with the Cross Loader

The cursor definition has to be put between the EXEC SQL and ENDEXEC keywords in the utility input stream:

EXEC SQL DECLARE cursor-name CURSOR FOR select statement ENDEXEC

In Example 4-11 we declare a cursor for extracting rows with ADMRDEPT = ‘A00’ from PAOLOR7.DEPT.

Example 4-11 Declare a cursor for the Cross Loader

EXEC SQL

DECLARE C1 CURSOR FOR

SELECT * FROM PAOLOR7.DEPT

WHERE ADMRDEPT = ‘A00’

ORDER BY 1 ASC

ENDEXEC

In Example 4-12 we show how this cursor can now be used in a LOAD statement (Cross Loader.)

Example 4-12 Usage of a cursor in the LOAD statement

LOAD DATA

INCURSOR C1

INTO TABLE PAOLOR2.DEPT

The SQL statement in the declare cursor definition can be any valid SQL statement including joins, unions, data conversions, aggregations, special registers, and UDFs.  The source data can be on a local server or remote server using DRDA access.

Leave a Comment

Name

Email

Website

Comments

More