There are the great number of options in the tool which helps to tune the migration process in the way that specific project requires. Some of them you can tune at the 'Conversion Options' page of the tool. Please click on specific group of objects or specific object on the left pane to tune the migration.

• Database Options
• Table Options
• Single Table Options

To tune database options need to click on 'Database' at the left pane of 'Specify DDL and Data Options' Page. There are options for objects DDLs. All changes will be applied for the whole migration scope. If you check the “Expert Mode” box in the lower left corner, additional tabs and options for expanded settings will be activated.

Options from DDL section of database options help to tune the migration of objects definitions.

This option controls whether the DROP statement is added to all the scripts or not. If the option is checked, the DROP TABLE statement is added to each file containing the DDL statement for the table. Also, where possible, for other database objects there will be created CREATE or REPLACE statement instead of just CREATE. For example for Oracle instead of:

CREATE PROCEDURE sp_proc
...

will be created

CREATE or REPLACE PROCEDURE sp_proc
...

NOTICE: If option Generate DDL is not checked this option is disabled.

If Yes is selected for “Remove schema names in SQL objects” option, the schema (owner) name is not added. This allows you to use the default schema (owner) name for the user who loads the DDL. This value is selected by default.
If No is selected for “Remove schema names in SQL objects” option, the schema (owner) name is added before the object name in each DDL statement created.
If Default is selected for “Remove schema names in SQL objects” option, all schema names from the source database will be saved, and the default schema names for the database will be mapped (for example, for the SQL Server - PG direction, the dbo schema will be converted to public).

This option is used to generate the sqlways.trc file in the export directory containing more detailed information regarding the export process. This information is primarily used by Ispirer developers to fix the export issues that may arise.

NOTICE: If this option is checked each SQL with the objects creation statements contains the commented source SQL code of the object in the same file.

To tune tables options need to click on 'Tables' at the left pane of 'Specify DDL and Data Options' Page. There are options for objects DDLs and for objects DDLs. All changes will be applied for the all tables, specified from a migration scope.

• DDL Options
• Data Options
• Single Table Options

• Global Data Type Mapping
• Generation Options
• Table Options

This feature allows you to change the default conversion for the appropriate datatypes all over the database. To set up the required conversion please choose “Tables” node in the tree on the left, then “DDL options” tab on the right and “Global Datatype Mapping” combobox. Please see the screen-shot below for the reference:

NOTICE: Local Data Type Mapping overrides the Global Data Type Mapping for the appropriate column.

If this option is checked, a DDL statement will be created for every table. If not - data import scripts and SQL scripts for the remained objects like views, procedures and etc. will be generated. If you check the “Expert Mode” box in the lower left corner, additional tabs and options for expanded settings will be activated.

This option controls the way identity values are generated in a target Oracle database. This option may be set to the values below:

• “ALWAYS” - values in IDENTITY columns will be generated always automatically. And in table definition will be used “generated always as IDENTITY” option.
• “ON NULL” - values in in IDENTITY columns will be generated only instead of nulls. And in table definition will be used “GENERATED BY DEFAULT ON NULL AS IDENTITY” option.
• “DEFAULT” - values in in IDENTITY columns will be generated by default. And “GENERATED BY DEFAULT AS IDENTITY” will be used in table definition in Oracle. If you check the “Expert Mode” box in the lower left corner, additional tabs and options for expanded settings will be activated.

NOTICE: This option works only for Oracle as a target database.

If you check the “Expert Mode” box in the lower left corner, additional tabs and options for expanded settings will be activated.

Change Column Name Case

Defines the case of column names.

Use Constraint names of the source database

This option will use the same constraint names as in the source database. Otherwise constraint names will be skipped.

Convert default values

This option converts default values for columns. If this option is disabled, conversion of default values will be skipped.

Convert identity columns

This option converts identities. If this option is disabled, conversion of identities will be skipped.

Force NOT NULL constraints for columns that make up a primary key

This option is used to set the NOT NULL constraints in the CREATE TABLE statement for columns making up a primary key. Some databases ( Sybase, Access e.g.) allow to not specify the NOT NULL constraints for primary key columns explicitly when creating a table and change columns to NOT NULL when adding a primary key. Other databases (IBM DB2, Microsoft SQL Server, MySQL e.g.) require primary key columns to be created with NOT NULL constraints before adding a primary key.

Remove NOT NULL constraints from all columns except primary key columns

If checked, NOT NULL constraints for all columns except primary key columns in the target database will be removed.

Convert CHECK constraints

This option defines whether check constraints should be converted. If unchecked, check constraints will be skipped.

Convert Unique constraints

This option defines whether unique constraints should be converted. If unchecked, unique constraints will be skipped.

Convert cascade referential integrity constraint to no action

If checked all Foreign keys with CASCADE action would convert to Foreign Keys with NO ACTION action.

Convert indexes

This option defines the types of the indexes that will be converted by SQLWays tool. ALL – means that all indexes will be converted. Unique – means that only unique indexes will be converted. None – means that all the indexes will not be converted.

Import Order For Tables

In the Import Order for tables, select the order of table constraints, importing data and indexes creating. Available values are:
“Clean” (Create constraints before importing data);
“Fast” (Import data before creating constraints and indexes, recommended for better performance in MySQL);
“Ready” (Create constraints and indexes before importing data).

Depending on this option SQLWays Wizard creates different command files to import tables. Default value is Clean.

• General
• Formats
• Files

You can use the “Export Data” option to specify whether or not to export data during the migration process.

Choose “Tables” in the tree on the left, “Data Options” tab, “General” combobox.

If option is checked, all data from the tables is exported for the subsequent import into the target Database.

If you need to migrate a certain number of rows, you can control it using “Start row” and “Number of rows” option to control number of exported rows.

“Start row” option controls start row position for data extraction.

If you need to migrate a certain number of rows, you can control it using “Start row” and “Number of rows” option to control number of exported rows.

You can specify required number of rows for migration in “Number of rows” option. If this option is empty, all available data rows will be exported.

This option controls the way boolean values will be converted in target database.

Possible values “Y/N”, “T/F” or “1/0”. If set “Y/N” will be used “Y” or “N” values, if set “T/F” will be used “T” or “F” values, if this option is set to “1/0”, then “1” and “0” will be used for boolean values.

Source database boolean representation is used by default.

By default the Divide data file into parts of check-box is is not checked, so the text file with exported table is not divided into parts. In this case the sqlways.log file contains the record like:

Output data file: C:\Program Files\Ispirer\SQLWays 3.7\Project\gsd_locker_assignment.txt

If the Divide data file into parts of check-box is checked it is possible to define the largest possible file size for text files in the edit box (2G is specified by default). G, M, K postfixes can be specified to define Gigabytes, Megabytes and Kilobytes. When the file size value specified is exceeded SQLWays Wizard divides the text file into parts. All parts are named the same except for the number at the end of the name, like:

table_name.txt
table_name2.txt
… 
table_nameN.txt

NOTICE: The minimal part size must exceed the maximum row size (without LOB data).

In case when splitting is specified the sqlways.log file contains:

Data file was divided into 2 files of 1G:
 Output data file: C:\Program Files\Ispirer\SQLWays 3.7\Project\gsd_locker_assignment.txt
 Output data file: C:\Program Files\Ispirer\SQLWays 3.7\Project\gsd_locker_assignment2.txt

If you need to perform the conversion of tables with the same data set and you have already created data files, here you can switch of the re-creation of data files. Default value is 2G.

This option specifies the format of the datetime datatypes in the text files.

Datetime data consist of valid date and time combinations. Depending on the database, this is the DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase.

A datetime format can be composed of one or more datetime format elements as listed below.

• YYYY - 4-digit year
• YY - Last 2 digits of year
• MM - 2-digit numeric abbreviation of month (01-12)
• MON - 3-symbol abbreviated name of month (JAN, FEB etc)
• DD - 2-digit day of month (01-31)
• HH - 2-digit hour of day (01-12)
• HH12 - 2-digit hour of day (01-12)
• HH24 - 2-digit hour of day (00-23)
• MI - 2-digit minute (00-59)
• SS - 2-digit second (00-59)
• F - Fraction of second. The number of F symbols represents the precision. For example, FFF for accuracy to the milliseconds and FFFFFF for accuracy to the microseconds.

For example, to get datetime values like 21-DEC-2002 21-21-00 set the datetime format DD-MON-YYYY HH24-MI-SS.

If no value is specified in this option, the default datetime format is used. By default, ODBC drivers convert datetime values using the ISO format YYYY-MM-DD HH24:MI:SS.FFF (the part FFF depends on database).

Specifies that the datetime columns can contain the fraction part in the text file. Datetime data consist of valid date and time combinations.

Time can contain a fraction part. For example, IBM DB2 TIMESTAMP data type can contain microseconds, Microsoft SQL Server and Sybase DATETIME can keep milliseconds.

Possible values - Yes, No. Yes means that datetime values can contain the fraction part in the text files, No means that if the fraction part exists, it is not saved in the text file.

The default value is Yes unless the target database is Oracle.

If the target database is Oracle and the datetime datatypes are converted to the Oracle DATE data type, the default value is No. The Oracle DATE data type doesn't support fractional seconds.

If the target database is Oracle and the datetime datatypes are converted to the Oracle TIMESTAMP data type, the default value is Yes. The Oracle TIMESTAMP data type supports fractional seconds.

Note. This option is ignored if any value is specified in the option Use datetime format.

This option specifies the format of the date datatypes in the text files.

A date is a three-part value (year, month and day). Not all databases have a data type that allows you to keep date values without a time part.

For example, such data type exists in IBM DB2 and MySQL (the DATE data type).

A date format can be composed of one or more date format elements as listed below.

• YYYY - 4-digit year
• YY - Last 2 digits of year
• MM - 2-digit numeric abbreviation of month (01-12)
• MON - 3-symbol abbreviated name of month (JAN, FEB etc)
• DD - 2-digit day of month (01-31)

For example, to get date values like 21-DEC-2002, set the datetime format DD-MON-YYYY.

If no value is specified in this option, the default date format is used. By default, ODBC drivers convert date values using the ISO format YYYY-MM-DD.

This option specifies the format of the datetime datatypes in the text files.

Datetime data consist of valid date and time combinations. Depending on the database, this is the DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase.

A datetime format can be composed of one or more datetime format elements as listed below.

• HH - 2-digit hour of day (01-12)
• HH12 - 2-digit hour of day (01-12)
• HH24 - 2-digit hour of day (00-23)
• MI - 2-digit minute (00-59)
• SS - 2-digit second (00-59)
• F - Fraction of second. The number of F symbols represents the precision. For example, FFF for accuracy to the milliseconds and FFFFFF for accuracy to the microseconds.

For example, to get date values like 20:35:59, set the datetime format HH24:MI:SS.

If no value is specified in this option, the default date format is used. By default, ODBC drivers convert date values using the ISO format HH24:MI:SS.

This option controls the conversion rule for numeric data. If Yes is specified, numeric data (REAL, FLOAT, DOUBLE datatypes) are converted to the text representation by ODBC driver. Otherwise, SQLWays Wizard performs the conversion. The default is Yes. Possible values - Yes, No.

This option controls allocating space for sign in data file when target migration direction is Oracle.

The option is available only if OUTFORMAT=FIX.

If it is set to “Yes”, the tool increases POSITION parameter for TINYINT, SMALLINT, INTEGER and BIGINT data types to be able to store sign. Otherwise, “No” option value generates POSITION in accordance with original data type length.

Possible values - “Yes”, “No” or Empty.

Default value - “Yes”.

Choose “Tables” in the tree on the left, “Data Options” tab, “Format” combobox.

Choose “Tables” in the tree on the left, “Data Options” tab, “Files” combobox.

The option controls output text format in which data will be extracted from source database for further importing into target database. Below output text formats are allowed. * Column delimited (CSV) * Fixed length: * TAB delimited * INSERT statements * XML * Btrieve data file * PC/IXF

The tool identifies more suitable out text format for specific target DBMS. For most databases data is extracted into *.TXT files.

The option controls column delimiter in files with data. By default comma “,” delimiter is used.

This option specifies the number of blanks that are placed between columns for FIX (fixed length) output format. The default value is 1.

This option is only valid for INS (INSERT statements) output format and is used to generate a COMMIT statement after the specified number of INSERT statements is generated. The default value is 0 (no commits are generated).

The option specifies line delimiter for files with data. By default carriage return (CR) is used.

Specifies a character string that replaces newline characters in the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix. Character columns like CHAR and VARCHAR e.g. can contain newline characters in the data. By default, SQLWays saves them in the export files. Using this option you can specify a string that will replace newline characters. For example, to replace newlines with zzz, set REPLACE_NEWLINE=zzz, to replace newlines with a blank, set REPLACE_NEWLINE=” ”. To specify the hexadecimal value of a character use the following syntax: 0xhh. For example, 0x2c specifies a comma character. Non printing characters like tab, carriage return and new line can be represented by \t, \r and \n accordingly. To specify a backslash character use '\\'. You can combine different character representations to specify the string. For example, REPLACE_NEWLINE=¦¦¦0x2c0x2c¦¦¦ When you need to remove newlines from the data, use the REMOVE_NEWLINE option. The REPLACE_NEWLINE option is ignored when the REMOVE_NEWLINE option is set to Yes (see below). In most cases, when you need to save newline characters in the data you have to change the line delimiter using the /LDEL option. This makes possible properly processing rows by import/load utilities.

The option controls whether newline characters should be removed or no. If “Yes” is specified, newline characters are removed from the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix. Character columns like CHAR and VARCHAR e.g. can contain newline characters in the data. By default, SQLWays Wizard saves them in the export files. Using this option, you can specify that newline characters be removed from the data. The default is No. Possible values - “Yes”, “No”. If you need to replace newline characters with another string, use the REPLACE_NEWLINE option (see earlier). In most cases, when you need to save newline characters in the data, you have to change the line delimiter using the /LDEL option. This makes possible properly processing rows by import/load utilities.

This option controls if resolving delimiter conflicts should be done during data export. It is helpful when source data contains the same symbols as used as delimiter. If delimiter conflicts are not resolved, target database will not be able to import data correctly. If resolving delimiter conflicts option is set into “Yes”, additional files with conflict data are created. In these files specific delimiters are used to demarcate each row and each column. By default “Yes” value is used.

The option controls if columns names should be add before data in the first row of data file. By default “No” value is used.

Using this text-box you can specify the path to the location of the files, that will contain the <acronym title="Large OBject">LOB</acronym> data for load into the Oracle or IBM DB2 databases after the export.

In the <acronym title="Large OBject">LOB</acronym> directory box, enter the path or click (…) to indicate the target folder for the <acronym title="Large OBject">LOB</acronym> data objects (large objects such as <acronym title="Character Large OBject">CLOB</acronym> and <acronym title="Binary Large OBject">BLOB</acronym> data types in Oracle, IBM DB2; TEXT and IMAGE data types in Microsoft SQL Server, Sybase; MEMO data types in Access and FoxPro). For each <acronym title="Large OBject">LOB</acronym> value, SQLWays Wizard creates one file in the <acronym title="Large OBject">LOB</acronym> directory, specifying the file name in the ASCII file containing the table rows.

The default value is the lob sub-directory of the export directory.

This option controls the LOB data storage in the output file(s). If this option is specified the data of the LOB columns (CLOB, BLOB, TEXT, IMAGE and MEMO) is written inside the text file.

This option influences the LOB values storage when the target databases are Oracle and IBM DB2. As the default for these databases each LOB value is written into a separate file. This eliminates the risk of misinterpreting the data during the load into Oracle using SQL Loader and IBM DB2 using IMPORT/LOAD utilities.

This option doesn't influence the LOB data storage for the target databases, like: Microsoft SQL Server, Sybase, MySQL or Pervasive SQL. For these databases the LOB data is always written inside the text file, as data load utilities and commands like BCP for Microsoft SQL Server and Sybase, LOAD DATA INFILE command for MySQL and BUTIL utility for Pervasive. SQL don't have the option to load the data into one of the columns in the table from a separate file.

• DDL Options
• Data Options

• Data Type Mapping
• Column Name Mapping

This feature allows you to change the default conversion of the datatype for the appropriate column. To set up the required conversion please choose the table name, where you would like to make changes, in the tree on the left, then please click the “DDL options” tab on the right and choose “Data Type Mapping” instead of “Column Name Mapping” in the combo-box. For the reference please see the screen-shot below:

Once mapping is specified, it is saved in sqlways.xml file, so the mapping can be further used in conversion using the batch mode.

NOTICE: Local Data Type Mapping overrides the Global Data Type Mapping for the appropriate column.

At this tab you can also select the list of columns, which should be migrated to target database. To exclude the column from target table you need just switch off the checkbox for column:

Allows to change target names of the columns for the specific tables . Choose table that you want to change data type in the tree on the left, “DDL options” tab, “Column Name Mapping” combobox.

Once mapping is specified, it is saved in the sqlways.xml file, so the mapping can be further used in batch mode. Also you could manually create xml file and use it in batch mode.

This option is useful when you need to migrate only a piece of data or a data cut and you know the conditions, how this piece of data can be defined. So this feature allows to control the data that should be migrated. And user can set such conditions for each table if required.

To use this feature please:

1. Click on a required table
2. Go to the “Data” tab
3. Please check “Condition” option on this tab
4. In the text box provide conditions like it can be done in WHERE clause, using the sql syntax of your source database.

For the reference please see the screen-shot below:

Next Page: Summary Page
Previous Page: Objects Selection

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com