This is an old revision of the document!


Table of Contents


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

Advanced Button

Oracle

Options on the “Advanced” tab for Oracle as a target database are divided into sections:

  • SQL Loader Options - includes options for sqlldr.exe utility
  • SQL Plus Options - includes options for sqlplus.exe utility
  • Tablespaces Options - includes options for tablespaces conversion
  • On The Fly Data Migrator Options - includes options for On The Fly Data Migrator tool

SQL Loader Options

Rows

Identifies the number of rows you want to read from the datafile before a data save for Direct path loads. The default is to read all the rows and save data at the end of the load.

Executable

The name of the executable module of the Oracle SQL Loader. The default value is sqlldr.exe.

Data Option

Specifies loading option in the generated SQL Loader control file. Possible values: Insert, Append, Replace, Truncate. The default value is Insert.

Max Errors

Defines maximum number of errors that are allowable after which the loader process exits. When this option is empty will be used default value that is equal to 50.

Bind Size

The BINDSIZE parameter specifies the maximum size (in bytes) of the bind array.

Read Size

The READSIZE parameter allows you to specify (in bytes) the size of the read buffer, if you choose not to use the default.

Stream Size

The STREAMSIZE parameter specifies the size (in bytes) of the data stream sent from the client to the server.

Rows

Identifies the number of rows you want to read from the datafile before a data save for Direct path loads. The default is to read all the rows and save data at the end of the load.

Character Set

This option defines the encoding that will be used to store the extracted data in the files in export folder and then load this data into Oracle database.

Length Semantics

This option controls length semantics for character data type columns in Oracle. Possible values - “CHAR”, “BYTE”.

Use Direct Path Load

Specifies the Oracle SQL Loader method to use either conventional or direct path. Possible values - Yes, No. “Yes” specifies a direct path load. “No” specifies a conventional path load.

Unrecoverable

If check data load process will be performed in the UNRECOVERABLE mode.

Use DECODE For Empty CHARs

Specifies generating DECODE function in the SQL Loader control file for CHAR NOT NULL columns for fixed-length text file. Possible values - Yes, No. The default value is “No”. If you want to load fields that contain only blanks into CHAR NOT NULL columns and the text file has the fixed length format, you have to use DECODE function, because SQL Loader treats blanks as NULLs. Set this option to “Yes” and SQLWays will generate DECODE function to convert NULLs to blanks.

SQL Plus Options

Executable

The name of the executable module of the Oracle SQL Plus. The default value is sqlplus.exe.

Generate The EXIT Command

If checked, the SQL Plus EXIT command is generated at the end of DDL scripts. This allows to execute DDL scripts from multiple files in batch mode.

Use The TO_DATE Function

If checked, the TO_DATE function with the corresponding date format is generated for DATE columns in SQL INSERT statements for SQL Plus. This allows to insert rows that contains DATE columns when the date format differs from the default date format for Oracle database. If unchecked, the TO_DATE function is not generated in SQL INSERT statements. Possible values - Yes, No. The default value is “Yes”.

Tablespaces Options

Tables

Defines tablespace name for tables.

Indexes

Defines tablespace name for indexes.

Use Tablespace Names From Source Database

If checked, will be used tablespace name from source database for tables and indexes.

Ispirer On The Fly Data Migrator

This section allows you to specify the settings for Ispirer On The Fly Data Migrator.

BIN directory

This text-box allows you to specify the path to the folder containing the ISOFDM.exe tool. Usually the path specification is not required as SQLWays automatically searches for the BIN directory. If the text-box is empty, first it searches the PATH variable, then it searches the registry. If it failed to find the path to the ISOFDM.exe, no path is added to the command and system may return the error on the command execution.

Read Size

This text-box allows you to specify the number or rows of data that is selected from source database in one iteration.

Commit Size

This text-box lets you specify the number of rows of data after which a Commit is initiated during the data load.

Bulk Size

This text-box lets you specify how many rows of data will be included into one BULK INSERT command to perform the load of the data into the target database. This allows to enhance the insert speed as it's far faster than initiating an insert of each row of data into table. If BULK INSERT is specified as 0 then insert of data into a target table is performed for each row.

Character set

Specifies how to interpret the data stored in the memory to be loaded to the database. If no character set is not specified then the default target database character set is used. You can enter a character set manually:

Trace level

Specifies the level of trace information.

0 - for saving log
1 - for saving errors
2 - for saving warnings
3 - for saving detailed info
4 - for saving debug info

Truncate target tables

This option allows to specify whether to perform TRUNCATE TABLE before import of the data into a table.

By default the Truncate target tables check-box isn't selected.

Disable table triggers

This option allows to specify whether to DISABLE ALL TRIGGERS for target table before import of the data.

By default the Disable table triggers check-box is selected.

Microsoft SQL Server

FILEGROUPS Section

Tables

This option defines FILGROUP for tables.

Indexes

This option defines FILGROUP for indexes.

Use Separate Filegroups

Use Filegroup Names From Source Database

If checked, will be used filegroup names from source database for tables and indexes.

Common Options

Max Errors

Specifies the maximum number of errors that can occur before the BCP utility is canceled. Each row that cannot be copied by BCP is ignored and counted as one error (Parameter -m of BCP). The default value is 10.

Batch Size

Specifies the number of rows per batch of data copied. By default, bcp copies n rows in one batch, where n is equal to the batch size. Batch size applies only when you are bulk copying in; it has no effect on bulk copying out. The smallest number bcp accepts for batchsize is 1.

Hint

This option specifies the hint or hints to be used during a bulk import of data into a table in MSSQL Database. All the possible hints are supported in our toolkit. More information about the hints supported by MSSQL BCP utility you can find here: MSSQL Server BCP Hints.
Possible values: Hints supported by MSSQL Server.

Codepage

Specifies a code page of the data in the data file for the BCP utility (Parameter -C of BCP). The following values can be specified for the CODE_PAGE option:
* ACP - ANSI/Microsoft Windows (ISO 1252).
* OEM - Default code page used by the client. This is the default code page used by bcp if -C is not specified.
* RAW - No conversion from one code page to another is taking place.
* <value> - Specific code page number, for example, 437.

Quoted Identifier

Case Sensitive Collation

This option check object names case. If this option is set to “Yes”, our tool will check the object names taking into account name case. If two objects have the same names and they are written in the same way, for example 'Tab' and 'Tab', in that case suffix will be added to one of the objects. If there are two objects with the same names, but they are written in different manner, for example 'Tab' and 'TAB', in that case no suffix will be added, as this option is set to “Yes”.

Use Active Directory

This option switches authentication mechanism to use ACTIVE DIRECTORY authentication. Thus, if this option is set to “Yes”, option -G will be added in the commands to SQLCMD and BCP utilities. The -G switch requires at least sqlcmd version 13.1.

MySQL

Table type

This option allows to specify the type of the tables created in MySQL database.

MySQL supports two different kinds of tables: transaction-safe tables (InnoDB and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM). For example, when InnoDB is selected, SQLWays generates the TYPE=InnoDB clause in the CREATE TABLE statements for MySQL, like:

CREATE TABLE customer
 (
  id INT,
  name VARCHAR(30)
 ) TYPE=InnoDB;

Data load option

Data load option combo-box contains the following values: Default, Replace and Ignore. Each of them specifies the behavior of the LOAD DATA INFILE command on the data load into the primary or unique key columns in the table.

  • Default - means that the LOAD command rises an error if the key with the same value already exists in the table. When Default is specified in the Wizard nothing is added to the LOAD command syntax.
  • Replace - means that the LOAD command replaces the rows of data with the new rows where the primary or unique keys match. The REPLACE keyword is added to the LOAD command syntax.
  • Ignore - means that the LOAD command doesn't insert the rows of data where the primary or unique keys match the existing in the table. The IGNORE keyword is added to the LOAD command syntax.

Character Set

Specifies for the loader how to interpret the data stored in the txt file to be loaded to the database. If no character set is specified then the default target database character set is used.

You can choose a character set from the list or enter it manually:

Import from client

This option lets specify whether the data files reside on the local computer or on the MySQL server.

By default the Import from client check-box is selected, so the LOCAL keyword is added for the MySQL LOAD DATA INFILE command that is used for importing data to MySQL. When LOCAL is specified, data files must reside on the client host.

If the Import from client check-box is not selected, the LOCAL keyword is not added to the command and data files must reside on the server.

Preserve comments

By default the Preserve comments check box is not selected. If checked, this option adds to mysql.exe command “–comments”. By default command “–skip comments” used in mysql.exe

Create Condition Handler

This option was created to emulate the Transact-SQL behavior inside the MySQL stored procedures and functions when the error in one of the statements inside the procedure or function body arises.

By default the Create condition handler check-box is selected. So, SQLWays adds the CONDITION HANDLER to each “BEGIN…END” block that handles the error arose without terminating the procedure or function execution. The errors are hidden in this case.

When the Create condition handler check-box is unchecked no CONDITION HANDLER is added and the first error encountered during the procedure or function run terminates the procedure or function execution. The error message is returned after the procedure terminates.

PostgreSQL

Tablespaces

In PostgreSQL tables and indexes can be stored in separate tablespaces. In SQLWays you can specify the tablespace for Tables or Indexes in the target PostgreSQL database, or Use tablespace names from source database in the Tablespaces section of the Advnaced page.

Create tables with OIDS

OID column is a system column created for each table. It contains the unique identifier for the row of data in the database and can be used as a unique pointer to the exact record in the database. As the default the check-box is checked. This means that all the CREATE TABLE statements will be created with the WITH OIDS option.

Teradata

Fastload Options

Use Fast Load

“Use fast load” option allows to use fastload.exe utility to load the data into the Teradata database. Also for fastload utility please specify such options like “Error files”, “Sessions”, “Tenacity”, “Sleep”, “Errors limit”.

Error Files

“Error files” - You need to specify two error tables where fastload utility will put information about the errors that occur during the fastload execution. The first error table record translation or constraint violation error. Second error table stores errors related to duplication of values for Unique Primary Index. Default suffixes “_Err1” and “_Err2”.

Error Limit

“Errors limit” - Need to specify maximum number of rejected errors that fastload utility will put in the first error table.

Sessions

“Sessions” - This option controls the number of sessions to establish with Teradata database.

Tenacity

“Tenacity” - Specify the number of hours that fastload tries to establish the connection.

Sleep

“Sleep” - Works together with “Tenacity” option. It controls the number of minutes to wait before fastload retry to establish the connection.

SAP Hana

Directory for CSV and CTL Files

In this option a path should be specified to the folder where CSV and CTL files will be moved after the conversion process in order to load the definitions and data into Sap Hana database.

Migrate Table to Sap Hana Column Store

This option allows to convert tables from source database to COLUMN tables in Sap Hana.

Character Set

This option defines the encoding that will be used to store the extracted data in the files in export folder and loaded into the target database.

Sybase

Client-Side Character Set Conversion

This option specifies client-side character set conversion for the BCP utility (Parameter -Y).
Possible values: “Yes”, “No”. Default value is “No”.

Client-Side Password Encryption

Specifies the client-side password encryption to the ISQL and BCP utilities commands (Parameter -X).
Possible values: “Yes”, “No”. Default value is “No”.

Batch Size

Specifies the number of rows per batch of data copied. By default, bcp copies n rows in one batch, where n is equal to the batch size. Batch size applies only when you are bulk copying in; it has no effect on bulk copying out. The smallest number bcp accepts for batchsize is 1. The largest number bcp accepts for batchsize is 2147483647L.

IBM DB2

Common Options

Use Full Path To BIN Directory

If checked, SQLWays Wizard will generate command to the native db2.exe utility using the full path to this utility.

Timestamp Format

This option specifies the format of the timestamp columns in the text file when the target database is IBM DB2. Possible values - IBM DB2, ISO.
The default value is IBM DB2 that means using the IBM DB2 native format of timestamps. The IBM DB2 native format is YYYY-MM-DD-HH.MI.SS.FFFFFF. The IBM DB2 IMPORT command requires this format to import text files containing timestamps not enclosed by double quotes (TAB delimited output format e.g.).
When ISO is specified, the ISO format of timestamps will be used. The ISO format is YYYY-MM-DD HH:MI:SS.FFFFFF. The IBM DB2 IMPORT command can import timestamp values in the ISO format, when they are enclosed by double quotes (CSV output format e.g.).
Note. This option is ignored when any value is specified in the option DATETIME_FORMAT (see earlier in the [DATA] subsection).
For example, when DATETIME_DATATYPE=DATE is specified, the datetime datatypes will be converted to DATE in IBM DB2.

Utilities Options

Use the IMPORT\LOAD Command

These two options control what command will be used to move data into the IBM DB2 database. By default LOAD command is checked.

Load From a Remote Client

If this option is checked, the CLIENT option is used in the IBM DB2 LOAD command. This option specifies that the data can reside on a remote client. Otherwise, the data must reside on the IBM DB2 server.

Nonrecoverable Load

If “Yes” is specified, the NONRECOVERABLE option is added to the IBM DB2 LOAD command. This option specifies that the load transaction is to be marked as non-recoverable, and that it will not be possible to recover it by a subsequent roll forward action.
With this option, when the database is configured to support online backups, table spaces are not put in backup pending state following the load operation.

Modified By

Specifies additional options that are added to the modified by option of the IBM DB2 IMPORT and LOAD utilities.
Some options like character, column delimiters and others are added to the IBM DB2 IMPORT/LOAD scripts by SQLWays. You can use this option if you need other options to be added to the modified by option.
For example, set modifiedby_options=usedefaults delprioritychar and these options will be added to the modified by option in each script for the IBM DB2 IMPORT/LOAD utilities.

Batch Size

Specifies that the load utility is to establish consistency points after every n rows. This value is converted to a page count, and rounded up to intervals of the extent size. Since a message is issued at each consistency point, this option should be selected if the load operation will be monitored using LOAD QUERY. If the value of n is not sufficiently high, the synchronization of activities performed at each consistency point will impact performance.

Tablespaces

Database Name

This option will add IN [database_name] clause to the converted table definition, that controls in what database this table should be created. By default this option is empty.

Tables

Specifies the table space in which the tables will be created. The table space must exist, and be a REGULAR table space. If no other table space is specified, all table parts will be stored in this table space. The default value is USERSPACE1.

Indexes

Specifies the table space in which any indexes on the tables will be created. The specified table space must exist, be a REGULAR DMS table space.

Large Objects

Specifies the table space in which the values of any long columns (LONG VARCHAR, LONG VARGRAPHIC, LOB data types) will be stored. The table space must exist, be a LONG DMS table space.

Use Separate Tablespaces

If checked, separate tablespaces will be used for each table.

Use Tablespace Names From Source Database

If checked, tablespace name from source database will be used for tables and indexes.

IXF

Single-Byte Codepage

This option specifies Single-Byte codepage, that will be used in the .IXF file, generated during the conversion step.

Double-Byte Codepage

This option specifies Double-Byte codepage, that will be used in the .IXF file, generated during the conversion step.


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays


© 1999-2024, Ispirer Systems, LLC. All Rights Reserved.