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 (schema) migration options.

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.

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.

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.

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.

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.

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

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

Define the start length for CHAR(n) columns to be converted to VARCHAR(n). For example, if -char_to_varchar_length=255 then all CHAR columns with the length equal or exceeding 255 are converted to VARCHAR.

You can use this option to solve "Table exceeds the maximum allowable table row size of 8060 bytes" errors when migrating to SQL Server.

Set the start value for IDENTITY (AUTO_INCREMENT) columns. By default, the start value is taken as the next value for the corresponding source table.

The number of restart attempts for DDL statements that failed with a deadlock error:

• SQL Server: “Transaction (Process ID <num>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
• MySQL, MariaDB: “Deadlock found when trying to get lock; try restarting transaction”

Default value is 10.

Data transfer options.

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.

Options applied when MySQL is the source or target database.

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.

Options applied when Microsoft SQL Server is the source or target database.

When exporting data from a SQL Server database, use Unicode for CHAR and VARCHAR columns, default is yes.

If no is specified, the tool will use the database collation, but note that even if the database collation includes _UTF8 in its name the data may be still converted to single-byte client locale, so you can see '???' characters.

Options applied when PostgreSQL is the source or target database.

If -pg_use_identity=always is specified (this is the default), IDENTITY columns are migrated to GENERATED ALWAYS AS IDENTITY column property.

  CREATE TABLE tab
  (
     id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    ...
  );

If -pg_use_identity=default is specified, IDENTITY columns are migrated to GENERATED BY DEFAULT AS IDENTITY column property.

  CREATE TABLE tab
  (
     id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    ...
  );

If -pg_use_identity=no is specified, IDENTITY columns from other databases are migrated to a SEQUENCE object and DEFAULT NEXTVAL('sequence') column property.

  CREATE TABLE tab
  (
     id BIGINT NOT NULL,
    ...
  );
 
  CREATE SEQUENCE tab_seq START WITH 1 INCREMENT BY 1;
  ALTER TABLE tab ALTER COLUMN id SET DEFAULT nextval('tab_seq');

PostgreSQL does now allow characters with zero code in character (non-binary) data types (CHAR, VARCHAR and TEXT). See Zero character code in CHAR or VARCHAR for more details.

If the source value is a string (non-binary) -pg_zero_char_replace option specifies the replacement character (decimal ASCII code) i.e 32 for a blank. Use an empty value to prevent any replacement and leave the zero characters as is.

Options applied when Informix is the source database.

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:

Example:

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