We have a VERY large database which has records dating back 8 years. The majority of the reports we run are against the current year
or last year. Thinking it would be beneficial to pull and create an extract with just the data we principally run against.

Know in FOCUS that we could create a SAVE file which would be a sort of permanent-temporary/hold file. Saw in the WebFocus Help that the
SAVE option is available. However, do not understand where this file would be and what format if we created it. Also, the Help
wasn't really helpful with how to create it. Our initial database is a SQL database.

This message has been edited. Last edited by: kerry,

In its simplest form - HOLD creates an output data set AND a data description file; SAVE just creates the output file.

The format is your choice and the location is your choice.

HTH

I would try to work with the database team to partition the database and put proper indexes before pursuing the FOCUS database path.
You might also need to get your security requirements laid out before you work with the FOCUS database.
For the creation of FOCUS database, you can find some pointers here

JI,

When you want to report from an extracted file, in almost all cases you should use HOLD and not SAVE, because HOLD also created an accompanying MASTER file.

This being said, how large would be a file of the current year? According to what you say, it would be quite large. What else could you say about the file? For example, if the file contained data pertaining to cities, would the subsequent reports mostly address one city or the whole file? Are there specific data against which IF/WHERE statements are issued?

There are many more considerations which would have an impact on the structure of the extracted file and how to build it.

So, I would suggest some more thought and analysis.

definitely go the FOCUS database route, we seem to agree. a HOLD file is a flat file, and won't save you much, those are usually work-in-progress files in the creation of a swell focus database. XFOCUS files can hold much more than FOCUS files, and make their own swell multi-dim indices.
I suggest a focus database structure,
One xfocus file for each year,
identical structures,
so they can be read either individually or UNION'd together.

Ditto with Susannah

JI,
The 'best method' might be different in different operating systems and your profile is empty so I am not sure of the best answer.

If you are in the mainframe (MVS or CMS) environment I would recommend that you TABLE each segment into a HOLD file and then load the segments into an alternate MASTER using MODIFY. This might take more time the first time you do this but you can use it forever and it will save you a lot of time in the future. You should also organize the history into chunks of time (1 year, 3 years, 5 years, etc.). Pick one time period and stick to it in the future to make maintenance easier. I would use yearly but I work for an insurance company and the files are huge!

Here are some reasons why I would do this:
1. Your history files will be easily access from FOCUS in the future and since they have the same format you can run your current reports against history data with a one line change in the code
2. Your 'new' FOCUS databases will be more efficiently reported from since they will be 0% disorganized with no empty pages to increase your I/O
3. You can put the real old files to tape and only reference them when needed
4. You can pick different time periods to combine to report from via the USE command.

If you are not in the mainframe environment then I am sure there are other methods that would be easier.

Michelle

We too have large amounts of data in our database. We create focus databases with limited amounts of data, ie.. two years. This works great for us.

quote:

0% disorganized

there's this year's tshirt slogan!!!

Split your files by year. Then have a 'use' statement concatenating the years as needed ( controlled by a DM statement based upon the dates requested).
User
CAR2004.FOC AS CAR
CAR2005.FOC AS CAR
CAR2006.FOC AS CAR
END