## SQLWays Wizard

SQLWays provides a functional and easy to use wizard to perform database migration tasks. The wizard consists of a set of pages representing each step of the migration process.

- Target Database Options Page
- DDL (Data Definition Language) Options Page
- Data Export Options Page
- Import Options Page

### Target Database Options Page

On this page you can specify options for the target database. The following options are available:

- Target Database

  Specifies the type of the target database:

  - IBM DB2
  - Oracle
  - Microsoft SQL Server
  - Sybase
  - MySQL
- Advanced

  Specifies advanced options for the target database.

  - IBM DB2 Advanced Options
  - Oracle Advanced Options
  - MySQL Advanced Options

### IBM DB2 Advanced Options

This dialog box specifies advanced options for the IBM DB2 target database. The following options are available:

- Use the IMPORT command

  If this radio button is selected, the DB2 IMPORT command is used to load the data to a DB2 database. Otherwise, the DB2 LOAD command is used.

- Use the LOAD command

  If this radio button is selected, the DB2 LOAD command is used to load the data to a DB2 database. Otherwise, the DB2 IMPORT command is used.

- Load from a remote client

  If this checkbox is selected, the data for the DB2 LOAD command can reside on the remote client. Otherwise, the data must reside on the DB2 server.

  SQLWays uses the CLIENT option in the LOAD command. This option is available in IBM DB2 7.1 or later.

- Nonrecoverable load

  If this checkbox is selected, the NONRECOVERABLE option is added to the 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.

DDL Conversion

- BIT data type

  This edit box specifies the datatype that represents the BIT data type in IBM DB2, since IBM DB2 does not support the BIT data type explicitly. The default value is CHAR(1).

  SQLWays will convert the BIT data type to the specified data type when migrating from databases that supports the BIT data type, for example Microsoft SQL Server or Microsoft Access.

  For example, when SMALLINT is specified, the BIT data type will be converted to SMALLINT in IBM DB2.

- Datetime data type

  This edit box specifies the IBM DB2 datatype in which the datetime datatypes are converted. Datetime data consist of valid date and time combinations. Depending on the database this is the DATE datatype in Oracle and the DATETIME datatype in Microsoft SQL Server and Sybase.

  SQLWays will convert the datetime datatypes to the specified IBM DB2 datatype. By default the datetime datatypes are converted to TIMESTAMP.

  For example, when DATE is specified, the datetime datatypes will be converted to DATE in IBM DB2.

- Max index name

  This edit box specifies the maximum length for index names in IBM DB2. If the index name exceeds this length, it is truncated. The default value is 18. This is the maximum length for IBM DB2 version 7.2 and earlier.

To specify IBM DB2 options in the command prompt

- SQLWays command prompt - [DB2] Section

### Oracle Advanced Options

This dialog box specifies advanced options for the Oracle target database. The following options are available:

DDL Conversion

- BIT data type

  This edit box specifies the datatype that represents the BIT data type in Oracle, since Oracle does not support the BIT data type explicitly. The default value is CHAR(1).

  SQLWays will convert the BIT data type to the specified data type when migrating from databases that supports the BIT data type, for example Microsoft SQL Server or Microsoft Access.

  For example, when NUMBER(1) is specified, the BIT data type will be converted to NUMBER(1) in Oracle.

- Datetime data type

  This edit box specifies the datatype that represents the datetime datatypes in Oracle. Datetime data consist of valid date and time combinations. Depending on the database this is the DATE datatype in Oracle, TIMESTAMP datatype in IBM DB2 and DATETIME datatype in Microsoft SQL Server and Sybase.

  SQLWays will convert the datetime datatypes to the specified datatype. By default the datetime datatype are converted to DATE.

  For example, when TIMESTAMP(6) is specified, the datetime datatypes will be converted to TIMESTAMP(6) in Oracle. The TIMESTAMP datatype is available in Oracle 9i Release 1 (9.0.1) or later.

- Convert LONG RAW to BLOB

  This option is valid only when tables are migrated between Oracle databases. If this checkbox is selected, the Oracle LONG RAW datatype is converted to the Oracle BLOB datatype. Otherwise, the Oracle LONG RAW datatype is not converted.

  Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG RAW columns.

To specify Oracle options in the command prompt

- SQLWays command prompt - [Oracle] Section

### MySQL Advanced Options

This dialog box specifies advanced options for the MySQL target database. The following options are available:

- Table Type

  This option specifies a table type that is used when creating tables in the 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.

To specify MySQL options in the command prompt

- SQLWays command prompt - [MySQL] Section

### DDL (Data Definition Language) Options Page

SQLWays creates SQL scripts when it exports the definitions of the source database objects (tables, views etc.). These scripts contain DDL statements that are used to create database objects in the target database.

On the DDL Options page you can specify options for SQL scripts. The following options are available:

- Generate the DROP TABLE statement for each table
  If this checkbox is selected, the DROP TABLE statement is generated before the CREATE TABLE statement.
- Cascade referential integrity constarints
  If this checkbox is selected, the CASCADE CONSTRAINTS option is generated in the DROP TABLE statement. Currently this option is supported by Oracle only. For more information, see Dropping Tables.
  This option is only available if the target database is Oracle and the Generate the DROP TABLE statement checkbox is selected.
- Convert identity columns
  If this checkbox is selected, SQLWays converts the identity properties of columns. Otherwise, SQLWays does not extract the identity properties.
  Identity columns are commonly used in conjunction with primary key constraints to serve as the unique row identifier for the table.

To specify DDL options in the command prompt

- SQLWays command prompt - [DDL] Section

### Data Export Options Page

SQLWays exports data to text files.

On the Data Export Options page you can specify text file formats and various properties. The following options are available:

- Datetime format
  If this checkbox is selected, you can specify a 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 datatype in Oracle, TIMESTAMP datatype in IBM DB2 and DATETIME datatype 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 this checkbox is not selected, 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

  Datetime data consist of valid date and time combinations. Time can contain a fraction part. For example, IBM DB2 TIMESTAMP datatype can contain microseconds, Microsoft SQL Server and Sybase DATETIME can keep milliseconds.

  If Yes is selected, the fractional seconds are saved in the text files. If No is selected, the fractional seconds are not saved in the text files.

  If Default is selected, the fractional seconds are saved unless the target database is Oracle.

  If Default is selected, the target database is Oracle and the datetime datatypes are converted to the Oracle DATE datatype, the fractional seconds are not saved, since the Oracle DATE datatype doesn't support fractional seconds.

  If Default is selected, the target database is Oracle and the datetime datatypes are converted to the Oracle TIMESTAMP datatype, the fractional seconds are saved, since the Oracle TIMESTAMP datatype supports fractional seconds.

  Note. This option is ignored if any value is specified in the option Datetime format (see above).

- Date format
  If this checkbox is selected, you can specify a 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 datatype that allows you to keep date values without a time part. For example, such datatype exists in IBM DB2 and MySQL (the DATE datatype).

  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 this checkbox is not selected, the default date format is used. By default, ODBC drivers convert date values using the ISO format YYYY-MM-DD.

- Time format
  If this checkbox is selected, you can specify a format of the time datatypes in the text files.
  A time is a three-part value (hour, minute and second). Not all databases have a datatype that allows you to keep time values without a date part. For example, such datatype exists in IBM DB2 and MySQL (the TIME datatype).

  A time format can be composed of one or more time 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)

  For example, to get time values like 21-21-00 set the time format HH24-MI-SS.

  If this checkbox is not selected, the default time format is used. By default, ODBC drivers convert time values using the ISO format HH24:MI:SS.

To specify Data Export options in the command prompt

- SQLWays command prompt - [Data] Section

### Import Options Page

SQLWays allows you to perform export and import on different systems and platforms. You can execute export on a Windows system, and then perform import on other Windows or Unix system.

On the Import Options page you can specify various import properties. The following options are available:

- Unix shell location
  This edit box specifies an Unix shell that executes import scripts on a Unix system. If you specify that import is executed on a Unix system, SQLWays generates Bourne shell scripts for importing database structure and data.

  By default, SQLWays specifies that shell scripts are executed by /bin/sh. Using this option you can specify another shell location, /usr/bin/sh e.g.

  This option is disabled if import is not executed on a Unix system.

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.