Analysis of potential problems in DB2 database application migration

In general, it is impossible to avoid any problems during the migration of DB2 database applications. This article will introduce some problems that may occur during the migration of DB2 database applications. BKJIA has previously introduced how to combine Oracle Data in DB2. Hope to help you.

I have worked as an engineer in DB2 database application migration to understand how the IBM MTK tool can complete tasks during the migration process. However, this tool does not help you complete all migration tasks, there is also a lot of migration work that we need to use our own experience and methods to complete. The following is a summary of some common problems during the DB2 database migration process. Here, I will only describe the common problems of migrating data from Oracle to DB2.

1 Relationship Between DB2 and common oracle Data Types

Oracle Data Type	DB2 UDB Data Type	Notes
CHAR (n) VARCHAR2 (n)	CHAR (n) VARCHAR (n)	1 <= n <= 254, if n> 255 uses VARCHAR (n) N <= 32672. If n> 32672 uses CLOB (n)
BLOB CLOB	BLOB (n) CLOB (n)	If n <= 2 GB, BLOB (n) is used) If n is <= 2 GB, CLOB (n) is used)
NUMBER	FLOAT/ REAL/ DOUBLE	In actual use, ORACLE is not used for non-precise data. For actual use, refer to DECIMAL (p, s) and DECIMAL (p) conversions.
NUMBER (p) NUMBER (p, s)	SMALLINT/ INTEGER/ BIGINT/ DECIMAL (p, s)	SMALLINT, if 1 <= p <= 4 INTEGER, if 5 <= p <= 9 BIGINT, if 10 <= p <= 18
DATE	DATE TIME TIMESTAMP	If you only use MM/DD/YYYY, select DATE If you only use HH: MM: SS, select TIME If you want to use the date and time, select the timestamp type The TO_CHAR () function in Oracle can be used to obtain the DATE string to match the DATE, TIME, and TIMESTAMP of DB2 respectively.

If you only use MM/DD/YYYY, select DATE

If you only use HH: MM: SS, select TIME

If you want to use the date and time, select the timestamp type

You can use the TO_CHAR () function in Oracle to obtain the DATE string to match the DATE, TIME, and TIMESTAMP of DB2 respectively.

2. ORACLE implicit conversion

The table above lists the mappings between DB2 and oracle Data Types. Apart from the type conversion between the two databases, during program migration, you must note that oracle supports implicit conversion between data types, but DB2 does not support this feature, therefore, you must configure the field type when using SQL for data query or update.

For example, in the table, the employee field empno is defined as a number.

 
 

  
  
select * from employee where empno='1010'; 
 
 

This statement can be executed in the oracle database environment, but the error SQLCODE:-408, SQLSTATE: 42821 is reported in DB2. Corrected

 
 

  
  
select * from employee where empno=1010; 
 
 

If a variable is used in a program, you can use the cast keyword to convert the data type. Syntax:

 
 

  
  
select * from employee where empno =cast(V_name as bigint)  
  
  
select * from employee where empno =cast(V_name as varchar(10)) 
 
 

3. Date and Time Processing

In oracle, the DATE type represents the DATE, time, and DATE and time values. In DB2, each type of time corresponds to a specific type, as shown below:

System Time:

 
 

  
  
Oracle  
  
  
SELECT sysdate from dual;  
  
  
DB2  
  
  
SELECT current timestamp FROM sysibm.sysdummy1;  
  
  
SELECT current date FROM sysibm.sysdummy1;  
  
  
SELECT current time FROM sysibm.sysdummy1; 
 
 

Convert date and time to character type:

 
 

  
  
Oracle  
  
  
 TO_CHAR(date_expression_r_r, 'YYYY-MM-DD')  
  
  
 TO_CHAR(date_expression_r_r, 'HH24:MI:SS')  
  
  
DB2  
  
  
CHAR(date_expression_r_r,ISO)  
  
  
CHAR(time_expression_r_r,ISO) 
 
 

Convert the date and time string to the date and time type:

 
 

  
  
Oracle  
  
  
 TO_CHAR(date_expression_r_r, 'YYYY-MM-DD')  
  
  
 TO_CHAR(date_expression_r_r, 'HH24:MI:SS')  
  
  
DB2  
  
  
DATE('2005-05-20')  
  
  
TIME('18:59:59')  
  
  
TIEMSTAMP('2007-2-1', '21:12:12')  
  
  
TIEMSTAMP('2007-2-1 21:12:12') 
 
 

DB2 also has TO_CHAR and TO_DATE functions, but only fixed conversion formats are available, as shown below:

 
 

  
  
TO_CHAR (timestamp_expression_r_r,'YYY-MM-DD HH24:MI:SS')  
  
  
TO_DATE (string_expression_r_r, 'YYY-MM-DD HH24:MI:SS') 
 
 

4. null value processing

Oracle null value processing is implemented through nvl function, while DB2 is implemented through COALESCE function. The NVL function has been implemented in DB2 V9.5.

In Oracle, the processing of null data is flexible. The results of comparing and splicing with NULL values are completely different from those of DB2. For example:

 
 

  
  
SELECT 'abc' || c1 FROM t1 (c1 IS NULL)  
 
 

ORACLE returns the abc result

If it is null in DB2, to get the result of abc, modify it according to the following syntax,

 
 

  
  
SELECT 'abc‘ || COALESCE(c1,'') FROM t1 
 
 

In Oracle, the select * from table where id = null syntax is allowed and is not allowed in DB2. However, in Oracle, although select * from table where id = null can be checked by syntax, however, it is different from the result set obtained by the select * from table where id is null statement. Therefore, we must consider the actual business meaning during migration and translate it into the DB2 syntax.

In addition, some applications have the following usage:

 
 

  
  
SELECT * FROM TABLE WHERE ('' IS NULL OR ID is null) 
  
  
AND ('' IS NULL OR NAME = '')  AND NUM = '0' 
 
 

In DB2, it can be translated:

 
 

  
  
SELECT * FROM TABLE WHERE (cast(null as varchar(10)) IS NULL OR ID is null)   
  
  
AND (cast(null as varchar(10)) IS NULL OR NAME is null)  AND NUM = '0'