Connecting to MySQL RDBMS through java

Question

I have a requirement to connect existing cobol application on z/OS to remote MySQL database using java. I read about the stored procedure approach but did not get a clear picture about the architecture. Can you please share your ideas regarding the cobol- java -MySQL stored procedure connectivity approach?

Answer 1

Oracle at least offers DRDA support via it's "transparent gateways (TG)" functionality.

http://www.oracle.com/technetwork/database/enterprise-edition/tg4drda-097332.html

The cost is significant, 5-figures when I looked at it.

On the IBM i (aka AS/400) Oracle offered both client & server support. The TG made the IBM i look like another oracle DB and made the oracle DB look like another IBM i. MS SQL Server offers DRDA client support, but not server. So MS SQL Server can happily connect to a DRDA server, but the DRDA server can't connect to MS SQL Server.

Price you pay for using an open standard, when your competitors don't.

IBM's solution would be to install a DB2 LUW "Federation Server". It would basically translate DRDA to ODBC/OLEDB..

Last option, would be to install and use type 4 JDBC drivers on your z. I'm assuming there's some way to call java programs from COBOL on z/OS. I know there is on IBM i.

Actually, since the DRDA spec is open you can conceivably create your own DRDA Application Requester (AR) that uses JDBC to talk to a remote server; giving you a DRDA gateway (bridge). In fact, there's an open source project for IBM i that does just that. ARDGATE is distributed as part of the AppServer4rpg project http://sourceforge.net/projects/appserver4rpg/?source=navbar

The ARDGATE component is written in Java, seems likely it could be ported to z/OS.

Answer 2

... another possibility would be, to port your cobol app to as400 (this should not be too hard, at source level) and acess whatever JDBC database you want using ArdGate, the universal Bridge from DB2/400 to any JDBC capable database.

Dieter

Answer 3

You have Java, that means you can use the JDBC drivers to talk to MySQL.

And you have Cobol, which integrates well with Java on the mainframe these days. You can write a Cobol object that will invoke the various methods in the JDBC driver.

You can wrap that in a procedural (or set of procedural) Cobol program(s) to trigger a stored procedure or prepared query and return results.

So a possible software stack might look like:

Existing Application (Cobol)
Procedureal Interface Module (Cobol)
Cobol-To-Java interace (OO-Cobol)
JDBC Driver for MySql (prepackaged JAR, Java)
MySQL database

Answer 4

ArdGate does not use DRDA, it's using the SQL Client integration Exit (AKA ARDPGM), which is AS400 only. So it's not portable to z-Series.

It should be possible to write a Bridge from DRDA to JDBC (this would run on all DB2 flavours), but the DRDA specs are not good documented (this was the reason to me to use the ArdPGM approach).

The IBM product is again renamed to InfoSphere Federation Server and requires an additional server and is rather expensive (it's IBM). Using this, you could access MS SQL Server with embedded SQL in COBOL like DB2 tables.

The stored procedure approach could work as following:

To get a resultset (open cursor) you would pass a SQL select statement to a stored procedure (implemented in Java) and inside the stored procedure you would have to deal with the MSSQL database inside the stored procedure to get the ResultSet, pulling it in an array (or a temporary table), passing back an open cursor. This could be very clumsy with z-OS, and I don't know whether or not it would scale well. On AS400 it wouldn't.