Hi there,

i have a requirement under which i need to query some data from my Mainframe database, while my application runs on Linux server.

Here are the challenges

1) i can have a JCL executing this SQL and FTP the data everytime (like 15 minutes once), here i will incur the very high cost of querying MF DB.

2) Is there a way, i can include this in a shell script. call that function, which can retrieve this data from mainframe DB and pull down to Linux box, if yes, do i need to create any channel, how does that work?

3) to Minimize the cost of option #1, can i use a Stored Proc, which will be better choice than running SQL every 15 mins, if yes, how can i do that?

I am not a DB guy, so simple explanation would be very much appreciated.

Thanks to all of DB experts here

The most common way is to use an IBM product called DB2 Connect that allows connectivity from a Linux, UNIX, or windows client to DB2 z/OS. You will need a z/OS systems administrator to configure the mainframe to allow such connections, even though DB2 Connect runs on your client. You will also need to catalog the remote DCS node, tcpip node, and database on your DB2 Connect Client. I would ask the DBA at your company for assistance with this, as perhaps they already have done this in the past.

I agree with Marcus_A that DB2 Connect is the way to go for this.

However where you go from there depends on your skills and requirements.

Here are some additional thoughts -

It is not commonly realised that as well as accepting inbound connections through DB2 Connect, the DRDA support on the mainframe also allows you to make outbound connections to DB2 for LUW (Linux, UNIX and Windows) to run SQL, call stored procedures and the like. This opens the possibility of running code on your Linux DB2 but controlled from a mainframe job scheduler (for example). The connections out to the LUW box can be in any language you want : we have used COBOL and REXX. It is even possible to get standard mainframe facilities such as SPUFI to work without much trouble.

If you want to suck large quantities of data down to the Linux server, then you should look at the crossloader (LOAD FROM CURSOR) facilities. You can run a LOAD utility on the Linux DB2 database which sources data from a cursor where the SQL uses nicknames (federated server support) pointing at tables on the mainframe. We've found this works very fast indeed. You can wrap the LOAD command up in a stored procedure using the ADMIN_CMD interface. Works very nicely. We use this technique to populate data marts from our main data warehouse on z/OS.

Related to the above, nicknames / federated server support are also very useful if you want to access mainframe data in conjunction with data on the Linux database.

Of course all of the above still requires you to have DB2 Connect installed and to have the mainframe DDF config done.

Feel free to contact me directly if you need any assistance / guidance on this.

Phil Nelson
ScotDB Limited