Accessing Oracle data on Unix from DB2 v9 for z/OS

Donna Domovic

Accessing Oracle data on Unix from DB2 v9 for z/OS

Hello,

We currently have DB2 v9 for z/OS and Oracle SQL*Net running on the mainframe and use the combination of this to access Oracle data on Unix from our mainframe applications including Cobol programs.  In addition to DB2, we also have MQ and Web Sphere. 

Oracle is no longer supporting SQL*Net on the mainframe so we'd like to find another method that will allow the Oracle data to be accessed from the mainframe applications.

Has anyone done this or do you have suggestions?  While we would like to save costs by taking advantage of what we already have in house, we are open to all suggestions.

Thanks in advance for your help.

Donna Domovic

David Tolleson

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Donna Domovic)

Hi, Donna,

One option is to install a copy of IBM's InfoSphere Federation Server on UNIX.  z/OS apps can access a Federation Server database through DRDA the same way z/OS apps can access a DB2 LUW database.  The Federation Server database can provide transparent access to Oracle databases (and others).  It accepts DB2 SQL and, optionally, can be configured to provide Oracle SQL compatibiliy just as DB2 LUW can.

david



In Reply to Donna Domovic:

Hello,

We currently have DB2 v9 for z/OS and Oracle SQL*Net running on the mainframe and use the combination of this to access Oracle data on Unix from our mainframe applications including Cobol programs.  In addition to DB2, we also have MQ and Web Sphere. 

Oracle is no longer supporting SQL*Net on the mainframe so we'd like to find another method that will allow the Oracle data to be accessed from the mainframe applications.

Has anyone done this or do you have suggestions?  While we would like to save costs by taking advantage of what we already have in house, we are open to all suggestions.

Thanks in advance for your help.

Donna Domovic

Roman Glodowski

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Donna Domovic)

Donna, do you have DB2 for LUW?

In our shop, DB2 for LUW is using as gateway to Oracle on UNIX. 

This solution works pretty fine, connections from mainframe are made thru ODBC. You can even do inserts into table on Oracle from standard programs, for example DSNTEP2.


In Reply to Donna Domovic:

Hello,

We currently have DB2 v9 for z/OS and Oracle SQL*Net running on the mainframe and use the combination of this to access Oracle data on Unix from our mainframe applications including Cobol programs.  In addition to DB2, we also have MQ and Web Sphere. 

Oracle is no longer supporting SQL*Net on the mainframe so we'd like to find another method that will allow the Oracle data to be accessed from the mainframe applications.

Has anyone done this or do you have suggestions?  While we would like to save costs by taking advantage of what we already have in house, we are open to all suggestions.

Thanks in advance for your help.

Donna Domovic

James Park

Error in granting Execute
(in response to Roman Glodowski)
DB2 V8 on z/OS

I generated below Grant statement using Admin Tool. I added the first 2 lines to change the SQL ID. Without the first 2 lines it worked fine. When I added the top 2 lines I get,

" SQLCODE : -104 DSNTIAR CODE : 0

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ";". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: <END-OF-STATEMENT>"

I wonder what went wrong.


SET CURRENT SQLID = 'A90XXX';
COMMIT;
GRANT EXECUTE
ON PLAN
"A33ADDRU",
"A33ASSMU",
.
.
.
.


Thank you for taking time to help.


------------------------------------------------------------------------------
This email and any file transmitted with it may be confidential and are intended solely for the use of the individual or entity to whom they are addressed. If you received this email in error, please notify the Comptroller's System Manager by forwarding this message to [login to unmask email]
==============================================================================

Jorg Lueke

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Roman Glodowski)

Hi Roman,


So do you create db links in LUW and then use those when you connect from z/os?


In Reply to Roman Glodowski:

Donna, do you have DB2 for LUW?

In our shop, DB2 for LUW is using as gateway to Oracle on UNIX. 

This solution works pretty fine, connections from mainframe are made thru ODBC. You can even do inserts into table on Oracle from standard programs, for example DSNTEP2.

 

Pete Suhner

Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to David Tolleson)

Hi Donna,

as David says: Federation Server can provide this connectivity. We use it in our shop and it proves to be very stable and well performing. And of course you are not limited to Oracle, but can access a wide variety of relational and non-relational data sources (SQL Server, Informix, XML, MQ, Excel Sheets, Flat files, and many more). Just in case...

One drawback might be that this is a relatively pricey product. So, to keep license cost at bay, you might want to consider PVU licensing with sub-capacity management. We are running one single, centralized Federation Server per stage (dev, pre-prod, prod), each of them being a virtualized box with as little CPU as possible. Allows us to only license the number of CPU cores we actually have assigned to the individual boxes.
The good news is that IBM are quite forward-looking with sub-capacity management (as opposed to - let's say - Oracle...) and they accept this licensing mode for about any relevant virtualization technology available.

My five cents,
Pete


To: [login to unmask email]
Subject: [DB2-L] - RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
From: [login to unmask email]
Date: Tue, 13 Dec 2011 14:43:23 -0700

Hi, Donna,
One option is to install a copy of IBM's InfoSphere Federation Server on UNIX. z/OS apps can access a Federation Server database through DRDA the same way z/OS apps can access a DB2 LUW database. The Federation Server database can provide transparent access to Oracle databases (and others). It accepts DB2 SQL and, optionally, can be configured to provide Oracle SQL compatibiliy just as DB2 LUW can.
david


In Reply to Donna Domovic:

Hello,
We currently have DB2 v9 for z/OS and Oracle SQL*Net running on the mainframe and use the combination of this to access Oracle data on Unix from our mainframe applications including Cobol programs. In addition to DB2, we also have MQ and Web Sphere.
Oracle is no longer supporting SQL*Net on the mainframe so we'd like to find another method that will allow the Oracle data to be accessed from the mainframe applications.
Has anyone done this or do you have suggestions? While we would like to save costs by taking advantage of what we already have in house, we are open to all suggestions.
Thanks in advance for your help.
Donna Domovic

Site Links: View post online View mailing list online Send new post via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

David Tolleson

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Jorg Lueke)

Hi, Jorg,

InfoSphere Federation Server is the basis for all of IBM's Oracle access from DB2 z/OS apps.  However, there are two situations where a DB2 LUW can provide the exact same function:

  1. DB2 Advanced Enterprise Server Edition and InfoSphere Warehouse Enterprise Edition come bundled with Federation Server's Oracle access (at no additional cost). 
  2. All other DB2 LUW and IW editions can be extended by installing Federation Server on top of them 

So, if Roman's doing this from DB2 LUW, it's one of these configurations.

 

At a high level, the way you set this up is as follows:

  1. Create a federated database on LUW.
  2. Create nicknames from this database to Oracle tables (yes, these are similar to dblinks).
  3. Create entries in the DB2 z/OS communications database that point to the federated database.

You can try out a federated database using DB2 Express-C (the free DB2 LUW) and a few instructions:

http://www.channeldb2.com/profiles/blogs/a-fast-way-to-get-started-with-1

DB2 Express-C does not provide access to Oracle tables.  However, once you have a DB2, IW, or Federation Server with Oracle access, you don't need to know much more about set up than is shown in the link above.  The DB2 LUW 9.7 Information Center has plenty of information about federated Oracle access under the folder called 'Federation' :)

thanks

david

 

P.S. If anyone's interested, here's where I get my info about DB2 AESE and IW, here you go :) 

IBM Announcement Letter: http://www-01.ibm.com/common/ssi/ShowDoc.jsp?docURL=/common/ssi/rep_ca/5/649/ENUSA10-0915/index.html&lang=en

developerWorks article: http://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2advancedenterprise2/index.html


In Reply to Jorg Lueke:

Hi Roman,


So do you create db links in LUW and then use those when you connect from z/os?


In Reply to Roman Glodowski:

Donna, do you have DB2 for LUW?

In our shop, DB2 for LUW is using as gateway to Oracle on UNIX. 

This solution works pretty fine, connections from mainframe are made thru ODBC. You can even do inserts into table on Oracle from standard programs, for example DSNTEP2.

 

Roman Glodowski

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to David Tolleson)

David, Jorg

Yes, I'm almost sure that case 1 describes our configuration (LUW part is beyond my job), this is DB2 for Linux runing on VM.

David, your step-by-step instrucion is worth to remember, at least because our linux guys are not really DBA's ;))).

After set up, the only thing to do on z/OS, is bind into remote LUW location.


In Reply to David Tolleson:

Hi, Jorg,

InfoSphere Federation Server is the basis for all of IBM's Oracle access from DB2 z/OS apps.  However, there are two situations where a DB2 LUW can provide the exact same function:

  1. DB2 Advanced Enterprise Server Edition and InfoSphere Warehouse Enterprise Edition come bundled with Federation Server's Oracle access (at no additional cost). 
  2. All other DB2 LUW and IW editions can be extended by installing Federation Server on top of them 

So, if Roman's doing this from DB2 LUW, it's one of these configurations.

 

At a high level, the way you set this up is as follows:

  1. Create a federated database on LUW.
  2. Create nicknames from this database to Oracle tables (yes, these are similar to dblinks).
  3. Create entries in the DB2 z/OS communications database that point to the federated database.

You can try out a federated database using DB2 Express-C (the free DB2 LUW) and a few instructions:

http://www.channeldb2.com/profiles/blogs/a-fast-way-to-get-started-with-1

DB2 Express-C does not provide access to Oracle tables.  However, once you have a DB2, IW, or Federation Server with Oracle access, you don't need to know much more about set up than is shown in the link above.  The DB2 LUW 9.7 Information Center has plenty of information about federated Oracle access under the folder called 'Federation' :)

thanks

david

 

P.S. If anyone's interested, here's where I get my info about DB2 AESE and IW, here you go :) 

IBM Announcement Letter: http://www-01.ibm.com/common/ssi/ShowDoc.jsp?docURL=/common/ssi/rep_ca/5/649/ENUSA10-0915/index.html&lang=en

developerWorks article: http://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2advancedenterprise2/index.html


In Reply to Jorg Lueke:

Hi Roman,


So do you create db links in LUW and then use those when you connect from z/os?


In Reply to Roman Glodowski:

Donna, do you have DB2 for LUW?

In our shop, DB2 for LUW is using as gateway to Oracle on UNIX. 

This solution works pretty fine, connections from mainframe are made thru ODBC. You can even do inserts into table on Oracle from standard programs, for example DSNTEP2.

 

Donna Domovic

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Roman Glodowski)

Roman,

Thanks for the suggestion.  We do have DB2 Connect but I don't think we have DB2 LUW.  It is something we'll have to consider as an option.

Thanks,

Donna Domovic

David Tolleson

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Roman Glodowski)

Hi, Roman,

You know, I go through this information about once or twice a month, but I haven't been able to any IBM documentation that puts everything together for people who've never done it.  So... I created a post on ChannelDB2:

http://www.channeldb2.com/profiles/blogs/accessing-oracle-netezza-and-microsoft-excel-from-db2-z-os

If you have a chance, take a look and let me know if you see anything to correct or think of anything that would be good to add.

  thanks

  david


In Reply to Roman Glodowski:

David, Jorg

Yes, I'm almost sure that case 1 describes our configuration (LUW part is beyond my job), this is DB2 for Linux runing on VM.

David, your step-by-step instrucion is worth to remember, at least because our linux guys are not really DBA's ;))).

After set up, the only thing to do on z/OS, is bind into remote LUW location.


In Reply to David Tolleson:

Hi, Jorg,

InfoSphere Federation Server is the basis for all of IBM's Oracle access from DB2 z/OS apps.  However, there are two situations where a DB2 LUW can provide the exact same function:

  1. DB2 Advanced Enterprise Server Edition and InfoSphere Warehouse Enterprise Edition come bundled with Federation Server's Oracle access (at no additional cost). 
  2. All other DB2 LUW and IW editions can be extended by installing Federation Server on top of them 

So, if Roman's doing this from DB2 LUW, it's one of these configurations.

 

At a high level, the way you set this up is as follows:

  1. Create a federated database on LUW.
  2. Create nicknames from this database to Oracle tables (yes, these are similar to dblinks).
  3. Create entries in the DB2 z/OS communications database that point to the federated database.

You can try out a federated database using DB2 Express-C (the free DB2 LUW) and a few instructions:

http://www.channeldb2.com/profiles/blogs/a-fast-way-to-get-started-with-1

DB2 Express-C does not provide access to Oracle tables.  However, once you have a DB2, IW, or Federation Server with Oracle access, you don't need to know much more about set up than is shown in the link above.  The DB2 LUW 9.7 Information Center has plenty of information about federated Oracle access under the folder called 'Federation' :)

thanks

david

 

P.S. If anyone's interested, here's where I get my info about DB2 AESE and IW, here you go :) 

IBM Announcement Letter: http://www-01.ibm.com/common/ssi/ShowDoc.jsp?docURL=/common/ssi/rep_ca/5/649/ENUSA10-0915/index.html&lang=en

developerWorks article: http://www.ibm.com/developerworks/data/library/techarticle/dm-1107db2advancedenterprise2/index.html


In Reply to Jorg Lueke:

Hi Roman,


So do you create db links in LUW and then use those when you connect from z/os?


In Reply to Roman Glodowski:

Donna, do you have DB2 for LUW?

In our shop, DB2 for LUW is using as gateway to Oracle on UNIX. 

This solution works pretty fine, connections from mainframe are made thru ODBC. You can even do inserts into table on Oracle from standard programs, for example DSNTEP2.

 

Roman Glodowski

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to David Tolleson)

Well, good idea.

I think there is no need to delve into deep details, however maybe others want it?

Frank Swarbrick

Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to David Tolleson)
We use Federation Server on Linux for exactly this. It works well enough, but performance is occasionally lacking; probably an inefficient conversion of DB2 SQL to Oracle SQL.
Anyone know of a way to see what "outgoing" SQL (the Oracle SQL) is created by a particular DB2 SQL statement?

I'm also curious about your statement: " It accepts DB2 SQL and, optionally, can be configured to provide Oracle SQL compatibility just as DB2 LUW can." I know what (in general) Oracle compatibility is in LUW, but how does this affect SQL statements from z/OS through Federation Server to Oracle? Sounds like an interesting thought! (Though we're on 9.5, and I believe(?) Oracle compatibility came with 9.7; but I could be wrong.)

Thanks!
Frank

From: David Tolleson [mailto:[login to unmask email]
Sent: Tuesday, December 13, 2011 2:43 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Accessing Oracle data on Unix from DB2 v9 for z/OS


Hi, Donna,

One option is to install a copy of IBM's InfoSphere Federation Server on UNIX. z/OS apps can access a Federation Server database through DRDA the same way z/OS apps can access a DB2 LUW database. The Federation Server database can provide transparent access to Oracle databases (and others). It accepts DB2 SQL and, optionally, can be configured to provide Oracle SQL compatibiliy just as DB2 LUW can.

david


In Reply to Donna Domovic:

Hello,

We currently have DB2 v9 for z/OS and Oracle SQL*Net running on the mainframe and use the combination of this to access Oracle data on Unix from our mainframe applications including Cobol programs. In addition to DB2, we also have MQ and Web Sphere.

Oracle is no longer supporting SQL*Net on the mainframe so we'd like to find another method that will allow the Oracle data to be accessed from the mainframe applications.

Has anyone done this or do you have suggestions? While we would like to save costs by taking advantage of what we already have in house, we are open to all suggestions.

Thanks in advance for your help.

Donna Domovic

-----End Original Message-----

The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

ZhiTong Dou

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Frank Swarbrick)

Hello Frank,

You can capture the outgoing SQL (to Oracle) using db2 explain tool by following the below steps:


db2 connect to $DB_NAME
db2 -tvf ~/sqllib/misc/EXPLAIN.DDL
db2 set current explain mode = EXPLAIN
db2 connect to $DB_NAME
db2 "your SQL statement"
db2exfmt -d $DB_NAME -1 -o db2exmt.out
db2 set current explain mode = no

then open file db2exmt.out and search for "Remote statement".

As for the "Oracle compatibility" topic, I think your understanding is correct.  This feature doesn't affect the SQL sent from DB2 z/OS.

In Reply to Frank Swarbrick:

We use Federation Server on Linux for exactly this. It works well enough, but performance is occasionally lacking; probably an inefficient conversion of DB2 SQL to Oracle SQL.
Anyone know of a way to see what "outgoing" SQL (the Oracle SQL) is created by a particular DB2 SQL statement?

I'm also curious about your statement: " It accepts DB2 SQL and, optionally, can be configured to provide Oracle SQL compatibility just as DB2 LUW can." I know what (in general) Oracle compatibility is in LUW, but how does this affect SQL statements from z/OS through Federation Server to Oracle? Sounds like an interesting thought! (Though we're on 9.5, and I believe(?) Oracle compatibility came with 9.7; but I could be wrong.)

Thanks!
Frank

From: David Tolleson [mailto:[login to unmask email]
Sent: Tuesday, December 13, 2011 2:43 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Accessing Oracle data on Unix from DB2 v9 for z/OS


Hi, Donna,

One option is to install a copy of IBM's InfoSphere Federation Server on UNIX. z/OS apps can access a Federation Server database through DRDA the same way z/OS apps can access a DB2 LUW database. The Federation Server database can provide transparent access to Oracle databases (and others). It accepts DB2 SQL and, optionally, can be configured to provide Oracle SQL compatibiliy just as DB2 LUW can.

david


In Reply to Donna Domovic:

Hello,

We currently have DB2 v9 for z/OS and Oracle SQL*Net running on the mainframe and use the combination of this to access Oracle data on Unix from our mainframe applications including Cobol programs. In addition to DB2, we also have MQ and Web Sphere.

Oracle is no longer supporting SQL*Net on the mainframe so we'd like to find another method that will allow the Oracle data to be accessed from the mainframe applications.

Has anyone done this or do you have suggestions? While we would like to save costs by taking advantage of what we already have in house, we are open to all suggestions.

Thanks in advance for your help.

Donna Domovic

-----End Original Message-----

The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

Frank Swarbrick

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to ZhiTong Dou)

I am getting the following when I try the db2exfmt.  Is it perhaps because I am using a 9.7 client, but the server is 9.5?  If so, is there a workaround?  (I'm not the DBA; I don't have access to the command line on the server.)

LINE    MESSAGES FOR db2exfmt.bnd
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.
30593   SQL0440N  No authorized routine named
                  "SYSPROC.EXPLAIN_FROM_SECTION" of type "PROCEDURE" having
                  compatible arguments was found.  SQLSTATE=42884
30621   SQL0440N  No authorized routine named
                  "SYSPROC.EXPLAIN_FROM_ACTIVITY" of type "PROCEDURE" having
                  compatible arguments was found.  SQLSTATE=42884
30649   SQL0440N  No authorized routine named
                  "SYSPROC.EXPLAIN_FROM_CATALOG" of type "PROCEDURE" having
                  compatible arguments was found.  SQLSTATE=42884
31420   SQL0204N  "SYSIBM.SYSMODULES" is an undefined name. 
                  SQLSTATE=42704
31441   SQL0206N  "PROC.ROUTINEMODULEID" is not valid in the context
                  where it is used.  SQLSTATE=42703
31462   SQL0206N  "PROC.ROUTINEMODULEID" is not valid in the context
                  where it is used.  SQLSTATE=42703
        SQL0082C  An error has occurred which has terminated
                  processing.
        SQL0092N  No package was created because of previous errors.
        SQL0091N  Binding was ended with "8" errors and "0" warnings.

ZhiTong Dou

RE: Accessing Oracle data on Unix from DB2 v9 for z/OS
(in response to Frank Swarbrick)

Hello Frank,

It seems the error is caused by your client/server version difference.  db2exfmt fails when it calls SYSPROC.EXPLAIN_FROM_* routines because these routines doesn't exist in V9.5.  (db2exfmt on your client is bound with V97.)  Do you have access to a V95 client to try this command?  Or, you can ask your DBA to try it on the server, if it doesn't impact your business.
In Reply to Frank Swarbrick:

I am getting the following when I try the db2exfmt.  Is it perhaps because I am using a 9.7 client, but the server is 9.5?  If so, is there a workaround?  (I'm not the DBA; I don't have access to the command line on the server.)

LINE    MESSAGES FOR db2exfmt.bnd
------  --------------------------------------------------------------------
        SQL0061W  The binder is in progress.
30593   SQL0440N  No authorized routine named
                  "SYSPROC.EXPLAIN_FROM_SECTION" of type "PROCEDURE" having
                  compatible arguments was found.  SQLSTATE=42884
30621   SQL0440N  No authorized routine named
                  "SYSPROC.EXPLAIN_FROM_ACTIVITY" of type "PROCEDURE" having
                  compatible arguments was found.  SQLSTATE=42884
30649   SQL0440N  No authorized routine named
                  "SYSPROC.EXPLAIN_FROM_CATALOG" of type "PROCEDURE" having
                  compatible arguments was found.  SQLSTATE=42884
31420   SQL0204N  "SYSIBM.SYSMODULES" is an undefined name. 
                  SQLSTATE=42704
31441   SQL0206N  "PROC.ROUTINEMODULEID" is not valid in the context
                  where it is used.  SQLSTATE=42703
31462   SQL0206N  "PROC.ROUTINEMODULEID" is not valid in the context
                  where it is used.  SQLSTATE=42703
        SQL0082C  An error has occurred which has terminated
                  processing.
        SQL0092N  No package was created because of previous errors.
        SQL0091N  Binding was ended with "8" errors and "0" warnings.