Using a Pervasive database external index file

Posted on 2009-12-27
Last Modified: 2013-12-25
I'm using Pervasive (v9.5) with some fairly large files. It would be convenient to be able to use external index files, but I can't figure out how to use them. I have used the Pervasive tools to create an external index file. My question is: How to I use Pervasive to actually use the file for reading data?

Pervasive built the file, but does not seem to recognize it as part of the database, though the file physically exists.

I've spent hours and hours in the documentation and find several reverences to using external index files, and examples of how to create them. Just no information on how to get the job done at runtime.

Do I have to take an extra step to associate the new index file with the original data file?
Once the association is made, does Pervasive automatically maintain the external index file?
Are there any special SQL commands that need to be included when starting a query?

Question by:digitron
    LVL 18

    Expert Comment

    What exactly do you mean by "External index files"?  I've never seen that in relation to Pervasive before. Where in the documentation are you looking?

    Pervasive data files are not automatically added to the database unless they are created using CREATE TABLE statements or DDF Builder tools or the DTI/DTO interface.  Data files can exist strictly as data files and are used by the Btrieve interface.  The Btrieve interface doesn't use DDFs or a database.

    What are you trying to accomplish by using the external index file?  Are you seeing performance issues executing queries?  

    LVL 28

    Accepted Solution

    External Index files are supported by the utilities, but they are never used by the engine itself in any way.  These external indexes are created with Maintenance and create a file with this definition:

    "An external index file is a standard data file that contains records sorted by the key you specify. Each record consists of the following:
    - A 4-byte address identifying the physical position of the record in the original data file
    - A key value"

    To use the external index, you must write your own Btrieve-level program to read the records, pull out the 4-byte position, and do a subsequent lookup into the database file using a GetDirect call.  Obviously, as records can have c hanged or been deleted since the index was created, they should only be used on a temporary basis, and you should always check your results to make sure that all records still match the index.

    As Mirtheil indicated, if you are trying to get the index data to be seen from SQL, then your only option is the standard CREATE INDEX option, which creates the index inside the file (and continues to maintain it from then on).  The SQL engine would have no way to validate the data in the external index file, even if it could somehow be linked to it.

    Author Closing Comment

    Thanks for your clear and complete answer. I may, in fact, try using Btrieve in the way you indicate. To answer Mirtheil about what I'm trying to do: I have a customer with some file of over 5 million records that is being accessed with Btrieve by several computers in different cities.

    Turns out that new indexes need to be applied to these files, but I don't want to have to update every workstation with new schemas until we have everything working. The ideal solution is some sort of external reference (indexed properly) that can be used for testing and development. I was hoping that the Pervasive external index files would work in that way, but maybe just building external data tables with the data needed for lookup in the main file would serve just as well for the purpose.

    Or course, if you have a better idea, I sure would like to hear it!

    LVL 28

    Expert Comment

    by:Bill Bach
    Why would you need to update all workstations?  In a typical Pervasive environment, the database is stored on a central server, along with the data dictionaries (DDF's files), which contain the schema.  A simple CREATE INDEX statement should be enough to permanently add the index, after which it will be available via any SQL-based applications.

    If the existing applications are Btrieve applications, of course, then the index will need to be added and THEN the application will need to be updated.  This is the nature of Pervasive's high-speed, low-overhead transactional interface -- the data interpretation is up to the app itself, not the database.

    Author Comment

    Hello. Many thanks for your help. As it is now, the program does use Btrieve. I've been called in to clean it up, since it has become bloated over the years.

    They have a stable version working (though one report takes 17 hours to run because the needed indexes don't exist). It may turn out that the most efficient way to do it is to just plan the schema changes carefully, then (as you say) update the application that gets distributed to all the users. The company is run by a programmer who understands all this and he's comepletely cooperative with these issues, which is more than a little helpful.

    I was just considering using Pervasive SQL (which can use the Btrieve files) to manipulate the data in some of the most-needed areas. I'll probably be working on this one for years. There are over 1,200 sub-programs and 1,000 files. The good news is that it all basically works--my job is to polish it up. It is just that every time I get into it, I find data being accessed without complete indexes, which might be "close enough" if MySQL or MSSQL were being used, but with Btrieve, you just have to have indexes with the proper segments in place or the whole process will switch over to sequential access.

    Again, thank you for being so generous with your help--you should get extra points. Again, if you have any ideas on how I might do this more easily, I would sure appreciate them. One programmer I know developed an ingenious external file index system that uses the data itself as something of an open indexing system. Once you maintain these extra files, you can get the data out in almost any order, on the fly, without building new internal indexes.

    Problem is, every program and subtask would have to be rewritten. Out of the question.
    LVL 28

    Expert Comment

    by:Bill Bach
    I would avoid the external index files.  Even PSQL doesn't use them, favoring instead to build complete separate files with the data set needed, and then adding an index on that data in order to satisfy the ORDER BY clause.

    The nice thing about Btrieve, of course, is that it is a simple API.  The applications know about the indices that they know about, and that is it.  This means that you can add a new index into a file for one application, and use it ONLY in that application, without having to recompile anything else if you don't want to.

    You are probably in the area where Pervasive's "best of both worlds" concept starts making sense.  You have all these old Btrieve applications, but you have some reports which take a very long time.  Add some "well-defined" data dictionaries, and you can start using the SQL interface for some of the reports.  Once you do this, you can add additional indices to satisfy the index needs of these reports to get improved performance.  The great part about this is that you do NOT need to do a complete re-write of every application!  Continue to use all of the old apps at the Btrieve layer, but add SQL where it makes the most sense.  Using the right tool for the job is what will make it easier in the long run.

    Author Comment

    Thanks again--you confirm what I'm thinking regarding Btrieve and Pervasive. There is one "fly in the ointment" that I need to look at. I'm using a 4GL called uniPaaS (used to be Magic PC, then eDeveloper). It contains it's own internal schema listing for each file. You can turn off the "check definition" feature in uniPaaS, which normally would not allow access to a file where the data file did not match this internal def. However, there are "safe" data definition differences allowed. As long as the Btrieve programs are only using the indexes that uniPaaS recognizes in it's definition dictionary, then having addional indexes internal to the file should not cause a problem (providing they come after the existing ones), so that's my next step. It may be as simple as adding indexes for new programs with Pervasive and in your wise words, use the right tool for the job.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now