Index conflict between Pervasive CC & Magic: cannot see the indexes on Pervasive SQL control center && definition mismatch on Magic

The MAGIC tool that builds the DDF's has been flawed for years -- but they don't seem interested in fixing it.  Your only solution is to understand the structures and either recreate all new DDF's that do properly match the file structures, or to try to patch your existing DDF's to match..

Unfortunately, you didn't mention your PSQL version, and the methods to do either change depending on your version of the engine and what tools you have.  With PSQL7, the tool you start with is DDFEase, which allows you to compare the DDF contents with the Btrieve files.  With PSQL2000i and PSQLV8, you right-click on the database and select Tasks/Check Database.  This brings up the Check Database Wizard which can allow you to run a full consistency check on the data.  If you have PSQLv9, you must download the consistency check separately from Pervasive's website -- look for the ComponentZone in the developer area to find it, and the run this tool.

Once you know which files don't match (likely most of them, from my MAGIC experience), you'll know why your PHP scripts run slow.  Any indices that don't match can be thrown out by the SQL engine, forcing a table scan in almost every case.  Not a big deal for small databases, but if you join four (tiny) 1000-record files, a tablescan on all joins can force the system to examine 1,000,000,000,000 combinations -- and it will NOT be pretty.

So, how to fix it?  With PSQL2000i and PSQLV8, you can use the built in table editor (Right Click the table, Edit Table Design) to modify the DDF structure.  However, it is imperative that you verify upon entering this tool that you are in UNLINKED mode (lower right corner) before making any changes.  If you are in LINKED mode, then you will change the Btrieve file, and will get errors from Magic.  Look at the Tools or OPtions menu to change the mode.  Once in UNLINKED mode, any changes you make will get changed in the DDF's ONLY, and the Btrieve files will be fine.  Make any needed changes and save them.  When done, run the consistency check once again to validate that it fixed the issues.  When learning these tools, do one index and file at a time until you get the hang of what is needed.

If you have PSQLv9, the problem is worse -- you'll need the Pervasive DDF Builder product, available via free download from their website.  Unfortunately, this tool is far from ideal and still has some serious flaws that limit its use.  If you must do this, work slowly and make frequent backup copies.

My own personal favorite solution is to build a set of CREATE TABLE statements (and CREATE INDEX, too) for my database.  With a bit of experience, you can build a CREATE TABLE statement that creates the DDF's and Btrieve file that EXACTLY matches the ones created by MAGIC.  Once you can do this, the resulting DDF's will match the Btrieve files, and will be much less work overall than using the GUI tools to edit them one at a time.  There are some cases whereby the CREATE statements do NOT create the same table, so some experimentation is also in order there.  However, we've been successful at building CREATE statements for numerous customer databases, and these have the advantage of being easily modifiable and easy to reproduce -- just re-run the scripts.  

mirtheil... yes i am sure the Pervasive database couldn't use indexes from Magice when generating the queries.

i have timed a query on php page, then added indexes on Pervasive CC then tested again. the speed is different.

BillBach...

thank you so much for your message. that is very helpful for seeing a clear vision of what i am encountering.

the version of the software i use:
Pervasive 9.50.077.002
Magic 8.3 SP7

i will try what you have suggested and come back to you with my results.

i have also got a reply from Pervasive in Australia. please see the following:

//=========email from Pervasive==============
The first thing to do would be to run the check database wizard against the newly created DDF's to make sure that they are consistent with the physical layout of your btrieve tables.
 
The check database wizard is a seperate download for PSQL:
http://www.pervasive.com/kb/
Refer to solution ID: 00017355
 
They must be 100% accurate, anything less can lead to unpredictable results.
 
Further than this we will need to open an incident with Pervasive to address.
See how you progress with my recommendation above, and if required I will advise you on the cost and procedure to open an incident.
 
Regards,
Domenic Russo
Technical Sales Support - CPP, CPT, CPD
Fagan Microprocessor Systems Pty Ltd
Phone: +613 9699 9899 Fax: +613 9699 7501
http://www.btrieve.com.au
//=========end of the email from Pervasive==============

thanks

shizhen

If Domenic can help, then that'd probably be the best place to go to start.  I don't know if they provide this service under the "per-incident" pricing plan, because it can take a long time to get some DDF's fixed, and some are in fact impossible to fix.  

We do the work for customers too, but on a time&materials basis, which can run quite a bit of money -- but which does result in a good set of DDF's without having to learn the in's & out's of the process.  (Of course, learning how to do it will pay off in many other ways, so if you have the time, I'd definitely invest in fixing it yourself.)

BillBach thanks again for the message

i have downloaded the consistency check patch (checkdb.zip) from Pervasive website, and installed as it instructed.

as you have presumed: none of my table definition matches the one in Magic. the major issue of each table definition mismatch is the indexes:

something like:
... ...
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 0
The dictionary does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 0
The data file does not.
... ...

//a bit of history of my project:
==>the project i am doing involves other people who do the Magic part of programming.

==>the database was created originally by them. and there is also a frontend application for the data input. which means during a day, there will be people answering the phone calls, and put the data into the database via Magic application;

==>and obviously my php website is using the database at the same time.

==>Magic people didn't do the DDFs for me. i have asked them to provide DDFs once, and they came with some DDFs which can never be used in any cases: in fact Pervasive Control Center failed to open those DDFs, and DDF builder opened them with a complete mess on unmatchable the tables and everything else. in the end i gave up on their creating DDFs on Magic

the only thing i haven't tried from what you suggested is: build a set of CREATE TABLE statements (and CREATE INDEX too)...

but i did try on the existing DDFs, ran the script which creates INDEXes for me such as:
CREATE UNIQUE INDEX customerid_idx IN DICTIONARY ON ECFIL001("customerid");
....
however it didn't bring the index to Pervasive Control Center index tab: nothing appears on there.

then i realized the index on Magic is called index 0, so i tried the following:
CREATE UNIQUE INDEX index_0 IN DICTIONARY ON ECFIL001("customerid");

but i still got a blank area on Pervasive Control Center.

alas! it didn't help much when i don't have control on the Magic part of programming.

shizhen

The problem is the IN DICTIONARY clause, which will oftentimes make the problem worse with the newer engines.  Are there *ANY* indices defined in the DDF set for your tables?  Try doing a "SELECT COUNT(DISTINCT(Xi$File)) FROM X$Index" and let me know what you get.

If there are NO indices at all, then I can probably repair the DDF's by creating the index records directly -- and more quickly than even the CREATE TABLE statements -- but this means that whenever a data file changes, you'd need help cleaning them up again.

Try this process:
1) Create a new database in an empty directory.
2) Pick a file to work with and do a BUTIL -STAT on the structure in the live data directory.
3) Create a CREATE TABLE statement (without the IN DICTIONARY) and CREATE INDEX statements for the table.
4) Run the CREATEs in the new database.
5) Do a BUTIL -STAT of the resulting Btrieve file in the temp directory.
6) If the STAT output does not mostly match (record length must match, index positions, lengths, & data types must match, etc.), DROP TABLE, fix the CREATEs, and Loop back to step 4.  Repeat as many times as needed.
7) When the STAT results match, copy the live file over to the temp directory, overwriting the empty one.
8) Do some simply queries on the data, and run the CheckDB wizard against it.  It should PASS.
9) If you have another file to fix, Loop back to Step 2.  Continue until all files are fixed in this way.
10) When done, your DDF set in the temp directory should be perfect.  Move into your database directory, and you should be set!

The query "SELECT COUNT(DISTINCT(Xi$File)) FROM X$Index" give the following result:

EXPR_1
   22

will do the 10 steps and let you know the result.

thanks heaps!!!

This says that of the files in the database, 22 have at least one index defined.  You can compare this against the number of tables that shows up in the database (and to the number of System Tables as well).  If 22 is considerably lower than the number of tables, then you are correct -- there are no indices defined.  If the number of tables is around 22 (including the 10 or so system tables), then you may have indices defined, which could point to a problem with the Edit Table tool instead.

There are 16 tables not including the system tables in the database.

i have done this query to find all indexes on INDEX.DDF

SELECT DISTINCT Xi$File, Xe$Name, Xf$Name FROM X$Index, X$Field, X$File where X$Index.Xi$File = X$File.Xf$Id and X$Index.Xi$Field = X$Field.Xe$Id

Xi$File,Xe$Name,Xf$Name
1,Xf$Id               ,X$File              
1,Xf$Name             ,X$File              
2,Xe$Dec              ,X$Field            
2,Xe$File             ,X$Field            
2,Xe$Id               ,X$Field            
2,Xe$Name             ,X$Field            
2,Xe$Offset           ,X$Field            
3,Xi$Field            ,X$Index            
3,Xi$File             ,X$Index            
3,Xi$Number           ,X$Index            
3,Xi$Part             ,X$Index            
4,Xu$Id               ,X$User              
4,Xu$Name             ,X$User              
5,Xr$Column           ,X$Rights            
5,Xr$Table            ,X$Rights            
5,Xr$User             ,X$Rights            
6,Xv$Id               ,X$View              
6,Xv$Name             ,X$View              
6,Xv$Version          ,X$View              
7,Xp$Id               ,X$Proc              
7,Xp$Name             ,X$Proc              
8,Xr$FId              ,X$Relate            
8,Xr$Name             ,X$Relate            
8,Xr$PId              ,X$Relate            
9,Xt$ActionTime       ,X$Trigger          
9,Xt$Event            ,X$Trigger          
9,Xt$File             ,X$Trigger          
9,Xt$ForEach          ,X$Trigger          
9,Xt$Name             ,X$Trigger          
9,Xt$Order            ,X$Trigger          
9,Xt$Sequence         ,X$Trigger          
10,Xa$Id               ,X$Attrib            
10,Xa$Type             ,X$Attrib            
11,Xo$FileId           ,X$Occurs            
12,Xvar$FileId         ,X$Variant          
14,zonecode            ,TABLE003            
16,coupon_number       ,TABLE010            
16,invoice_number      ,TABLE010            
21,debtors_unique_no   ,TABLE031            
25,coupon_no           ,TABLE064            
26,coupon_no           ,TABLE022            
26,private             ,TABLE022            
27,coupon_no           ,TABLE020            
29,outcome             ,TABLE028            
31,collection_centre   ,TABLE037            
31,state               ,TABLE037            
33,customer_service_rep,TABLE071            
33,email_address       ,TABLE071            
33,service_rep_name    ,TABLE071            
36,coupon_number       ,TABLE019            
36,debtors_unique_no   ,TABLE019            
42,Xd$DependName       ,X$Depend            
42,Xd$DependType       ,X$Depend            
42,Xd$Trigger          ,X$Depend        

those indexes from my tables are however not within DAT files.
hope i will be able to attach this screen shot from my DDF Builder.

(but if you cannot see it, here is the link to the image    
http://s3.ec1.cc/shizhentest/imagelibrary/500018500012.gif)

<br><img src="http://s3.ec1.cc/shizhentest/imagelibrary/500018500012.gif" border=0>

on the screen shot, the ones which are not ticked on the "SQL incl" column are from Magic, and therefore those are the ones in the DAT file.

the ones are ticked, are created by Pervasive Control Center which upset Magic application and gives "definition mismatch" on that table on Magic application. then i had to delete those ones from Pervasive Control Center but they remained on Index.DDF, that is why i can see them on DDF Builder.

and just a silly question from creating a database in your way:

how do i do "BUTIL -STAT on the structure in the live data directory"?

thanks

i have fixed it by adding INDEXes to the definitions

CREATE UNIQUE INDEX index_0 IN DICTIONARY ON ECFIL018(customerid);
...
...

the reason why it was not showing on Pervasive Control Center 9.5 was: you cannot have DDF Builder running at the same time, nor would you see it by hitting F5 or refresh. but when i close PCC then reopen it again, it is there!

BillBach...

another thing i have found out is: the INDEXes will have to add into DDFs as PCC looks to DDFs for the indexes while DDF builder looks to DAT file for such.

but thank you so much for your comments.