Automating Data Extract from a mainframe? 3

[FumaBob]

Hello

Rookie question(s). I just inherited a 'business intelligence' team at work that sees each analyst spending about 80% of their time requesting data from a COBOL mainframe (via JCL, I think), only to take the output and dump it into excel for management reporting. They run the same queries each day. Can this be automated in any way, either through the code or a 3rd party tool?


Thanks!

 

[MarcLodge]

Hi,
There are various ways to do this and it will depend on the tools that you available on the mainframe.

There are many job schedulers that will automatically submit jobs at specified times during the day. Talk to somebody at your site and ask how they do this. You can then set up the job to run every day.

The difficult part is getting the data off the mainframe and onto the LAN to be imported into Excel. Again, there are various tools to do this - Connect Direct is one such tool - you will need to ask onsite what tools they have. You'll then need to get this added to the now automated JCL. Access paths to the LAN will need sorting as will authority.

Another avenue that might be open to you is ODBC. You mention that the team run queries. If these are queries to something like DB", it may be possible to embed the query in the spreadsheet and rather that run a job to get the data, the team open the spreadsheet which automatically refreshes the data or they could do it manually. For this you would need to have the relevant ODBC drivers to enable you to connect to the DB2 source. Once again, access and authority would need to be in order.

Hope this helps.

Marc

 

[TysonLPrice]

Excel has options to connect to external sources to extract data. I've never tried to connect to a mainframe but it may be worth your while to check it out. Maybe you can save everybody a couple steps. Possibly with an ODBC connection.

For example I have spreadsheets that query MS SQL directly. When the spreadsheet is opened the data can be refreshed with a click.

 

[papadba]

Pretty much anything that is run by submitting JCL can be scheduled. At many sites, i've supported processes extract data from "wherever" on the mainframe (some database, vsam, etc), generate delimited text files for use on the remote systems, and transfer the delimited data to the target system(s).

The best way to accomplish the mainframe scheduling is to co-ordinate with the mainframe scheduling people (for many years, every mainframe i've worked with has one scheduling product or another).

 

[noggin]

You could write some VBA code to sit behind the spreadsheet and retrieve the mainframe data via an ODBC link. But I believe this would require additional third-party software to be installed on the mainframe. Ditto for using web services.

My company is a big Websphere MQ user so we opted to write some VBA code which uses the (free) MQ client software to send request messages to our mainframe. A triggered service written in COBOL retrieves the data and sends back a reply message. All the messages are in XML and the VBA code populates the appropriate spreadsheet cell depending on the tag name. It took a while to figure out and write but it works well and saves loads of time for our analysts who were previously cutting and pasting from green screens.

Obviously this is an option only if you already have Websphere MQ installed but it is commonly used at large companies.

 

[papadba]

If there is already a working process on the mainframe that extracts and reformats the data correctly, suggest you spend a bit of time with the scheduling people to make these run as needed automatically rather than manually.

Also, suggest that you consider adding ftp processes to send the data to the remote(s) when each extract completes. There should be no need to download the data manually either. . .

 

[FumaBob]

Thank you all for your great answers. This has pointed me in the right direction!

 

[dchristensen]

I'm part of a data warehouse team that daily extracts mainframe data that's moved to the LAN and loaded into SQL Server. We generate delimited files and transport the files using FTP. While there are alternate ways to do this, our solution is clean and reliable due to its simplicity.

You can also keep watch on some open source solutions that have direct mainframe dataset access in their future: Talend and Expressor Studio. We have experimented with Host Integration Server, which has been combined into other Microsoft products. Using the HIS, we perform keyed access on VSAM files to feed web services, direct access from a VB GUI program. At the time, we had issues if the record description was complicated.

Access using ODBC works well if you're accessing databases on the mainframe, but I haven't seen tools that use that method to move dataset data. Years ago I think there were some companies talking about it, but haven't seen it.