Ensure Valid DDFs Before a BTR2SQL Migration

Oct 30, 2018 4:01:00 PM / by Oliver Nelson

  Oliver Nelson

Before getting started with BTR2SQL, here's what you need to know about DDFs and the role they play in converting Btrieve/Pervasive files to SQL tables.  

What is a DDF?

Btrieve/Pervasive applications often make use of a Data Dictionary File (DDF) during the development process to precisely describe the layout of a data record. This DDF is not used in deployment, it is part of the source code of an application. Magic-based applications normally don’t have DDFs, but they do have a similar means of describing the Btrieve record layout using uniPaaS XML-based source.

BTR2SQL requires valid DDFs to convert Btrieve/Pervasive files to SQL tables and migrate the data. It is absolutely essential the DDFs describe the entire layout of the data, or schema, within the Btrieve files. Without the DDFs or uniPaas XML to describe the layout of the data, BTR2SQL has no way to tell where one piece of data begins and ends.


How to create or update DDFs

Below are several options you can use to create DDFs when they are not available or use a very old format:

    • If you are running Pervasive.SQL 7.0 (or greater) you can create the table definitions using the Pervasive DDF Builder tool included with the Pervasive installation
    • If your software platform includes an export object option, you can use that to create DDFs from your class definitions
    • You can also create a Pervasive SQL script to CREATE TABLEs based on record structures defined in your code (this is an advanced technique)

    • For Magic-based applications only: You don't need DDFs! BTR2SQL can directly work with the uniPaaS XML source format. See this article for more details.

How to validate your DDFs

Once you have DDFs, Mertech's DDF Validator can be used to verify your DDFs adhere to a compatible layout. The goal of DDF Validator is to alert you to possible problems and to make suggestions on how to fix them BEFORE migration.


DDF Validator

  • Searches for common mistakes, like more than one table defined for the same Btrieve file or a Btrieve file found in the database folder that is not defined in the DDFs
  • Examines each table to ensure the number of rows read matches the number reported by B_STAT, record length and column definitions are in sync, table names are valid and field types are compatible for SQL
  • Verifies that the field length and content matches the data type
  • Looks for proper index definitions
  • Reads every row (you can also set the maximum number of rows to read) from each table in the database and examines each field for a proper match with the field definition

When an error is detected, a message displays providing the error code, severity from 1 (the most severe) to 3 (least severe) and a suggestion to help you fix the problem. In some cases the suggestion indicates the action that will be taken during migration if the problem is not resolved.

Sample output from DDF Validator

Error: Btrieve file 'filename' found in database location but is not defined in the table list.
Suggestion: Should this file have a table definition?

Error: No primary index and no Identity column defined for this table.
Suggestion: Performance is better when a primary index is properly defined. 'MDS_RECNUM' will be added as the primary index column.

Error: Field has binary data. '1' found at offset 4 in the field. (error will be suppressed in subsequent records).
Suggestion: This may mean adjacent fields are misaligned or mis-sized. Or, the field should be defined as Binary instead of Text.

Error: Column defined beyond Btrieve record size.
Suggestion: Columns must fit within defined record length.
Note: If the file is variable length, the field will be dropped/ignored when the migration is run.

Performing your migration

Use the suggestions to update the DDFs and then rerun DDF Validator. Once you are happy with the output from DDF Validator, perform the migration using the BTR2SQL GUI Migration Utility.

Detailed instructions for running DDF Validator and the BTR2SQL GUI Migration Utility are given in the BTR2SQL User's Guide, which is included with the BTR2SQL installation.


Help for end users

If you're not the application developer, using BTR2SQL without the support of the developer and without the source code from the application is not something we advise. If you are an end user of an application that has been abandoned or the application vendor is no longer in business, please contact our consulting team for a consultation on available options.


Originally published Feb 14, 2017, updated Oct 30, 2018


Topics: Btrieve

Oliver Nelson

Written by Oliver Nelson

Director of Engineering at Mertech Data Systems, Inc.