A script for DB2 data extraction and conversion

To migrate data from the mainframe to DB2 UDB on the AIX platform, the process is usually as follows: log on to the mainframe, extract data to a file, and ftp file to the AIX machine, then load the data to DB2 UDB. This requires two different working environments.

The alternative is: by using only one script, I can complete the entire process in the DB2 UDB AIX environment. This process is very fast and will not burden the mainframe or AIX environment.

What you need to do is to use the mainframe connection and use the EXPORT command to listen to Data. For example:
Db2 connect to DB2S390 USER xxxxxx USING yyyy

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

You can import the output file to DB2 UDB with the AIX system. I have written a Korn shell script on AIX. You only need to give the chart name, username, and password to extract and load data from the mainframe using a command. It is also worth mentioning that you can use the db2look command to extract DDL data using the DB2 Connect connection of the mainframe.

#! /Bin/ksh
# Extract data from Mainframe and Load into
# 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
Db2 connect to DB2S390 USER $ USERID using $ PSWD

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
Db2 DISCONNECT DB2S390;

# 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
Replace into dbo. $1 statistics no indexing mode autoselect"

Echo 'date'
Echo "End of Load"

Db2 DISCONNECT DDHAP00;

# Reset to previous directory
Cd $ PWD