The Flex2SQL Classic Migration Utility¶

Table Creation and Restructuring¶

The Create and Restructure Table options are a very powerful and dynamic subset of the Flex2SQL Classic 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.

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 Classic 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 Classic 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 Classic 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.

Inverse Key Maintenance¶

The Flex2SQL Classic Migration Utility may create additional columns, called inverse key columns, during migration to handle case sensitive indexes and descending index segments. This option is needed when an SQL backend (for example PostgreSQL) does not efficiently support case-insensitive index segments.

The Mertech driver makes sure that inverse key fields remain up to date if a table contains them. However, if non-DF tools are used to update a table, care has to be taken to keep the inverse key fields consistent with their corresponding field partners.

The Flex2SQL Classic Migration Utility contains an option to create a trigger on a table to ensure that inverse key values are always updated. Right-click the table and then select Enable Trigger to Handle Inverse Key from the popup menu (this option is also available from the Database menu). It is recommended to use triggers to maintain inverse keys if any non-DF tools will update the table. Otherwise, the trigger can be omitted for a slight performance boost. Select the Disable Trigger to Handle Inverse Key option to disable the trigger.

The Flex2SQL Classic Migration Utility also contains an option to examine and fix corrupted inverse key columns. Select the tables you want to check, and then choose Check Inverse Key Columns from the Database or popup menu. If any tables have corrupted inverse key values, the Check Inverse Key columns dialog box displays letting you know which tables are corrupted, and giving you the opportunity to fix them.

The need for inverse keys has diminished with new features in later SQL backends. It is especially uncommon to require inverse key fields in MS SQL Server.

Open 'invt.int' as invt
Move invt.File_Number to iFile
Get_Attribute DF_FILE_NUMBER_FIELDS of iFile to iFields
For iField from 1 to iFields
   Get_Attribute DF_FIELD_NAME of iFile iField to sFieldName
   Get_Attribute DF_FIELD_INVERSE_KEY_TYPE of iFile iField to iInverseKeyType
   For iInverseKeyType from INVK_CASE_SEG to INVK_DESC_AND_CASE_SEG
      If (iInverseKeyType = INVK_CASE_SEG) Move 'INVK_CASE_SEG' to sInverseKeyType
      If (iInverseKeyType = INVK_DESC_SEG) Move 'INVK_DESC_SEG' to sInverseKeyType
      If (iInverseKeyType = INVK_DESC_AND_CASE_SEG) Move 'INVK_DESC_AND_CASE_SEG' to sInverseKeyType
      Get_Attribute DF_FIELD_INVERSE_KEY_TYPE of iFile iField iInverseKeyType to iHasInvK
      If (iHasInvK) Begin
         Get_Attribute DF_FIELD_INVERSE_KEY_NAME of iFile iField iInverseKeyType to sInverseKeyName
         Showln 'Field#' iField ' - Field Name: ' sFieldName ' - Inverse Key Type: ' sInverseKeyType ' - Inverse Key Name: ' sInverseKeyName
      End
   Loop
Loop

Relationship Synchronization¶

The Flex2SQL Classic 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.

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 Classic 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 Classic 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.