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.
#!/bin/ksh # 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 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
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 [nnn] 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!
- 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.