Primary Key¶
Checked if the selected index is a primary key.
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.
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.
Trademarks used in this document are purely for publication purposes and are the property of their respective owners.
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.
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.
In the Server Type field, select the desired SQL server from the drop-down list.
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.
In the User Name field, type the user name for this session.
In the Password field, type the user password.
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.
Click Connect to log into the server.
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.
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.
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.
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.
Click Attach.
Click OK.
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.
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.
Select your migration preferences, then click OK.
Click Select DF Files on the Filelist dialog box.
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.
Click Convert Database.
The Convert DAT to Table dialog box displays.
Modify the migration options if necessary (for a full description of these options see Convert DAT to Table dialog box).
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.
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.
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.
Connect to the target server (File > Login).
Optionally, set global migration preferences (Tools > Preferences).
Open the DataFlex workspace or filelist (File > Open) and select the file(s) to be migrated.
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.
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.
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.
Display the Loader Options tab and choose settings to be saved in the control file.
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.
The DBA runs the loader.bat file to load the text data.
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
Connect to the target server (File > Login).
Optionally, set global migration preferences (Tools > Preferences).
Open the DataFlex workspace or filelist (File > Open) and select the file(s) to be migrated.
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).
Select Tools > Generate .INT > .INT File from .DAT
This creates the .INT file(s), <filename>.int.
Select Database > Create Text File for server-specific Loader.
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.
Display the Loader Options tab and choose settings to be saved in the control file.
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.
The DBA runs the script to create the new table (filename.sql) and the loader.bat file to load the text data.
Generate the script to analyze the loaded data (Tools > Post Loader Steps).
Note: Only available for PostgreSQL, MySQL, MariaDB and Oracle.
The DBA runs the post loader script.
See Also: High-Speed Script Based Migration - Let Flex2SQL Perform the Tasks
Server-side control scripts must be run before you connect to Flex2SQL for Oracle (ORAFlex). These must be run as a SYSDBA.
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.
In the Server Type field, select Oracle from the drop-down list.
Select SYSDBA from the AutoMode drop-down list.
In the Server Name field, type the name of the server you are logging into.
In the User Name field, type the SYSDBA user name (for example SYS or SYSTEM).
In the Password field, type the user password.
Click Connect to log into the server. Server-side license scripts run automatically.
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.
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.
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.
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.
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.
The Convert DAT to Table dialog box contains tabs to set general conversion options and server specific options.
Radio buttons at the bottom of the dialog box determine which files are created on the server and whether data is migrated.
Both table and indexes are created and records are copied over to the target database.
Both table and indexes are created, but no records are copied over to the target database.
Only the table structure is created. No data is copied. This option is used during High-Speed Script Based Migration.
Only indexes are created.
Buttons at the bottom of the dialog box are used to set preferences, save the fileset and associated settings, and perform the conversion.
View or modify the preferences used during migration. Preferences can also be set from the Tools menu.
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.
Converts all the tables in the filelist or workspace.
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.
Activates contextual help for the Flex2SQL application.
Cancels the conversion and close the Convert DAT to Table dialog box.
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.
Byte length of the record calculated based on DataFlex field types.
Number of fields in the file.
Number of records in the data file.
Number of indexes in the file.
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.
The name of the SQL table to be created.
The name of the .INT file to be created.
The location where the .INT file will be saved.
Determines whether the SQL table uses DataFlex Recnum Table or RowId Table style processing.
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).
Check to enable storage in local cache.
Enter the maximum number of records to be fetched in one network round-trip.
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.
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.
This option allows you to select the system default or to enter your own default value for NOT NULL fields.
ASCII fields have default value of a single space.
Numeric fields have a default value of 0.
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
This option creates another column with information for the index when there is a partial overlap. This option improves performance.
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.
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).
Field/column name (read-only).
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.
Field length in bytes. Position the cursor on a field length, then click the Length column header to display the Field Changes Pop-up.
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.
Used to select the auto-increment field. RECNUM is the default auto-increment field.
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.
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.
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.
The file and field from a parent table that this field is related to.
Special data mask applied to the field.
Allows you to specify the name of a constraint that you may have defined for a particular field in the target/destination server.
Field name used for the Inverse key.
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 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.
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.
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
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.
This tab allows you to select the database and set migration options that are unique to MS SQL Server databases.
Select the database where the converted data will be saved on the back-end table.
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.
This option allows the user to use existing field name formats for inverse keys.
If this option is selected legacy name formats are used when defining inverse keys.
See Also: Legacy Index Support
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.
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.
This value overrides the default Flex2SQL value. This can be valuable to save time during large data migrations.
This value overrides the default Flex2SQL value. This can be valuable to save time during large data migrations.
This option locks the table during the conversion. If selected, no DataFlex operations can take place during the conversion.
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.
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.
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.
This option allows the user to use existing field name formats for Inverse Keys.
If this option is selected legacy name formats are used when defining Inverse Keys.
See Also: Legacy Index Support
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.
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.
Applies to TEXT and BINARY fields when calculating storage requirements.
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
The number of records in the DataFlex table.
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
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.
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.
This is the initial size in kilobytes allocated for the new Oracle table on the Oracle server.
The size the Oracle table is increased by when it is full.
This tab allows you to select the data and index tablespace and set migration options that are unique to MySQL & MariaDB databases.
Select the database where the converted data will be saved on the back-end.
Selects the table type or storage engine for the migrated files. MySQL & MariaDB support the following storage engines:
The first storage engine created for MySQL. These tables resemble DataFlex files.
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.
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.
Transaction safe tables that support page locking (similar to Oracle).
A collection of MyISAM tables used as a single table.
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)
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.
When this option is selected, user-entered values for the parameters below are ignored and Flex2SQL creates the table(s) using default values.
Check this option if the updated values should be stored and used for future table conversions.
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.
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.
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.
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.
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.
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.
Encrypts the .frm file with a password. This option does not apply to the standard MySQL version.
This option delays key table updates until the table is closed (MyISAM). This option is turned OFF in Flex2SQL by default.
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”.
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.
This is the most secure and fastest format.
Used on tables containing variable length data types.
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.
This tab allows you to select the data and the index tablespace and set migration options that are unique to PostgreSQL databases.
Select the database where the converted data will be stored on the back-end.
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.
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.
The tablespace where the index files will be created.
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.
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.
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.
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.
A few customers have reported unexpected problems running the Flex2SQL GUI. Error messages have been observed indicating that an unhandled program error occurred.
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.
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).
An error message was reported attempting to connect to an SQL server from Flex2SQL Utility Classic.
The following troubleshooting steps are suggested if this problem occurs.
Verify that the required services are running
Start the Control Panel then select Administrative Tools > Services.
The status for the SQL Server <Instance> and SQL Server Browser should be Started.
Check your version of SQL Server Native Client
Start SQL Server Configuration Manager.
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.
For a complete description of new features in version 16 refer to the Release Notes.