Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Help: Updating a focus hold file

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Help: Updating a focus hold file
 Login/Join
 
Gold member
posted
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
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
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?
 
Posts: 86 | Location: Atlanta | Registered: May 10, 2007Report This Post
Expert
posted Hide Post
trob, you know how to use MODIFY, yes?




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
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)


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Help: Updating a focus hold file

Copyright © 1996-2020 Information Builders