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?