The data from DB2 on Mainframe platform is to be transformed and loaded to ORACLE on Unix platform. My DataStage server will be on the Unix machine. DataStage Clients on various Windows machines are connected to this server. I have three ways in mind to achieve the above mentioned data transfer.
1. Use DataStage mainframe ETL jobs to extract and transform data from DB2 and load temporarily in the files on mainframe. Then use FTP to transfer these files on mainframe to the unix platform. Then use these files to load the Oracle tables using DataStage server ETL jobs.
2. Use DataStage mainframe ETL jobs to extract data from DB2 and load temporarily in the files on mainframe. Then use FTP to transfer these files on mainframe to the unix platform. Then use these files as source to transform and load the Oracle tables using DataStage server ETL jobs.
3. Use DataStage server ETL jobs to extract data from DB2 (for this DB2 Client has to be installed on the Server machine [i.e., the Unix platform ] to enable connection to the DB2), transform and load the Oracle database on the Unix machine.
The pros and cons for each of the above technical architectures will be very helpful. If any one of u have practically implemented any of the above architectures, please let me know.
Thank You.
Rajesh
Data transfer from DB2/Mainframe to Oracle/Unix
Moderators: chulett, rschirm, roy
-
rajesh_potluri
- Participant
- Posts: 6
- Joined: Wed May 07, 2003 5:29 am
- Location: India
- Contact:
Hi Rajesh,
I have only recently registered on this forum and I am looking through the posts - sorry if this is a bit late!
The pros of 1. are that most of the processing will be done on the mainframe and if the MIPS are cheap on the mainframe then you can create SQL loader format files that can be loaded directly into Oracle on the Unix paltform. There will also be less data going across the network to the Unix machine.
The cons of 1. are Do you have DSXE 390 and the Mainframe COBOL skill needed. Also, the DSXE 390 development cycle can be a bit long winded compared to straight Server job development (create job in client DS - transfer COBOL code & JCL to Mainframe - Compile - test - re-code etc, etc).
The pros of 2. are pretty much the same as for 1 except that you are restricting some of the transform functionality into the Server side jobs that will presumably use simple sequential files built on the mainframe and do some further processing before the load to Oracle on Unix.
The cons of 2 are the same as for 1.
The pros of 3. are that everything is contained on the Unix platform and is therefore a bit more controllable as well as not having to have DSXE 390 (Expensive!)
The cons of 3. are that all the processing will be on the Unix DS server and if it is a busy machine it will be busier using this approach. There may also be political barriers to having the DB2 client access to mainframe DB2 data (happens sometimes!). There may be higher network traffic as you will need to transfer more data to process for lookups etc. from the DB2 tables on the Mainframe.
The big questions you have to answer is "Where do you want the processing to occur?" and "How much load do I want to put on my network and my Unix DS server".
Hope that helps,
Cheers,
Gil.
I have only recently registered on this forum and I am looking through the posts - sorry if this is a bit late!
The pros of 1. are that most of the processing will be done on the mainframe and if the MIPS are cheap on the mainframe then you can create SQL loader format files that can be loaded directly into Oracle on the Unix paltform. There will also be less data going across the network to the Unix machine.
The cons of 1. are Do you have DSXE 390 and the Mainframe COBOL skill needed. Also, the DSXE 390 development cycle can be a bit long winded compared to straight Server job development (create job in client DS - transfer COBOL code & JCL to Mainframe - Compile - test - re-code etc, etc).
The pros of 2. are pretty much the same as for 1 except that you are restricting some of the transform functionality into the Server side jobs that will presumably use simple sequential files built on the mainframe and do some further processing before the load to Oracle on Unix.
The cons of 2 are the same as for 1.
The pros of 3. are that everything is contained on the Unix platform and is therefore a bit more controllable as well as not having to have DSXE 390 (Expensive!)
The cons of 3. are that all the processing will be on the Unix DS server and if it is a busy machine it will be busier using this approach. There may also be political barriers to having the DB2 client access to mainframe DB2 data (happens sometimes!). There may be higher network traffic as you will need to transfer more data to process for lookups etc. from the DB2 tables on the Mainframe.
The big questions you have to answer is "Where do you want the processing to occur?" and "How much load do I want to put on my network and my Unix DS server".
Hope that helps,
Cheers,
Gil.