Solved

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

Posted on 2007-11-28
9
4,984 Views
Last Modified: 2011-10-03
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
Comment
Question by:Enuda
  • 4
  • 3
  • 2
9 Comments
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 125 total points
ID: 20370792
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
 

Author Comment

by:Enuda
ID: 20371031
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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 125 total points
ID: 20371854
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
 

Author Comment

by:Enuda
ID: 20372859
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 125 total points
ID: 20373079
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
 

Author Comment

by:Enuda
ID: 20374357
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
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 125 total points
ID: 20374746

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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 20376265
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
 
LVL 45

Expert Comment

by:Kdo
ID: 20379158

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now