Ispirer Home Page
Ispirer SQLWays Migration Product Page
Request SQLWays
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 'Specify DDL and Data Options' page of the tool. Please, click on specific group of objects or specific object on the left pane to tune the migration.
To tune databse options need to click on 'Database' at the left pane of 'Specify DDL and Data Options' Page. There are options for objets DDLs and for objets DDLs All changes will be aplied for the whole migration scope.
Options from DDL section of database options help to tune the migration of objects definitions.
| Option name | Descriptions |
|---|---|
| Generate the DROP TABLE Statement | If checked, the DROP TABLE statement is generated before the CREATE TABLE statement |
| Omit Schema Names | Lets you specify whether to omit schema (owner) names in SQL scripts for DDL statements. |
| Convert cascade referential integrity constraint to no action | Converts Foreign Keys with CASCADE action to Foreign Keys with NO ACTION action |
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 ...
NOTE: If option "Generate DDL" is not checked this option is disabled.
If No is selected for “Omit schema names in SQL scripts” option, the schema (owner) name is added before the object name in each DDL statement created.
If Yes is selected for “Omit schema names in SQL scripts” 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.
If checked all Foreign keys with CASCADE action would convert to Foreign Keys with NO ACTION action.
To tune tables options need to click on 'Tables' at the left pane of 'Specify DDL and Data Options' Page. There are options for objets DDLs and for objets DDLs All changes will be aplied for the all tables, specified from a migration scope.
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:
NOTE: Local Data Type Mapping overrides the Global Data Type Mapping for the appropriate column.
| Option name | Descriptions |
|---|---|
| Generate DDL | If checked, DDL statement will be created for each table. |
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.
This option controls the way identity values are generated in a target Oracle database. This option may be set to the values below:
Please note: This option works only for Oracle as a target database.
If you have some additional questions or face some difficulties, please contact our support team and provide a detailed description of your issue: support@ispirer.com
Defines the case of column names.
This option will use the same constraint names as in the source database. Otherwise constraint names will be skipped.
This option converts default values for columns. If this option is disabled, conversion of default values will be skipped.
This option converts identities. If this option is disabled, conversion of identities will be skipped.
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.
If checked, NOT NULL constraints for all columns except primary key columns in the target database will be removed.
This option defines whether check constraints should be converted. If unchecked, check constraints will be skipped.
This option defines whether unique constraints should be converted. If unchecked, unique constraints will be skipped.
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.
| Option Name | Description |
|---|---|
| Export Data | Specifies whether or not to export the data |
| Start row | Specifies start row position for data export |
| Number of rows | Specifies how many rows should be exported |
| Boolean representation | Specifies boolean values format |
| Use Ispirer On the Fly Data Migrator | Specifies whether or not to migrate the data without storing it on PC |
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.
Also you can use the “Use Ispirer On The Fly Data Migrator” option to specify whether or not to migrate the data directly from one database to another database, bypassing the export data into files. This allows hasten the data migration process between the databases. Settings for On The Fly Data Migrator need to be specified on the Advanced page.
| Option Name | Description |
|---|
| Use datetime format | Specifies required datetime format |
| Save datetime fraction | Convert fraction part for datetime or no |
| Use date format | Specifies required date format |
| Use time format | Specifies required time format |
| Use ODBC float conversion | Convert numeric data to the text representation by ODBC driver or no |
| Allocate space for sign in data file | Specifies if allocation space for sign in data file is required or no |
| Empty strings to NULL | |
| Decimal point | Specifies a single character, which is used instead of a decimal point character for numeric values |
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.
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.
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 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 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.
| Option name | Description |
|---|---|
| Default for the target | Controls output text format |
| Column delimiter | Specifies column delimiter for files with data |
| Blanks count | Specifies the number of blanks between columns for FIX output format |
| Commit count | Controls number of rows for COMMIT during data insert |
| Line delimiter | Specifies line delimiter for files with data |
| Replace newline | Specifies a character string that replaces newline characters in the data |
| Remove newline | Controls whether newline characters should be removed or no |
| Resolve delimiter conflicts | Controls if resolving delimiter conflicts should be done during data export |
| Output column names in the first row of data file | Controls if columns names should be add before data in the first row of data file |
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 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.
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.
NOTE: Local Data Type Mapping overrides the Global Data Type Mapping for the appropriate 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 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:
For the reference please see the screen-shot below:
Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays
|
© 1999-2022, Ispirer Systems Ltd. All Rights Reserved. Privacy Statement |