SQLines Data Tool - Configuration File Reference

You can run SQLines Data tool from a command line, and this article describes the configuration file options. Additionally, you can specify options in Command Line.

The default configuration file is sqldata.cfg located in the current directory. You can use -cfg command line option to specify another location and file name for the configuration file.

All configuration file options can be specified in the command line as well.

Note. Please see sqldata.cfg supplied with the tool to see the full list of available options.

DDL Options

DDL (schema) migration options.

-ddl_tables

When -ddl_tables=yes the tool executes DDL statements defined by -topt command line option (CREATE; DROP and CREATE TABLE, or TRUNCATE TABLE statements). When no is specified the tool does not perform any DROP, CREATE, TRUNCATE statements for tables.

Default is yes.

-constraints

When -constraints=yes the tool migrates table constrains (primary and unique keys, check constraints, foreign keys).

Default is yes. It is possible to set -ddl_tables=no and -constraints=yes to migrate constraints only for existing tables.

-indexes

When -indexes=yes the tool migrates table indexes. Note that some indexes are defined by constraints (like primary or unique keys) and they will not be migrated by this option.

Default is yes. It is possible to set -ddl_tables=no and -indexes=yes to migrate indexes only for existing tables.

-object_name_case

When -object_name_case=lower the tool converts object names (tables i.e.) to lower case in all generated and executed DDL statements. When -object_name_case=upper the conversion to upper case is performed.

By default the object names case of the source database is used.

-column_name_case

When -column_name_case=lower the tool converts column names to lower case in all generated and executed DDL statements. When -column_name_case=upper the conversion to upper case is performed.

By default the column names case of the source database is used.

-column_name_quotes

Set characters to quote column identifiers in SQL scripts. For example, specify ”” for double quotes (or \”\” if you use the option in command line).

-object_name_quotes

Set characters to quote object identifiers (schemas, table etc). For example, specify ”” for double quotes (or \”\” if you use the option in command line).

Data Options

Data transfer options.

-fetch_lob_as_varchar

When SQLines Data transfers LOB values it uses LOB API functions to read and write LOB data in parts as the maximum size of a LOB column in the table is unknown. Using LOB API functions significantly reduces the transfer performance even if the actual data in LOB columns do not have large size (i.e. 10-30 KB).

When you set -fetch_lob_as_varchar=yes, SQLines Data still creates LOB columns in the target database, but transfers data as regular VARCHAR columns without using LOB API. This can increase performance by 3x - 10x.

Set this option only if you know that the data in LOB columns do not exceed 32,700 bytes, otherwise a data truncation error raises.

The default value is no that means LOB API is used to transfer LOB data.

MySQL Options

Options applied when MySQL is the source or target database.

-mysql_set_foreign_key_checks

When a value is set for the -mysql_set_foreign_key_check option, each session executes the following statement after establishing the connection:

  SET FOREIGN_KEY_CHECKS=value

If -mysql_set_foreign_key_check=0 is set, each session executes SET FOREIGN_KEY_CHECKS=0 that

  • Allows creating a foreign key constraint even if the parent table does not exist
  • Does not check key values when a foreign key is created

This can significantly increase the migration performance as MySQL requires significant time (often much more than data transfer time) to check foreign keys, but inconsistent data can be loaded into tables that can violate referential constraints.

Informix Options

Options applied when Informix is the source database.

-informix_client_locale

Informix ODBC driver ignores CLIENT_LOCALE options set in SETNET32 and environment variables, so you have to set the -informix_client_locale option to specify which client locale to use for the connection to Informix.

If you do not set option, and the database locale and your default client locale is difference, you can get conversion errors:

21005 [Informix][Informix ODBC Driver]Inexact character conversion during translation.

Example:

  -- CLIENT_LOCALE=en_US.819 will be set for the connection
  -informix_client_locale=en_US.819