Microsoft SQL Server migration

This section provides an overview of the migration process required from Microsoft SQL Server to DB2 Express-C. At this time, we only describe migrations from MS SQL Server 2000, and not MS SQL Server 2005; however, many of the discussions should be applicable to both.

The first important difference to note between SQL Server and DB2 is that SQL Server is only supported on the Windows platforms. DB2 Express-C, on the other hand, is supported on Linux and Windows platforms and in a variety of hardware architectures. The DB2 code for these platforms is essentially the same with very minor platform-specific modules. In addition, if your needs grow in the future, it is easy to upgrade to other DB2 Editions with no changes required.

Architecture Comparison

These are some of the differences in architecture between MS SQL Server and DB2:

Instances

The concept of an instance in both products, is the same; however, the implementation is totally different. In SQL Server each instance (also referred to as "server") that is created requires its own SQL Server code. In DB2, there is only one copy of the DB2 code which is used by all instances.

Windows services

Table 1 provides a mapping between the Windows services in both, SQL Server and DB2.
Table 1 - SQL Server Windows services versus DB2 Windows services

*There is only one DAS per DB2 machine.

Databases

In DB2, an instance can contain several databases. Each database is a truly closed and independent unit with its own catalog table space (storing metadata), temporary table space, and user table space. There is no database that shares metadata information across databases the way that the SQL Server's master database does. Likewise, there is no database that is shared among other databases to hold temporary data the way SQL Server's tempdb does. DB2 UDB does contain a binary file known as the system database directory that contains entries of all the databases you can connect from your DB2 machine. This directory is kept at the instance level.
Databases within an instance normally don't interact with each other; however, if your application has such a requirement, this can be supported by enabling federation support. Read this article for more information.

Object names

SQL Server object names have a four-part structure as follows:
[SQL Server Instance].[Database Name].[Owner].[object name]

The first three parts of these names are optional, depending on the context in which the object is used.

In DB2, objects have a two-part structure:
Schema_name.object_name

The schema name is used to group objects logically. The schema name does not have to match to a user id.
Stored procedures, triggers and user-defined functions (UDFs)

All of these objects are supported in both products. SQL Server uses its proprietary Transact-SQL (T-SQL), while DB2 uses SQL Procedural Language (SQL/PL), both are extensions of the SQL/PSM standard. With version 8.2 of DB2 (equivalent to DB2 V8.1 Fixpak 7) SQL/PL stored procedures do not require a C compiler.

Since both T-SQL and SQL PL are extensions of the same standard, many of the same features are available in both languages, although, sometimes with differing syntax. Proprietary extensions are usually what cause difficulty when converting from T-SQL to SQL PL, since there is not always a direct translation from one language to the other.

Security

SQL Server uses the concepts of authentication, roles and permissions to implement security. DB2 uses the concepts of authentication, authorities, and privileges. Table 2 provides a mapping between SQL Server and DB2 security concepts.
Table 2 - SQL Server security concepts to DB2

Migration considerations

Here are things you need to consider with respect to SQL differences between SQL Server and DB2:

1. The data types supported in SQL Server can be mapped to equivalent DB2 data types.

2. SQL Server allows for implicit casting while DB2 provides strong type casting. If in SQL Server you insert a datetime column into a varchar column; to migrate this statement to DB2, you need to use a casting function such as CHAR(column_name) explicitly. The Migration toolkit (MTK) handles most of these issues for you.

3. SQL Server's ROWVERSION data type can be migrated to a TIMESTAMP data type, or a GENERATE_UNIQUE function can be used to generate unique values.

For further information about other SQL considerations, please refer to the resources section.

Tools for migration

The IBM Migration toolkit(MTK) can save you time and effort when migrating from SQL Server to DB2. The MTK can extract your SQL Server database structure and convert it into DB2's structure. Stored procedures, triggers and functions can also be converted with this tool. The MTK can also be used to extract your data and load it into DB2.

Resources

This page provided a brief comparison between SQL Server and DB2, and described some migration considerations. For more information, please refer to these sources:

• DB2 Express-C - Free download
• Microsoft SQL Server to IBM DB2 UDB Conversion Guide (SG24-6672-00)
• Leverage your Microsoft SQL Server 2000 Skills to Learn DB2 UDB Version 8