Fwd: COBOL to SQL server

Jim McAlpine

Fwd: COBOL to SQL server
cross posted to IBM-MAIN list.

Does anyone know if it is possible to issue some SQL from a batch COBOL
program running on z/OS to an SQL Server database.

Jim McAlpine

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information


Philip Sevetson

Re: Fwd: COBOL to SQL server
(in response to Jim McAlpine)
Jim,
1) DB2 Federated Server might could handle that, I haven't investigated details
2) I don't know if Oracle Transparent Gateway has a z/OS interface?
3) I'm sure there are vendor solutions.
4) Without one of the above, you're probably out of luck.

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jim McAlpine
Sent: Friday, May 06, 2011 7:26 AM
To: [login to unmask email]
Subject: [DB2-L] Fwd: COBOL to SQL server

cross posted to IBM-MAIN list.

Does anyone know if it is possible to issue some SQL from a batch COBOL program running on z/OS to an SQL Server database.

Jim McAlpine

________________________________

[ http://www.idug.org/images/stories/Tech/idug_anaheim_r-728.jpg ] < http://www.idug.org/north-america-2011/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information


Peter Suhner

Re: Fwd: COBOL to SQL server
(in response to Philip Sevetson)

Jim,

the only solution I know is setting up InfoSphere Federation (on which I just had a session at IDUG).

You basically have a DB2 LUW server running, which has Federation activated and can access a variety of non-DB2 data sources (including MS SQL Server, of course).

Federation uses the following additional objects:
- Wrappers, which is additional binary code that translates from/to DB2 (communication protocols, code pages, SQL dialects, data types, etc.)
- Servers, which define details on a specific target system
- Uesr Mappings, which map a local Federation Server user to an user existing on the remote system
- Nicknames, which are kind of "Table Alias" for remote data objects

You can access Nicknames through SQL (read-only or modifying) like they were local DB2 tables, but in reality the request is passed to the remote system and executed there.

You can easily access such a Federation system from DB2 z/OS by defining it as a remote system in your DDF Connection Database.
You will need to bind your application packages to the Federation server.

A few considerations for DB2 z/OS and SQL Server:
- Federation properly takes care of Codepage translation
- All SQL Server data types supported with the exception of "VARIANT"
- Two phase commit supported
- Access to local and remote tables in one SQL statement NOT supported (DB2 z/OS Optimizer restriction)
- Supported SQL Server security systems depend on ODBC driver used on Federation system
- SQL Server internal security always supported
- Active Directory, etc. can only be supported if Federation system runs on Windows

Last point: While this solution is good, stable and well performing, it unfortunately doesn't come for free.

Drop me a line if you're interested in the presentation slides.

Best regards,
Peter

_______________________
Peter Suhner
[login to unmask email]


IDUG North America 2011 is the premier training and networking event dedicated exclusively to DB2.
Register for IDUG NA 2011 and attend my session on "Federation - Join Your Forces in the InfoSphere"


Please consider the environment instead of printing this message




Date: Fri, 6 May 2011 12:25:49 +0100
From: [login to unmask email]
Subject: [DB2-L] Fwd: COBOL to SQL server
To: [login to unmask email]


cross posted to IBM-MAIN list.

Does anyone know if it is possible to issue some SQL from a batch COBOL program running on z/OS to an SQL Server database.

Jim McAlpine







The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
International DB2 User Group (IDUG) - Independent, not-for-profit, User Run
Your only source for independent, unbiased, and trusted DB2 information


Jim McAlpine

Re: Fwd: COBOL to SQL server
(in response to Peter Suhner)
No one has mentioned JDBC, is it possible to use that. We can invoke a Java
program from COBOL. Anyone ever done something like that ?

Jim McAlpine

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv