SQLines Data Tool - Database Migration from IBM DB2 to MariaDB

SQLines Data is a scalable, high performance data transfer, schema conversion and validation tool for IBM DB2 to MariaDB migration.

The tool requires connections to DB2 (DB2 CLI) and MariaDB (MariaDB C API) databases. To convert standalone SQL statements and SQL scripts, use SQLines SQL Converter tool.

Databases:

  • IBM DB2 for LUW, z/OS and OS/400 11.x, 10.x, 9.x, 8.x and 7.x
  • MariaDB 10.x and 5.x

Why SQLines Data

SQLines Data benefits:

Migration Features

  • All Data Types
  • NOT NULL Constraints
  • DEFAULT Clauses
  • CHECK Constraints
  • Primary and Foreign Keys
  • Unique Constraints
  • Indexes
  • Comments on Tables and Columns
  • Schema Name Mapping
  • Validation by Row Count

You can use SQLines SQL Converter tool to convert stored procedures, functions, triggers, views and other objects.

Scalability and High-Performance

  • Transferring and validating tables in multiple concurrent sessions
  • Performing concurrent read and write operations in each session
  • Using DB2 CLI with array processing to extract data from IBM DB2
  • Using MariaDB C API with LOAD DATA INFILE command to load data to MariaDB
  • Multi-threaded C/C++ application
  • Linux and Windows, 32-bit and 64-bit

Designed for DBAs and Enterprise-Class Migrations

  • Easy to set up and configure the transfer of a large number of tables
  • Wildcards to select and exclude tables
  • Comprehensive performance stats to help you tune the data transfer
  • No space required to hold DB2 export
  • No ODBC drivers and ADO.NET providers required
  • No .NET dependency
  • No installation required

Logging and Statistics

  • Comprehensive performance stats to help you tune the data transfer
  • Execution time for each DDL statement
  • Read and write performance for each table (rows/sec and bytes/sec)
  • Saving all executed DDL SQL statements into .sql file
  • Saving all failed DDL SQL statements into a separate .sql file
  • Summary performance information

SQLines Data in Command Line

You can use SQLines Data tool in command line. Just launch sqldata.exe on Windows or ./sqldata on Linux with the specified options.

For information how to set up IBM DB2 and MariaDB connections, see SQLines Data Connection String Formats.

Examples:

  • Transfer a single table
sqldata -t=emp -sd=db2, user/pwd@dbname -td=mariadb, user/pwd@host,db_name

-t option defines the table name, -sd and -td options (source and target databases) specify the connection strings for DB2 and MariaDB, respectively.

This command transfers table emp from DB2 database to MariaDB db_name database located on host.

For list of options, see Command Line Reference.

Troubleshooting SQLines Data

SQLines Data Logs

There are two main sources that can help you troubleshoot SQLines Data:

  • sqldata.log file

sqldata.log file contains the detailed information about MariaDB to DB2 migration process.

By default, sqldata.log file is located in the current working directory. You can use -log command line option to change its location and file name.

  • sqldata_ddl.sql file

sqldata_ddl.sql file contains information about all DDL statements executed in MariaDB during migration.

In this file you can see the full SQL statements, the execution result (success or failure) and the execution time. In case of an error, you can find the error message returned by MariaDB.

By default, sqldata_ddl.sql file is located in the current working directory. You can use -out command line option to change its location.

  • sqldata_failed.sql file

If there are any failed SQL statements, you can find them in sqldata_failed.sql file.

  • sqldata.trc file

You can enable trace by specifying -trace=yes in the sqldata.cfg file. The trace file can be helpful for SQLines Data developers to resolve crashes or specific data issues.

Data Transfer - The used command is not allowed with this MariaDB version

Sometimes you can receive The used command is not allowed with this MariaDB version during the data transfer:

  USERS - Data transfer failed
	The used command is not allowed with this MariaDB version
     Rows read:     17 (274 rows/sec)
     Rows written:  0 (0 rows/sec, 0 bytes, 0 bytes/sec)
     Transfer time: 812 ms (62 ms read, 0 ms write)

The tool uses in-memory LOAD DATA LOCAL INFILE command, and possible reason is that it is not allowed by your MariaDB server configuration.

Edit my.cnf (or my.ini on Windows) and set local-infile=1 in [mysqld] section:

[mysqld]
local-infile=1

You have to restart the MariaDB server for the change to take effect.

Specified key was too long; max key length is 3072 bytes

In DB2 you may have a column defined, for example, as VARCHAR(1200 OCTETS) and an index on it. Trying to create this index in MariaDB you get: “SQL Error (1071): Specified key was too long; max key length is 3072 bytes”. How is this possible?

The reason is that the column size in DB2 is defined in bytes, while the size of VARCHAR column is defined in characters in DB2.

If you migrate from a single-byte character set in DB2 to UTF-8 in MariaDB, it may take up to 4 bytes now to represent a single character, so 1200 characters can easily exceed 3,072 bytes.

In some cases as a solution you can define the length prefix for the key when creating an index:

  ALTER TABLE messages ADD KEY (response(1500));

This limits the number of bytes used for indexing.