SQLines Data - Database Migration and ETL

SQLines Data is a high performance data transfer, schema conversion and migration validation tool that supports major enterprise databases:

• Oracle
• Microsoft SQL Server
• MySQL
• MariaDB
• PostgreSQL
• IBM DB2 LUW, iSeries (AS/400) and zSeries (S/390)
• Sybase Adaptive Server Enterprise, Sybase SQL Anywhere, Sybase IQ and Sybase Advantage
• Informix

SQLines Data is written in C/C++ and uses native low-level in-memory bulk loader APIs to transfer data. It is available for Linux, Windows, both 64-bit and 32-bit platforms.

The tool provides a powerful command line interface to help you automate and customize data transfer tasks.

You can use SQLines Data for cross-platform database migration. The tool migrates table definitions, constraints, indexes and transfers data.

Migration Features:

• Options to create schema, transfer data, create constraints and indexes to set up flexible migration process
• UI and command line interface

You can use SQLines Data in your ETL processes as a data extract-load tool.

ETL Features:

• Parallel high performance data transfer to target databases
• Parallel high performance export to flat files
• Choosing columns to transfer
• Defining SELECT expressions including computed columns
• Defining WHERE conditions for tables for incremental transfer
• Defining SQL SELECT queries to transfer data
• Command line interface

• How to Map Database Schemas
• How to Map Table Names
• How to Map Column Names and Data Types
• How to Map Data Types
• How to Transfer Tables without Constraints and Indexes
• How to Select, Transform and Add New Columns
• How to Specify Filters (WHERE Conditions) on Tables
• How to Transform Column Data
• How to Restart Transfer of Failed Tables
• How to Specify Queries to Transfer Data
• How to Generate Dump Files
• How to Skip Data Transfer

Often you need to change the database schemas during the migration, see -smap option how to do this.

Often you also need to change the table names during the migration, you can use it modifying sqlines_tmap.txt supplied with the tool.

Every line of this file contains a single table name mapping:

Use fully qualified table name to specify source tables, and for Oracle, IBM DB2 it should be in the upper case.

If you need to change the column names and their data types you can use sqlines_cmap.txt supplied with the tool to define the column mapping.

Every line of this file contains a single column name mapping as follows:

The target datatype is optional, and if you do not specify it then only the column name is mapped and the default data type conversion is used

For example:

Use fully qualified table name to specify source tables, and for Oracle, IBM DB2 it should be in the upper case: SCHEMA.TABLE

Sometimes you need to change the default data type mapping provided by SQLines Data tool. You can use sqlines_dtmap.txt file to define your own mapping.

For example, by default SQLines Data converts Oracle VARCHAR2(4000) to VARCHAR(4000) in MariaDB. You can change mapping to TEXT by defining:

in the sqlines_dtmap.txt. For more details, see -dtmapf configuration option.

This configuration file provides the global data type mapping i.e. this mapping will be used for all tables.

Sometime you do not need to move constraints and indexes into the target database. For example, in case when you move data for reporting purpose, or if you want to migrate constraints and indexes later.

Edit sqldata.cfg configuration file, and set -constraints=no and -indexes=no.

Use sqlines_tsel.txt and -tself option to define which columns to transfer, transform column values and add new computed columns based on specified expressions.

If nothing is specified for the table all its columns transferred as is.

Edit sqlines_twhere.txt file and specify individual filter for any source table. If a filter is not specified for the table all its data are transferred.

Edit sqlines_tsel.txt file and specify column expressions (using SQL dialect of the source database), add/remove columns, define computed columns and so on.

During the database migration some tables may fail to transfer. SQLines Data generates sqldata_failed_tables.txt file with the list of failed tables. Using -tf=sqldata_failed_tables.txt command line option you can re-run the transfer for the failed tables only.

In addition to specify tables, you can also specify any ad-hoc queries to define which data to transfer. Use -qf option to define your queries.

When importing data into MySQL, MariaDB or PostgreSQL you can add command line option -trace_data=yes to generate the dump files i.e. data that were sent to MySQL, MariaDB LOAD DATA INFILE and PostgreSQL COPY commands. It can help debug data transfer issues if any.

If you need to transfer DDL (tables, constraints, indexes etc.) without transferring data, set -data=no in the command line or sqldata.cfg configuration file.

If you have any questions, please contact us.