The Flex2SQL Unicode Migration Utility

The Flex2SQL Unicode Migration Utility contains all the features that a developer needs to migrate an existing DataFlex application to a SQL backend or to integrate non-DataFlex tables into his/her application. It lets you set global preferences for script generation, file casing, field mappings, date handling, and more. Custom preferences can also be selected for a specific migration. A built-in browser lets you examine both DataFlex and converted tables. It can also create new tables and indexes or restructure existing tables and indexes.

Select the Flex2SQL Unicode Migration Utility shortcut from the Windows Start menu.

Flex2SQL Unicode Migration Utility.png

Database Migration

Commands on the Flex2SQL Unicode Migration Utility Database menu let you perform the database conversion, create a text file for high-speed text migration, browse DataFlex or migrated tables, copy data between the DataFlex and target database, and perform other database modifications.

Table Creation and Restructuring

The Create and Restructure Table options are a very powerful and dynamic subset of the Flex2SQL Unicode Migration Utility. They allow you to create or restructure tables and/or indexes without having to interact with any backend specific utilities. All enhancements can be saved in the accompanying .INT and .FD files, maintaining the integrity between the frontend and backend.

The Create or Restructure Table dialog box contains a comprehensive range of edits to fields and field attributes, indexes, and table information.

There are two additional features in this tool that reduce developers’ time and effort. One is the ability to generate SQL scripts and restructuring code. This allows for changes to take place in a development environment and have them replicated in a production environment. The other is the ability to incrementally save restructure steps without losing any of the previous work.

Create Table.png

Index Maintenance

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

The Index Maintenance option allows the user to check whether the indexes for each file were created properly. To use the tool, select all the files in the Filelist dialog box, then right-click and select Index Maintenance. The Flex2SQL Unicode Migration Utility examines each DataFlex file and compares it with the indexes created on the target server. If an index was not created, the Flex2SQL Unicode Migration Utility displays a red check mark next to it.

Index Name Mapping

Starting with version 17.1.9897.0, the migration utility now has an option to migrate tables with index name mapping. Having indexes mapped based on names is much simpler (and also faster). The downside to mapping dataflex index numbers to backend index names is that if the structure of an index changes on the backend, rather than this being reported as it was before, it will be silently mapped and not used. This isn’t something that happens often though, so it shouldn’t be a problem.

Index Name Mapping

Relationship Synchronization

The Flex2SQL Unicode Migration Utility Synchronize Relationship option allows you to transfer DataFlex relationships to the server back-end or to retrieve the relationship information from the server into the .INT files.

After a database conversion, all the relationships are stored in the .INT file, but they are not propagated to the SQL database. Select the Synchronize the SQL Foreign Key with the INT Relationship option to transfer the relationships to the SQL back-end. The right pane then shows the relationships that are created.

Synchronize Relationships.png

With the Synchronize Relationship option you can also use external tools to create database relationships and then update the .INT file using the Synchronize the INT file relationship with the SQL table foreign key option.

Relationships need to be maintained in the INT file to ensure that DataFlex operates as expected. It is up to you to determine if you need relationships on the SQL side. The Mertech driver works the same with or without the relationships

Scripting Options

The Flex2SQL Unicode Migration Utility lets the developer generate scripts that perform data migration and database maintenance and lets the DBA review the scripts before running them on the live server. The Flex2SQL Unicode Migration Utility Tools menu contains commands to generate SQL scripts to:

  • Create SQL tables, including any constraints and indexes.

  • Restructure SQL tables.

  • Analyze the SQL database after a text file load and adjusts the RECNUM values to run properly with DataFlex.

  • Initialize auto increment columns (in PostgreSQL) or sequences (in Oracle) to be the highest number in the table plus one.

  • Enable or disable a trigger so that inverse key values are properly updated.

  • Drop objects such as sequence definitions on Oracle or an index or table in any SQL database engine.