Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

Specify DDL and Data Options Page

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.

Database options

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.

DDL options

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

SQL scripts

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

Generate the DROP TABLE Statement

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.

Omit Schema Names

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.


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.


Tables Options

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.

DDL options

Global Data Type Mapping

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.


Generation options

Option name Descriptions
Generate DDL If checked, DDL statement will be created for each table.

Generate DDL

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.

Generate IDENTITY Columns

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.
 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


Data Options

DDL and Data Options - Data Options/General Combobox

Overview

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

Export Data

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.

Start row

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.

Number of rows

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.

Boolean representation

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.

Use Ispirer On the Fly Data Migrator

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.


DDL and Data Options - Data Options/Format Combobox

Overview

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

Use datetime format

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).

Save datetime fraction

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.

Use date 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.

Use ODBC float conversion

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.

Allocate space for sign in data file

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”.

Decimal point

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


DDL and Data Options - Data Options/Files Combobox

Overview

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.

Default for the target

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.

Column delimiter

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

Blanks count

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

Commit count

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).

Line delimiter

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

Replace newline

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.

Remove newline

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.

Resolve delimiter conflicts

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.

Output column names in the first row of data file

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

Single table options

DDL options

Local Data Type Mapping for single Table

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.

Column name mapping

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.

Data Options

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:

Ispirer Home Page Ispirer SQLWays Migration Product Page Request SQLWays

sqlways/users-guide/wizard/specify-ddl-data.txt · Last modified: July 27, 2018, 01:04:03 PM by alexandr.kirpichny
 
© 1999-2018, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement