Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Extract data from DB2 on MVS from within DB2 UDB on AIX

Use this script to extract data from DB2 on MVS from within DB2 UDB on AIX.

To migrate data from the mainframe to DB2 UDB on AIX, the process normally followed involves this: Log into the...

mainframe, run a job to extract the data into a file, ftp'ing the file to the AIX box and then loading the data in DB2 UDB. This requires two different environments to work from.

Instead, I can do the entire process from within DB2 UDB on AIX by using only one script. And, the process is very fast and did not overtax the mainframe or the AIX environment.

All you need do is connect using the mainframe connection and then use the EXPORT command to extract the data. Example:

db2 connect to DB2S390 USER xxxxxx USING yyyy 

db2 "export to exportfile.ixf of IXF select * from SYSIBM.SAMPLE"

You can then use the exported file to load into a database in DB2 UDB on AIX. I've create a Korn shell script on AIX to do the extract (from the mainframe) & load with one command. Just specify the table name, Userid and password. Also worth mentioning is the fact that you can use the DB2 Connect connection to the mainframe for extracting DDL by using the db2look command.

#   Extract data from Mainframe and Load into the 
#   Specified Table in the DDHAP00 Database
export LOADFILE="/udb/db2aix01/file/file1/$1"
export USERID=$2
export PSWD=$3
export CurrentPWD=$PWD  # Save current directory

echo 'date'

# Connect to mainframe

echo "Extracting data from mainframe..."

# Extract data from mainframe
db2 "export to $LOADFILE.ixf of IXF select * from DBDEV.$1"

echo 'date'
echo "Extract completed"

# Disconnect from mainframe

# Connect to database on AIX
db2 CONNECT TO DDHAP00 USER xxxxx USING xxxxxx;

# Clear load messages file
cp /dev/nul /udb/db2aix01/file/file1/Load_$1.msg

echo 'date'
echo "Loading data..."

# Load data from flat file
db2 "LOAD FROM $LOADFILE.ixf OF IXF MODIFIED BY pagefreespace=0
totalfreespace=0 ROWCOUNT 999999999 WARNINGCOUNT 9 MESSAGES Load_$1.msg

echo 'date'
echo "End of Load"


# Reset to previous directory
cd $PWD

Reader Feedback

Carl C. writes: Thanks for this valuable tip. I have a question: How does one define the DB2 on MVS connection to make the following statement in the tip work?

db2 connect to DB2S390 USER xxxxxx USING yyyy

Note: We may have many DB2 databases on MVS.

Eva H. writes: I am glad to see such info become available to the DB2 community. I have used such scripts on both UNIX and NT to gather data with the mainframe (OS/390 and z/OS). Besides all the good points, I'd like to point out one drawback, the performance when dealing with a large volume of data.

Underneath the export/import command, an INSERT operation is performed rather than the bulk LOAD. So, if you are doing a massive data movement, this method will take a long time, if not forever. Also, if you are going from NT or UNIX up to the mainframe, the mainframe does not recognize commitcount, so you will run out of log space really fast and the import operation will fail.

To upload data to mainframe, one could used the mainframe utility stored procedure that can do the LOAD operation from NT or UNIX via DB2 connect. However, that is a different subject...

Rich G. writes: In answer to Carl C.'s response pertaining to defining the connect to DB2 on the mainframe, the following commands should be issued on the AIX box:

db2 catalog tcpip node db2s390 remote [mainframe-DNS] server
db2 catalog db db2s390 as db2s390 at node db2s390
authentication dcs
db2 catalog dcs db db2s390 as db2s390

This sets up the appropriate link to the mainframe. Hope that helps!

More on this topic

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.


Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.