IBM Migration Toolkit (MTK) FAQ

• What is MTK?
• Why is a Migration Toolkit Needed?
• What does MTK convert?
• What does MTK not convert?
• What is involved in a database migration?
• Installation directory name
• MTK won’t start at all
• Source metadata extraction
• Translation problems
• Refine problems
• Deployment to DB2
• What source databases are supported?
• On what platforms does MTK run?
• Migrating DB2 UDB V7.2 to DB2 UDB V8.1 on a Windows platform
• Example of DB2 database connection problem
• DB2-specific questions
• Source database connectivity errors
• Out of memory errors
• Continuing out of memory errors
• What are the steps for conversion?
• In Sybase or SQLServer conversion, CREATE DEFAULT and sp_bindefault statements are not replaced by DB2 statements
• Why does MTK produce separate files for TEXT, IMAGE, CLOB, or BLOB data?
• Why isn't the data moved when converting from Oracle?
• Common setup issues
• MTK fails to load LOB data into the DB2 table
• When I selected from my table I saw references (such as "D161/C20_807113.out") instead of the actual LOB data
• I want to distribute MTK UDFs with my migrated application
• I have encountered "transaction log full" problem when deploying data to DB2 by selecting the "import" function
• I have some stored procedures in MSSQL with default values. It looks like this functionality is not supported in DB2 - is that correct?
• Problem with moving Unicode data from SQL Server to DB2
• Codepage issues into DB2
• I am getting this error when I try to run the SQL Translator: --* [200040] "C:\MTK\projects\testsql\SQLTranslator.input"(24:1)-(24:14) Input Error: card_trans_vue is not a valid table, view, or table function name.
• Whenever I try to migrate the objects from Sybase to DB2, by default it is moving all of the UDT data types also. How can I avoid this?
• How do we specify a specific Tablespace name for DB2 tables?
• MTK creates a lot of functions with SYB, MS7, ORA, or INFX schema. How do I migrate those from one DB2 database to another?
• The installation of the JAR file completed successfully, but creation of each JAVA UDF fails with DB21034E
• I want to keep the original comments on the translation of my stored procedures
• I have my tables in one file and my procedures in another. When I translate the procedures with the tables file set as the context file, the translator says it cannot find the tables.
• Why is a NULLABLE CHAR translated to NULLABLE VARCHAR?
• I cannot connect to the Oracle database
• I do not have an ODBC connection and the native driver option is grayed out
• How do I resolve the Java warning: WARNING: Could not create system preferences directory. System preferences are unusable.
  
  

• Originally developed for DB2 LUW
• Extended for DB2 on z/OS and iSeries and Informix Dynamic Server
• Supports wide range of source DBMS

• No part number
• Internally used by IBM migration specialists and externally used by business partners, ISVs, and more.
  

• Different standards levels
• Differences in ANSI compliance
• Differences in implementations:
• Proprietary source extensions
• Data types and type semantics
• Syntax Procedural language
• Built-in functions
  
  

• DDL statements:
• CREATE TABLE, CREATE INDEX, CREATE VIEW, and more.
• ALTER TABLE and more.
• Constraints
• SQL Statements:
• SELECT, INSERT, UPDATE, DELETE, and more.
• Triggers
• Procedures
• Functions
  
  

• Applications
• Replication schemes
• OLAP specific features
• Catalog/system tables
• Statements specific to system administration
• Disk Partitioning Schemes
  
  

• MTK must not be installed into a directory with a space in the name. Everything will work, until deployment. During deployment, the DB2^® commands to load the DB2 file into DB2 will fail because of the space in the file name.
  
  

The extractor puts statements in the file to set the database or schema environment for the objects in the file. Imported files often lack those USE, CONNECT, or SETUSER statements. The translator uses a default schema for the objects, but it does not always work for helping the translator resolve object names (especially if you are using a mixture of imported and extracted files), and the default schema rarely works for data extraction.

MTK users are sometimes confused about the default schema entry field. When it is set to "from_first_object," the translator takes the schema for the first object as the schema to be replaced by the DB2 user ID used in deployment. When it is set to "specify_schema_for_all_object," the translator keeps the schema on every object through deployment. The user can also type in a specific schema, which is then replaced by the IBM^® target server user ID used during deployment.

If the problem is not caused by user error or is an exception in the translator code, take the source and try to isolate the problem to the smallest test case possible. Provide this test case to the MTK development team.

Refine problems
When the default schema entry filed in the Convert step is set to "from_first_object," the translator takes the schema for the first object as the schema to be replaced by the IBM target user ID used in deployment. Because of this, even if this schema for the first object is refined, it does not take effect as the IBM target user ID that is used during deployment and verification steps.


Deployment to DB2
Make sure you have your IBM target and java environment setup correctly.

To diagnose problems with data extraction look at the mtk.log and the Verify_*.out file. To diagnose problems with deployment into the IBM target database look at the Deploy_*.log files.

What source databases are supported?

• Sybase 11, 12, 12.5 SQL Server 7, and SQL Server 2000.
• SQLServer 6.5 has been reported to work, but is not tested.
• Oracle 7, 8, 9, and 10g.
• Informix^® 7.3 and Informix 9. Data movement from Informix 7.2 is limited by the lack of a TO_CHAR function to put dates into a format that DB2 consumes easily.

• Windows^®: Windows XP and Windows 2000.
• Unix: AIX 5L™ 5.2, Linux^® RHEL 3, and Solaris 2.9/9 platforms.
• Java™ on Solaris is hard to get installed and running. All of Sun's JREs come with a list of required Solaris patches. http://sunsolve.sun.com/pub-cgi/retrieve.pl?doctype=patch&doc=J2SE_Solaris_7_Recommended. The MTK readme has the recommended patches for Java on Solaris 7.
  
  

• Look at this Redbook which discusses moving data across the DB2 family: http://www.redbooks.ibm.com/abstracts/sg246905.html
• Look at this Redbook that highlights differences between DB2 systems: http://www.ibm.com/developerworks/db2/library/techarti
• Find help on the DB2 support website: http://www.ibm.com/db2/udb/support.html
• Contact your IBM representative and ask for support. If you have no IBM representative, send an email to db2mig@us.ibm.com.
  
  

ERROR MTKDB2ConnectionUNO DB2 UDB JDBC Driver not found:COM.ibm.db2.jdbc.app.DB2Driver

ERROR DeploymentExtensionUNO The DB2 Connection to the database ASDTTST1 failed (rc=-6) with the following error message:No suitable driver

This problem was caused because MTK uses the JDBC driver COM.ibm.db2.jdbc.app.DB2Driver which is in db2java.zip. SQLLIB\java\db2java.zip is needed in the classpath.

Check your CLASSPATH by opening a window and typing SET CLASSPATH. The revealed classpath should include:

D:\SQLLIB\java\db2java.zip;D:\SQLLIB\java\runtime.zip;D:\SQLLIB\java\sqlj.zip;D:\SQLLIB\bin;

Where D:\SQLLIB is replaced by the drive and path for your local installation of DB2.

The classpath should not include any .dll files.

You can either change the classpath for the system or add the DB2 jar files to the classpath in the MTKMain.bat file.

If you are running a local 32-bit application (such as MTK) in a 64-bit DB2 instance, LIBPATH, SHLIB_PATH, and LD_LIBRARY_PATH should contain sqllib/lib32 instead of sqllib/lib.

DB2-specific questions

• A request to size an xSeries^® hardware configuration is not within the expertise or scope of MTK or the SMPO team. Techline can do this kind of thing. Check out the following website: http://www.ibm.com/support/americas/techline/sizing.html.
• A C++ compiler is required for stored procedures in DB2 before version 8.2. Alternatives are listed in the DB2 documentation.
  
  

With delimited format, binary data (BLOB) or strings of undefined content, as it is often the case with CLOB data, may cause problems. If the target is specified as DB2 Version 8, MTK uses LOB LOCATOR STRINGS, and puts lob data for many rows into a single file.

Why isn't the data moved when converting from Oracle?
Assuming you have checked the "Extract and store data on this system" and "Load data to target database using generated scripts" boxes on the deployment page, the likely problem is that you are connected to Oracle with a userID that does not have SELECT permission on the tables you are trying to migrate. There will be error messages stating this in the log file (mtk.log in the installation directory).

MTK can extract the metadata because that only requires SELECT permission on the system catalog, however, extracting data requires SELECT permission on the specific table.

Common setup issues

• Cannot connect to the source database:
  Refer to the MTK documentation for information on setup of source database clients: .
• MTK on Unix does not support connections to source database via ODBC:
  Make sure you have the required JDBC clients on Unix.
  
  

• SQL 1042C error on DB2 on Linux when running MTK Java UDFs:
  Make sure your Java environment for Linux is correctly set. For more information, refer to DB2 Information center at http://publib.boulder.ibm.com/infocenter/db2luw/v8//index.jsp
• DB2 UDB JDBC Driver not found:
  Make sure SQLLIB\java\db2java.zip is needed in the classpath.
  
  

If the DB2 server can connect to the source database server, either MTK can be installed on the DB2 server to run the migration there, or the scripts that MTK creates for moving data manually can be executed on the DB2 server, which gets around the LOBSPATH problem. There are detailed instructions for doing this in the help, in the section titled .

If the DB2 server cannot connect to the source database server, things get more complicated:
1. You can use the Directory for data extraction field on the Generate Data Transfer Scripts page to put the extracted data into a directory on the DB2 server.
2. After deploying the objects and extracting the data on the Deploy to DB2 panel, you can move the DataMove_*_db2.bat or DataMove_*_db2.sh file to the DB2 server.
3. Edit DataMove_*_db2.bat or DataMove_*_db2.sh to correct the path.
4. Run DataMove_*_db2.bat or DataMove_*_db2.sh to load the data into DB2


When I selected from my table I saw references (such as "D161/C20_807113.out") instead of the actual LOB data
The LOAD statement used should have two parameters to instruct DB2 on where to get the LOB data. For example:
LOBS FROM C:\MTK\projects\SQL2000\DataOutScripts\dbo_Categories\  MODIFIED BY  LOBSINFILE
If either is missing, then references are written instead of LOB data.


I want to distribute MTK UDF's with my migrated application
Given that IBM provides you the UDFs "as-is" without warranty or indemnification, you can redistribute them with your application.


I have encountered "transaction log full" problem when deploying data to DB2 by selecting the "import" function
To fix this:
1. In the Generate Data Transfer Script panel, select Import and then select the Advanced options button.
2. In the dialog box that appears, enter a number of rows in the commit count field. If the DB2 database is configured with "Log retain for recovery status" set to no (the default), the log space will be recovered at each commit.


I have some stored procedures in MSSQL with default values. It looks like this functionality is not supported in DB2 - is that correct?
MTK stripped off the default values without issuing a message. The default parameter values are not supported by DB2. However, the converter does not lose the default value when it removes it from the procedure statement, instead it adds the default value in the calling statement.


Problem with moving Unicode data from SQL Server to DB2
The load or import statement for a Unicode file needs the "no check lengths" option selected on the advanced options page before creating the data transfer scripts. The codepage may also need to be set on that page.


Codepage issues into DB2
In the Advanced Options for Generate Data Transfer Scripts panel there is a field labeled "code page." This field needs to be filled in with the appropriate value so that the generated LOAD command contains the "codepage=xxx" modifier that tells DB2 to interpret the special characters in the transfer file correctly.

See this article for more information: http://www.ibm.com/developerworks/db2/library/techarticle/0

I am getting this error when I try to run the SQL Translator: --* [200040] "C:\MTK\projects\testsql\SQLTranslator.input"(24:1)-(24:14) Input Error: card_trans_vue is not a valid table, view, or table function name.
The SQL Translator does not have a definition of card_trans_vue available. You can include the definition of card_trans_vue in the script window. If you have run a script containing the definition of card_trans_vue through the Convert step, you can select "Use all files" in the drop-down list next to the Paste button. Then the SQL Translator will have the definition of card_trans_vue available and will not issue the error.


Whenever I try to migrate the objects from Sybase to DB2, by default it is moving all of the UDT data types also. How can I avoid this?
MTK always extracts the UDT data types. The only way to avoid migrating those to DB2 is to edit the source file after extraction and delete the unneeded UDT data types in the file before conversion.


How do we specify a specific tablespace name for DB2 tables?
The MTK team recommends that customers use the DB2 Control Center Wizard to create their tablespaces.

MTK provides an initial CREATE TABLESPACE statement at the beginning of the DB2 file when you chooses that option in the converter advanced options. Providing this CREATE TABLESPACE statement causes the CREATE TABLE statements to keep the IN tablespace parameter and put the tables into tablespaces of the correct names.

You need to add index and long tablespace clauses to the DB2 file after the column list:
 '-IN--tablespace-name1-+----------------------------+---+----------------------------+---
                       |                            |   |                            |
                       '-INDEX IN--tablespace-name2-'   '-LONG IN--tablespace-name3--'


MTK creates a lot of functions with SYB, MS7, ORA, or INFX schema. How do I migrate those from one DB2 database to another?
The functions with the SYB schema are being created by mtksyb.udf during the deployment. If you look in the Deploy_conversion.bat file, you will see a block of lines like this (with cmp replaced by your database name):
@ECHO Installing JAVA UDFs file under name syb.cmp...
DB2 -v -td! -f "C:\MTK\mtksybdrop.udf" >null
DB2 CALL SQLJ.REMOVE_JAR('syb.cmp') >null
DEL null
DB2 -v CALL SQLJ.INSTALL_JAR('file:C:\MTK\sybUDFs.jar','syb.cmp') >>%UDFLOGFILE%
DB2 -v -td! -f "mtksyb.udf" >>%UDFLOGFILE%
@ECHO Creation of MTK UDFs done.
Here is a guide for repeating the deployment of the UDFs into another database:

• SYB from mtksyb.udf or mtksybISeries.udf
• MS7 from mtkms7.udf or mtkms7ISeries.udf
• ORA from mtkora8.udf or mtkora8ISeries.udf
• INFX from mtkinfx.udf or mtkinfxISeries.udf
  
  

SQL20204N The user defined function or procedure "PROCID" was unable to map to a single Java method. LINE NUMBER=3. SQLSTATE=46008

The JAR file is installed under a different name than is used in the external name of the procedure. In other words, CALL SQLJ.INSTALL_JAR('file:/db2home/db2inst2/worksp/scripts/sybUDFs.jar','syb.cmp'):
CREATE FUNCTION SYB.procid()
RETURNS INTEGER
EXTERNAL NAME 'syb.udfjar:com.ibm.db2.tools.mtksybudf.sybUDFs.procid'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
DETERMINISTIC
NOT FENCED
NULL CALL
NO SQL NO EXTERNAL ACTION
DBINFO
When MTK prepares to deploy the UDF file to DB2, it changes the instances of udfjar in the external name to match the DB2 database name.


I want to keep the original comments on the translation of my stored procedures
MTK only preserves all comments (both those created by MTK and original sp comments) or removes all comments. Most of the comments produced by MTK are the original source statements. Because translation to DB2 cannot be a one-for-one mapping of statements, there is no way to copy only the source comments into the DB2 with any assurance that the comments would be placed somewhere that makes sense. Therefore, MTK keeps source comments only with the original source statements.


I have my tables in one file and my procedures in another. When I translate the procedures with the tables file set as the context file, the translator says it cannot find the tables.
The translator maps each database or schema combination into a schema for DB2, since all the objects go into a single DB2 database:

• The objects in the context file all go to dbo schema.
• Objects with dbo schema in the next database go into dbo1 schema.
• If the procedures file contains use database or a setuser username with a different database or username than appears in the tables file, it tells the translator that the following objects are in a new database or schema combination, which will map to dbo1.

You can either remove the use or setuser statement from the procedures file, or add the same statement to the tables file to tell the translator to treat all of the objects as being in the same database or schema combination. The translator will then successfully find the tables.

Why is a NULLABLE CHAR translated to NULLABLE VARCHAR?
Except for the special case of character types of length one (CHAR(1)), nullable character types are converted to variable length character types. This is because Sybase the behavior of VARCHAR is the same as a nullable CHAR. Meaning extra space padding is truncated and NULL values are allowed. However, in DB2 even if a CHAR column is nullable, it remains fixed length for all values except NULL. Also, in DB2 the default is that columns are nullable rather than non-nullable as is the default for Sybase.

I cannot connect to the Oracle database
The machine running MTK needs either an ODBC connection to the Oracle server or an Oracle client and classes111.zip or classes12.zip in the classpath in order for MTK to be able to access the Oracle server. The Oracle native driver connection uses a service name and relies on the Oracle client to use the information in the tnsnames.ora file to translate that into the details of the connection string.
The entry in the tnsnames.ora file on that machine supplies the host and port information. For example:
oracle =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = machine.at.ibm.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oracle)
)
)


I do not have an ODBC connection and the native driver option is grayed out
Oracle
MTK needs either classes111.zip or classes12.zip to be found in the classpath.
MS SQLServer 2000
MTK needs three MS jar files to be found in the classpath. The jar files needed are:

• msbase.jar
• mssqlserver.jar
• msutil.jar