Ispirer SQLWays Database Migration Software
Table of Contents

Conventions Used in This Manual

Introduction

Key Benefits

Supported Databases

Database Object and Features Support

IBM DB2 for Linux, Unix and Windows Support

IBM DB2 for z/OS and OS/390 Support

IBM DB2 for iSeries and AS/400 Support

Oracle Support

Microsoft SQL Server Support

Sybase Adaptive Server Enterprise Support

PostgreSQL Support

MySQL Support

Pervasive.SQL Support

What's New

Getting Started

Preparing for Installation

Installing SQLWays

Putting SQLWays in Operation

Database Migration Concepts

Migration Process in SQLWays

Stages of Migration Process

Stage 1. Exporting and Converting

Files Created for Importing to IBM DB2

Files Created for Importing to Oracle

Files Created for Importing to Microsoft SQL Server

Files Created for Importing to MySQL

Stage 2. Transferring and Processing (optional)

Stage 3. Importing

Comparative Analysis of Databases

Identifiers

Maximum Length of Identifiers

Allowed Characters in Identifiers

Delimited Identifiers

Expressions and Statements

Variable Declaration

Conversion of Variable Declarations from Microsoft SQL Server to MySQL

Conversion of the Informix variable declarations to Oracle

Conversion of Oracle %ROWTYPE to MySQL

Conversion of Oracle %TYPE to Microsoft SQL Server

Declaration of Local Variables with Composite Data Types

Conversion of Oracle RECORD Variable to Microsoft SQL Server

Assignment Statements

Conversion of Assignment Statement from Microsoft SQL Server to Oracle

Conditional Expressions

Conversion of Oracle DECODE to MySQL CASE

SELECT Statement

Restricting Number of Rows in Result Set

Executing Procedures and User-Defined Functions

Conversion of Execution Procedures and User-Defined Functions from Microsoft SQL Server to Oracle

Conversion of Sybase Adaptive Server Anyware CALL to Microsoft SQL Server

Executing Dynamic SQL Statements with Parameters

Conversion of Dynamic Statement Execution from Microsoft SQL Server to Oracle

Cursors

Cursor Declaration

Conversion of Cursor with Parameters from Oracle to MySQL

Transaction Control

Starting Transaction

BEGIN TRANSACTION Conversion from Microsoft SQL Server to Oracle

COMMIT Statement

COMMIT conversion from Microsoft SQL Server to Oracle

Functions

Number Functions

Converting String to Number

String Functions

Concatenating Strings

Converting Expression to String

Converting ASCII Code to Character

Converting Datetime Expression with Format String to String

Conversion of Oracle TO_CHAR(datetime) with format string to MySQL

Returning Substring from String

Returning String in Uppercase

Conversions of Microsoft SQL Server CHARINDEX to Oracle

Removing (or Trimming) Characters from a String

Returning Information about Database and Current Connection

Returning Information about Current User

Replace NULL value functions

Returning the first non-NULL expression

Returning one of expressions depending on whether check expression is NULL or NOT NULL

Techniques

Returning Non-Table Data as Result Set (Dummy Tables)

Non-Table Result Set Conversion from Sybase Adaptive Server Anywhere to Microsoft SQL Server

Returning Result Sets from Procedure

Returning Result Set to Client

Result Set Conversion from Microsoft SQL Server to IBM DB2

Migrating to IBM DB2

IBM DB2 Data Types

CHAR, VARCHAR and LONG VARCHAR

GRAPHIC, VARGRAPHIC and LONG VARGRAPHIC

BIGINT, INTEGER and SMALLINT

DECIMAL or NUMERIC

FLOAT, REAL and DOUBLE

DATE, TIME and TIMESTAMP

BLOB, CLOB and DBCLOB

DATALINK

IBM DB2 Functions and Expressions

IBM DB2 Expressions

Simple CASE Expression

IBM DB2 Functions

COALESCE

LEFT

LENGTH

RIGHT

IBM DB2 Special Registers

CURRENT TIMESTAMP

IBM DB2 LOAD Command

LOAD Command Options

IBM DB2 Version Differences

LOAD Command

Migrating to Oracle

Oracle Data Types

CHAR, NCHAR, VARCHAR2 and NVARCHAR2

NUMBER and FLOAT

DATE and TIMESTAMP

INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND

LONG, RAW and LONG RAW

BLOB, CLOB, NCLOB and BFILE

ROWID and UROWID

Oracle Functions and Expressions

Oracle Expressions

DECODE Expression

Oracle Functions

LENGTH

NVL

SUBSTR

SYSDATE

Oracle Reserved Words

Oracle Version Differences

TIMESTAMP data type

Migrating to Microsoft SQL Server

Migration from Oracle to Microsoft SQL Server

Microsoft SQL Server and Oracle Functions and Expressions

Migration from Sybase to Microsoft SQL Server

Microsoft SQL Server and Sybase Data Types Differences

Microsoft SQL Server Data Types

char, nchar, varchar and nvarchar

bigint, int, smallint and tinyint

datetime and smalldatetime

money and smallmoney

decimal and numeric

float and real

text and ntext

binary, varbinary and image

bit, uniqueidentifier and timestamp

Microsoft SQL Server Functions and Expressions

SQL Server Expressions

Simple CASE Expression

SQL Server Functions

COALESCE

GETDATE

ISNULL

LEFT

LEN

RIGHT

SUBSTRING

Microsoft SQL Server Version Differences

bigint data type

INFORMATION_SCHEMA.ROUTINES view

Migrating to Sybase

Sybase Adaptive Server Enterprise (ASE)

Sybase ASE Data Types

char, nchar, varchar and nvarchar

int, smallint and tinyint

datetime and smalldatetime

money and smallmoney

decimal and numeric

float, double precision and real

text

binary, varbinary and image

bit and timestamp

Sybase ASE History

Sybase ASE Versions Evolution

Sybase Adaptive Server Anywhere (ASA)

Sybase ASA Data Types

char, varchar and long varchar

bigint, int or integer, smallint and tinyint

date, datetime, smalldatetime, time and timestamp

money and smallmoney

decimal and numeric

float, double and real

text

binary, long binary, varbinary and image

bit

Sybase ASA History

Sybase ASA Versions Evolution

Migrating to MySQL

MySQL Data Types

CHAR, NCHAR and VARCHAR

BIGINT, INT, INTEGER, MEDIUMINT, SMALLINT and TINYINT

DECIMAL, DEC and NUMERIC

FLOAT

DOUBLE, DOUBLE PRECISION and REAL

DATE, TIME, DATETIME, TIMESTAMP and YEAR

TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB

TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT

BIT and BOOL

ENUM and SET

MySQL Reserved Words

Importing Data into MySQL

Migrating to Pervasive.SQL

Pervasive.SQL Data Types

CHAR and VARCHAR

BIGINT, UBIGINT, INTEGER, UINTEGER, SMALLINT, USMALLINT, TINYINT and UTINYINT

DATE, TIME and TIMESTAMP

DECIMAL, NUMERIC, NUMERICSA and NUMERICSTS

MONEY and CURRENCY

FLOAT, REAL, DOUBLE, BFLOAT4 and BFLOAT8

LONGVARCHAR

BINARY and LONGVARBINARY

BIT, IDENTITY and SMALLIDENTITY

Tables

Column Default Values

Conversion of DEFAULT syntax to MySQL

Conversion of DEFAULT values from IBM DB2 to Oracle

Dropping Tables

Importing Tables

Application Migration Concepts

Progress 4GL to C# .NET Migration

PowerBuilder Migration

Oracle PL/SQL to Java Migration

User's Guide

SQLWays Wizard

Welcome Page

Step 1- Choose a Source Database

Step 2- Choose a Target Database

IBM DB2 Advanced Options

Oracle Advanced Options

Microsoft SQL Server Advanced Options

Sybase Adaptive Server Enterprise Advanced Options

MySQL Advanced Options

Pervasive.SQL Advanced Options

Step 3- Specify Database Objects or Query

Step 4 - Set DDL and Data Options

DDL Options

Data Options

Step 5 - Specify Export File Options

Step 6 - Specify Import Options

SQLWays Command Line

Command Line Options

/D - Data source name (ODBC alias)

/U - User name

/P - Password

/T - Table name, list or template

/V - View name, list or template

/SP - Stored procedure name, list or template

/FN - Function name, list or template

/TG - Trigger name, list or template

/PKG - Package name, list or template

/F - Script files path, name, list or template

/FF - Text file with the list of script files path, name, list or template

/S - SELECT statement

/SF - File containing SELECT statement

/EXC - List of columns to be excluded from converting

/SROW - Start row

/CNROWS - Number of rows to be exported

/TARGET - Type of the target database

/TPROD - Target database product

/TVER - Target database version

/TD - Target database name

/TU - User name for the target database

/TP - User password for the target database

/MIGS - Migration sequence

/IMPS - Import system

/DIR - Export directory

/LOBDIR - Directory for LOB files

/IMPDIR - Directory where import will be executed

/IMPLOB - Import directory for LOB files

/OSN - Output schema (owner) name

/OTN - Output table name

/OFN - Output files name

/OTF - Output text file name

/EMPS - Omit the schema (owner) name in DDL statements

/DDL - Generate DDL statements only

/OF - Output format

/CDEL - Column delimiter

/LDEL - Line delimiter

/DECPT - Decimal point character

/STDEL - Statement termination string

/LOBIN - Write the LOB data inside the text file

/TABLST - Generate list of tables available by using template

/R - Prefetch count

/INI - Initialization file

/NSTOP - Continue when an error occurs

/GCMD - General command file name

/NODDL - Not generate DDL scripts

/NOCMD - Not generate OS command files

/RPT - Report file name

/LOG - Log file name

/TRACE - Run in trace mode

/REG - Run for registration

/UNREG - Run for unregistration

Initialization File Options

[Common] Subsection

[Data] Subsection

[DDL] Subsection

[Windows] Subsection

[Unix] Subsection

[Oracle] Subsection

[IBM DB2] Subsection

[MSSQL] Subsection

[Sybase] Subsection

[MySQL] Subsection

[Pervasive] Subsection

[Formatting] Subsection

Command Line Tips

Return Codes

Using OS Special Characters

Setting Up ODBC Data Sources

Configuring ODBC Connection to Sybase Adaptive Server Anywhere

Sybase Adaptive Server Anywhere Driver

ODBC Sybase ASA Driver Setup Dialog: ODBC Tab

ODBC Sybase ASA Driver Setup Dialog: Login Tab

ODBC Sybase ASA Driver Setup Dialog: Database Tab

ODBC Sybase ASA Driver Setup Dialog: Network Tab

ODBC Sybase ASA Driver Setup Dialog: Advanced Tab

Certicom Encryption Options Dialog

Sybase Adaptive Server IQ Driver

Sybase Adaptive Server Enterprise Drivers

ASE 12 ODBC Driver

System 11 ODBC Driver

Configuring ODBC Connection to Sybase Adaptive Server Enterprise

ODBC Sybase ASE Driver Setup Dialog : General Tab

ODBC Sybase ASE Driver Setup Dialog : Advanced Tab

ODBC Sybase ASE Driver Setup Dialog : Connection Tab

ODBC Sybase ASE Driver Setup Dialog : Performance Tab

Configuring Connection to MySQL using MyODBC

SQLWays Studio

Choosing Source and Target

Running Conversion

SQLWays Troubleshooting Guide

IBM DB2 Database

Importing to IBM DB2

SQL0286N A default table space could not be found with a pagesize of at least "<pagesize>" that authorization ID "<user-name>" is authorized to use

Exporting from IBM DB2

SQLSTATE 01517 - A character that could not be converted was replaced with a substitute character

Oracle Database

Oracle SQL Loader does not terminate

DROP TABLE Errors, ORA-02449: unique/primary keys in table referenced by foreign keys

SQL*Loader-350: Syntax error - found "TIMESTAMP"

MySQL Database

Importing data to MySQL 4.0.x using the LOCAL DATA INFILE command (LOCAL keyword - The used command is not allowed with this MySQL version)

Access Database

Export from Access

Excel Files

Syntax for specifying Excel table names

Frequently Asked Questions

FAQ: Export Database Schema (DDL) only

How to Omit Schema Names?

FAQ: Export Data only

How to Change a Decimal Point Character?

How to Change a Line Delimiter?

What Export File Formats are Supported by SQLWays?

Ispirer Systems Resources and Contacts

Online Documentation

Technical Support

How To Order

Legal Notices

SQLWays License Agreement

Trademarks