Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5186
  • Last Modified:

Any Script to Unload from DB2 Z/OS(Mainframe) for load into DB2 UDB Windows and Linux

Hi all,
I have a need to copy z/OS data and load into DB2 UDB on Linux and Windows Servers. What are the options that I have to accomplish this requirement. Please be kind enough to describe the steps in detail and if possible sample scripts will be very welcomed.

My thanks and appreciation in advance.

Enuda
0
Enuda
Asked:
Enuda
  • 4
  • 3
  • 2
5 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Enuda,

Two obvious approaches come to mind.

The first is to extract the definitions from the DB2 systems and apply them to the UDB/LUW systems.  The Export the data and import it back to the UDB/LUW systems.

The other approach is the Federate all of the systems.  If you're using the Enterprise edition of UDB/LUW you should be able to federate the other UDB/LUW systems quite easily.  To federate UDB systems, set FEDERATED=YES in the configuration, and stop/restart DB2.  Control Center will walk you through setting up the federated links.

If you've got a lot of data, the export/import approach is probably the way to go.  It's generally faster than SQL for this kind of thing.  But if our data volume is small to moderate, it may be easier to simply write SQL to copy the data.  It's pretty easy to generate the SQL to copy an entire table:

  INSERT INTO newschema.table1 SELECT * from oldschema.table1


Good Luck,
Kent
0
 
EnudaAuthor Commented:
Hello kdo,
Thanks for your response. I wish all I needed was move data from one UDB to another. However, what I need is to Unload data from the mainframe Z/OS DB2 , turn around and load the extracted data into its UDB clone. The data format as you know is EBCDIC to ASCII I think. And to make matter worse the UDB column names may not be the same, but I need to consider that also.

So, that brings me to the question: How can I do this? Anyone with any example?

Thanks

0
 
momi_sabagCommented:
hi
which vesion of db2 are you running on the main frame ?
if its version 8 or 9 then you can use the unload utility to unload the data in csv format and then you just import it into udb. in this case it does not matter if the columns have different names. if the columns are ordered differently within the table you can ask the unload utility to change the order of the column it unloads using the from table cluase.
the unload utility can also perform the ebcdic->ascii conversion, all you have to do is to tell it to which ccsid you want to convert the data

if you are still using version 7 , then the possible solution for you would be to use the dsntiaul utility (it comes with db2 installation). when you invoke dsntiaul invoke it using parm('SQL'), that allows you to specify a select statement that it will use to unload the data. when you do that you can generate a csv file that can be used to load the data in the udb instance. you need to convert in your sql all the columns into the varchar datatype and you need to concatenate all the column to one another, for example if you have a table with 5 columns name col1 through col2 you can perform
select varchar(col1)||','||varchar(col2)||','||varchar(col3)||','||varchar(col4)||','||varchar(col5)
from mytable

the problem with this solution is that db2 version 7 does not have a built in function to conevrt data from ebcdic to ascii, but what you can do is unload the data in ascii, and then convert it when you transfer it to the linux machine (some ftp servers support data conversion, you just need to make sure you are transfering the files using ascii mode and that you set up the right translation table)

hope this can get you going
i can provide more details if you need them
good luck
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
EnudaAuthor Commented:
Hi momi_sabag,
We are run DB2v8 on a z/OS machine.  Could you provide a example unload SQL code for the Unload to csv format including how to specify the ccsid?

Thanks
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Enuda,

You can federate the mainframe DB2 to UDB/LUW, so that's not a hinderance.  And the federation will automatically take care of EBCDEC / ASCII issues.

On the federated server, access to the other databases is accomplished via a Nickname.  To the DBA, the nickname is used exactly like a table or column name would be used so the SQL is pretty trivial.

I'm not suggesting the federation is the solution, just that it shouldn't be summarily dismissed.  Not only are the issues that you mentioned trivial, the character set conversion is actually easier in a federated environment.


Good Luck,
Kent
0
 
EnudaAuthor Commented:
How do one go about federating  the mainframe DB2 to UDB/LUW - what are the steps to do this?

I read several ways of doing what I need to do - move data fom MF to LUW - and that gives me more ways to do things.
Thanks
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Do you have "Control Center" installed for the LUW systems?

If so, you should be able to follow the steps under "Federated Objects".  

-  Create a Wrapper
-  Create Server Definition (under that wrapper)
-  Create User Mappings (under that server)
-  Create Nicknames

You may also need to edit the DBM configuration on the LUW server that is going to be the federated system.

db2 get dbm cfg | grep FEDERATED

If LUW is on Windows

db2 get dbm cfg > x
# edit x (using notepad or your favorite editor)

If FEDERATED = NO, do the following:

db2 update dbm cfg using (federated yes)
db2stop
db2start


Now you should be able to set up the federated environment to other DB2 systems.


Kent
0
 
momi_sabagCommented:
hi
i would seriously suggest you consider the unload approach since the federation approach will be much slower
you can find the unload utility syntax here
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.ugref/cunload.htm#xf4117

you need for example, assuming your table is called myschem.mytable
unload data from table myschem.mytable
ascii delimited
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Hi Momi,

The federated approach is a lot faster than I had expected.  I federate several UDB/LUW 8/9 on AIX 5.3 systems to other databases, including Oracle 8,9, and 10.

Using LOAD FROM CURSOR, I can load about 1,000,000 rows/minute across the network(1GB backbone) using the federated hooks.  The drivers handle all of the date, time, timestamp, decimal, integer, float, and character set conversions on the fly.

I'll take 1,000,000 rows a minute.  Absolutely.


Kent

Kent


0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now