One of the applications developer in our shop wanted to know if he can copy data from a mainframe DB2 table to DB2 UDB on LUW. Though there are several ways of doing it, this is the one suggested him assuming the table structure in z/OS and LUW are the same.
1. Some of the tools that can be used to achieve this: Quest Central for DB2, Toad for DB2 or DB2CMD interface
2. Connect to the mainframe database (CONNECT TO ssid) and issue the EXPORT command "EXPORT TO d:\file_name.ixf OF IXF MESSAGES d:\msg_export.txt SELECT *FROM prefix.table_name"
Note: Don't forget to check the message file before moving onto the next step
3. Connect to the LUW database and issue the IMPORT command
"IMPORT FROM d:\file_name.ixf OF IXF MESSAGES d:\msg_import.txt INSERT INTO prefix.table_name"
Note: Don't forget to check the message file
It's that simple. But remember that this works well for relatively smaller tables. For larger tables, you may have to use the LOAD command with proper COMMIT COUNT.
7 comments:
Hi,
What are the utilities to download the data from Mainframe(zo/s Db2 9.1 running. I would like to know for downloading/exporting the data to flat file so that later it can be uploaded anywhere.
Please help us his is urgent.
Hi Rajaram,
You can issue the EXPORT command from db2cmd window and export the data into a flat file on Windows (please refer the example in the blog post). In db2cmd, if you type ? export and hit enter you'll get more details about this command. You can export the data in IXF or DEL (delimited) format. Later you can import it into any other database.
If you have QMF for workstation, you can fetch the data and then export to a windows file as well.
Hope this helps !!
Hi Kumaresh,
Thanks for your reply.
From OMVS please let me know how to connect to DB2 Mainframe and run the EXPORT command.
I am running below script.
/* Starts here */
args=("$@")
servername=${args[0]}
username=${args[1]}
password=${args[2]}
if [[ -z $password ]]; then
prompt="Provide password for $2 :"
while IFS= read -p "$prompt" -r -s -n 1 char
do
if [[ $char == $'\0' ]]
then
break
fi
prompt='*'
password+="$char"
done
fi
mkdir -p log
mkdir -p data
db2 connect to $servername user $username using $password
db2 export to data/userid.T07PORT.dat of DEL lobs to ./ modified by lobsinsepfiles coldel"#" datesiso nochardel "select \"PORTID\",'',\"CLUSTVAL\",'',\"FKEQPT\",'',\"PORTRCPT\",'',\"CNTYRCPT\",'',\"PORTLOAD\",'',\"CNTYLOAD\",'',\"PORTDCHG\",'',\"CNTYDCHG\",'',\"FPORTDCH\",'',\"FCNTYDCH\",'',\"DLVRYLOC\",'',\"FDLVRLOC\",'', '' from userid.\"T07PORT\""
/* Ends here */
Please let me know whether the above script works in DB2 Mainframe for exporting the data to the flat file.
Also please let me know if you have any sample script to export data from DB2 Mainframe to flat file.
Thanks,
RajaRam S
I'm sorry Rajaram, I don't know much about OMVS. I've hardly used it other than looking for the directory structure.
As far as I know, there is no way to export data from mainframe as such. You'll have to pull the data from a DB2 client such as db2cmd.
May I know the purpose of doing it? Is there any particular reason for not pulling the data from the DB2 client?
Thanks again for your quick reply.
I have installed DB2 client on my Window's desktop. Please let me know how I can connect to DB2 Mainframe database server from db2cmd, so that I can use EXPORT command to unload the data to flat file.
Rajaram,
1. Catalog your mainframe DB2 on your machine using these commands:
1a. Catalog DB2 node
DB2 CATALOG TCPIP NODE node-name REMOTE lpar-name SERVER tcpport-number >> C:\users\public\CATALOG.LOG
You can find the tcpport-number in your DB2 MSTR started task as TCPPORT under the message DSNL004I.
1b. Catalog DB2 Subsystem
DB2 CATALOG DATABASE database-name AS subsystem-id AT NODE node-name AUTHENTICATION DCS >> C:\users\public\CATALOG.LOG
1c. Catalog DCS Subsystem
DB2 CATALOG DCS DATABASE subsystem-id AS subsystem-id >> C:\users\public\CATALOG.LOG
2. After cataloging the database (subsystem), connect to the database using this command:
DB2 CONNECT TO database-name USING user-id
3. After you connect to the database, issue the EXPORT command
DB2 EXPORT TO d:\file_name.ixf OF IXF MESSAGES d:\msg_export.txt SELECT *FROM prefix.table_name
Thanks Kumaresh for your detailed explanation.
I will try to execute the commands as you suggested and let you know if need any help.
Thanks again for your time.
Post a Comment