Results of the Consistency tests in my PSQL 2000i DB

[boolean]

Hi,

We had recently moved to Pervasive 2000i from Pervasive 7.0 and have been facing problems following that...
We are having the problem of the DDFs not matching the existing btreive files. I had run the *consistency test* on my database and most of the tables had index inconsistency or Record length mismatches.
Here are the Statistics:

Pervasive.SQL Compatibility Mode = 7
Tables Checked = 269
Tables Passed = 117
Tables Failed = 152

Illegal Object Names = 7
Record Length Inconsistencies = 25
Variable Length Column Inconsistencies = 0
Data Type Inconsistencies = 0
Column Overlaps = 0
Full Path (instead of Relative) Table Locations = 0
Table Files Don't Exist = 1
Index Inconsistencies = 519
System Table Inconsistencies = 0
General Dictionary Inconsistencies = 1

Here is my basic standpoint on the issues:

Illegal Object Names: This was seen to occur because of columns of the tables being given names that are a part of the Pervasive SQL 2000i Keyword list. I had noticed that we seemed to have tables/columns with the name ‘ACTION’, which was a keyword in PSQL. I believe ‘ACTION’ was not a keyword in your PSQL v7.0 release.
Record length inconsistencies: There seems to be an additional 1 byte that is being added to the field in the tables with 'Fixed Record Length mismatch’. It seems that PSQL 2000i has a new mechanism of tracking null fields. PSQL would add an additional byte for each null able field. Could this the reason for the ‘Record length inconsistencies’?
Table Files Don’t Exist: The DDF in the existing database seems to point to a data file (btr file) that does not exist!
Index Inconsistencies: The index inconsistencies that are being referred to here, I believe are linked to the ‘Index ACS Flag Inconsistency’ that is the most common crash in the Consistency tests.
General Dictionary inconsistencies: I haven’t looked into this part yet. Please pass on your comments on the same.

Our application here that uses the PSQL2000i backend is one that is used by many different clients (with separate databases) and our conversion from PSQLv7.0 to PSQL2000i resulted in problems all around. So we are looking for an approach that would be global. All we would like the users to be doing is to run a program that would synchronize the DDFs in their respective databases with the corresponding Btrieve files.
Following this deliberation, I had gone in for the SQL approach, wherein I would make a simple program to create a new database (that would contain empty DDFs by default) and subsequent to that I would be creating new tables in the database from the Btrieve files that would side-by-side update the DDFs in the database.

I would appreciate if you could answer the above questions and also validate my approach of creating/editing the DDFs to take care of the DDF-datafile failures in the Consistency tests...

Thanks,
Sham

 

[Vancouverite]

Here's my opinions, for what they are worth, from someone who migrated from Btrieve 6.15 to PSQL 2000i.

(1) Illegal object names: Ignore 'em. The queries will still work if you quote the field name. Sure, it would be cleaner if they didn't exist, but it's not actually a problem, more of a warning.

(2) Record Length: How did you convert the DB? We copied the DDFs, attached them to a new Database, and then pointed them to the tables. After that, we ran the conversion utilities to upgrade the tables to the new format, including true nulls.

If you created the DDFs by SQL statement and then copied the files, you will have a mismatch. As you had deduced, 2000i uses an extra byte for "True Null" support. You can create definitions that match older format tables by using the SET TRUENULLCREATE option. For example, say you had this legacy table:

Emp_ID integer not null
Emp_Name varchar(25) not null
Emp_Spouse varchar(25) null

If you create this in 2000i with true nulls, the DDFs will expect one more byte than the old physical file contains. However, if you create the definition like this:

SET TRUENULLCREATE Off#
CREATE TABLE Employees (Emp_ID integer not null, Emp_Name varchar(25) not null, Emp_Spouse varchar(25) null)#
SET TRUENULLCREATE On#

The DDFs will now match the legacy table layout.

(3) Table doesn't exist: Drop it, unless you actually use the table.

DROP TABLE BadTable IN DICTIONARY

I haven't used ACS, so I haven't run into these problems.

NOTE: DDFs can be upgraded from 7.0 to 2000i directly, if needed. Use the CNVDDF utility to upgrade existing DDFs without making any other changes. You can copy the DDFs to a new location then use CNVDDF, and you have 2000i compatable DDFs that should map to your existing tables with no other changes. You can find the CNVDDF utility at

http://www.pervasive.com/support/toolbox.asp