I will apologize up front for my lack of knowledge with FOCUS...and for the long post to follow.

I have been given a task to make something work and am in the process of learning FOCUS. We have limited access to FOCUS on our mainframe and all I have figured out how to do is create a new FOCUS executable and run it to query our data. Here is what I am trying to do:

I have an external database with user information in it with a common key (SSN). That information is not connected directly to our mainframe and gets out of sync. I have two specific data elements in my local database that I need to check on the mainframe side to see if they have changed...those are OFFICEID and PAYPLAN.

I have figured out how to write a FOCUS report, FTP that focus report up to the mainframe..say in a file called ER01, I have a piece of JCL that has been written to kick off that job ER01, I FTP that JCL up to the mainframe which runs the job and the results are put into a HOLD file which I retrieve after all this is done. It works great.

What I'm doing right now is generating my FOCUS report on the fly in my external database. So say I select 100 names from my database and click a button RUN FOCUS REPORT ...that generates the FOCUS code:

TABLE FILE EMPTABLE
WHERE SSNO IN (ssn1, ssn2, ssn3, ..., ssn100)
BY SSN
BY PAYPLAN
BY OFFICEID
ON TABLE HOLD AS DOWNLOAD FORMAT COMMA
END

I send that file up to the mainframe, run it and collect the download file when it's done.

Using that download file I then have a bunch of other hoops I jump through locally to parse through and see if any of my data in my local db needs to be updated.

What I would really like to do is do all that checking on the FOCUS side and only pull back in my report file those values that need to be updated. I don't think I have access to create a new table on the mainframe. I was hoping that there is some way to create a temporary table in memory or an array or something that I could instantiate with the data from my local database ... and then use that array to cycle through and check the mainframe data.

Again, I'm sorry for such a long post...I wanted to provide as much info as possible...thank you for any advice.

ER

PS: The one thing that looks promising is something called a STACK ... but I have not figured out yet how to create and manipulate one...or if I'm barking up the wrong tree.

This message has been edited. Last edited by: <Kathryn Henning>,

don't know what sort of 'mainframe' you have, and when you say 'local copy', does that mean your desktop?
easiest way seems to be to download your mainframe extract(can be just a fixform file, doesn't have to be csv), and do the comparison locally, using Dev Studio, on your own desktop. So, lets start with the first question: do you have Dev Studio installed on your own desktop? If not, you should have.

Thank you for the reply.

By Local Copy I mean down to my local computer. I download the extract file to a temp dir something like c:\tmp\focus\download.txt

I'm not sure what kind of mainframe we have ... I work for the US Forest Service and they are pretty rigid about installing non-standard software...I will look into Dev Studio but will have to jump through some hoops to get an authorization to install it.

But if I understand you correctly Dev Studio would take in the extract file and then I'd do my comparisons locally in Dev Studio -- I'm pretty much doing that successfully already albeit a clunky workaround. I just have some code in my local db that parses through that extract file doing the comparisons/updates.

While this works in prototype mode...I'm only looking at 100 or so records in this extract file...of which maybe only 2 or 3 records need to be updated. In production I would be looking more at tens of thousands ... so I feel like if I can do the comparisons up in FOCUS and only bring down what needs to be updated then I won't have to pull down a large extract file.

Thanks again for your advice,

ER

I came up with a solution. I think I was reading too much into it and should have thought more about the query side of things ... this is working for me. I generate the FOCUS code below using the values in my local database ... and it reports only those records whose data values do not match whats up on the mainframe data table.

TABLE FILE EMPTABLE

WHERE
(SSN EQ 'ssn1'
AND
(
OFFICEID NE '5025' OR PAYPLAN NE 'GS'
)
)

OR

(SSN EQ 'ssn2'
AND
(
OFFICEID NE '5096' OR PAYPLAN NE 'GS'
)
)

...etc....thru ssn100

BY SSNO
BY PAYPLAN
BY OFFICEID

ON TABLE HOLD AS DOWNLOAD FORMAT COMMA
END

Thank you,

ER