MySQL

This section provides an overview of the migration process required to move from MySQL to IBM DB2 Express-C.
When considering a migration from MySQL to DB2 Express-C, it is important to recognize that DB2 Express-C is a true ACID-compliant database, with no extra transactional engine cost. DB2 is also fully SQL standards compliant. This means that applications and databases built on DB2 Express-C are scalable for the future. IBM DB2 Express-C provides an advantageous upgrade path to the benefits of the entire DB2 family: high scalability in massively multi-processor environments, High Availability Disaster Recovery (HADR), and database partitioning.
There are many areas that need to be taken into consideration in migration to DB2 Express-C, such as: architectural differences, DDL and DML mapping, data objects availability and differences. In MySQL, these differences vary from one version to another. In general, the earlier versions of MySQL are less complex.

Architecture Comparison

These are some of the differences in architecture between MySQL and DB2:

Instances
MySQL distributions from version 5.0.3 onwards provide an instance manager to monitor database server instances. You can configure numerous data to be started using MySQL instance manager. In DB2 Express-C, the instance stands for a logical database manager environment control, where environment and configuration parameters can be set.
During DB2 installation, a DB2 instance is created by default. In addition, a DAS (DB2 Administration Server) is created. The DAS is used primarily for remote GUI client access, job scheduling and management. Tasks that can be performed against an instance include:

• Autostart - db2iauto
• Create - db2icrt
• Delete - db2idrop
• Start - db2admin start
• Stop - db2admin stop

You can have as many instances as required, normally each instance by itself does not require many resources, but this depends on the number of databases it contains.

Databases
The database concept in MySQL and DB2 Express-C is similar; a database is considered to be a self contained operational unit containing data records. Typically, the data does not need to be shared across different databases. However, when need arises, both MySQL and DB2 can be configured to allow federated database access. In MySQL, you define tables with the option, ENGINE=FEDERATED and a connection string using the parameter CONNECTION for federated access. In DB2 Express-C, federated database access can be setup using the DB2 Control Center. Refer to this federation article for more information on DB2 Express-C federation setup.
The relationship between a DB2 instance and a DB2 database is that one instance can have many databases, but one database can only reside in one particular instance. The total number of databases allowed in one instance depends on the resources available. There are two different types of table spaces you can specify in database creation; system managed space (SMS) and database managed space (DMS). SMS tablespace is an operating system managed tablespace, where data is stored in the file system. In SMS, space is allocated when it is required on a page by page basis. However, you can opt for an extent (multiple consecutive pages) allocation by setting a parameter. DMS table space, on the other hand, is managed by the DB2 database manager, with space pre-allocated during creation. DMS table space affords greater flexibility and in many cases better performance, but with the tradeoff of more work required to maintain it. Choosing between SMS and DMS will depend greatly on the database's intended usage.

Database Objects
Database objects such as stored procedures, user defined functions, views, and tables will be discussed here. * Stored procedures - This is a new feature to MySQL starting in version 5.0. The syntax for stored procedure construction is similar to DB2 Express-C. Stored procedures can be migrated quite readily to DB2 Express C, as long as the content of the stored procedure is SQL standard compliant. Stored procedures can be written in either SQL/PL language (SQL stored procedure) or SQLJ (Java stored procedure).

• User defined functions (UDFs) - Applicable in newer versions of MySQL. UDFs often require human intervention to migrate, as the syntax can be different. UDFs employing built-in functions could break easily, as many functions do not have direct mapping to DB2 Express-C functions.
• Views - Views were added in MySQL 5.0.1. The syntax for a view is similar in both databases. However, DB2 Express-C supports updateable views and typed views. In DB2 Express-C, you can update a view either by writing a trigger or by using the updateable view.
• Triggers - Both MySQL and DB2 Express-C support triggers, and the syntax is similar. The effort required to migrate a trigger is dependent on how much the trigger commands adhere to SQL standards.
• Tables - In DB2 Express-C, there is no need to specify table storage type, as done by the MySQL ENGINE parameter during table creation. Also, note that all DB2 tables are ACID compliant by default. Users do not have to choose transactional safe (INNODB) or non-transactional safe (MyISAM) storage types. Additionally, there is no concern with size limitations, as in MyISAM environments, as DB2 Express-C scales to the limit of your hardware resources.
  DB2 Express-C provides other functions such as sequences, nested tables, temporary (common table expression) tables, and typed and structured tables.

Data Type Mapping
When mapping data types, most data types map well, but there are a few data types that may require attention. Refer to the MySQL & DB2 Express-C data type mapping table for the MySQL data types that differ from DB2 Express-C.

DML and DDL Mapping
The syntax between DML and DDL among these two databases is not portable. The DDL in MySQL includes its own set of parameters that are not applicable to DB2 Express-C. The DDL difference is, however, less drastic than DML between the two systems. For example, the select statement in MySQL has the following non portable statement keywords:* DISTINCTROW

• HIGH_PRIORITY
• SQL_SMALL_RESULT/SQL_BIG_RESULT/SQL_BUFFER_RESULT
• LIMIT
• INTO OUTFILE
  Fullselect and subselect queries are supported for both databases. Migrating them requires the same attention as a normal select.

Security
DB2 Express-C uses operating system authentication. There is no concept of roles, as in MySQL. All privileges and authorizations are predefined. A user or a group of users can have more than one authority level. Predefined authorizations are:* SYSADM (administrative authority)

• DBADM (administrative authority)
• SYSCTRL (System control authority)
• SYSMAINT(System control authority)
• SYSMON (System monitoring authority)
• LOAD (data loading authority)

Privilege level in DB2 Express-C defines the task that can be performed against a database object either creating or accessing the object. For example, to select a table, users need to have connectand select privileges. A user or a group of users who access database objects without the necessary privileges will receive an error. In summary, privileges can be categorized into three categories:* CONTROL privilege - one who creates an object will have full control of that object

• Individual privilege - atomic in nature, giving privileges to create, update, delete and select. These privileges include schema, table, index, and view privileges.
• Implicit privilege - this is the kind of privilege that is granted automatically, resulting from a higher level privilege being granted to a user.
  Users with GRANT option can grant the privilege to others as well. Privileges and authorizations together enforce the security in DB2 Express-C. In addition, data and passwords can both be encrypted, should there be a need for a higher degree of security.

Migration considerations

Further to the migration considerations made obvious in the previous section, you need to consider the compatibility of built-in functions with DB2-Express-C. These functions range from string manipulation functions and date/time functions, to aggregate functions such as GROUP_CONCAT(). Migrating from MySQL databases that use non SQL-standard extensions often require manual intervention. You will likely have less to worry about if your MySQL database is from an earlier version, as many of the contemporary database standards weren't in place. The complexity of the newer MySQL versions, coupled with the use of non SQL-standard extensions, increases the migration considerations and the required effort for a migration.

Tools for migration

The IBM Migration toolkit (MTK)can save you time and effort when migrating from MySQL to DB2 Express-C. With the MTK, you can automatically convert data types, tables, columns, views and indexes into equivalent DB2 database elements. IBM Migration Toolkits support MySQL versions 3.23.48 and higher. A new alpha version of the MTK for MySQL is available here. Contact mtk@us.ibm.com for more information.

Resources

This page provided a brief comparison between MySQL and DB2, and described some migration considerations. For more information, please refer to these sources:
DB2 Express-C - Free download
Leveraging MySQL skills to learn DB2 Express
MySQL to DB2 UDB Conversion Guide