Pervasive

My main database has several related side databases that contain similarly structured, but client specific data.  I have been successful in adding Data Paths (using PSQL DDF Builder), for these various side databases, and have been able to define the columns in the tables in the prototype database.  I have used the Copy SQL Definition to create table definitions for these side tables (eg. copy tblACNTS to tblACNTS_A, tblACNTS_B, ...;  tblACNTS_A points to file ..\A\ACNTS.DAT, tblACNTS_B points to ..\B\ACNTS.DAT, ...)

Looking in X$File, I see correct entries for several of these tables in the Xf$Name column, but the corresponding entries for Xf$Loc are not in all cases the correct path.  Also, one of the tables does not appear in the SQL Tables list for the database in the PSQL DDF Builder list of Database (however, I can query that table and get correct data).  Using "Change Associated Data File" doesn't save the change when I point it to the right table in this convoluted structure, preferring the table name without a path..

This raises the questions:

1)Is there a way that mere mortals, such as I, can avoid the "you're not the owner" error 51 in fixing X$File? (eg. update x$File set xf$Loc = '..\B\Acnts.DAT' where xf$id = 23)?  (And yes, I understand from reading the documentation that direct manipulation of these files is generally frowned on).

2)Interactions between X$Index, X$File, and X$Field seem relatively well defined and straight forward.  Are there other tables, other than those listed in "System Objects" that need to be considered in manually updating system objects?

3)I see an "X$Variant" table in System Objects, that I cannot find reference to in the SQL 10.10 documentation.  Is there documentation available for this table?

4)Using multiple data paths seemed like the best way to proceed to make the SQL definition conform with existing practices.  Would it have been preferable to set up separate databases for these side databases, or is there another "best practices" approach that I should consider?

I have found the ALTER TABLE xxxxxxx IN DICTIONARY USING 'yyyyyyy' command, which appears to be the supported means of solving the issues raised in question 1 above, and negates the need to worry about questions 2 & 3.  Question 4 is more a question of whether I'm sane or not, which is likely not a topic that this group can determine.  For the record in the above example, the way to straighten out the problem with what is stored in X$File is:

    ALTER TABLE tblACNTS_A IN DICTIONARY USING '..\A\ACNTS.DAT'

Gary O.

Please use the alter table syntax to put in the paths, we do not recommend or support manipulating the DDFs directly with update queries:

In the DEMODATA sample database, the Person table is associated with the file PERSON.MKD. If you create a new file named PERSON2.MKD, the statement in the following example changes the dictionary definition of the Person table so that the table is associated with the new file.

ALTER TABLE Person IN DICTIONARY USING 'person2.mkd' 

You must use either a simple file name or a relative path in the USING clause. If you specify a relative path, Pervasive PSQL interprets it relative to the first data file path associated with the database name.   

 Example:  I used alter table person1 in dictionary using '..\d1\person1.mkd'

    11   PERSON1                ..\d1\person.mkd                                                         64             
    12   PERSON2                ..\d2\person.mkd                                                         64             
    13   PERSON3                ..\d3\person.mkd                                                         64     

 The only information we provide on variant.ddf and occurs.ddf:

Two other system tables that you may encounter are VARIANT.DDF and OCCURS.DDF (for a V1 database) and PVVARIANT.DDF and PVOCCURS.DDF (for a V2 database).These two system files are used for COBOL support and do not require any direct intervention by a user. Future versions of the utilities for COBOL may implement a different architecture, in which case these system tables may no longer be required.

The simplest way to is to have the ddfs and all its associated files in the same directory.   This precludes having physical files with the same names.   If the dictionaries have only the names of the files in the location field, they will attempt to find the file in all the datapaths provided.   Extra care has to be taken to distinguish the tables when physical files of the same name are associated with one ddf and live in different datapaths (like the example, above).  In the example above, DDFBuilder created all three table definitions but the definitions were pointing to the same physical table.   To make the definitions point to the correct physical table, alter tables were done.   If you are going for an approach like company1, company 2, or division1, division2, etc. where you are going to use the same files over and over, one ddf to one set of files is the easiet method.