IBM Mainframe Forum

Hi,

Below are the tasks i need to achive.

1 ) I need to count number of records in two input PS files
2 ) Then update the count value in an excel sheet that is present in a link.

If you have a look at this topic, assembler/topic6188-10.html, you should be able to crack the count part.

Then you'll need to transfer the counts to the PC Network. Take them into the Excel spreadsheet.

This all assumes you don't want to do anything but the minimum, don't need to check you have the rights files from the right date, etc

Hi Billy,

I am actually planning to write a job that can be sceduled and update the sheet daily. Here is my JCL, i am done with task 1, but struggling to achieve the task 2 ( updating the excel sheet that is present in a link with the count value). Please help me.

JCL :

//STEP01 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DISP=SHR,DSN=*****
//IN2 DD DISP=SHR,DSN=****
//RPT DD DSN=******,DISP=MOD
//TOOLIN DD DISP=SHR,DSN=********(TOOL1)
//CTL1CNTL DD DISP=SHR,DSN=*******(CTL1)
//CTL2CNTL DD DISP=SHR,DSN=*******(CTL2)
//*

DATA PRESENT IN TOOLI :

COPY FROM(IN1) TO(RPT) USING(CTL1)
COPY FROM(IN2) TO(RPT) USING(CTL2)

DATA PRESENT IN CTL1:
OUTFIL FNAMES=RPT,REMOVECC,NODETAIL,BUILD=(400X),
TRAILER1=('UK -',COUNT=(M10,LENGTH=8))

DATA PRESENT IN CTL2:

OUTFIL FNAMES=RPT,REMOVECC,NODETAIL,BUILD=(400X),
TRAILER1=('ROI -',COUNT=(M10,LENGTH=8))

Please let me know if you need any more info.

If you just want the count, I suspect COUNT instead of COPY with NODETAIL would be a better choice.

Your best bet now for the second part of your task is to talk to people at your site. If something similar is being done already (transferring data from the mainframe to the PC network) then you should do it the same way. If it is not being done already, someone will be interested in doing it securely and for it to fit in with your site standards/software available.

I strongly suggest transferring a business date/data data which is associated with the files. Personally, I'd not send data from the Mainframe without this, The business date/data date should be checked on the receiving system, as well as something to identify the file, so both on a header record. Otherwise when the wrong data is used, someone is going to ask you why. I'd also include a trailer, with record counts and hash total. It might seem "over the top" for such a small amount of data, but I'd do it the same as any other file I had to send anywhere. Then I'd be happy with the data as it leaves, the receiver knows how to check it, and if it is the wrong date/file/gets a bit trashed somewhere, the problem would not come back to me.

Without doing it properly, one day it will go wrong. Count on it. And that will be the day that someone is presenting the spreadsheet to the Board of Directors. Count on it.

Hi Billy,

Thanks for your valuable suggestion. I have written jcl to send mail with excel attachment to my users. But here, this is completely different which was not introduced in our system before. And more over not everyone in the system will have access to open the spreadsheet link, so the data in the spreadsheet is secured. Please let me know if you have a code to update the values in the spreadsheet in the link.

Thanks in advance.

Hi,

The data in the spreadsheet can be as secure as it likes. Problem is, what ensures that the data is not wrong? There are always three issues with external data, security, validity/integrity. Hi Akatsukami (that last bit is just a joke). The real third one is timeliness. If you don't cover them all, then the job is not done properly.

I have no idea about your environment/software available/standards etc, which is why I'm saying you have to go to someone at your site (if you don't know who, start with your boss, get them to tell you/find out). You'll also learn much more that way, and by doing it properly. OK, you do learn by not doing it properly - when everyone ends up with custard-covered faces, then you think long and hard about how it happened, and how to avoid it ever happening again... Always remember, if it can go wrong, it will go wrong. If it will go wrong, it'll go wrong at a most inopportune moment.