Load a file from LUW to remote DB2 z/OS

Hooman Tarahom

Load a file from LUW to remote DB2 z/OS

Hi 

We have a BI solution for Oracle DB but our client wants to use this solution for there DB2 on z/OS. We don't have IBM InfoSphere DataStage instead We have ODI. For our Business Intelligence project we are going to connect Oracle ODI to DB2 z/OS(target DB) as ETL tool.

As we've understood ODI does not support mainframe datasets, z/OS system commands, and even run JCL. However it can runs everything on Unix systems, it is easier to prepare input file on unix(with shell scrips) and then load it to remote DB2.

1) Generally, Is it possible to load DB2 on z/OS from a file that is on a remote LUW system for example using DB2 client/connect?(I don't want to use INSERT, since source file is very large)

2) Does anyone use ODI for DB2 z/os? is it better to transfer file to z/os and simulate all ODI procedures in z/os Or use ODI procedures and just load data to target DB2?

Thanks in advance

Hooman

Jørn Thyssen

RE: Load a file from LUW to remote DB2 z/OS
(in response to Hooman Tarahom)

Hi Hooman,

To my knowledge:

Re 1) It is possible in V12.

In V11 and prior you will have to transfer the file to the mainframe and run a standard DB2 load utility job.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Charles Brown

Load a file from LUW to remote DB2 z/OS
(in response to Hooman Tarahom)
Hello Hooman,
You've guessed it, DataStage is your best bet. It does it seamlessly. For a real time solution -- your tool is Q-replication. Anything else is definitely a compromise or circumvention. However, You can do it the old fashioned way. Meaning db2export from LUW to cvs and FTP to z and in z your choice. My preference is DSNUTILB.
Takeaway: For all it worth getting data in and out of db2 mainframe has never been quite easy. Why? Simply because db2 mainframe is not an open architecture. Db2 mainframe is strictly a DRDA compliant product. The others are not
Hope this helps
Charles
NZ dba

Sent from my iPhone

> On May 7, 2017, at 7:33 AM, Hooman Tarahom <[login to unmask email]> wrote:
>
> Hi
>
> We have a BI solution for Oracle DB but our client wants to use this solution for there DB2 on z/OS. We don't have IBM InfoSphere DataStage instead We have ODI. For our Business Intelligence project we are going to connect Oracle ODI to DB2 z/OS(target DB) as ETL tool.
>
> As we've understood ODI does not support mainframe datasets, z/OS system commands, and even run JCL. However it can runs everything on Unix systems, it is easier to prepare input file on unix(with shell scrips) and then load it to remote DB2.
>
> 1) Generally, Is it possible to load DB2 on z/OS from a file that is on a remote LUW system for example using DB2 client/connect?(I don't want to use INSERT, since source file is very large)
>
> 2) Does anyone use ODI for DB2 z/os? is it better to transfer file to z/os and simulate all ODI procedures in z/os Or use ODI procedures and just load data to target DB2?
>
> Thanks in advance
>
> Hooman
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Learn how ESAi's fast data refresh & Test Data Management products can save up to 90% in CPU, I/O
> and manual efforts compared to typical solutions. Be a hero to your users with BCV5 & XDM. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Javier Estrada

RE: Load a file from LUW to remote DB2 z/OS
(in response to Charles Brown)

Hello:

  Just another idea, if you have the chance to set it up, storing the files on a NFS shared filesystem can take out the FTP by also sharing it with the z/OS LPAR and it will see them as datasets and not unix files.

Regards

Javier Estrada Benavides,

IBM Certified DB2 11 for z/OS System Administrator. Mexico

Hooman Tarahom

RE: Load a file from LUW to remote DB2 z/OS
(in response to Javier Estrada)

Thank you, Jørn Thyssen, It is good to know that DB2 v12 has the ability to load from remote file, however, we don't have this version for the time being.

Javier Thanks for your idea. It was interesting. I don't know for larger files which one is better NFS or FTP.

Charles Thanks for your information. I don't know whether you've worked with ODI for mainframe or not, our problem with the heterogeneous Oracle ETL+IBM DB2 environment is that we couldn't do A to Z business intelligence tasks using ODI, as I know. By putting files into the z System everything should be done using JCL and DB2 utilities and it is not flexible. Do you recommend buying DataStage(it should be expensive) or is there any less expensive way?

I have another question, IBM Integration Bus is an integration solution. Has anyone used IIB as an ETL tool? Is it right to use it?

Thanks a lot

Hooman 

 

Edited By:
Hooman Tarahom[Organization Members] @ May 11, 2017 - 12:37 PM (America/Eastern)
Hooman Tarahom[Organization Members] @ May 11, 2017 - 12:45 PM (America/Eastern)

Javier Estrada

RE: Load a file from LUW to remote DB2 z/OS
(in response to Hooman Tarahom)

Hi again:

  NFS is actually very versatile, the main point of using it is like having a small data lake where each "member" sees the filesystem as local, and if a z/OS LPAR is a "member" or the actual source of the information, then you can, for example, create your input files directly on the Unix/Linux side and the z/OS will see them as local MVS datasets which you can just load as any other load utility. No need for FTP anymore. You can use this approach for many uses, after all, it's your small data lake and it didn't get to Hadoop level or any other complex structure.

 

Regards,

Javier Estrada Benavides

IBM Certified DB2 11 for z/OS System Administrator, Mexico

Charles Brown

Load a file from LUW to remote DB2 z/OS
(in response to Javier Estrada)
Javier, I love your suggestion of using shared drive over FTP. Can't think of a seamless or better method of moving data between platforms. Good!

Sent from my iPad

> On May 12, 2017, at 5:38 PM, Javier Estrada <[login to unmask email]> wrote:
>
> Hi again:
>
> NFS is actually very versatile, the main point of using it is like having a small data lake where each "member" sees the filesystem as local, and if a z/OS LPAR is a "member" or the actual source of the information, then you can, for example, create your input files directly on the Unix/Linux side and the z/OS will see them as local MVS datasets which you can just load as any other load utility. No need for FTP anymore. You can use this approach for many uses, after all, it's your small data lake and it didn't get to Hadoop level or any other complex structure.
>
>
>
> Regards,
>
> Javier Estrada Benavides
>
> IBM Certified DB2 11 for z/OS System Administrator, Mexico
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> Learn how ESAi's fast data refresh & Test Data Management products can save up to 90% in CPU, I/O
> and manual efforts compared to typical solutions. Be a hero to your users with BCV5 & XDM. See
> http://www.ESAIGroup.com/idug
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>