I need help updating a focus hold file. I've searched all over the boards and tried about 20 different suggestions however nothing worked.

History:
We have a heavily taxed data server so in order to run reports on our data we store reporting information locally on our WebFocus server as FOCUS databases. The staff that setup the current procedures opted to run a daily full extraction of every record on every update and overwrite the existing FOCUS database. I don't agree with this process so I want to move to an update procedure in which we only update records that have changed.

Some tables have timestamps or sequentially number field that I can use to select my update and others I would like to do a match to find records that have changed. I would like to see an example I could use for each.

Update Procedure 1:
Data Server Connection: dtasrv_con
Table synonym metadata name: srvtbl1
Fields: recordnumber(sequential Key), customernumber, customername
Focus DB: custdata
(I would like to add only new records from table srvtbl1 to focus database custdata.)

Update Procedure 2:
Data Server Connection: dtasrv_con
Table synonym metadata name: srvtbl2
Fields: customernumber(Key),address
Focus DB: custinfo
(I would like to update focus database custinfo with any records that have changed in table srvtbl2. Example: Update only records where the customer's address changed)

TIA

Environment:
MySQL Data Server
Windows2k3 Webfocus Server 7.6.1

This type of processing is full of if..but..maybe.

I have a similar type of problem with data arriving from another system every hour. One file is customer data, another geographic data. The geographic data worked fine as a MATCH and then add, delete or update records. The customer data performed better as a new load every time, though both sets of data coming in are the same (roughly) number of records. A third file coming in daily was a pig and would not perform with either MATCH or reload. Ended up using MAINTAIN, using sorted stacks for comparison, and updating/deleting/including as required.

So I have 3 similar files that now load in 3 different ways to get best/acceptable performance.

Don't always assume that a reload is bad, because a MATCH, to find changed data, can be very heavy indeed. But, without a method of getting only new/changed records, you will have to extract everything anyway, and a well constructed FOCUS db can load pretty fast.

So your 1st option could work well as a MATCH as there are only 2 fields to deal with. The 2nd option means comparing a number of alpha fields, heavy MATCH work. My code is just too complex to put here, but I hope you give this some thought.

Thanks for your suggestions. The tables I listed above are only examples. My live tables are much larger but the same principles will apply. Based on my real data I will be working with, most of my tables have a time stamp that I can use to narrow down the record selections before doing a match. I'm just having a hard time figuring out the most simple way of doing this.

For example on procedure 1,
in order to achieve my goal I would like to be able to select the last recordnumber from the custdata and only update custdata with records in srvtbl1 where srvtbl1.recordnumber is greater than custdata.recordnumber. I know the logic but don't understand how to code it.

For procedure 2,
I need to compare both tables and only update custinfo with records that are unique in srvtbl2. Guessing if I have to use a match that doesn't really help processing time unless I were to say there was also a timestamp field where I could do a similar record selection as procedure 1 where srvtbl2.timestamp is greater than the latest timestamp in custinfo.

Wow, am I getting over my head? Does anyone follow me?

trob, you know how to use MODIFY, yes?

For example 1, in pseudo(ish) code.

TABLE FILE CUSTDATA
SUM MAX.RECORDNUMBER
ON TABLE SAVE
END
-RUN
-READ SAVE &RECNUMBER
TABLE FILE SRVTBTBL1
PRINT CUSTOMERNUMBER CUSTOMERNAME
BY RECORDNUMBER
WHERE RECORDNUMBER GT &RECNUMBER ;
ON TABLE HOLD
END
-RUN
-IF &LINES EQ 0 GOTO :NOLOAD;
MODIFY FILE CUSTDATA
FIXFORM FROM HOLD
MATCH RECORDNUMBER
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON HOLD
END
-:NOLOAD

Which is relatively straightforward as you have a key to know what needs to be added from srvtbl1 to custdata.

For example 2, where there is no key to be able to define what has changed in srvtbl2, then rather than trolling through every part of the address on each record with a MATCH, which is hard work, you need to be able to find, easily, changed records. If you have no timestamp or recordkey for changes to an address then you have an issue. You could use the customernumber (from the HOLD file above) to retrieve new records and add them to custinfo, but that does not get the changed records from srvtbl2.

This comes back to the if..but..maybe. How easily does all the data load from srvtbl2 to custinfo? Does it actually achieve a result in a short space of time? Try and imagine what a MATCH would need to do:
MATCH FILE SRVTBL1
SUM ZIP
BY CUSTOMERNUMBER
BY CUSTOMERNAME
BY ADDRESSLINE1
BY ADDRESSLINE2
BY ADDRESSLINE3
BY CITY
BY STATE
BY ZIP NOPRINT
RUN
FILE CUSTINFO
SUM ZIP
BY CUSTOMERNUMBER
BY CUSTOMERNAME
BY ADDRESSLINE1
BY ADDRESSLINE2
BY ADDRESSLINE3
BY CITY
BY STATE
BY ZIP NOPRINT
AFTER MATCH HOLD OLD-NOT-NEW (think this is the right option!)
END
Then run an update MODIFY from the HOLD file.

Now for 10,000 records that might work (but not always), for 100,000 you may run into big trouble. You're talking about sorting through a lot of data.

Compare that to a straight load, you still need to extract everything from srvtbl2, but then a MODIFY load into your FOCUS db can run straight off it, no hard work, and load (in my experience) quite quickly.

In my experience there is no hard and fast rule, just try what seems reasonable, if it doesn't work try something else.

(As an aside, we found that one of the greatest performance improvements came from regular defrag of the server disk)

One other point to remember is keeping the FOCUS DB efficient for retrieval.

With a reload of a FOCUS DB (providing it is efficient) you should not have to perform a rebuild upon it.

If you are constantly updating your FOCUS DB then you should run a REBUILD, CHECK against it each time you update to ensure that it is not too disorganised.

As Alan will tell you there are many different ways of loading a FOCUS DB in segment, sort order etc. etc. but the main concern you should have is the efficient retrieval from it as that is when the end user requires speed - the update / reload is overnight batch generally and the end user isn't concerned about the time taken unless it encroaches on the on-line day.

T

Absolutely Tony.

I assumed, but that is not always correct, that the FOCUS db structures are efficient here for their purpose, and the load also efficient. A disorganised db will add considerably to the reporting overhead.

And you reminded me that it is very often more efficient to load the FOCUS db without any indices and add them later with a rebuild.

Absolutely Alan, it's something we all learn with experience.

Trob

The thing to realise is that there are so many things to consider when maintaining FOCUS databases (or any other RDBMS for that matter) that sometimes it is quicker to just overwrite than to update and that is probably why you have inherited the processes that you have now!

T