To retreive the Mainframe Job runtime to create Excel report

[vignesh]

Hi,

We are monitoring some scheduled jobs manually(around 30 jobs) and enter the end times for those scheduled jobs in a excel sheet under "end time column" on daily basis. In JHS the job run time is present for pervious days runs too.So we need to enter the last recorded end time of that particular job under "end time " column in Excel.

We are doing this process manually. Is there any way to automate the process in Excel. To retreive the Job End time in Excel sheet.

Could you please suggest me is it possible to write any automation script for the above requirement?

Please let me know if you need more information.


Thanks in advance.

Vignesh.

[Sisyphus]

Hi,

If I understand you correctly, you want to retrieve data from JHS (which I presume to be a mainframe database) and carry them to an Excel worksheet. Excel can't access mainframe data using its worksheet functions. It can probably be done using code but I doubt that your IT will approve of that.

The alternative is to export the data from JHS, say, in CVS format. Then the job would be to import the CVS data to your Excel sheet. Hypothetically, one might find a way to convert the CVS data to a spreadsheet, then load that sheet into the workbook containing the "end time" column. That column should then contain a formula taking the times from the currently loaded CVS-conversion. You change that sheet every day and, consequently, always have current data in your end time column. There might be a problem with correlating the rows. You wouldn't want the end time for a new job in the end time cell for a job that has been completed. That, too, can probably be managed.

So, the first step would be to have a look at the data that come out from JHS.

Does that help?

[vignesh]

Hi Sisyphus,

Thanks for your valuable suggestion.
Could you please clarify me on this procedure of Mainframe -> CVS -> Excel.

Lets consider the Example:

Mainframe JHS(Job History display for job ALM2LFLG)
Job Job# Started Ended Code
ALM2LFLG J000177 12132011 0600 12132011 0600 0 STEP NEXEC
ALM2LFLG J014893 12142011 0601 12142011 0601 0 STEP NEXEC
ALM2LFLG J029638 12152011 0600 12152011 0600 0 STEP NEXEC
ALM2LFLG J013603 12162011 0600 12162011 0600 0 STEP NEXEC
ALM2LFLG J009694 12192011 0600 12192011 0600 0 STEP NEXEC

Now I want to move only the Last Ended date of this job (12192011) in an excel column. Similar way need to do it for a list of other jobs.

Pardon me that I am not aware of CVS concept. Can you please explain me on this and the procedure to move my jobs run time from JHS to CVS.

Thanks in advance,
Vignesh

[Sisyphus]

Hi,

Can't say that I am familiar with CVS files, either. Don't even know the proper name. Sorry, it is CSV, of course, and the abbreviation stands for Comma Separated Values. CSV is also the extension of such files. As the name implies, the values are separated by commas, and there are no other formats in the file.Excel just puts each value in a cell by itself and starts a new row at the end of each line.

The text you sent can be read by Ecxel in this fashion. Try to open the file in Excel. But instead of breaking on commas, start a new cell at each space. So, open Excel. Press File/Open and browse to the file containing the data. Excel should show you an interface where you can specify these things.

If this succeeds you have taken the first hurdle, because you imported the mainframe data into Excel. Trouble is that you have 30 files which to produce probably takes longer than entering the end times by hand. But you may like to cross that bridge when you come to it.
Regards,
Sisyphus