Mr. Ram: ORACLE to DB2 Migration

Sunday, January 15, 2012

ORACLE to DB2 Migration

Table of Contents

Abstract

Part I : Schema and Data Migration

Part II : Application Changes

ABSTRACT

This document explains about the issues and resolutions related to 'ORACLE to DB2 Migration' process. Oracle 11g EE and DB2 luw 9.7 EE Fix Pack 5 environments are chosen for this process. IBM Data Movement Tool (DMT) is used to move database schema and database data from ORACLE to DB2. The Java/J2EE application is working fine with ORACLE database. The application is built with MVC frameworks like Struts 2 and Spring 3; and Object-Relational Mapping (ORM) framework like Hibernate 3; and J2EE Design Patterns. Currently, the application is working with the environment : Linux OS 5.6, JDK 6, Jboss 4.2.3 GA and Oracle 11g EE. DB2 JAR files (db2jcc.jar and db2jcc_license_cu.jar) are used for database connectivity. This document explains the issues occurred while performing 'Oracle to DB2 Migration Process' and the resolutions are made to overcome the issues.

Part I : Schema and Data Migration

Issue 1: Pagesize 32k - column length

Issue Description:

DB2 Limitation of row size. The row length of the table exceeded a limit of "<length>".

Issue Resolution:

The issue is with TABLE. Database table columns are re-sized to VARCHAR2(200).

Issue 2: CLOB usage

Issue Description:

SQL0583N - The use of routine or expression "SYSIBMADM.DBMS_LOB.GETLENGTH" is invalid.

Issue Resolution:

The issue is with VIEW. We can rewrite the query

WHERE lower(dbms_lob.substr(data_value, 4,1)) = 'http'

AND status != 'D'

AND data_value >= 5 AND data_value <= 2000 ;

WHERE lower(dbms_lob.substr(data_value, 4,1)) = 'http'

AND status not in('D')

AND length(data_value) BETWEEN 5 AND 2000;

Issue 3: XML extract

Issue Description:

SQL0104N - An unexpected token "extract" was found following ", table(XMLSequence(". Expected tokens may include: "XMLGROUP".

Issue Resolution:

The issue is with TRIGGER. Column has been changed to XML. Trigger is rewritten.

Issue 4: Sequence curval

Issue Description:

SQL0348N - "DB2_SCHEMA.NRP_SUP_QUEUE_S.CURRVAL" cannot be specified in this context.

Issue Resolution:

The issue is with TRIGGER. Currval is not supported in create trigger statement. Trigger rewritten to take nextval into a variable and then reuse it.

Issue 5: Index tablespace

Issue Description:

DB2 Limitation of row size. The row length of the index exceeded a limit of "<length>".

SQL0614N - The index or index extension "UQ120_NRDOC_FS_DEFAULT_FSENTRY" cannot be created or altered because the combined length of the specified columns is too long.

Issue Resolution:

Issue is with INDEX. The table has been recreated with 32k page-size table-space. Then, the index has been recreated.

But, how do we ensure the table and indexes default to 32k page-size?

During index creation, by including IN clause, we can allot the index to the 32k page-size table-space.

Part II : Application Changes

The changes that made at application side are in the following two ways.

I) Database query changes at DAO level

II) Application level changes

Database query changes at DAO level:

There are two cases for 'database query changes at DAO level'.

1) Update the query to make it compatible with ORACLE and DB2.

2) Prepare a separate query for DB2.

Application level changes:

Application level changes may be related to the following areas.

*) Database configurations in XML and PROPERTIES files.

*) ResultSet Scroll issues with Statement and PrepareStatement.

*) executeQuery() vs executeUpdate() vs execute() issues.

*) jackrabbit tool configurations.

*) Hibernate JAR issues with DB2Dialect and GUID.

*) Others

Issues & Resolutions

Issue Resolutions that are compatible to both ORACLE and DB2 databases

Issue 1:

Exception in getting DAO Factory Class com.nr.common.daofactory.DB2/NTMYDAOFactory. Will return generic class.

Issue Description:

java.sql.DatabaseMetaData dbMetaData = javax.sql.DataSource.getConnection().getMetaData();

String dbProdName = dbMetaData.getDatabaseProductName();

The above specified String variable dbProdName returns the value “Oracle” in case of Oracle database. Based on this variable value, the application would prepare SQLManager file dynamically. In case of Oracle, the SQLManager file name is MyOracleSQLManager.java. But, the variable dbProdName returns the value “DB2/NT” in case of DB2 database. Due to this, the application is trying to prepare SQLManager file dynamically as MyDB2/NTSQLManager.java which is syntactically wrong as per file-naming conventions.

Issue Resolution:

To overcome this issue, we have prepared a bean (as the application supports Spring) with the database related mappings, to make the application prepare SQLManager file dynamically as MyDB2SQLManager.java. We have prepared one more mapping for Application Server related constants.

Example:

<util:map map-class="java.util.HashMap">

</util:map>

</property>

<util:map map-class="java.util.HashMap">

</util:map>

</property>

</bean>

Issue 2:

Exception in createBillInfo method of BillingHibernatePersister java.lang.UnsupportedOperationException : dialect does not support GUIDs

Issue Description:

This issue is related to hibernate.jar file. Hibernate configuration hbm file is having the <generator> element as “guid”. DB2 is not supporting GUID concept. hibernate.jar file is not having the alternative method to handle this issue.

Issue Resolution:

To overcome this issue, we have created a derived class (of DB2Dialect) with DB2 supported GUID function, as below specified. The same customized class (MyDB2Dialect) was mentioned for the Hibernate property “hibernate.dialect” instead of DB2Dialect.

Example:

package com.nr.common;

import org.hibernate.dialect.DB2Dialect;

public class MyDB2Dialect extends DB2Dialect

{

public String getSelectGUIDString()

{

return "select GENERATE_UNIQUE() from DUAL";

}

Hibernate Configuration:

<prop key="hibernate.dialect">com.nr.common.MyDB2Dialect</prop>

Issue 3:

Exception in getConfigParamValues com.ibm.db2.jcc.am.SqlException: [jcc][t4][10179][10900][3.57.82] This method should only be called on ResultSet objects that are scrollable (type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE). ERRORCODE=-4476, SQLSTATE=null

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10179][10900][3.57.82] This method should only be called on ResultSet objects that are scrollable (type TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE). ERRORCODE=-4476, SQLSTATE=null

Issue Description:

The issue was raised when ever Java program trying to move the ResultSet cursor randomly with the following statements rs.beforeFirst(), resultSet.afterLast() and resultSet.previous(), etc. instead of sequetially with resultSet.next() method.

Issue Resolution:

To overcome this issue, we have used the ResultSet constants (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY) while preparing Statement/PreparedStatement objects, as follows.

Example:

PreparedStatement pstmt=conn.prepareStatement("select sysdate from dual", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

ResultSet rset=pstmt.executeQuery();

------------------------------------------------------------------------------------------------------------

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rset = stmt.executeQuery("select sysdate from dual");

Issue 4:

EXCEPTION logEventDetails() com.ibm.db2.jcc.am.SqlException: [jcc][10103][10941][3.57.82] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null

Issue Description:

executeQuery() method was used for INSERT operation instead of executeUpdate() or execute() methods. There is no issue with ORACLE database.

Issue Resolution:

Either executeUpdate() or execute() method can be used for INSERT operation.

Issue 5:

Error occurred in getLockForUser com.ibm.db2.jcc.am.SqlException: [jcc][10104][10942][3.57.82] Method executeUpdate cannot be used for query. ERRORCODE=-4476, SQLSTATE=null

Issue Description:

executeUpdate() method was used for SELECT FOR UPDATE operation. There is no issue with ORACLE database for this query execution.

Example:

SELECT ACTOR_ID FROM NR_PERSON WHERE ACTOR_ID=4002409 FOR UPDATE

Issue Resolution:

execute() method was used for this SELECT FOR UPDATE operation.

Issue 6:

com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-21000, SQLSTATE=42724, SQLERRMC=null, DRIVER=3.57.82

Issue Description:

The issue is related to CONTAINS() function. The query works only if valid parameters are passed to the search, in DB2. The query fails in case either the parameters are absent or wild cards are used.

Example:

SELECT *

FROM SERV_TBL

WHERE ( CONTAINS(LANG_CODE, '%') > 0

OR UPPER(DESCR) LIKE ('%')

OR UPPER(SERV_NAME) LIKE ('%')

OR UPPER(DESCR) LIKE ('%'))

Issue Resolution:

Query is modified for DB2 in the following two ways.

Query when parameter is available.

SELECT *

FROM SERV_TBL

WHERE ( CONTAINS(LANG_CODE, '*A') > 0

OR UPPER(DESCR) LIKE ('%')

OR UPPER(SERV_NAME) LIKE ('%')

OR UPPER(DESCR) LIKE ('%') )

Query when parameter is not available.

SELECT *

FROM SERV_TBL

WHERE ( CONTAINS(LANG_CODE, 'ALL') > 0

OR UPPER(DESCR) LIKE ('%')

OR UPPER(SERV_NAME) LIKE ('%')

OR UPPER(DESCR) LIKE ('%') )

But, the DB2 windows service is required for text indexing. It is a manual service that has to be restarted after other DB2 services on server restart.

Issue 7:

com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-20448, SQLSTATE=22007, SQLERRMC=2011-10-04-00.00.00;dd-mm-yy, DRIVER=3.57.82

Issue Description:

The issue is with DATE format in the query.

Example:

SELECT TO_DATE(CURRENT_DATE,'dd-mm-yy') FROM DUAL

Issue Resolution:

The query has been modified to make it generic, as follows.

Example:

SELECT TO_DATE(TO_CHAR(CURRENT_DATE,'dd-mm-yy'), 'dd-mm-yy') FROM DUAL

SELECT TO_DATE(CURRENT_DATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL

Issue Resolutions that are applicable to DB2 database

Issue 1:

16:43:53,607 ERROR [GenericPromotionDAO] Exception in getPromotionShortInfoForIds():

com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=3.57.82

Issue Description:

The query is not working for DB2 database, in case the PreparedStatement is Scrollable and SELECT list is having CLOB column.

Issue Resolution:

If PreparedStatement is Scrollable, CAST is required for the CLOB columns that are mentioned in the SELECT list. If we are using CAST function on CLOB column, then, we need to mention alias of that column name explicitly.

Example:

Suppose, if the DESCRIPTION field is a CLOB column in SELECT clause, then, it should be specified as follows:

CAST(DESCRIPTION as VARCHAR2(2000)) DESCRIPTION

Issue 2:

Using SQLManager, SQL query operation error: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT SYS_GUID() FROM DUAL]; nested exception is com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SYS_GUID;FUNCTION, DRIVER=3.57.82

Issue Description:

This is query execution issue. The following query works in ORACLE.

SELECT SYS_GUID() FROM DUAL

Issue Resolution:

The following DB2 related query is used.

SELECT GENERATE_UNIQUE() FROM DUAL

Issue 3:

Sql EXCEPTION in processEvent: com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=3.57.82 at com.ibm.db2.jcc.am.bd.a(bd.java:668)

Issue Description:

The DATE TIME or TIMESTAMP value is invalid. Colon (:) is used instead of Dot (.).

Issue Resolution:

TIMESTAMP configuration has been updated for DB2 as follows.

Example:

yyyy-MM-dd-HH.mm.ss.SSS

Issue 4:

Error in getAdminUsers: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-1585, SQLSTATE=54048, SQLERRMC=null, DRIVER=3.57.82

Issue Description:

The issue was raised when ever the main-query and sub-query are using the same VIEW.

Issue Resolution:

We have created another view Nr_PersonInfo_Vl2 which is similar to existing view Nr_PersonInfo_Vl and updated the query as follows.

Example:

SELECT *

FROM Nr_PersonInfo_Vl

WHERE Serv_Id = 0

AND status = 'A'

AND org_id = 4000501

AND actor_id = ( SELECT MIN(actor_id)

FROM Nr_PersonInfo_Vl2

WHERE serv_id = 0

AND status = 'A'

AND org_id = 4000501 )

Issue 5:

Error while getting Language details com.ibm.db2.jcc.am.co: DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=DECFLOAT, DRIVER=3.57.82

Issue Description:

The issue was raised while comparing two dis-similar fields. One field is String datatype; and another field is Decimal datatype.

Issue Resolution:

TO_CHAR() function was used for type-casting as follows.

Example:

SELECT *

FROM NR_ACTOR_ATTRS NRA, NR_LANGUAGE NRL

WHERE NRA.ACTOR_ID = 4000502

AND NRA.GROUP_TYPE = 'ORGANIZATIONLANGUAGE'

AND NRA.STATUS = 'A'

AND NRA.ATTRIBUTE_VALUE = TO_CHAR(NRL.LANGUAGE_ID);

Issue 6:

SQL error in getCategoriesForBusinessCategory com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-245, SQLSTATE=428F5, SQLERRMC=TO_CHAR;1, DRIVER=3.57.82

Issue Description:

The issue was occurred when ever TO_CHAR was used on a CLOB column. DB2 is not supporting such a usage.

Issue Resolution:

Replace TO_CHAR(FIELD_VALUE) With CAST(FIELD_VALUE AS VARCHAR2(2000)).

Issue 7:

ERROR org.apache.jackrabbit.core.fs.db.DatabaseFileSystem - failed to initialize file system

2011-11-16 09:20:09,769 ERROR [STDERR] com.ibm.db2.jcc.am.io: DB2 SQL Error: SQLCODE=-604, SQLSTATE=42611, SQLERRMC=number(38,0), DRIVER=3.57.82

2011-11-16 09:20:09,769 ERROR [STDERR] at com.ibm.db2.jcc.am.bd.a(bd.java:676)

Issue Description:

The issue is related to Jackrabbit tool. The tool is expecting DB2 related configurations.

Issue Resolution:

jackrabbit-core-1.6.0.jar file is copied into JBOSS's server/default/lib; and the following DB2 configurations are specified at repository.xml.

</FileSystem>

and

</PersistenceManager>

Issue 8:

com.ibm.db2.jcc.am.SqlException: [jcc][10389][12245][3.57.82] Failure in loading native library db2jcct2, java.lang.UnsatisfiedLinkError: no db2jcct2 in java.library.path: ERRORCODE=-4472, SQLSTATE=null

Issue Description:

The issue is related to DataBase configurations.

Issue Resolution:

Use a Type-4 URL like jdbc:db2://server:port/database.

Existing:

<connection-url>jdbc:db2:@(description=(address=(host=ServerName)(protocol=tcp)(port=PortNumber))(connect_data=(SERVICE_NAME=DatabaseName)))</connection-url>

Proposed:

<connection-url>jdbc:db2://ServerName:PortNumber/DatabaseName</connection-url>