Docs »
BTR2SQL v6.1 »
GUI Migration Utility

GUI Migration Utility¶

The BTR2SQL GUI Migration Utility assists developers during and after a data migration. It contains all the features that a developer needs to migrate an existing Btrieve application to the target backend. Mertech designed the user-friendly, intuitive user interface with the assumption that you do not require detailed knowledge of the target backend.

BTR2SQL works with the files listed in the FILE.DDF. You must select a file before you can perform any database-related operation on the file. You can select either a single file or multiple files to perform the operation in a single or batch mode.

NOTE: The GUI migration utility requires DDFs. The Migrating Magic uniPaaS Applications command-line migration tool works with Magic uniPaaS applications where the source code is stored in XML format.

Menu bar¶

The menu bar contains all the commands necessary to migrate a Btrieve application to an SQL backend.

    Menu                Contains commands to
    
    File                Select the FILE.DDF, connect to the target server,
                        login, or choose global migration settings.

    Table               Perform the data migration, browse Btrieve or
                        migrated tables, perform index maintenance, define
                        an owner for a data file, or create a text file for
                        high-speed
                        migration.

    Reports             Examine the migration and trace reports.

    Help                Obtain information about the BTR2SQL migration
                        utility.

File Menu¶

Select File.DDF¶

Allows you to select the Btrieve FILE.DDF that contains the Btrieve data file to be converted.

Select Database¶

MS SQL Server and PostgreSQL support multiple instances of the database engine running concurrently on the same computer. The Select a Database option allows the user to select a specific database.

NOTE: In order to create, delete, or manipulate MS SQL Server table structures through BTR2SQL, the schema id (the user) must be either be a “dbo” (db_owner) or have rights to create and delete tables in the selected database.

Attach¶

Connects database data and log files to an instance of SQL Server and brings the database online (MS SQL Server only). This means that you can distribute a database as standard files by detaching the database from the server, copying the database files to another computer, and then attaching to them using that computer’s installation.

MDF file¶

SQL Server databases use two files - a master database (MDF) file, known as the primary database file, which contains the schema and data, and a LDF file, which contains the logs. Browse to select an existing MDF file. The name must include the full path. Any associated LDFs (log files) and NDFs (secondary data files) must be in the same folder.

Database Name¶

Enter a database name (optional). If no database name is entered, the name given when the database was created is used. The MDF file name is typically the same as the database name.

Detach¶

Takes the database offline and removes it from the SQL Server instance to which it was attached. The database data and log files remain intact and you can then attach the database at a later point to another SQL Server instance (MS SQL Server only).

Login¶

Allows you to login to the target database. In order to carry out a successful migration, BTR2SQL must logon to the target database and also must have access to the Pervasive/Btrieve engine to read the Btrieve/Pervasive data files.

The login server name, user name, and password and must be properly set up. Contact your database/network administrator or consult the documentation of the target database.

A BTR2SQL Quick Start Video and Quick Start Guide are provided to facilitate getting started as a new BTR2SQL user. Both can be accessed by selecting Mertech’s ISDBC Drivers for Btrieve from the Windows Start menu, through commands on the Migration Utility Help menu and from the Migration Utility Login dialog box.

For a detailed description see Login to the target database

Preferences¶

The Preferences dialog box provides options for global migration settings, the location of migration reports, and the location of trace files, assignment of default values for NOT NULL field types, and updates to data-type mappings used during migration.

For a detailed description see Setting Migration Preferences

Exit¶

Terminates the BTR2SQL program

Table Menu¶

The Table Menu is a dynamic menu. The displayed commands depend on whether one or more files are selected in the File Selection dialog box. Commands on the Table menu allow you to perform the database conversion, create, drop and truncate tables, browse Btrieve and SQL data, verify and update index definitions, specify a Btrieve owner name, create a text file for SQL*Loader (Oracle only), and generate .INT files and SQL scripts for creating tables.

Commands available on the Table menu are the same commands that are accessible on the popup menu when you right-click the File Selection dialog box.

For a detailed description see Convert Data Files.

Reports Menu¶

The Reports menu is used to clear or display the migration report and to view the driver trace file.

Migration Report¶

Displays the Migration Report. This is a report summary that displays any errors that may have occurred during the migration(s), the license and version of the migration tool, a table creation report, an index creation report, and a data migration report.

Clear Migration Report¶

Clears the current file containing the migration report(s).

Driver Trace¶

View the driver trace file generated during migration.

Help Menu¶

In addition to this User’s Guide, a Quick Start Guide and Quick Start Video are available from the Windows Start menu under the Mertech’s ISDBC Driver for Btrieve heading. Additional documentation is also available on the Mertech web site.

BTR2SQL Help F1¶

Opens the Contents tab of the BTR2SQL User’s Guide. Press F1 at any time to navigate to the help topic specific to the currently displayed screen.

Index¶

Opens the Index tab of the BTR2SQL User’s Guide.

Quick Start Guide¶

Provides information to facilitate getting started as a new BTR2SQL user. The guide is in pdf format.

Quick Start Video¶

Provides information to facilitate getting started as a new BTR2SQL user. The video is a self-running executable that does not require any special software to be installed.

Mertech on the Web¶

You can download the latest BTR2SQL version, get product news, FAQs, online support, and the blogs. It’s well worth your time to get acquainted with this site.

About¶

Provides version and copyright information about BTR2SQL.

Main Screen Display¶

After selecting the FILE.DDF, the display lists of all of the data files in the FILE.DDF. This is the main area for completing all tasks related to data migration. You can perform actions on one or more data files.

This dialog box allows you to identify and select files you want to migrate. The icon next to the file name indicates whether or not the file has been migrated. An icon without an arrow indicates that the file is still in the native Btrieve format while an icon with a green arrow shows that a table has been migrated to the target RDBMS. The status line at the bottom of the dialog box displays the number of files currently selected and the target server and database.

Figure 7 File Selection Dialog Box

All¶

Selects all entries in the list

None¶

De-selects all selected files in the list

Invert¶

Toggles the entries. All marked entries become unmarked and all unmarked entries become marked.

Valid Btrieve Files¶

Selects all of the data files in the FILE.DDF that have a valid and accessible Btrieve data file.

Valid SQL Tables¶

Selects all of the data files that have been migrated and checks for errors in accessing tables on the SQL backend.

Setting Migration Preferences¶

Select File | Preferences to choose global migration settings, the location of migration reports, and the location of trace files. Values entered here are stored in the registry and remain after the tool exits.

Figure 8 Preferences Dialog Box

Overwrite Existing Tables Without Warning¶

If this option is selected, a table with the same name is overwritten during migration. By default, the option is turned off, and you are prompted before a table is overwritten.

Erase Table Before Migration¶

If this option is selected, the contents of the target table are erased before the contents of the Btrieve data file are copied. By default, this option is turned off. If this option is NOT selected, rows from the Btrieve file are appended to the existing data.

Require Btrieve Data Files¶

If this option is checked, BTR2SQL skips a table if the data file is not found.

Prompt For Btrieve Owner If Needed¶

During migration if the program detects a file that requires owner information, it prompts the user for it. If this option is unchecked, BTR2SQL skips the operation for that file.

Migration Report File Name¶

This is the name of the migration report file — a summary of the migration process. You can access this report by selecting Reports | Migration Report.

Press the button to browse for the location where you want to save the migration report file.

Append to Existing Migration Report¶

The latest report is appended to an existing one. If this option is not checked, the existing migration report is deleted.

Migration Trace File¶

When you select this option, BTR2SQL generates a trace log file for all of the calls at the driver (low) level that occur during migration. You can view this file by selecting Reports | Driver Trace.

NOTE: Leaving this trace file ON all of the time could slow down the performance of the migration process because each driver operation is logged into the trace file.

File Name¶

Press the button to browse for the location where you want to save the migration trace file.

Level¶

Use the drop-down arrow to choose the trace level. There are multiple “levels” of tracing, from -1 to 3, depending on the amount of detail needed.

See Runtime Driver Trace for a description of each level

Runtime (debug) Trace File¶

Sets the TRACE_ON registry setting in HKCU\Software\Mertech Data Systems\DB Drivers\Btrieve\BTR2SQL\<backend>\v3. If TRACE_ON is enabled, then all calls by the driver to and from the application will be logged in the file name specified. This option must be set with caution in order to not adversely affect performance.

NOTE: Depending on the TRACE_LEVEL setting, leaving the trace file ON all of the time may slow down your application and generate a very large output file.

Set Field Defaults¶

Allows you to set default values for ASCII, NUMERIC, DATE and TIME field types. Default values are used if a value is not provided when data is inserted into a table. NOT NULL attributes can be set for a given field type that will persist for all tables during migration. When a field is created as NOT NULL, Oracle and MS SQL Server databases expect a default value. You can specify the default values or let the drivers handle it. If not specified, the driver assigns a date value of 01/01/1753 for MS SQL Server and 01/01/0001 for Oracle. Numeric fields have a default of 0 and ASCII fields a single space. Time fields have a default of 00:00:00.

Figure 9 Field Default Values

NOTE: 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).

When is a field NOT NULL?

Fields that are part of an index are always created as NOT NULL. BTR2SQL will not allow you to change this because this directly affects FIND performance when using indexes based on those fields. If a field is in the NULL state and a SELECT statement is generated for that field, both Oracle and MS SQL Server perform a full table scan of the data file, which slows down performance. You can click on the check boxes under the NOT NULL column to set a field that is not part of any index to NOT NULL.

NOTE: If fields are selected as NOT NULL, SQL databases require that those fields always have a value during an INSERT statement. If the program field receives no value, the driver automatically inserts a default value. You can control these defaults during migration by specifying the defaults for a particular field. For indexed fields, you can change preset defaults provided by BTR2SQL.

NOTE: Field default values can be specified at the global level by selecting * **File| Preferences* from the menu bar. Field default values can be set for an individual migration from the Convert Data Files dialog box.

Set Data Type Mapping¶

Allows you to modify the field-type mappings used during migration. The drop-down list of field-types ensures that only suitable mappings can be selected. Most of the time it is not necessary to change the assignment made by BTR2SQL, but there may be instances when this is useful. For example, you can map a string field to a date/time field to provide meaningful interpretation of date/time information.

Figure 10 Data Type Mapping

NOTE: Data type mapping values can be specified at the global level by selecting * **File| Preferences* from the menu bar. Data type mapping values can also be set for an individual file by selecting the Fields tab in the Convert Data Files dialog box.

Convert Data Files¶

<Right-click> on any file/files in the File Selection dialog box and choose one of the options available from the popup menu. Procedures for these menu options are described on the following pages. The displayed menu depends on whether one or multiple files are selected.

One File Selected	Multiple Files Selected

Figure 11 File Options - Popup Menu

The first option, Convert to database, creates the table, copies the data and then adds indexes, all in one operation. It can operate on one file at a time or multiple files. The Convert Data Files dialog box is different depending on whether one file or multiple files are selected.

One File Selected	Multiple Files Selected

Figure 12 Convert (Data) Files Dialog Box

NOTE: When multiple files are selected for migration, all selection options are available on the main tab. The fields and indexes are migrated as defined in the DDFs. When you select OK to convert multiple files, the program migrates each file separately, instead of as a batch process.

See also Options Tab, Fields Tab, Indexes Tab, Server-Specific Tab, Copy Data to Table

Options Tab¶

Figure 13 Convert File – Options Tab

Get Server Name from Login¶

If you uncheck this option, the server name is stored in the INT file during migration. If you leave this option checked, the server name is NOT stored in the INT file during migration. At runtime, the driver looks for the server name in 1) the INT file, 2) the mds.ini file. If the server name is not stored in either file, the driver displays the Login dialog box.

Get Schema Name from Login¶

If you uncheck this option, the schema name is stored in the INT file during migration. If you leave this option checked, the schema name is NOT stored in the INT file during migration. At runtime, the driver looks for the schema name in 1) the INT file, 2) the mds.ini file. If the schema name is not stored in either file, the driver displays the Login dialog box. Not used in Oracle.

Get Database Name from Login¶

If you uncheck this option, the database name is stored in the INT file during migration. If you leave this option checked, the database name is NOT stored in the INT file during migration. At runtime, the driver looks for the database name in 1) the INT file, 2) the mds.ini file. If the database name is not stored in either file, the driver displays the Login dialog box.

See Specifying the target backend

OEM to ANSI Translation¶

When checked, converts the OEM character set to the ANSI character set.

OK¶

Selecting OK starts the conversion process.

See also Fields Tab, Indexes Tab, Server-Specific Tab

Fields Tab¶

The Fields tab allows you to change the data type mapping assigned to a column when the table is created on the SQL server. The Fields tab also lets you enter a default value for fields defined as NOT NULL. Fields created as NOT NULL need a default value that you provide if a value is not provided by the program or if the field is left blank.

Figure 14 Convert File – Fields Tab

Name¶

Displays the alphanumeric identifier for the field (the column name).

Type¶

Displays the Btrieve data type assigned to the field.

SQL Type¶

Displays the default field-type mapping that will be used when creating the SQL table during migration. You can modify this setting.

See Set Data Type Mapping to set the mapping as a global preference

Binary¶

Allows you to tag string fields as binary during migration. Check this option if a string field contains non-ASCII values (<32 or >128).

Offset¶

Displays the field offset in the record buffer.

Size¶

Displays the field length in bytes.

Dec¶

Used for numeric fields, displays the number of decimal spaces the field can contain.

Null¶

Indicates whether NULL values are allowed in the field. You can change this setting, however, fields participating in an index cannot be NULL.

Default¶

Allows you to enter a default value to be used for a NOT NULL field when the field is left blank.

See also Fields Tab, Indexes Tab, Server-Specific Tab

Indexes Tab¶

The indexes tab shows details of all keys and the fields that comprise the keys.

Figure 15 Indexes Tab

Field Name¶

Displays all the fields that make up the currently selected key.

Desc¶

Check to indicate this field is descending.

Case¶

Check to indicate this field is case insensitive.

NOTE: The PostgreSQL citext module provides the case-insensitive character string type CITEXT that allows for case insensitive fields without the need for inverse key columns.

Properties and Storage (Oracle Only)¶

Primary Key¶

Check to indicate this index is a primary key. All SQL databases perform exceptionally well with a correctly selected Primary Key. By default, the driver sets Index 1 in your Btrieve file as the primary key on the server. You can choose any index, as long as it is unique.

Unique¶

Check to indicate this index is unique.

NOTE: All Mertech drivers support auto-increment fields on the server-side. However, because of the limitation on MS SQL Server, it allows only one auto increment field per table. If you have an auto increment field and you expect that table to be accessed from other non-Btrieve programs, be sure to *`enable the auto-increment trigger <#enable-trigger-to-handle-auto-increment-and-inverse-key>`__ *for the table.

Initial¶

Displays the size of the first extent for the index tablespace on the Oracle server.

Next¶

Displays the size of the next extent to be allocated when the initial space is full.

% Free¶

Displays the space reserved in each of the cluster’s data blocks for future expansion. The value of the parameter is expressed as a whole number and interpreted as a percentage.

See also Fields Tab, Indexes Tab, Server-Specific Tab

Server-Specific Tab¶

The server-specific (MSSQL and Oracle in the example) tab contains settings that determine how the data is loaded.

Oracle users run the Analyzing Oracle Storage Requirements option and then use the drop-down list in the Oracle tab to assign different locations for the data and indexes for the currently selected tables.


Figure 16 MSSQL Tab	Figure 17 Oracle Tab

See also Fields Tab, Indexes Tab,

Table Operations¶

The following table operations are available from the Table menu and from the popup menu displayed when you right-click file(s) in the File Selection dialog box.

Copy Data to Table¶

This option copies records from a Btrieve data file to a table that has already been created using Mertech’s BTR2SQL tool. Use this only if the table exists on the server. The following copy options can be selected:

Lock Table: Check to lock the table and prevent updates to the table while the data is being copied to the SQL server.

Erase Table: Check to erase the contents of the target table before the contents of the Btrieve data file are copied. If this option is NOT checked, rows from the Btrieve file are appended to any existing data.

Bulk Copy Operations: Migrating records to the database is much more efficient using a bulk copy operation. MSSQL lets you determine whether to commit updates based on the number of rows per batch or kilobytes per batch.

Create Table¶

Creates an empty table and associated indexes without migrating the data.

Select this option when you want to use SQL*Loader for high-speed data migration or for creating blank tables that are later populated by the application.

Drop Table¶

Deletes a table in the target database for each data file that is selected in the FILE.DDF.

Truncate Table¶

Erases the contents of the table in the backend database that has the same name as the data file currently selected in the FILE.DDF.

Browse Operations¶

Browse Btrieve Data¶

Displays the data for the highlighted file. The data is read from the native Pervasive / Btrieve file. You can also. You are prompted for the Pervasive database name. Enter the logical name for the Pervasive database that contains the selected file (for example, Demodata).

NOTE: The OLEDB provider for Pervasive.SQL must be installed to access the Btrieve files. The OLEDB provider is needed only for the data browsing function.

Browse SQL Table¶

Displays the data for the highlighted table. Retrieves data from the migrated SQL table.

Figure 18 Browse Data Dialog Box

NOTE: You can also double-click a filename to browse the data. If the file has not yet been migrated, the Btrieve file is opened. If the file was already migrated, the SQL table is opened.

Index Maintenance¶

This option allows you to check the index definition of each file, whether or not the indexes have been created properly, and to re-create or drop indexes on a file.

The most common reason why a Btrieve program does not perform optimally after migration to an SQL database backend is because the indexes were not properly created or not created at all. The driver fails to create indexes for a number of reasons, but the two most important are duplicate data and running out of disk space. For example, a Btrieve table might contain dates like 12/12/99 and 12/12/1999 in two separate records. Since BTR2SQL converts all dates to four-digits during migration, such data causes a unique constraint violation if this date field is part of an index.

For this option to work properly, make sure that your Btrieve files are present for comparing the database structures.

Figure 19 Index Maintenance

Select all the files in the File Selection dialog box.

<Right-click> on a file and select Index Maintenance.

BTR2SQL searches each Btrieve file and compares it to the index created on the server. If the index is not created, BTR2SQL displays it with a red check mark next to it.

If the index was not created, you can drop and re-create the table or drop and recreate the index using the Index Maintenance dialog box.

Specify Btrieve Ownership¶

In Btrieve, you can define an owner for a data file for security purposes. BTR2SQL cannot access these files unless the owner (password) is specified while opening these files. For migration purposes, BTR2SQL requires that you specify the owner name for these files to successfully migrate the data to the SQL Server.

The File Selection dialog box, shown in Figure 7, displays a lock icon for the files requiring the owner specification.

1. In the File Selection dialog box, select a file with next to it.

<Right-click> and select Btrieve | Specify Owner Names…

The Specify Btrieve Ownership dialog box appears.

Figure 20 Specify Btrieve Ownership

Type the owner’s information in the Owner column.

Press OK.

Number

Number assigned to the file in FILE.DDF. This column is non-editable.

File Name

Name given to the Btrieve file. This column is non-editable.

Owner

Files owner information. The specified owner information is verified. If it does not match, you are prompted again to enter the information for that file.

Analyzing Oracle Storage Requirements¶

IMPORTANT: This feature should be used before ANY data migration is done. Running out of disk space during data migration creates a situation where Btr2SQL does not properly create or migrate data.

This option dynamically calculates the server storage requirement for each individual table based on the Btrieve file sizes. The sizes indicated are in bytes. If the tablespace is not large enough, the tablespace is highlighted in red. In these cases, you can use Oracle’s Storage Manager utility to increase tablespace sizes.

NOTE: You must have rights to alter the tablespaces, even the ones to which you are migrating. You can change user rights using Oracle’s Enterprise Manager.

1. In the File Selection dialog box, select a Btrieve file(s) to be migrated to the SQL backend.

2. <Right-click> and select Oracle Specific Options | Analyze Storage Requirements.

Figure 21 Analyze Storage Requirements (Oracle Only)

Tablespaces¶

Tablespace

All tablespaces the current user can access.

Available (bytes)

Total remaining space for the tablespace. You can compare this to the Required Size of the tablespace to see if there is enough space.

Required Size (bytes)

The space required by the Btrieve files selected. This turns red if the required size is larger than the available size.

Totals

Remaining server-side space in bytes and the space required by the currently selected files.

Btrieve Data Files¶

Name

The name assigned to the selected Btrieve file.

Data Tablespace

The tablespace currently assigned for the migrated Btrieve data.

Recommendation: It is recommended that you create index and data tables on two separate tablespaces, preferably on separate hard drives. You can choose different tablespaces for index and data tables, and assign individual tables to different tablespaces using the Oracle tab in the Convert File dialog box.

Size (bytes)

The space required on the server-side for the Btrieve table data.

Index Tablespace

The tablespace currently assigned for the migrated Btrieve indexes.