Quick Start Guide

Thank you for trying Mertech’s Btrieve to SQL database application drivers. By now you have either downloaded a trial version or purchased the product and you have installed it. This Quick Start guide is a summary of the BTR2SQL User’s Guide located in the Mertech ISDBC Drivers for Btrieve program group, which you can access by using your Start button. This Quick Start Guide lets you know where to go from here, or, how to get started.

The BTR2SQL product bundle consists of a GUI Migration Utility and database driver dll. The Migration Utility takes an existing Btrieve database and migrates it to a Microsoft SQL Server, Oracle, PostgreSQL database. The Migration Utility automatically creates the SQL tables and indexes, and copies records to the newly created tables. The Migration Utility requires data dictionary files (DDFs) to determine field types, field names and field size along with index structures.

The Migration Utility stores the SQL table schemas (derived from the DDFs) in INT files that allow the Mertech driver to connect to and open the converted SQL tables at runtime.

Important: DDF files are required for migration. Contact Mertech before attempting the migration if you do not have DDF files.

Prerequisites

Btrieve 6.15 or Pervasive.SQL 7.0 (or greater)

BTR2SQL contains database driver dlls to replace your Btrieve 6.15 engine (wbtrv32.dll) or Pervasive.SQL 7.0 (or greater) engine (w3btrv7.dll). The replacement drivers will allow your existing application to access the newly migrated SQL backend.

Btrieve data files with schema defined in Pervasive.SQL DDFs

The BTR2SQL GUI Migration Utility requires a Pervasive.SQL 7.0 (or greater) engine to read Data Definition Files (DDFs) during data migration. DDFs define the layout, or schema, of the tables in the Pervasive.SQL database. Without the DDFs, the data cannot be efficiently migrated to SQL.

If you are currently running Btrieve 6.15, you can obtain a later version of Pervasive.SQL and the DDFCONV tool from Pervasive (Actian). V2 Metadata (introduced in Pervasive.SQL 10) is not supported.

If your application is written in UniPaaS (now called Magic xpa Application Platform), Mertech provides MigrateUniPaasData, a command-line migration tool that works with Magic uniPaaS applications where the source files are stored in XML format. Contact Mertech for additional information.

See the Pervasive documentation and many other resources and tools available online for further information on building DDFs if they are not included in your database.

SQL back-ends

MS SQL Server, Oracle and PostgreSQL are the four SQL backends currently supported by the ISDBC for Btrieve drivers. One of these backends must be installed and running. The client software must be configured on the computer where the migration and the application are run. Refer to your database server documentation for details.

MS SQL Server

  • The DB_OWNER role should be set for the User ID identified for the migration process.

  • For the fullest support of data types, the SQL Server 2012 Native Client should be used. Alternatively, the 2008 or 2005 Native Client can be used. (Download the Native Client from the Feature Pack available here: http://msdn.microsoft.com/en-us/data/aa937733.aspx)

Oracle

  • Using Oracle’s Security Manager, add the Select Any Table privilege to the user ID used for the migration process.

PostgreSQL

  • The server must be 9.1 or above.

  • PostgreSQL does not support storing Zero (null) bytes in a string. If your data needs to include a Zero byte, you must include the Binary flag on the field in the DDFs (bit 12 / 0x1000 on field flags).

  • The PostgreSQL citext module provides the case-insensitive character string type CITEXT, which allows for case insensitive fields without the need for inverse key columns.

.NET Framework 3.5

The version 5 migration tool was rewritten to use the .NET framework resulting in an improved interface. The version 5 migration tool requires .NET Framework 3.5. The installation automatically installs .NET 3.5 if it is not already installed, except on Windows 8.

NOTE: Windows 8 includes .NET 4, but not the required 3.5 version. When you run an application that requires an older .NET version, Windows 8 displays a dialog box asking you to download it.

Licensing

If you have already purchased the ISDBC drivers, then you have received a registration file in a ZIP file along with instructions. If you are evaluating the product, a limited 30-day license is already installed. If you want to request a fully functional 30-day license file, contact sales@mertechdata.com. Please include your company name and some information about how you intend to use the drivers.

The ZIP file you receive also contains a license file: sql_btr.cfg for MS SQL Server, ora_btr.cfg for Oracle, pgs_btr.cfg for PostgreSQL. The file must be in the Windows PATH. The license is required by the Migration Utility, so place a copy of the CFG file in the installation folder (<programfiles>\Mertech Data Systems\DB Drivers\Btrieve\bin).

The license file is also required at runtime by your application. See Step 3 for information to replace the Pervasive dlls — we recommend that you place a copy of the license file in the same folder with the access dll and your application exe.

Your First Migration

Pre-migration: Validate the DDFs

Quality DDFs are the most important requirement for successful migration. Any mismatches in the data and the definition can cause problems. Mertech provides a tool to quickly check the DDFs and tell you if they appear to be well-defined. You can learn the details about this tool in the User’s Guide or by running DDFValidator.exe /?.

Step 1 - Migrate data to SQL backend using Btr2SQL

You must move the data from the original Btrieve files before the application can access the data from the SQL backend. You can do this either by using native tools such as BUTIL, DTS, Pervasive Integration products, etc., or you can use the Btr2SQL Migration tool provided by Mertech.

  1. Open the Btr2SQL tool in Mertech ISDBC Drivers for Btrieve using the Start button.

  2. Use the Select Files menu option and select the files/tables to migrate. We suggest that you use Select All for this step.

  3. Click Convert Data Files.

  4. Click OK to migrate all the selected tables using default settings.

Suggestion: Assuming Pervasive.SQL and the data reside on a machine other than the new MS SQL or Oracle server, run the Btr2Sql migration process on the Pervasive.SQL machine. This splits up the processing requirements of SQL and the data conversion.

What happens during the migration?

  1. A table definition file (.INT) is created in the directory with the data files. These contain schema information that the ISDBC driver will now use when accessing the data in SQL.

  1. The table is created on the SQL backend. It may contain extra columns or indexes needed by the ISDBC driver.

  2. The data is copied from Btrieve to SQL.

NOTE: At this point, Oracle, MS SQL or PostgreSQL tools will be used to access the data. However, after you test the application against the SQL backend, you may discover that the table definitions are misaligned. If this is the case, then it is best to use the same DDF editor that you used previously to correct the schema, and then you can migrate the affected table(s) to SQL again.

Step 2 - Post-Migration: Check migration using Migration Validator

Every row in the data files should be copied to the SQL backend or display as an error in the migration log and trace. As an extra precaution, we suggest that you run the Migration Validation tool for verification. For details about this procedure, look in the User’s Guide or by running MigrationValidator.exe /?.

Step 3 - Replace Btrieve Access dlls

A 32-bit Windows application accessing Btrieve data uses either wbtrv32.dll or w3btrv7.dll. The Mertech drivers are provided as a replacement for these two dlls. In the installation folder <programfiles>Mertech Data SystemsDB DriversBtrieveDeploy\ are subfolders, depending on the SQL backend being targeted. If you are not sure which dll is used, then replace both.

There are several places that you can place the dll, but the application must find the Mertech version of the dll on the Windows PATH first:

  1. Place the wbtrv32 or w3btrv7 dll in the directory with the application executable. Typically, this guarantees that the application locates the correct version.

  1. Place the dll in WindowsSystem32 or in some other common location in the system PATH.

  2. Replace the dlls that currently reside in the PVSWBIN folder. This ensures that the Mertech dll is used. However, many of the Pervasive tools will no longer function. For instance, if the table definitions need to be corrected, the original Pervasive dlls must be restored. Be sure that you save a copy of the original dlls before you replace them.

Important: The license file also must be found in the system PATH. We recommend that you place the sql_btr.cfg, ora_btr.cfg, or pgs_btr.cfg file in the same folder as the access dll.

Example:

If your application executable file is located in c:myappbin and you will be accessing data in Oracle:

Copy C:Program FilesMertech Data SystemsDB DriversBtrievedeploy\ Oraclew3btrv7.dll and ora_btr.cfg to c:myappbin.

Step 4 - Test

At this point, the Btrieve data has been moved to the SQL backend and the application should be using the new Mertech driver dll. The application should not need modification to access the data within SQL.

As an example, an application written for the Pervasive DemoData database uses the B_OPEN API to open c:pvswdemodatabilling.mkd. The Mertech dll is loaded and used for the B_OPEN because it is located in the application’s folder. It finds the .INT file, reads it, and then displays a login dialog box, which allows the user to input a user id and password for the selected SQL backend. If this dialog box does not display, either the Mertech dll was not found or the user chose to save the user id and password previously.

You may choose to shut down the Pervasive services and stop the workgroup engine. The application should continue running because the data is now being provided by the chosen SQL server.

You may now test the application with the knowledge that your customers will have highly scalable and reliable database access.

Step 5 - Deploy the application

If your application will not be accessing Btrieve and SQL data simultaneously when the application is deployed, the Pervasive components, located in the pvsw directory, are no longer needed. Rather than including the entire Pervasive.SQL installation, the Mertech dll (w3btrv7 or wbtrv32) is installed in the application directory. No registry keys or COM registration is required. The Btrieve data files and DDFs are also not needed.

The SQL client (MS SQL Server client, Oracle client or PostgreSQL client dlls) must be installed on the system running the application.

The .INT files will be installed. The application will continue doing a B_OPEN on billing.mkd, for instance, even though billing.mkd does not exist. The Mertech driver dll will read Billing_mkd.int instead.

Hardware considerations

  1. Utilize RAID 1+0. 1+0 offers the best balance of performance and data integrity.

  2. Defragment the server drives. If you have never defragmented a drive, you will be amazed at the improvement in access speed.

  3. Have at least 4G of RAM. More memory means better performance.

    The usual amount of data often accessed should fit in the server’s cache with room to spare for optimal performance.

  4. Turn off the Mertech driver trace. When initially testing the

    application, you may have used the driver trace. Be sure to turn this off when you are finished. Leaving this turned on causes a small slowdown in performance.

  5. Place Data Index and Transaction Logs on different physical hard

    drives on the server to balance the drive access.

  6. Pre-allocate database or table space according to the current

    size of the Pervasive.SQL database if the backend allows it. Leave room for growth and once again, defragment the drive. Both of these steps help ensure that the tables are not fragmented across the hard disk.

  7. Set the Recovery Model to BULK LOGGED for Microsoft SQL Server.

Utilizing Function Executor

Pervasive provides a low-level tool for executing Btrieve commands called Function Executor. Since the tool runs directly off of w3btrv7.dll calling the standard BTRCALL function, it is a good troubleshooting tool when an application is receiving an unexpected error.

A simple technique to discern the difference between Btrieve and SQL behavior is to create two disk folders. In one, place wbexec32.exe (from pvswbin) and the original w3btrv7 dll. In the second folder, place another copy of the wbexec32 along with the Mertech w3btrv and sql_btr.cfg (or ora_btr.cfg, etc). Then create shortcuts to each of these and you can selectively execute Btrieve API calls against Btrieve and against SQL.

Using the History window, you can save a sequence of API calls and then play them back later or ask it to repeat them any number of times. During playback, there is a small arrow in the lower right of the dialog which will access a record of time to execute the calls. This can be used to quick performance comparisons.