Migration Utility

The Flex2SQL Utility Classic contains all the features that a developer needs to migrate an existing DataFlex application to an SQL back-end or to integrate non-DataFlex tables into his/her application. Mertech designed the easy-to-use, intuitive user interface with the assumption that the user does not require detailed knowledge of the target back-end.

The Flex2SQL Classic product bundle is comprised of 1) a GUI migration tool that takes your existing DataFlex tables and indexes and migrates them to your target back-end, creating the table structures, index structures, and RECNUM values for all the files in the filelist and 2) the database driver DLL or shared-object that works with the DataFlex runtime and handles all client/server connection and database operations. Flex2SQL supports migration to Oracle, MS SQL Server, MySQL, MariaDB, and PostgreSQL.

Flex2SQL Utility Classic 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. Flex2SQL can also create new tables and indexes or restructure existing tables and indexes.

The enhancements made to date make Flex2SQL Utility Classic a well-rounded tool that supports procedures required before, during, and after a migration.

SplashScreen.png

Copyright: © 2021 Mertech Data Systems, Inc. All rights reserved. This document is for informational purposes only. Mertech makes no warranties, expressed or implied, in this document.

DataFlex is a registered trademark of Data Access Corporation
Microsoft, SQL Server, and Windows are registered trademarks of Microsoft Corporation
Linux is a registered trademark of Linus Torvalds
PostgreSQL is a registered trademark of PostgreSQL Inc.
UNIX is a registered trademark of The Open Group

Trademarks used in this document are purely for publication purposes and are the property of their respective owners.

A Sample Migration Using the Flex2SQLUtility

This section describes the steps for migrating DataFlex files to an SQL backend.

Note: For Oracle, license control scripts must be run before performing a migration (see License Count View for ORAFlex). Once these scripts are run, always login as a user with default privileges (select AutoMode DEFAULT) when performing a migration.

Flex2SQL offers full support for migrating to all MS SQL Server instances, including LocalDB, SQL Express, and Windows Azure SQL Database. Except for changing the connection string, no code changes are required on the Flex2SQL side.

If you have a lot of data to migrate to Windows Azure SQL Database, migration tends to be slow inserting records one-by-one. Instead you might consider migrating to an on-premises SQL Server instance and then using the SQL Server Management Studio option to deploy the database to SQL Azure.

Note: Contact Mertech sales or technical support regarding licensing for Windows Azure SQL Database.

Login to the SQL Server

  1. Select the Flex2SQL Utility Classic shortcut from the Windows Start menu.

Note: It is recommended that you use the same version of the Flex2SQL GUI migration tool and Flex2SQL database drivers. If a mismatch is detected (for example, v12.0 migration tool and v12.1 drivers, a warning message displays.

The Login dialog box displays.

  1. In the Server Type field, select the desired SQL server from the drop-down list.

  2. In the Server Name field, type the name of the server you are logging into.

Flex2SQL saves the last five servers you connect to, so you can easily switch between them using the drop-down list. This can facilitate switching between a test and live server.

  1. In the User Name field, type the user name for this session.

  2. In the Password field, type the user password.

  3. To save the password for subsequent logins, select the Save Password checkbox.

There is an additional checkbox, Trusted Connection, for MS SQL. Select Trusted Connection and omit the User Name and Password to rely on Windows Authentication.

  1. Click Connect to log into the server.

Select the Target Database

  1. If you logged into MS SQL Server, MySQL, MariaDB or PostgreSQL select a database from the Select Database dialog box.

Note: Hover the mouse over the database name to display a tooltip containing the location of the database file.

  1. If you chose localdb, default (localdb)v11.0, as your Server Name in the Login dialog box, an additional option, Attach (New Database), is available in the Select Database dialog box.

    1. Click Attach, then browse to select an existing master database file (MDF). This is the name of the primary database file. The name must include the full path. Any associated LDFs (log files) and NDFs (secondary data files) must be in the same folder.

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

    3. Click Attach.

  2. Click OK.

Choose the Files to be Migrated

Choose the DataFlex file or workspace to be migrated (File > Open )

  • Select Workspace: Choose a VDF workspace (.sws) for the selected VDF Version

  • Select Filelist: Browse to select a DataFlex filelist (filelist.cfg)

  • Files Without Filelist Entry: Browse to select individual data files (.dat) for conversion

  • Open Last Filelist: Open the most recent filelist used by Flex2SQL

For a complete description of these options see the Open command.

Set Migration Preferences

  1. Display the Preferences dialog box. (Tools > Preferences).

The Preferences dialog box allows you to set options that affect your migration including the terminating character(s) for SQL scripts, file casing preferences, filelist position for the new tables, how errors are handled, whether SQL specific information is included in one or more .INT files, the DFPath Flex2SQL uses during data migration, and more.

For a complete description of the options that are available see Preferences.

  1. Select your migration preferences, then click OK.

Convert the Selected Files

  1. Click Select DF Files on the Filelist dialog box.

Filelist.png

Flex2SQL searches for the DF files in the DFPATH. The located files are automatically highlighted. If a file is not found, examine your path settings in the Tools > Preferences > DF Path tab.

It is strongly recommended that you do not include the FlexErrs, CodeType, and CodeMast DataFlex tables in the migration. These files are only used by DataFlex and are never required in the SQL database. If there is an issue with backups or disaster recovery, please refer to the Failover section in the Flex2SQL Programmer’s Guide.

  1. Click Convert Database.

The Convert DAT to Table dialog box displays.

  1. Modify the migration options if necessary (for a full description of these options see Convert DAT to Table dialog box).

  2. Choose Create Tables & Indexes and Migrate Data, then click Convert or, if multiple files are selected, Convert All to begin the migration process.

The Flex2SQL migration converts the selected files to the target database.

Examine the Filelist dialog box after the conversion is complete. Notice that the selected data files names have a driver prefix (for example, Sql_drv ). This shows a successful transfer of data files to the server and creation of an intermediate file for each file. Intermediate files are stored in the workspace working directory, or if files where opened with the Filelist option, where the .DAT file resides.

Examine the Migration Report

Review the Migration Report (Reports > Migration Report) when the conversion is complete. The Migration Report 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. At the top of the report is a summary that lets you determine whether any errors occurred during the migration, so that you do not have to search through the whole report to check for errors.

Note: You can modify the converted table(s) using options available on the Maintenance menu.

High-Speed Script Based Migration

Several databases include a utility that allows the use of text files to populate tables. Flex2SQL has an easy to use feature called Create Text File for Loader that generates the text files and all the necessary script and control files needed for migration using this utility.

Note: The Create Text File for Loader option is available for Oracle, PostgreSQL, MySQL and MariaDB.

There are two ways to use the text-file loader option.

  • Let Flex2SQL perform all the tasks except for loading the data

Flex2SQL

DBA

Flex2SQL

1 Create empty SQL table(s) and INT file (s)

2 Generate txt, bat and ctl files

Run server-specific loader

Run Analyze After Loader

  • Let Flex2SQL generate scripts so a DBA can perform the tasks

Flex2SQL

DBA

Flex2SQL

1 Generate SQL script for creating table(s)

2 Generate INT file(s)

3 Generate txt, bat and ctl files

1 Run SQL script to create table(s)

2 Run server-specific loader

Generate script to analyze loaded data

Run post loader script to analyze loaded data

Important: After each loader execution, you must run the Analyze After Loader option (or the post loader script) for each migrated file. THIS IS VERY IMPORTANT. This option analyzes the system and adjusts the RECNUM values to run properly with DataFlex. If this option is not executed FIND commands behave erratically.

High-Speed Script Based Migration - Let Flex2SQL Perform the Tasks

  1. Connect to the target server (File > Login).

  2. Optionally, set global migration preferences (Tools > Preferences).

  3. Open the DataFlex workspace or filelist (File > Open) and select the file(s) to be migrated.

  4. Select Convert Database, choose Create Table Only and then click Convert or, if multiple files are selected, Convert All.

This creates an empty SQL table(s) and the .INT file(s), <filename>.int.

  1. Select Database > Create Text File for server-specific Loader.

Note: The displayed dialog box is server-specific. Examples that follow are for an Oracle database.

  1. To save the data in a text file, select the Export Data & Control File option. Otherwise, only the controlling scripts and batch (.bat) file are generated. You can change the location of the text file and select whether you want a comma delimited file or a fixed format file.

  2. Display the Loader Options tab and choose settings to be saved in the control file.

  3. Click Generate or, if multiple files are selected, Generate All.

During the generation process, three files are created: <filename>.txt, loader.bat, <filename>.ctl. The <filename>.txt file contains the data in text format. Loader.bat is a batch file that contains the loader calls for each migrated file. The <filename>.ctl file contains control information used by the loader.

  1. The DBA runs the loader.bat file to load the text data.

  2. Run the Analyze After Loader option for each migrated file (Database > Analyze after server-specific Loader).

  • See Also: High-Speed Script Based Migration - Let Flex2SQL Generate Scripts for the DBA

High-Speed Script Based Migration - Let Flex2SQL Generate Scripts for the DBA

  1. Connect to the target server (File > Login).

  2. Optionally, set global migration preferences (Tools > Preferences).

  3. Open the DataFlex workspace or filelist (File > Open) and select the file(s) to be migrated.

  4. Select Tools > Generate Scripts > SQL Script for Creating Table.

This creates a script, <filename>.sql that the DBA can use to create the table(s).

  1. Select Tools > Generate .INT > .INT File from .DAT

This creates the .INT file(s), <filename>.int.

  1. Select Database > Create Text File for server-specific Loader.

  2. To save the data in a text file, select the Export Data & Control File option. Otherwise, only the controlling scripts and batch (.bat) file are generated. You can change the location of the text file and select whether you want a comma delimited file or a fixed format file.

  3. Display the Loader Options tab and choose settings to be saved in the control file.

  4. Click Generate or, if multiple files are selected, Generate All.

During the generation process, three files are created: <filename>.txt, loader.bat, <filename>.ctl. The <filename>.txt file contains the data in text format. Loader.bat is a batch file that contains the loader calls for each migrated file. The <filename>.ctl file contains control information used by the loader.

  1. The DBA runs the script to create the new table (filename.sql) and the loader.bat file to load the text data.

  2. Generate the script to analyze the loaded data (Tools > Post Loader Steps).

Note: Only available for PostgreSQL, MySQL, MariaDB and Oracle.

  1. The DBA runs the post loader script.

  • See Also: High-Speed Script Based Migration - Let Flex2SQL Perform the Tasks

License Count View for ORAFlex

Server-side control scripts must be run before you connect to Flex2SQL for Oracle (ORAFlex). These must be run as a SYSDBA.

  1. Select the Flex2SQL Utility shortcut from the Windows Start menu.

Note: It is recommended that you use the same version of the Flex2SQL GUI migration tool and Flex2SQL database drivers. If a mismatch is detected (for example, v12.0 migration tool and v12.1 drivers, a warning message displays.

The Login dialog box displays.

  1. In the Server Type field, select Oracle from the drop-down list.

  2. Select SYSDBA from the AutoMode drop-down list.

  3. In the Server Name field, type the name of the server you are logging into.

  4. In the User Name field, type the SYSDBA user name (for example SYS or SYSTEM).

  5. In the Password field, type the user password.

  6. Click Connect to log into the server. Server-side license scripts run automatically.

  7. Logout then login as a DEFAULT user before performing any migration work.

Note: If you do not have DBA privileges, ask your DBA to run one of the following scripts to create the required Mertech view.

For a non-RAC Oracle database:

create or replace view mds_session_info as select username, machine, terminal, module from v$session where module like ‘ORAFlex%’;

grant select on mds_session_info to public;

drop public synonym mds_session_info;

create public synonym mds_session_info for sys.mds_session_info;

commit

For an Oracle Real Application Cluster (RAC)

create or replace view mds_session_info as select username, machine, terminal, module from gv$session where module like ‘ORAFlex%’;

grant select on mds_session_info to public;

drop public synonym mds_session_info;

create public synonym mds_session_info for sys.mds_session_info;

commit

Questions have been asked about the requirement to create the MDS_SESSION_INFO view within the SYS schema. The purpose of MDS_SESSION_ INFO is to limit v$session information that is available to the application user to only ORAFlex connections (“where module like ORAFlex%”).

Alternately, you can create the view in the user’s schema, but you must then add “grant select on v_$session” to the script. Since v_$session is the underlying table of v$session view, this method opens up v$session to the user.

We recommend creating the view as the SYS user, since this provides the highest-level of security.

ciText for PostgreSQL

The PostgreSQL ciText module provides ciText, a locale-aware, case-insensitive Text type. Essentially, LOWER() is called when comparing ciText values. Otherwise, ciText data types behave exactly like Text.

The Mertech driver for PostgreSQL supports data type ciText to allow for case insensitive fields without the need for inverse key columns. Using the ciText SQL functions provides much better performance with case- insensitive data types than using inverse keys.

When you login in to a PostgreSQL server, the driver checks to see if the PostgreSQL server has ciText support enabled. If ciText support is not enabled a message displays to explain how-to enable this feature on your PostgreSQL server.

Flex2SQL023.png

Note: Install the ciText module by running pgsql/contrib/citext/citext.sql. For later versions of PostgreSQL you may have to run the CREATE EXTENSION citext command. Refer to PostgreSQL documentation for additional information.

Toolbar

The toolbar contains icons that can be used instead of a menu command for commonly used functions. Hover the mouse over an icon to determine the icon’s function.

Toolbar.png

For example, instead of choosing Database > Select a Database from the menu bar, you can click the Select a Database icon.

Toolbar icons are grouped by the function they perform. Right-click the toolbar to select which toolbar icons are visible.

File Menu

The File menu lets you select the DataFlex filelist or workspace, connect to the target server, or run a DataFlex program.

Login

Relational databases require users to connect to the server before manipulating data. This ensures that only authenticated users can access sensitive data.

The Login command allows a user to provide the connection parameters to log in to the target database. The parameters include Server Type, Server Name, User Name, and Password.

Note: Flex2SQL saves the last five servers you connect to, so you can easily switch between them using the drop-down list. This can facilitate switching between a test and live server. The account used for the migration must have sufficient rights to create tables in the selected database.

The User Name and Password can be omitted if you are using a Trusted Connection.

Login Localdb.png

If you logged into MS SQL Server, MySQL, MariaDB, or PostgreSQL, the Select Database dialog box is displayed after a successful login.

Logout

Disconnects the user from the database server. When a user disconnects from the database, all resources and connection and statement handles allocated to the user are released. The user must log in to the database again to access the tables.

If you are using Microsoft LocalDb, the Logout dialog box also includes a Detach button allowing you to disassociate the current database from the mdf datafile. Hover the mouse over the Detach button to see the name of the database to be detached. If you have multiple databases attached and detach one by clicking the Detach button, the tooltip will update to display the database that will be detached if you click the Detach button again. All open files are closed when the Detach button is clicked.

Sample Logout dialog box with the localdb Detach tooltip displayed:

Localdb Logout.png

Open

Displays a submenu to open a workspace, filelist, or file.

Select Workspace

Displays the Select Workspace dialog box, which lists the available Visual DataFlex (VDF) workspaces for the selected VDF Version.

The Flex2SQL workspace manager allows you to work with any version of Visual DataFlex starting with version 4. In environments that have more than one VDF version installed, the workspaces for each version are available.

VDF Version Pulldown Menu

Allows you to select an available Visual DataFlex version.

Display Pulldown Menu

Allows you to choose how you want to view the workspace information. The options are description, key, or both.

Note: Hover the mouse over the key to display a tooltip containing the location of the sws file.

Info Button

Displays the workspace name, description, and paths for all files in the workspace.

Browse Button

Displays a Windows open dialog box so you can browse to register a workspace file (.sws or .sw file).

Select Button

Selects the highlighted workspace and displays the Filelist dialog box.

Close Button

Closes workspace manager.

Select a Filelist

VDF Version Pulldown Menu

Allows you to select an available Visual DataFlex version.

Console Mode

Check this option for character mode DataFlex versions such as DataFlex 3.2 that support Windows Console Mode and MS-DOS environments or for Linux (used through a mounted file system).

FileList

Click the Browse button to displays a Windows Open Filelist Files dialog box so you can browse to select a DataFlex filelist (filelist.cfg).

Files without Filelist Entry

Displays a Windows Open DAT/INT Files dialog box so you can browse to select individual data files (.dat) for conversion.

This option is useful when migration consists of one data file.

Open Last Filelist

Opens the most recent filelist used by Flex2SQL.

  • See Also: Filelist dialog

Add Filelist Item

Displays the Add Filelist Entry dialog box so you can add a file to the filelist. Enter an available file number (the first available file number is displayed when the dialog box opens), root name (physical table name), user name (a user-friendly description that will appear in the filelist), and DataFlex Name (logical name as it appears in a DataFlex program).

Use the Find Previous and Find Next command buttons, or the File Number spin button, to page through the existing filelist entries.

Edit Filelist Item

Displays the Edit Filelist Entry dialog box so you can modify a file in the filelist. The dialog box displays the assigned file number, root name (physical table name), user name (a user-friendly description that will appear in the filelist), and DataFlex Name (logical name as it appears in a DataFlex program).

Use the Find Previous and Find Next command buttons, or the File Number spin button, to page through the existing filelist entries.

Remove Filelist Item

Deletes the selected file from the filelist. The file/table itself is not deleted.

Load Selections

Displays a Windows Load a file selection set dialog box so you can browse to select a previously saved fileset (.fileset).

  • See Also: Save Set

Recent Filesets

Lists the recently used filesets and allows you to load one.

  • See Also: Save Set

Clear List

Clears all history from the recently used fileset list.

Run a DataFlex Program

Displays a Windows Run DataFlex Program dialog box so you can browse to select and run a DataFlex program.

Note: You can only use this option if the program to be run is compiled with the same version of Visual DataFlex as Flex2SQL. Even though DataFlex data files are compatible across different versions of DataFlex, compiled VDF programs are not.

Select Help > About from the Flex2SQL menu bar to view the VDF Runtime Version. Version 17.0.22 in the example below indicates VDF version 17 programs can be run.

About.png

Exit

Exits Flex2SQL.

Database Menu

The Database menu lets you select conversion settings, perform the data migration, create a text file for high-speed migration, or browse DataFlex or migrated tables. Commands on the Database menu require that a file(s) is selected in the filelist. Database menu commands are also available from the Filelist popup menu.

Convert Database

Database conversion is the core Flex2SQL operation. Click Convert Database to display the Convert DAT to Table dialog box and initiate the conversion.

ConvertDAT.png

Database conversion requires a connection to the target server, a source filelist or workspace, and if the target server is MS SQL Server, MySQL, MariaDB, or PostgreSQL a target database. For Oracle, license control scripts must be run before performing a migration (see License Count View for ORAFlex).

Browse Data

Browse Data.png

Displays the data from the entry (s) selected in the Filelist dialog box. Data is displayed from the migrated table if:

  • The entry contains a driver prefix (for example, Sql_drv ) and that prefix matches the server you are currently logged in to (for example, MS SQL).

  • The entry contains a driver prefix (for example, Sql_drv) and that prefix does not match the server you are currently logged in to (for example, PostgreSQL) but you checked the Migrate Non-DataFlex Files in the Filelist dialog box and specified the folder where the MS SQL intermediate file files can be found. In this case, the data from the MS SQL table is displayed.

Otherwise, data is displayed from the Data Flex table.

Note: The browse option can also be launched by double-clicking a filelist entry.

Icons

Icons at the top of the Browse Data dialog box can be used to navigate through the selected file(s). Choose the first icon ShowFileList.png to hide or show the selected filenames in the left panel of the dialog box. Use other icons to position in the list. Hover the mouse over an icon to learn its function.

Index

Controls how the displayed data is sorted. The default is by RECNUM but you can choose another index from the pulldown list to modify the sort order.

Data tab

Displays records retrieved from the selected file. Only columns checked in the Columns tab are visible.

Columns tab

Displays the column number, name, DataFlex type, index segment, and length in bytes, for each column and allows a column to be shown or hidden. Check the Display option to show a column in the Data tab. When you select a column in the top panel, column attributes are shown in the bottom panel.

You can also right-click the grid in the Columns tab to display a short-cut menu that allows you to select all columns, deselect the currently selected columns, or rebuild the Data tab grid to show the selected columns.

RebuildGrid.png

Attributes tab

Displays DataFlex table attributes for the selected file.

Extended Attributes tab

Displays extended attributes that you can use with Mertech drivers. These are explained in detail in the Flex2SQL Programmer’s Guide.

Intermediate File

Displays the contents of the .INT file.

Drop Table

Drops the selected table or tables from the SQL database.

Drop View

Drops the selected view from the SQL database. This option is used if a filelist entry was created based on a view.

Analyze Storage Requirements

With ORAFlex 2.0, Mertech introduced a very important enhancement to the Flex2SQL utility regarding Oracle storage calculations. Before performing an Oracle data migration, you can analyze the Oracle storage requirements and determine if the Oracle tablespaces have sufficient room.

You can choose different tablespaces for index and data tables, as well as, assign individual tables to different tablespaces. Flex2SQL dynamically calculates the storage requirement for each table. If the tablespace is not sufficient, the calculation is highlighted in RED. Use Oracle’s Storage Manager utility to increase tablespace sizes.

There are two values that must be set before proceeding with the analysis of storage requirements. These two settings deal with the differences between Oracle and DataFlex record lengths. The two settings are Use Storage Clause (which includes the % filled for large fields) and Number of Records to be used for Empty Files. These settings are entered in the Oracle Tab of the Convert DAT to Table dialog box.

TableSpace Selection Lists

The tablespace selection lists at the top of the Analyze Storage Requirements dialog box are used to select the default Oracle tablespaces for the migrated data and index tables.

Data TableSpace

The tablespace that will contain data tables. It is advisable that index and data tables be created on two separate tablespaces, residing on different hard drives.

Index TableSpace

The tablespace where the index files will be created.

DataFlex Files

The DataFlex files grid displays information for the selected DataFlex files.

Name

Name of the DataFlex file.

Records

Number of records in the DataFlex file.

Data TableSpace

The tablespace name for the migrated DataFlex file. Click the Data TableSpace >> column heading to change the tablespace selection for this file.

Size (Bytes)

The required number of bytes for the migrated table.

Index TableSpace

The tablespace name where the indexes will be created.

Size (Bytes)

The required number of bytes for the migrated indexes.

Total Size (Bytes)

The total size required on the server side for all the DataFlex files displayed in the grid.

TableSpace Info

Displays size information for all the tablespaces the current user can access.

TableSpace

The name used to identify the tablespace.

Available Space (Bytes)

The available space on the server.

Required Size (Bytes)

The space required by the selected DataFlex tables. Turns red if the required size is larger than the available space.

Total Size (Bytes)

The total available space and required space on the server for all DataFlex files and indexes.

Note: Only users with SELECT ANY TABLE rights or DBA rights have access to this feature. Consult your Oracle Enterprise Manager manual for more details.

Create Text File for Loader

Allows you to generate a text file from one or more DataFlex files. The text file can then be used with the database server’s high-speed text migration utility. An example is SQL*Loader for Oracle.

This option is supported for Oracle, PostgreSQL, and MySQL/MariaDB drivers. You have the option of creating new RECNUM values or keeping the original RECNUM values.

  • See Also: High-Speed Script Based Migration

Export Options tab

Settings in the Export Options tab determine the format and location of the output data.

File

The DataFlex file and number of records in the file are displayed. These fields are read-only. The name of the text file that will be created is also displayed. Type a new name for the text file if you want and use the Browse button to change the export path for the file.

Export Options

Select the Export Data & Control File option if you want the data to be exported to a text (.txt) file.

Select the Export Control File Only option if you only want to generate the controlling scripts and batch file (this option is only available for Oracle).

Use the pulldown lists to choose the field and decimal separators to be used in the text file. These two separators must be different.

Select Preserve Recnum to copy existing DataFlex RECNUM values to the RECNUM column in the SQL table. If this option is not selected, the SQL server assigns RECNUM values starting with one.

If Recnum Range numbers are filled in, then only the rows that have RECNUM values in the given range are exported. Otherwise the entire file is exported.

Oracle SQL*Loader Options tab

The SQL Load Options tab contains server-specific settings that determine how the data is loaded.

Control File Name

The name of the SQL*Loader control file. The control file tells SQL*Loader how to interpret the data file. It includes for example, the names and format of the data files, how to identify the start and end of data fields, and the type of load to be performed.

Client Execution

If the loader scripts will be run from the client, select the Client Execution option. If the loader scripts will be run from the server, uncheck the Client Execution option.

DIRECT PATH Load

SQL*Loader provides two methods for loading data: conventional path load and direct path load.

A conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

A direct path load parses the input data according to the description given in the loader control file, converts the data for each input field to its corresponding Oracle column data type, and builds a column array structure (an array of <length, data> pairs).

Direct path load is faster than conventional path load. However, there are certain restrictions on direct path loads that may require you to use a conventional path load. Consult your Oracle Enterprise Manager manual for more details.

Load Type

REPLACE: drops the existing table and then creates the table and loads the new data.

APPEND: indicates that the table need not be empty before SQL*Loader is run. Existing data is preserved and new data is loaded.

TRUNCATE: deletes all data from the table before performing the load.

PostgreSQL Load Options tab

If the loader scripts will be run from the client, select the Client Execution option. If the loader scripts will be run from the server, uncheck the Client Execution option.

MySQL/MariaDB Load Options tab

If the loader scripts will be run from the client, select the Client Execution option. If the loader scripts will be run from the server, uncheck the Client Execution option.

Load Type

REPLACE: if an existing row in the table has the same value as a new row for a primary key or a unique index, the old row is deleted before the new row is inserted.

IGNORE: input rows that duplicate an existing row on a unique key value are skipped.

Analyze After Load Execution

This option is used to recompute index statistics and reset sequences (Oracle, MySql, MariaDB, and PostgreSQL only).

Note: After each loader execution, you must run the Analyze After Load Execution option for each migrated file. THIS IS VERY IMPORTANT. This option analyzes the system and adjusts the RECNUM values to run properly with DataFlex. If this option is not executed finds will behave erratically.

Adjust Sequence Definition

Resets the Oracle sequence definition to start from the next available value in the sequence. This is required if your data was migrated with the Preserve Recnum Value option. Running this option, updates the Oracle sequence definition to start at 1 plus the highest RECNUM value in the DataFlex table.

Inverse Keys

The following options are available from the Database menu:

Enable Trigger to Handle Inverse Key

Enables a trigger to automatically manage inverse keys whenever an insert, update, or deletion of a record takes place.

Inverse key fields are additional fields created during data migration to handle indexes with descending index segments. These additional fields allow the driver to scan a set of data in descending order without issuing an order by clause. Inverse keys are also used to support upper case indexes.

Disable Trigger to Handle Inverse Key

Disables the trigger to handle inverse keys.

Check Inverse Key Columns

Examines the selected table(s) and determines if inverse key columns need to be created.

Remove Inverse Key Columns

Removes any existing inverse key columns.

Select Database

MS SQL Server, MySQL/MariaDB, 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. Every user is assigned a default database. This database is highlighted in the Select Database dialog box.

Note: Hover the mouse over the database name to display a tooltip containing the location of the database file.

In order to create, delete, or manipulate MS SQL Server table structures through Flex2SQL, 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.

When you choose localdb, default (localdb)v11.0, as your Server Name in the Login dialog box, an additional option, Attach New Database, is available in the Select Database dialog box.

Attach New Database

LocalDB allows you to specify a database file location. This means that you can distribute a database as standard files by detaching the database from the LocalDB server, copying the database files to another computer, and then attaching to them using that computer’s LocalDB installation.

MDF file

Browse to select an existing master database file. This is the name of the primary database 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.

Select File > Logout from the menu bar to detach the database.

Change Table Handler (MySQL/MariaDB)

Changes the MySQL/MariaDB table format. When creating MySQL or MariaDB tables using FLEX2SQL you can choose from any one of the six formats (see Convert Database – MySQL Tab).

To change the table handler:

  1. In the From Type pulldown list, select the table format that matches the one shown in the Actual Type column.

Note: If you select a different table format than the one shown in the Actual Type column, Flex2SQL does nothing.

  1. Select the new type using the To Type pulldown list.

  2. Click RUN to start the process.

  3. Click Yes, or if multiple tables were selected, Yes to All to acknowledge the change.

  4. Confirm that the change took place by reviewing the Actual Type column.

Copy or Erase Data

The following commands are available from the Database menu:

Copy Data from .DAT File to Table

Copies data from a DataFlex file to an existing table. Typically, this option is used to copy data to an empty table created using Flex2SQL.

Copy Data from Table to .DAT file

Copies data into a DataFlex file from a table on the server. This is normally done on a blank data file.

Erase a DataFlex file

Uses the DataFlex zerofile command on the DataFlex file to remove all records from the file.

Erase a Table

Deletes all records from the selected table.

Maintenance Menu

The Maintenance menu contains commands that are useful in maintaining your file structure after migration. Options are available that make it easy for you to modify your table structure while still maintaining compatibility with your DataFlex application.

Create Table

Opens the Create Table dialog box that allows you to add tables and indexes on the back-end.

The top panel in the dialog box allows you to enter the table name, path and name of the .INT file, DataFlex file number and whether the table uses DataFlex RECNUM style processing, RowId style processing, or the table is a Dynamic Primary Key (DPK) table.

To add a table:

  1. Enter the table name.

  2. Enter the physical location of the INT file.

Note: The INT filename defaults to “table name.INT” The physical name is automatically populated with the path for the current workspace.

  1. The file number defaults to the next available file number. You can use the spin button to change this value.

  2. Choose the table style: RECNUM, RowID, or Dynamic Primary Key.

Create Table.png

A RECNUM table emulates DataFlex record processing where a unique consecutive positive number (record number) is used to identify each record. The record number designates a record’s relative position in the table. This is useful for legacy applications.

A rowId table does not use RECNUM to uniquely identify a record, but instead relies on you specifying one or more fields that when combined create a unique record identity. Standard tables use the RowId concepts in Visual DataFlex instead of the older RECNUM concept. A RowId table can take advantage VDF RowId functions.

A dynamic primary key (DPK) table uses a GUID (Globally Unique Identifier), also referred to as UUID (Universally Unique Identifier), field as the primary key. A GUID is a 128-bit quantity generated by an algorithm that nearly guarantees to make it unique.

DPKs are supported by MS SQL, Oracle and PostgreSQL.

Server

Data Type

Notes

MS SQL

uniqueidentifier

Supported on all tables that do not use insert triggers. Insert triggers prevent the driver from returning the newly created primary key. UUIDs can still be used, just not as the primary key.

Oracle

RAW(16)

RAW (16) is the native type for a GUID in Oracle. Not supported for Oracle running on AIX.

PostgreSQL (version 8.2 and above)

uuid

The “uuid-ossp” library, which enables you to generate UUID values server-side, must be installed for DPK support in PostgreSQL. The DPK radio button is disabled if this library is not installed. A tooltip hint over the disabled radio button explains how-to install the library.

Note: MySQL & MariaDB do not have the interface required to support server-side dynamic primary keys. You can however, still use UUIDs but you need to generate them in your code.

Creating a Dynamic Primary Key Table.

  1. Select Table Style Dynamic Primary Key Table.

  2. A UUID field is automatically added and named UUID, change this name if you want.

    Although a UUID is stored as a 128-bit integer on the back end, the assigned DataFlex type is ASCII. VDF treats UUIDs as strings, making searching easy. A UUID is displayed as a sequence of hexadecimal digits, in groups separated by hyphens. For example: {C0E61A92-BE06-4557-AABA-36AB263E0457}.

  3. A function is automatically selected for the default value. You can assign your own function or choose from the drop-down list. The functions available in the drop-down list are the standard server-specific built-in SQL functions for generating unique GUIDs.

    This is the function that is used to generate the dynamic primary key when a record is inserted if you do pass in a value for the field. This is unlike a trigger, which automatically fires whenever a new record is added.

Note: Refer to the Flex2SQL Programmer’s Guide for additional information. Requires driver 12.1.6222 or above.

  1. Add, modify and delete fields from the new table using the Fields tab (see Restructure Fields Tab for a description).

  2. Add, modify, and delete indexes from the new table using the Indexes tab (see Restructure Index Tab for a description).

  3. Select database settings, enter the table’s root, logical, and display names, and determine whether connection information is stored in the INT file using the Table tab (see Restructure Table Tab for a description).

  4. When all changes are complete, click the Next button. The Restructure Results dialog box displays.

Note: If you click Cancel and there are unsaved changes, you are prompted Are you sure you want to cancel? Click Yes to close the dialog box without saving your changes. Click No to continue working in the Restructure Table dialog box.

  1. In the Restructure Results dialog box, you can choose to save your changes in scripts that can be compiled and sent to remote sites to perform automatic restructuring, or click Commit to make changes directly on the server and to generate the DataFlex .FD and .INT files.

Restructure Table

Allows you to modify SQL tables.

To restructure a table(s):

  1. Select one or more tables in the Filelist dialog box.

The Restructure Table dialog box displays. The top panel contains the read-only table name, physical pathname of the .INT file, and DataFlex file number. The top panel also contains the table style (refer to earlier descriptions of Recnum Table, RowId Table, Dynamic Primary Key Table).

To change the table style to Dynamic Primary Key Table:

  1. If the table is currently a Recnum Table, select RowID Table.

  2. Select Add Field and choose Native Type GUID.

  3. For Default Value, use the drop-down list to select an available server-side function to generate the UUID.

  4. Uncheck Allow NULL.

  5. Select the Indexes tab, add the new GUID column to an index and select it as the Record Identity and Primary Key.

Note: The procedure to convert a table to a DPK table displays in a tooltip when you hover the mouse over the Dynamic Primary Key Table radio button.

  1. Add, modify and delete fields in the Restructure Fields Tab.

  2. Add, modify, and delete indexes in the Restructure Index Tab.

  3. Update the table in the Restructure Table Tab.

  4. When all changes are complete for the selected table, click the Next button. If multiple tables were selected, the next table is displayed for update. When all updates are complete, clicking Next displays the Restructure Results dialog box.

Note: If you click Cancel and there are unsaved changes, you are prompted Are you sure you want to cancel? Click Yes to close the dialog box without saving your changes. Click No to continue working in the Restructure Table dialog box.

  1. In the Restructure Results dialog box, you can choose to save your changes in scripts that can be compiled and sent to remote sites to perform automatic restructuring, or click Commit to make changes directly on the server and to generate the DataFlex .FD and .INT files.

Restructure Fields Tab

Allows you to add modify or delete fields in the table.

Field Names

Lists the names of the fields in the table. Click Add Field to add a new field to the end of the table. Select a field name then click Delete Field to delete the field from the table.

Name

Field selected in the Field Names panel.

DataFlex Type

The DataFlex field type for the column. Defaults to ASCII for new fields but can be changed using the pulldown list.

Native Type

The assigned field type mapping on the target database. Allowed types are available using the pulldown list.

Length

The number of bytes in the DataFlex field. If the field is an overlap field, click <> or press the <F4> button to display the Define the Overlap definition popup dialog box.

Define the Overlap definition popup dialog box

Use the Define the Overlap definition popup dialog box to enter the overlap field information. This includes the starting field and offset, the ending field and offset and the total length of the overlap field in bytes.

Default Value

If the field is to be created as NOT NULL (automatically NOT NULL if the field is part of an index), then the Default Value specified here is inserted into the column if a value is not provided by the application (left blank).

Note: You can select a GUID generator function from the drop-down list, or enter another server-side function to be used to create the ID for the field if a value is not provided by the application.

DefaultValue1.png

DefaultValue2.png

Main Index

Defines the primary index for the table. Click <> or press the <F4> button to display the Select the main index popup dialog box

Select the Main Index popup dialog box

Used to select the primary index from the list of available fields.

Relates To

Selects the table and field for which this field is a foreign key. Position the cursor in the first text box, then click <> or press the <F4> button to display the Select a File popup dialog box. Position the cursor in the second text box, then click <> or press the <F4> button to display the Select the field to fetch popup dialog box.

Note: Click Clear Relationship to delete an existing relationship.

Select a File pop-up dialog box

Select the Relates-To (foreign) table from the list of presented tables and click OK.

Select the fields to fetch pop-up dialog box

Select the Relates-To (foreign) field (column) from the list of available fields and click OK.

Restructure Index Tab

Allows you to add modify or delete indexes for the table.

Index No:

Displays the currently selected DataFlex index number. Fields that make up the index structure are listed in the grid on the right. Use the spin button to select an index.

Select a field popup dialog box

The Select a field popup dialog box is displayed when adding an index, adding an index segment, or inserting an index segment. It lists available fields in the table.

Delete Index

Drops the selected index from the table.

Add New Index

Allows you to add a new index to the table. The available fields are listed in the Select a field popup dialog box.

Segment

The fields that comprise the index segment.

CS

Select CS if the index values are case sensitive. Leave this selection unchecked if indexes are case insensitive.

Desc

Select Desc if the sorting order of the segment is descending. Leave this selection unchecked if the sorting order is ascending.

Add Segment

Adds a new segment to the current index. The fields are selected using the Select a field popup dialog box. Adds the segment at the end of the segment list.

Insert Segment

Adds a new segment to the current index. The fields are selected using the Select a field popup dialog box. Adds the segment in the middle of current segment list.

Delete Segment

Deletes an existing segment.

Legacy Index Support

The following options are provided for backward compatibility.

When Mertech launched its products in 1997, most RDBMS did not allow for creation of ASC or DESC index segments or support case sensitive indexes. Mertech created a unique approach using INVERSE key values to support this functionality. However, most new databases now allow these features, so the use of inverse keys is no longer needed.

These options are still available but not as the default. To turn them on, check the following options.

Create Inverse Keys

Creates inverse key columns in the table.

Create Old-Style Inverse Key Names

In an earlier version of Flex2SQL, Mertech modified how inverse keys were named. Select this option to use the older inverse key naming conventions. Unless you require explicit field names for inverse keys on the SQL server, this option is not needed.

Restructure Table Tab

Allows you to select database settings, enter the table’s root, logical, and display names, and determine whether connection information is stored in the INT file.

Root Name

The physical name of the file.

Logical Name

The name as it appears in a DataFlex program.

Display Name

A user-friendly description as it appears in the filelist.

Database Name

The database on the server where this table resides. Select the name of the database from the pulldown list when creating a new table.

Note: You cannot select database storage and schema options if you are restructuring a table. You can only select these options if you are creating a new table.

Data Tablespace

The tablespace where data resides. Select the name of the tablespace from the pulldown list when creating a new table.

Index Tablespace

The tablespace where indexes reside. Select the name of the tablespace from the pulldown list when creating a new table.

Schema name

The table owner.

OEM to ANSI

Select this option to convert the OEM character set (used by DataFlex) to the ANSI character set.

  • See Also: Preferences - OEM Support

System File

Select to specify this file is a system file, a DataFlex file containing only one record.

Cache Mode

Select to enable local data caching. The specified number of records (Max Rows) is held in local cache for fetching.

Set Mode

The number of records selected for Max Rows (default is 10) is fetched in one network round-trip. Use the spin button to set the number.

Login defaults

Select an option to store the indicated information in the .INT file. If an option is not selected, the information must be provided in a connection string. For example if Put Server Name into INT file is not checked, the server name must be included in the connection string.

Restructure Results

Keeps a log of your updates and creates code you can use to propagate your table modifications. You can either commit changes directly or save the code so it can be compiled and sent to remote sites to perform automatic restructuring.

Click Commit to make changes on the server and to generate the DataFlex .FD and .INT files. Alternately use the Save options in the individual tabs (see descriptions below).

Note: Once you click Commit the changes are applied and the dialog box closes. Scripts and source code are no longer available.

Script Tab

Shows the script that can be submitted to the SQL Server to propagate the updates to customer sites. Click Save to save the script for the currently selected table. Click Save All in one script to save all the scripts in one file.

INT File Tab

Displays the new INT file for the selected table. Click Save to save this INT file. The new INT file can be copied directly from the saved location or it can be copied from the development drive once the changes are committed and the new INT file is generated. If more than one table was updated be sure to save all the INT files.

Log Tab

Lists the operations you performed. Click Save to save the log file.

Source Code Tab

Lists the DataFlex restructure code for the operations you performed. Click Settings to update the content for the restructure code.

  • Generate Source For: Select the radio button that corresponds to the desired VDF version.

  • Generate Comments in the source: Select to include comments in the restructure code

  • Program Skeleton: Pathname for the starting source file (.src). A Use statement that references the saved INC file is added to this skeleton program. Click Open Source in Studio to open this file in Visual DataFlex studio.

  • Add Restructuring code to skeleton program: Select to save the code that performs the restructuring. The restructure code is saved in an INC file.

  • Add Login details to source: Select to include a login statement with the server type, server name, user name, and password in the skeleton program.

Click Save to save this source code in an INC file.

Index Maintenance

IndexMaintenance.png

Allows you to determine whether the indexes were properly created. The Index Maintenance dialog box can also be used to delete existing native indexes or recreate native indexes that were deleted. You cannot create a new index with this menu option.

Note: To use the Index Maintenance option the DataFlex workspace or filelist you are viewing must already be converted to a database engine back-end.

When you select this option, Flex2SQL examines each DataFlex file and compares it with the indexes created on the target server. If an index was not created, Flex2SQL displays a red check mark next to it.

Note: Mertech recommends that you run the Index Maintenance option after every conversion to check the validity of all the indexes.

The Index Maintenance dialog box uses a tree-view. To display detailed information. Click the Plus.bmp at the location in the tree where details are needed or click the Expand.bmp icon (the far-right icon). To remove a native index, highlight the index to be deleted and click the Delete Index.bmp icon. To add a native index back in that was previously deleted, click the Lightening.bmp icon at (the far-left icon).

Segments

Displays all the fields in the currently selected index.

Primary Key

Checked if the selected index is a primary key.

PCTFREE

Specifies 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. (Oracle only)

Initial

The initial size in kilobytes allocated for the newly created Oracle index on the Oracle server.

Next

The size the Oracle index will be increased by when it is full.

Tablespaces

The tablespace where the indexes were created.

  • See Also Restructure Index Tab

Synchronize Relationships

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.

SyncRelationship-before.png

If you examine a database diagram for the converted tables (using for example Object Explorer in Microsoft SQL Server Management Studio) you see a list of all the tables but no relationships between the tables.

ObjectExplorer-before.png

Select Synchronize the SQL Foreign Key with the INT Relationship (Synchronize.bmp in the left pane) to transfer the relationships to the SQL back-end. The right pane then shows the relationships that are created.

SyncRelationship-after.png

Once the relationships are created on the server, data modeling tools can read the data model from the database server and create a complete data model diagram.

ObjectExplorer-after.png

With the Synchronize Relationship option you can also use external tools to create database relationships and then update the .INT file using Synchronize the INT file relationship with the SQL table foreign key (Synchronize point left.bmp in the right pane).

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.

Hover the mouse over a tool icon to learn the tool’s function.

INT File Relationships

image1 Creates a foreign key on the SQL server.

image2 Deletes a relationship in the .INT file.

image3 Synchronizes the SQL foreign key with the INT relationship (can result in relationships being deleted on the SQL server)

image4 Lines up the right tree with the left key.

image5 Collapse the tree. Only the top level is visible.

image6 Expands the tree so all levels are visible.

SQL Table Foreign Keys

Create point left.bmp Creates a relationship in the .INT file.

Delete.bmp Deletes a foreign key from the SQL table.

Synchronize point left.bmp Synchronize the SQL foreign key with the INT relationship (can result in relationships being deleted in the INT file).

Collapse.bmp Collapse the tree. Only the top level is visible.

image7 Expands the tree so all levels are visible.

Note: If a relationship is missing the corresponding entry in the tree is labeled Missing and marked with a red X.

Reports Menu

Mertech’s Flex2SQL database migration utility contains a migration and trace report feature. This comprehensive reporting facility gives users a complete picture of the migration process. It is no longer necessary to analyze the migration process on a table-by-table basis.

Migration Report

The Migration Report 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. At the top of the report is a summary that lets you determine whether any errors occurred during the migration so that you do not have to search through the whole report to check for errors.

The report provides details on table and index creation along with a comparison of the number of records migrated with the number of records in the DataFlex files. If a process fails for any reason, the STATUS column shows the status as “FAILED” and the ERROR LOG column indicates the error generated.

You can print and save the report text file.

The Migration Report is enabled in the Tools > Preferences > Reports tab.

  • See Also: Preferences - Reports Tab

Clear Migration Report

Each time you perform a data migration, a new migration report is appended to the end of the previous migration report. Use the Clear Migration Report option to start with a fresh, blank report.

Driver Trace Report

The Driver Trace Report displays detailed driver level traces logged while the Flex2SQL application is running.

The level of logging is set in the Tools > Preferences > Reports tab.

  • See Also: Preferences - Reports Tab

Tools Menu

The Tools menu allows you to create scripts, generate .INT, .DEF, or .FD files, and set migration preferences.

Generate Scripts

Flex2SQL allows you to generate SQL scripts to perform certain tasks on the server. You can either create all the tables using the convert database option or you can generate SQL scripts to create blank tables.

SQL Script for Creating Tables

Generates scripts to create tables, indexes, primary key constraints, and sequence objects (where applicable).

SQL Script for Post Loader steps

Generates scripts for post loader steps. Enter the SQL script file name and path to start. This includes estimating statistics, adjusting sequences, and updating text columns to restore stripped out CRLF pairs and restarting Identity column values.

SQL Script for Adjusting Sequence Definitions

Generates scripts to initialize auto increment columns (in PostgreSQL) or sequences (in Oracle) to be the highest number in the table plus one.

SQL Script for Enabling Inverse Key Triggers

Generates scripts for enabling inverse key triggers. Enter the SQL script file name and path to start.

SQL Script for Disabling Inverse Key Triggers

Generates scripts for disabling inverse key triggers. Enter the SQL script file name and path to start.

SQL Script for Dropping objects

Generates scripts for dropping indexes, tables, and other objects such as sequence definition on Oracle or a table in any SQL database engine. Enter the SQL script file name and path to start.

Generate .INT

Generate .INT File from .DAT

This option is used when you want to generate the .INT file from your DataFlex DAT file. Based on the DAT file structure, Flex2SQL generates an .INT file respecting the current settings defined in Flex2SQL.

Generate .INT File from Table/View/Synonym

This option is used when you write a DataFlex program that accesses a table that was created or modified on the target DBMS. Before you can access the table from a DataFlex application, you must bring the table into the DataFlex environment by generating the .INT file, adding an entry for it in the filelist and generating .FD files for compilation.

When the dialog box is first displayed, the previously selected Fetch objects are retrieved from the target database and displayed in a grid on the left side of the dialog box.

Grid display

The grid contains the following columns

Check Box

Select the check box to mark this table for importing into DataFlex.

Icon

A green arrow over the list icon indicates the table already exists in DataFlex.

Table Name

The name of the SQL table on the server.

File Number

The existing (if the table already exists in DataFlex) or proposed (for a new table) file number. File numbers for existing tables cannot be changed. File numbers for new tables can be modified.

The INT file name and FD File Name for the currently selected table are displayed at the bottom of the dialog box.

Fetch

Select a Fetch radio button on the right to choose the type of object to be retrieved from the target DBMS.

Import Options

Import SQL relationships

Select to import any SQL relationships that exist between tables in the SQL database into the DataFlex tables.

Show INT File info view

Select this option to display the Create Intermediate File dialog box, which allows you to choose additional import settings. Additional settings are not usually required.

Insert into Filelist

Select to insert the imported tables into the DataFlex filelist.cfg file.

Generate FD File

Select to generate the FD file.

Select All

Selects all the displayed objects.

De-Select All

Deselects all highlighted entries.

Invert Select

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

OK

Performs the selected import options and closes the dialog box.

Generate .DEF

This option is used when you want to save the structure of your SQL table in a DEF file. The DEF file is a text file with a specific format defined by DataFlex to describe the data files.

Generate .FD

This option is used when you have an SQL table and you want to access it through a DataFlex application. The .FD file is required by the DataFlex compiler to map fields.

Preferences

Set global preferences for using Flex2SQL Utility Classic.

Preferences - General Tab

Preferences.png

This tab allows you to set options that affect all migrations including the terminating character(s) for SQL scripts, file casing preferences, filelist position for the new tables, and how errors are handled.

Overwrite Existing File

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 Data Migration from DAT

If this option is selected, the contents of the target table are erased before the contents of the DataFlex data file are copied. By default, this option is turned off.

Save current settings for the future

If this option is selected, preferences are saved for future use.

Disable Status Panel

If this option is selected, the progress panel is disabled during migration.

SQL Script Generator

SQL Scripts for creating tables, indexes, sequences, and other database objects can be terminated with just a line feed (LF) or with a carriage return (CR) and LF. The SQL generator creates an ASCII file. By selecting the appropriate end of line character, the file can be viewed in the UNIX or Windows environment. For UNIX, select the LF character as a terminator. For Windows, select the CR and LF characters.

Terminate each with LF

Generate text file in the Unix/Linux format. This should be used if you are going to run your scripts from a UNIX or Linux platform.

Terminate each with CR+LF

Generate text file in Windows format. This should be used if you will run your scripts on a database server running on a Windows platform.

File Casing

You can control whether existing file casing is used or upper or lower case conversion takes place. This option is used in conjunction with MySQL, MariaDB and PostgreSQL conversions to determine if the objects (tables, columns, indexes, etc) are created with upper or lower case letters.

Upper

Selecting the Upper radio button forces all text to be converted to uppercase.

Lower

Selecting the Lower radio button forces all text to be converted to lowercase.

Keep Casing

Selecting the Keep Casing radio button maintains all text case as it is currently stored in each DataFlex file.

Error Handling During Data Migration

Set options to handle errors during migration.

Cancel Migration after N errors

This option terminates the migration process if too many errors are encountered. This is typically due to bad data and you must correct all data related errors before proceeding with the migration.

Show All Error Messages

This option displays a popup dialog box with error messages as they are encountered during migration.

Language Support

If your computer is configured with multiple languages, you can choose the display language from this pulldown list.

Preferences - Login Tab

This tab controls whether SQL specific information is included in one or more .INT files. This is a useful option when the files are generated in one environment and deployed in a different environment.

Put Server Name into INT File

If this option is selected, the SERVER_NAME token is placed in the .INT file. The server name provided in the login command is used. By default, this option is turned off (unchecked box).

Put Schema Name into INT File

If this option is selected, the SCHEMA_NAME token is placed in the .INT file. The Schema displayed in the Filelist dialog box is used as the schema name. By default this option is turned off (unchecked box).

Put Database Name into INT File (For MS SQL Server only)

If this option is selected, the DATABASE_SPACE_NAME token is placed in the .INT file. By default this option is turned off (unchecked box).

Preferences - DFPATH Tab

This tab controls which DFPath Flex2SQL uses during data migration. If the data files are not found in any of the DFPath directories, migration does not take place.

Since there are multiple ways to select a filelist, this setting is very important. When migrating data for a character mode application, select a filelist explicitly. When using Visual DataFlex 4 and above, use workspaces.

DFPath To Use Sub-tab

Open Type

Identifies how the user selected the files to be migrated (i.e. if a workspace was used or a filelist was selected). The option marked here is used to populate the list of directories in the DFPath. This option is read-only.

  • Workspace: This option is marked if the files were selected using the File > Open > Select Workspace option.

  • Filelist: This option is marked if files were selected using the File > Open > Select Filelist option.

  • Files Without Filelist Entry: This option is marked if files were selected using the File > Open > Select Files Without Filelist Entry option.

Check boxes

When you check one of these boxes, the contents of the associated sub-tab are included in the DFPath that Flex2SQL searches to locate DAT or INT files. Check boxes are listed in priority order, so when User Defined is checked, files in the User Defined sub tab are searched first.

  • User Defined: Check this option to include additional paths you enter in the User Defined sub-tab.

  • Use DFENV.cfg: Check this option to include paths listed in the DFENV.cfg sub-tab.

  • Workspace: Selected by default if the Open type is Workspace. When this option is checked the DFPath includes the paths listed in the Workspace sub-tab.

  • Registry: When this option is checked, the DFPath includes the paths listed in the Registry sub-tab.√

  • Path: Check this option to include paths listed in the PATH sub-tab.

Preferences DFPATH.png

User Defined Sub-Tab

Paths you enter in this sub-tab are searched first when the User Defined option is selected in the DFPath To Use sub-tab.

DFENV.cfg Sub-Tab

This sub-tab is populated using the dfenv.cfg file and has an entry like: DFPATH=df31dusrdata;df31dwasa.

Browse to select the location for the dfenv.cfg file. This option is usually only used by console mode DataFlex.

Workspace Sub-Tab

This sub-tab is populated using the filelist path (without the filelist name) and the data path from the current workspace.

Registry Tab

This sub-tab is populated from the SystemDFPath in the Windows Registry [HKEY_LOCAL_MACHINESOFTWAREWow6432NodeData Access WorldwideVisual DataFlex17.0Workspaces]

Path Sub-Tab

This sub-tab is populated using the PATH environment variable.

Main DataDir

All auxiliary files created by Flex2SQL during data migration will be created in this directory. This is usually the data file directory. This directory is the first path in the DFPath. If you want to change this directory, add this path first in the User Defined sub-tab.

Filelist

Shows the currently selected filelist.

Preferences - Reports Tab

Mertech’s Flex2SQL Database Migration Utility contains a migration report feature. This comprehensive reporting facility gives users a complete picture of the migration process. It is no longer necessary to analyze the migration process on a table-by-table basis.

Enable Migration Logging

This feature is useful in identifying bad data in the DataFlex database, which might result in incomplete migration. Mertech’s drivers have various levels to define tracing granularity.

The Errors Only option is especially useful during migration as it logs only the errors rather than all driver calls to the server. If bad data is encountered during migration, the target back-end raises an error. On receiving this error, the driver logs the contents of the record buffer to the log file, stops migrating that file, and moves on to the next file.

The user can look at the trace file, correct the data in the DataFlex file, and then run the migration process on the file again. The most common problems are in data fields.

Enable Application Logging

This option sets the registry key to turn on the driver traces to allow you to generate driver level traces while you are running your application.

Note: This option severely affects performance if left on and generates very large trace files. Remember to turn this off once you have generated the trace you need.

Detail Level

Allows you to control how detailed a trace you want the driver to generate. For most data related problems, Error Only is the best option.

In order to determine which trace level is most appropriate for you, here is a description of the available options. Notice that the trace levels build on the previous one:

  1. Errors Only logs only native SQL error related information and driver identification. No other detailed information is included in the log file. The message(s) are preceded by ‘ERROR’ or ‘WARNING’. This option also minimizes the size of the trace file.

2. Debug is the default trace level. It includes the previous level’s information in addition to logging all the database calls issued by the application, like FINDs, SAVEs and DELETEs, LOGIN, LOGOUT and the embedded SQL calls. Besides logging these commands, the call parameters are also logged.

3. Detailed 1 includes the previous level information and adds information about the MOVE commands from/to File field buffers and the calls to the Get_Attribute and Set_Attribute commands.

4. Detailed 2 includes the previous level information and adds text file generation through the driver.

5. Detailed 3 includes all the information from the previous level, this option contains the most comprehensive level of trace information. It logs all calls to the driver and when they start and when they finish.

  • See Also: Driver Trace Report

Enable Migration Report

This option turns on the report 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.

  • See Also: Migration Report

Preferences - OEM Support

If OEM to ANSI translation is selected during data migration, certain characters used in DataFlex for drawing lines, etc. are not always saved and retrieved correctly. To avoid this, Mertech has provided a mechanism to preserve the translation of these characters. You can specify which characters are to be avoided during translation. This information is saved in a file called OEM.CFG that is created in the same directory as the driver.

Note: You must always keep the OEM.CFG file in the same directory as the driver to maintain consistency across different users and programs.

ASCII Nr.

Enter the ASCII number (between 0 and 255) for characters in the OEM character set that should not be translated.

OEM To ANSI

Select this option to convert the OEM character set (used by DataFlex) to the ANSI character set.

Preferences - Field Mappings

Allows you to set global field mapping between DataFlex data types and the data types of the target RDBMS. You can also set this at table level.

  • See Also: Convert Database

Preferences - Date Handling

Allows you to set how DataFlex displays and handles dates.

Use Epoch Year

The Use Epoch Year setting is used when the DataFlex database contains 2-digit years. All dates are saved as 4-digit dates on the target database and any year on or before the epoch year value is treated as a new year in the millennium 2000.

Program Displays 2-Digit Year

If your program displays 2-digit years and you want a 4-digit year extracted from the table on the target server displayed, uncheck the Program Displays 2-Digit Year option. After retrieving the data field, the driver converts the date to 2-digit year and returns just the last two digits to the runtime for display.

Window Menu

The Windows menu lets you customize the Flex2SQL window.

Display Options

Displays or hides the status bar at the bottom of the Flex2SQL window and determines how toolbar icons are displayed.

Cascade

Displays multiple open views in cascade style.

Tile Horizontally

Tiles open views horizontally.

Tile Vertically

Tiles open views vertically.

Minimize all Windows

Minimizes all currently open views. Views are located at the very bottom of the main Flex2SQL window.

Restore all Windows

Restores all minimized open views to their previously open state. If the windows were tiled, they are shown tiled.

Auto Arrange Icons

Restores toolbar icon settings to the default settings.

Open Windows

Lists the currently open windows. The active window is checked. To switch to another window choose it from the list.

Help Menu

The Help menu lets you obtain information about Flex2SQL.

Flex2SQL Help F1

Displays online help for the Flex2SQL database migration utility.

Mertech Site

Connects to the Mertech Data Systems home page where you can download the latest version of the utility, get product news, FAQs, and online support.

Users Guide

Displays the Flex2SQL Programmer’s Guide.

About

Displays version information for the Flex2SQL database migration utility and driver as well as license information.

Filelist Options

Filelist dialog

The Filelist dialog box is used to select the files for the data conversion.

Note: Double-click a filelist entry to browse data in the file. Right-click the Filelist dialog box to see a popup menu with a list of available database commands.

Convert Database

Displays the Convert DAT to Table dialog box, where migration options are selected and the file conversion is initiated.

Add Driver Prefix

Adds the driver prefix (e.g., Sql_drv) to the root file name entry in the filelist. This effectively tells the DataFlex runtime to treat the file as a non-DataFlex file. When an open command is issued, the DataFlex runtime passes the open operation to the driver associated with the file.

Remove Driver Prefix

When you remove the driver prefix, DataFlex runtime treats the file as a native DataFlex file. Using driver prefixes is a good way to switch between your DataFlex and newly migrated tables on the server.

Select DF Files

Highlights all the files in the filelist that do not contain the driver prefix and for which the DAT file can be physically located by searching directories in DFPATH.

  • See Also: Preferences - DFPATH Tab

Select Tables

Selects all entries in the filelist that have a valid corresponding table on the target server. The command may take a few seconds, depending on the number of files in the filelist, because the target server’s data dictionaries are used to verify the existence of these tables.

Select All

Selects all DF files and tables.

De-Select All

Deselects all highlighted entries.

Invert Select

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

Show Only Unique Root Names

Since DataFlex works with file numbers and not file names, two entries with the same name at a different filelist slot are considered two separate files. It is quite common to have files of the same name at different locations as alias files. If this option if checked, duplicate names are hidden. If the file has already been migrated it is not necessary to check this option.

Schema

Select the owner for the converted files from the pulldown list.

Migrate Non-DataFlex File

When this option is checked, Flex2SQL uses the appropriate driver to read data from the source file and perform the data migration.

The most common usage is when you are migrating Pervasive.SQL files. You must have the driver for Pervasive installed in the Flex2SQL bin directory with the proper license registration.

.INT File Directory

Specify the location of the .INT files generated by the driver for Pervasive.SQL.

Note: Care must be taken not to over write these .INT files with the ones generated by Flex2SQL; otherwise, you cannot open the Btrieve files again. You must generate the Flex2SQL .INT files in a separate directory.

Convert Database Options

Convert DAT to Table dialog box

The Convert DAT to Table dialog box contains tabs to set general conversion options and server specific options.

Creation Options

Radio buttons at the bottom of the dialog box determine which files are created on the server and whether data is migrated.

Create Table and Index and Migrate Data

Both table and indexes are created and records are copied over to the target database.

Create Table and Index

Both table and indexes are created, but no records are copied over to the target database.

Create Table Only

Only the table structure is created. No data is copied. This option is used during High-Speed Script Based Migration.

Create Indexes Only

Only indexes are created.

Conversion Options

Buttons at the bottom of the dialog box are used to set preferences, save the fileset and associated settings, and perform the conversion.

Preferences

View or modify the preferences used during migration. Preferences can also be set from the Tools menu.

Save Set

Saves the currently selected files and migration settings in a fileset that can then be loaded using the File menu Load Selections or Recent Filesets options.

Convert All

Converts all the tables in the filelist or workspace.

Convert

Converts the current file in your selection list.

Note Use the Convert button to convert your selected files one at a time and set file-specific conversion options.

Help

Activates contextual help for the Flex2SQL application.

Cancel

Cancels the conversion and close the Convert DAT to Table dialog box.

File Statistics

Statistical information for the table name shown at the top of the dialog box displays in the status bar at the bottom of the dialog box.

Record Length

Byte length of the record calculated based on DataFlex field types.

Number of Fields

Number of fields in the file.

Total Records

Number of records in the data file.

Number of Indexes

Number of indexes in the file.

Migration Options Tab

During the data migration process, options can be set to determine how NOT NULL fields are handled, the table style, and whether local cache is used. Options set in this dialog box appear as tokens in the .INT file and the .TD file and can be edited manually.

Table and File Name

Table Name

The name of the SQL table to be created.

File Name

The name of the .INT file to be created.

File Path

The location where the .INT file will be saved.

Table Style

Determines whether the SQL table uses DataFlex Recnum Table or RowId Table style processing.

Set Mode

Determines the number of records fetched in one network round-trip and whether these records are stored in local cache.

These attributes can also be set from within a program (see the Flex2SQL Programmer’s Guide).

Local Cache

Check to enable storage in local cache.

Max Rows

Enter the maximum number of records to be fetched in one network round-trip.

Multi-Directory File Handling

Used to handle creation of files with the same names in the current database.

It is common in the DataFlex and Pervasive environments to create files with the same name residing in different directories. For example each directory might be dedicated to a specific company, for example CompanyA/customer.dat and CompanyB/customer.dat. The DataFlex program knows based on the directory path, which file to access.

Mertech introduced the concept of “Table Suffix” or “Table Prefix” to support multi-company files. Each file name can now be assigned a custom prefix or postfix value to be prepended or appended to the file name. The table is created as “<prefix>RootName” or alternatively “RootName<postfix>” on the server. This approach allows different files to reside in the same database without conflict.

To specify a prefix, select the Prefix radio button and enter the prefix value. To specify a suffix, select the Suffix radio button and enter the suffix value.

Create All Fields as NOT NULL

You can set NOT NULL attributes for a given field type that persists for all tables during migration. When a field is created as NOT NULL, SQL databases expect a default value. You can specify the default values or let the driver assign it.

Checking this option automatically selects the Create all ASCII Fields as NOT NULL, the Create All Numeric Fields as NOT NULL, and the Create all Date and Datetime Fields as NOT NULL options. You can also select each of these options individually.

Use System Defaults

This option allows you to select the system default or to enter your own default value for NOT NULL fields.

ASCII

ASCII fields have default value of a single space.

Number

Numeric fields have a default value of 0.

Datetime

The default format for datetime fields depends on the configured date format (for example, military, US, or European) for the computer on which the application runs. The difference between these formats is the order of the fields. For example, yyyy-mm-dd vs. dd-mm-yyyy. The default datetime value, shown as (yyyy-mm-dd) is:

0000-00-00 00:00:00 for MySQL Windows drivers v12.0 and above

0000-00-00 00:00:00 for MySQL SCO drivers v11.0 and above

0001-01-01 00:00:00 for earlier versions of the MySQL drivers

0001-01-01 00:00:00 for PostgreSQL drivers

0001-01-01 00:00:00 for Oracle drivers

For MS SQL drivers the default value depends on the native date format:

1753/01/01 00:00:00 for DATETIME

1900/01/01 00:00:00 for SMALLDATETIME

0001/01/01 00:00:00 for the other formats

Inverse Key and Overlap Handling

Create Overlap Fields

This option creates another column with information for the index when there is a partial overlap. This option improves performance.

Create Inverse Keys

Mertech’s driver creates additional columns called inverse key columns that handle case sensitive indexes and descending index segments. Selecting this option creates the inverse key columns. This option is turned on by default.

Fields Tab

The Fields tab lists all fields in the data file and allows you to set attributes that affect the way these fields are created on the SQL back-end. The Fields tab contains a grid with the following columns:

Column number (read-only).

Name

Field/column name (read-only).

Type

Structural type assigned to the field by DataFlex.

There are two scenarios in DataFlex where the overlap field type is used. One is to concatenate several fields and consider them as one field. The other is to create a field from a subset of a field.

The driver treats the first scenario logically and it functions similarly to how native DataFlex treats an overlap. When an overlap field is selected as part of an index to an SQL Table, it may not be created due to the number of segments the index is composed of. These are SQL limitations.

Partial overlap (underlaps) fields are created as a native column only if the field is part of an index, otherwise it is treated the same as the first scenario.

Length

Field length in bytes. Position the cursor on a field length, then click the Length column header to display the Field Changes Pop-up.

Native Type

This is the data type that will be used for this field on the SQL back-end. Choose the desired type using the drop-down list or click the column header and choose the type from the Native Type Pop-up.

Note: Default field mappings can be made in the Tools > Preferences > Field Mappings tab.

AutoInc.

Used to select the auto-increment field. RECNUM is the default auto-increment field.

Index

Main index associated to a field. Position the cursor on a field index, then click the Index column header to display the Field Changes Pop-up.

not NULL

Indicates whether or a null value is allowed in this field. Leave this option unchecked if null values are allowed. SQL databases require that fields specified as NOT NULL are given a value during an insert. The driver automatically inserts a default value if a value is not specified. Fields that are part of an index are always created as not NULL, and Flex2SQL does not allow you to change this because it directly affects FIND performance using indexes based on those fields. You can enter a default value in the panel on the right side of the screen, or by positioning the cursor on a not Null value, and clicking the column header to display the Field Changes Pop-up.

The Fields tab also contains a panel on the right side of the screen that allows you to enter additional information for the selected field.

Default

The default value that will be assigned to a not NULL field.

Note: Global settings for the default values for not NULLL field types are made in the Migration Options Tab.

Relates To

The file and field from a parent table that this field is related to.

Mask

Special data mask applied to the field.

Check Constraint

Allows you to specify the name of a constraint that you may have defined for a particular field in the target/destination server.

Inverse Key

Field name used for the Inverse key.

Field Changes Pop-up

Click the Length, Index, or not NULL column heading in the Fields tab to display the Field Changes dialog box. Allows the default value for not NULL fields, the check constraint, and mask type to be changed.

Indexes Tab

Indexes on the target server are created based on the indexes in the DataFlex data file. These are also the default indexes on the server. Mertech recommends that you keep the indexes the same and make no changes during the first phase of data migration. Working with defaults and allowing Flex2SQL to handle data migration and index creation ensure proper and optimal functioning of the driver. Once you develop a familiarity with SQL databases and the Mertech drivers, you can make modifications in index structures to enhance performance.

Primary Key

The Primary Key tab shows the index number in the DataFlex file and the fields that comprise the index and are used to create a primary key on the target server. By default, Index 1 is selected in a DataFlex file to be the primary key in the table, but the user can change this. For fast and efficient access, it is suggested that the primary key be comprised of columns that give maximum selectivity.

Flex2SQL selects the Inverse Key checkbox in the Primary Key tab and Native Index tab if one segment is DESC or C/S.

Primary Index

RECNUM is the primary index in a DataFlex file. Whenever you perform any operation using RECNUM, the RECNUM index (Index.0) is used. If you are using only the DataFlex database, there is no choice; RECNUM is the primary index. However, with other databases, you can specify any index as the primary index. In ORAFlex, RECNUM, by default, behaves just like the RECNUM field in DataFlex. However, the user can select another index to be the primary index. This is controlled during migration or can be changed after migration in the intermediate file by changing the PRIMARY_INDEX token to an index number specified in the intermediate file.

For ORAFlex, you can select an index created on a non-numeric field to be the primary index. But to make this work, you have to change the definition of the RECNUM field in your .FD file from a numeric to a string data type. For example, if you have a definition like:

#REPLACE VENDOR.RECNUM |FN21,0

In your .FD file, you will have to change this to:

#REPLACE VENDOR.RECNUM |FS21,0

Native Index

The Native Index tab allows you to control which indexes are created on the server. If you do not plan to change your DataFlex program, Mertech recommends that you use the default selections that are presented to you by Flex2SQL. The primary key is not selected because the primary key is always created.

MS SQL Server Tab

This tab allows you to select the database and set migration options that are unique to MS SQL Server databases.

Use Database

Select the database where the converted data will be saved on the back-end table.

Filegroups

An SQL Server database can be partitioned using files and/or filegroups. A filegroup is simply a named collection of individual files grouped together for administration purposes. A file cannot be a member of more than one filegroup. Tables, indexes, text, ntext, and image data can all be associated with a specific filegroup. This means that all their pages are allocated from the files in the filegroup. Log files are never a part of a filegroup.

Note: Microsoft Windows Azure SQL Database does not support filegroups.

There are three types of filegroups:

Primary: The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.

User-defined filegroup: User-defined filegroups are any filegroups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

Default filegroup: One filegroup in each database operates as the default filegroup. When SQL Server allocates a page to a table or index for which no filegroup was specified when they were created, the pages are allocated from the default filegroup. Only one filegroup at a time can be the default filegroup. Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another.

If no default filegroup is specified, the primary filegroup is the default filegroup.

SQL Server 2000 can work quite effectively without filegroups; so many systems do not need to specify user-defined filegroups. In this case, all files are included in the primary filegroup and SQL Server 2000 can allocate data anywhere in the database. Filegroups are not the only method that can be used to distribute I/O across multiple drives.

Members of the db_owner fixed database role can back up and restore individual files or filegroups instead of backing up or restoring an entire database.

With this new feature in Flex2SQL, you can now select the filegroup where you want to place your tables and index files.

Legacy Index Support

This option allows the user to use existing field name formats for inverse keys.

Use Older Name Format for Inverse Keys

If this option is selected legacy name formats are used when defining inverse keys.

  • See Also: Legacy Index Support

Use Regular Save During Migration

This option overrides the default migration mode (bulk insert) that is designed for high-speed migration of data from a DataFlex database SQL Server database. Due to a bug in MS SQL Server, there are some cases where data is not correctly loaded into the table for tables with large number of columns. The bug has been reported to Microsoft.

Select Use Regular Save During Migration to use regular inserts to copy data. This is significantly slower than the default option.

This option is useful if you have problems during the migration. Records are inserted one by one instead of in bulk. This lets you migrate all records that do not have any errors and skip problematic ones. If you are migrating in bulk and one row in a bulk group has problems, the entire bulk will fail to migrate.

Bulk Copy Options

To allow the user more flexibility during the data conversion Flex2SQL now includes the capability of customizing the bulk copy settings for the data migration. The user can specify the number of rows per batch or kilobytes per batch.

Note: Only one value can be specified; either rows or kilobytes.

Rows per Batch:

This value overrides the default Flex2SQL value. This can be valuable to save time during large data migrations.

Kbytes per Batch:

This value overrides the default Flex2SQL value. This can be valuable to save time during large data migrations.

Lock Table:

This option locks the table during the conversion. If selected, no DataFlex operations can take place during the conversion.

Oracle Tab

This tab allows you to select the data and index tablespace, as well as set the table storage options that are unique to Oracle databases.

Data Tablespace

Allows the user to select the name of the Oracle tablespace where the data will be stored once it is converted. The space available in the selected tablespace is displayed.

Index Tablespace

Allows the user to select the name of the Oracle tablespace where the index will be stored once it is converted. The space available in the selected tablespace is displayed.

Legacy Index Support

This option allows the user to use existing field name formats for Inverse Keys.

Use Older Name Format for Inverse Keys

If this option is selected legacy name formats are used when defining Inverse Keys.

  • See Also: Legacy Index Support

Functional Indexes

A functional index is an index defined on the result of a function applied to one or more columns of a single table. Functional indexes can be used to obtain fast access to data based on the result of function calls.

Functional indexes can be used to select a custom sort order. If you select the Custom radio button the associated list box is populated with sort options including e.g., Arabic, ASCII7, Big5, and French.

Use Regular Save During Migration

This option overrides the default migration mode that is designed for high-speed migration of data from a DataFlex database SQL Server database.

This option is useful if you have problems during the migration. Records are inserted one by one instead of in bulk. This lets you migrate all records that do not have any errors, and skip problematic ones. If you are migrating in bulk and one row in a bulk group has problems, the entire bulk will fail to migrate.

Use Storage Clause

Applies to TEXT and BINARY fields when calculating storage requirements.

% of Field Length

DataFlex text and binary fields have to be allocated a “fill” factor indicating what percentage of the DataFlex field size should be used in calculating the table size. This is because DataFlex has a fixed length field size for binary and text fields and Oracle uses a variable length field type. For example, if you have a 4K field size, but expect that most records will contain 1K of data, you should use 25% as the fill factor. This does not affect the length of the field but only the storage calculation

Number of Records

The number of records in the DataFlex table.

Number of Records to use for Empty Files

Flex2SQL uses the current number of records in a table to calculate storage requirements. In the case where there no records in the DataFlex file, a default number of records can be used to calculate these requirements. If you expect the number of records in such a file to be large, then allocate a large number.

  • See Also: Analyze Storage Requirements

Table Extent Information

PCTFREE

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

PCTUSED

Specifies the limit that Oracle uses to determine when additional rows can be added to a cluster’s data block. The value of this parameter is expressed as a whole number and interpreted as a percentage.

Initial Extent

This is the initial size in kilobytes allocated for the new Oracle table on the Oracle server.

Next Extent

The size the Oracle table is increased by when it is full.

MySQL Tab

This tab allows you to select the data and index tablespace and set migration options that are unique to MySQL & MariaDB databases.

Use Database

Select the database where the converted data will be saved on the back-end.

Select Table Handler

Selects the table type or storage engine for the migrated files. MySQL & MariaDB support the following storage engines:

ISAM

The first storage engine created for MySQL. These tables resemble DataFlex files.

MyISAM

The new binary portable storage engine that is used in lieu of ISAM. MyISAM provides high-speed storage and retrieval, as well as full text searching capabilities.

InnoDB

A transaction-safe storage engine that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB also supports row-level locking. To maintain data integrity, InnoDB tables also supports FOREIGN KEY referential-integrity constraints.

Note: Mertech officially only supports the InnoDB table handler.

BDB

Transaction safe tables that support page locking (similar to Oracle).

Merge

A collection of MyISAM tables used as a single table.

Heap

The data in these structures are only stored in memory. This option is only available if AUTO_INCREMENT (RECNUM column) table property is not selected. The HEAP storage engine is also referred to as the MEMORY storage engine.

ISAM, MyISAM, MERGE, and HEAP storage engines handle nontransactional tables. The InnoDB and BDB storage engines provide transaction-safe tables.

  • See Also: Change Table Handler (MySQL/MariaDB)

Table Creation Parameters

Enables and disables access to the parameters below. If this option is not checked the parameters below are not included in the CREATE TABLE statement.

Use Default

When this option is selected, user-entered values for the parameters below are ignored and Flex2SQL creates the table(s) using default values.

Apply Selection to All Tables

Check this option if the updated values should be stored and used for future table conversions.

AVG_ROW_LENGTH

This option is used to pre-allocate space for large tables with variable size records. If an average row length is not specified Flex2SQL uses the value from the DataFlex file property “DF_FILE_RECORD_LENGTH” as its default.

CHECKSUM

This option maintains a checksum for all rows (MyISAM). It also slows the performance when updating but facilitates the search for corrupted tables. This option is turned ON in Flex2SQL by default.

COMMENT

A 60-character text string used to better identity the table and its content. Flex2SQL uses the value from the DataFlex file property “DF_FILE_DISPLAY_NAME” as its default.

MAX_ROWS

This option is used to pre-allocate disk space. It is the maximum number of rows you plan to store in the table. Flex2SQL uses a calculation (MIN_ROWS * 1.5) as its default.

MIN_ROWS

The minimum number of rows stored in the table. Flex2SQL uses the value from the DataFlex file property “DF_FILE_RECORDS_USED” as its default.

PACK_KEYS

This option compresses the indexes saving disk space. Due to its compression mechanism it makes updates slower and reads faster (MyISAM & ISAM). Setting it to 0 disables packing of keys. Setting this to DEFAULT (MySQL 4.0) tells the storage engine to only pack long CHAR/VARCHAR columns. This option is turned ON in Flex2SQL by default.

PASSWORD

Encrypts the .frm file with a password. This option does not apply to the standard MySQL version.

DELAY_KEY_WRITE

This option delays key table updates until the table is closed (MyISAM). This option is turned OFF in Flex2SQL by default.

ROW_FORMAT

Defines how the rows should be stored. Currently this option only works with MyISAM tables. Flex2SQL checks if DataFlex has compressed the table. If compressed it defaults to “Dynamic” otherwise it defaults to “Default”.

Default

The default row format is static unless MySQL or MariaDB encounters columns defined as VARCHAR, BLOB, or TEXT in the table. In that case Dynamic ROW_FORMAT is used.

Static

This is the most secure and fastest format.

Dynamic

Used on tables containing variable length data types.

Compressed

Tables that are compressed take up very little disk space, which minimizes disk usage. Compressed tables are read-only.

Note: Do not select compressed format for tables that require updating.

PostgreSQL Tab

This tab allows you to select the data and the index tablespace and set migration options that are unique to PostgreSQL databases.

Use Database

Select the database where the converted data will be stored on the back-end.

Use Schema

Allows the user to select or enter a new schema (owner) name for the table being converted. If this field is left blank the default schema is used.

Data TableSpace

The tablespace name that will contain migrated data tables. It is advisable that index and data tables be created on two separate tablespaces, residing on separate hard drives.

Index TableSpace

The tablespace where the index files will be created.

OIDS – Object Identifiers of Rows

Object Identifiers of Rows - This optional clause specifies whether rows in the new table should have OIDs (object identifiers) assigned to them. The default is to have OIDs.

Note: If the new table inherits from any tables that have OIDs, then WITH OIDS is forced even if With OIDs is not selected.

WithOIDS

Allows the user to select generation of OIDs for rows of a table. It may be worthwhile to leave this option unchecked for large tables, since it will reduce OID consumption and thereby postpone wraparound of the 32-bit OID counter. Once the counter wraps around, uniqueness of OIDs can no longer be assumed, which considerably reduces their usefulness. The use of WITHOUT OIDS is not recommended for tables not having a primary key, since without either an OID or a unique data key, it is difficult to identify specific rows.

Use Regular Save During Migration

This option overrides the default migration mode that is designed for rapid migration of data from a DataFlex database to a PostgreSQL database. This setting does a direct save, which is not as fast as the default migration option.

This option is useful if you have problems during the migration. Records are inserted one by one instead of in bulk. This lets you migrate all records that do not have any errors, and skip problematic ones. If you are migrating in bulk and one row in a bulk group has problems, the entire bulk will fail to migrate.

DataFlex File Types

CFG file Configuration file. The filelist.cfg file associates each datafile with a unique, internal, DataFlex number.

DAT file Datafile or table (each datafile contains one table).

DEF file Table definition file.

DFENV file Environment file. The dfenv.cfg file contains application specific environment variables.

FD file Field offset definition file. An FD file contains internal definitions for the real name of a datafile and each column in the file.

FLX file A compiled DataFlex program.

INC file An include file. A include file contains a set of programming instructions that are designed to be included in another file.

INT File Intermediate file. Flex2SQL creates an INT file for each migrated table. The INT file contains information required to allow Visual DataFlex to connect to and open the converted SQL tables.

SRC file A file containing DataFlex source code.

SWS file Studio workspace file. Created using Visual DataFlex (VDF).

TD file Temporary cache file created by Flex2SQL. Mertech optimized file opens using just the .INT file, eliminating the need for .TD files. When using the v10.x driver with applications migrated with older drivers that support .TD files, the .TD files are ignored. However, you can turn on .TD file support by adding TableDefinition=TDFile in the mertech.ini file

WS file DataFlex workspace file, created using earlier versions of DataFlex. A workspace is a set of folders in which the DataFlex database and source code are stored.

Troubleshooting

Restoring Codejock Controls to Resolve Flex2SQL GUI Runtime Error

A few customers have reported unexpected problems running the Flex2SQL GUI. Error messages have been observed indicating that an unhandled program error occurred.

CodeJock Error 2.png

The Problem

This problem occurs if VDF17.0 is uninstalled. Required runtime files and Codejock OCX controls, which are used by the Flex2SQL menus, are unregistered when VDF17.0 is uninstalled.

The Solution

Re-register the Codejock controls:

Start a command prompt as an administrator

CD C:Program Files (x86)Mertech Data SystemsDB DriversDataFlexbin

Regsvr32 Codejock.CommandBars.v13.4.2.ocx

Regsvr32 Codejock.ReportControl.v13.4.2.ocx

Note: If you are running a 32-bit version of Microsoft Windows, the Mertech Data SystemsDB DriversDataFlexbin directory is in Program Files instead of Program Files (x86).

Flex2SQL Utility Classic Login Failure

An error message was reported attempting to connect to an SQL server from Flex2SQL Utility Classic.

LoginFailed.png

The following troubleshooting steps are suggested if this problem occurs.

Verify that the required services are running

  1. Start the Control Panel then select Administrative Tools > Services.

  2. The status for the SQL Server <Instance> and SQL Server Browser should be Started.

Check your version of SQL Server Native Client

  1. Start SQL Server Configuration Manager.

  2. SQL Native Client nn Configuration is listed in the left panel.

Note: The Microsoft SQL Server Native Client version can also be found in the registry under HKEY_LOCAL_MACHINE > SOFTWARE > Microsoft

By default the Mertech driver looks for Microsoft SQL Server MSOLEDB Driver and then falls back to an earlier version of SQLNCLI if it is not available.

  • Microsoft SQL Server Native Client 11.0 is installed when you install SQL Server 2016

Check that you are using the correct server name or IP address

If you are using the server name make sure the DNS Manager is able to resolve your server name.

Determine if a firewall is blocking the connection to the server

Examine firewall rules to determine if a connection to the server is allowed. If logging is turned on, examine the firewall the log file.

What’s New in the Flex2SQL Version 16

For a complete description of new features in version 16 refer to the Release Notes.