Sybase to DB2 migration, Part 4: Issues and resolutions

Information assets are key to day-to-day business. They are also a huge source of institutional value and intellectual property that must be preserved, extended, and repurposed as the systems underpinning them change. Given the current business environment, migration has taken on strategic importance for a number of reasons. This article helps you to understand and map various features and data types of Sybase and DB2®.

Share:

Amol D. Barsagade (amolbarsagade@in.ibm.com), Software Architect, IBM

Author Photo: Amol Barsagade Amol D. Barsagade is a software architect with IBM Enterprise Marketing Management, IBM India Software Lab, Pune. Amol provides database solutions for database architecture, performance tuning, capacity management, application development, and migrations to various partners and customers in Asia-Pacific. Amol has a bachelor's degree in computer science and several years of experience working with relational database systems, including Oracle and SQL server. He has made valuable contributions to IBM developer work by reviewing and publishing articles.


developerWorks Contributing author
        level

20 December 2012

Also available in Chinese

Introduction

This article focuses on various migration issues that may occur when migrating from Sybase ASE 15.x to DB2 9.7.x/DB2 10.1.x. The purpose of this article is to enable you to successfully migrate various features of Sybase into DB2.

While migrating to DB2, you can use the following IBM migration tools:


User-defined types

Sybase has user-defined data types, which may not directly map to DB2. In such cases, you can redefine the objects by replacing the user-defined data types of Sybase to equivalent system-defined data types in DB2.

Listing 1. User-defined type sample code in Sybase
create table authors
 (
 au_id id not null,
 au_lname varchar(40) not null,
 au_fname varchar(20) not null,
 phone char(12) not null,
 address varchar(40) null,
 city varchar(20) null,
 state char(2) null,
 country varchar(12) null,
 postalcode char(10) null,
 unique nonclustered (au_id))

In the above code, the au_id column is a user-defined data type ID that accepts numeric and alphabetic data. In DB2, varchar is one such system-defined data type that adheres to all the above characteristics. So on the target server, you can redefine the table by declaring au_id as a varchar data type.

Listing 2. User-defined type sample code in DB2
drop table authors!
create table authors
(
au_id varchar(20) not null,
au_lname varchar(40) not null,
au_fname varchar(20) not null,
phone char(12) not null,
address varchar(40),
city varchar(20),
state varchar(2),
country varchar(12),
postalcode varchar(10),
unique(au_id)
)

Sybase and DB2 data type mapping

When migrating from Sybase to DB2, the data type mappings play an important role especially in the data formatting. The following table shows you various data type mapping parameters, which you can consider while migrating from Sybase to DB2.

Table 1. Data type mapping for Sybase and DB2
SybaseDB2
BIGDATETIMETIMESTAMP(10)
BIGTIMETIMESTAMP(10)
BIGINT(10)BIGINT(8)
BINARY VARCHAR(2) FOR BIT DATA
BIT SMALLINT(2)
CHAR(20)CHARACTER(20)
DATEDATE
DATETIMETIMESTAMP(10)
DECIMAL(10,5)DECIMAL(10,5)
FLOAT(8)DOUBLE(8)
IMAGEBLOB(1073741824)
INT INTEGER(4)
ID CHARACTER(11)
LONGSYSNAMEVARCHAR(255)
MONEYDECIMAL (19,4)
NCHAR(20)CHARACTER(20)
NVARCHAR(20)VARCHAR(20)
NUMERIC(10,5)DECIMAL(10,5)
REALDOUBLE(8)
SMALLDATETIMETIMESTAMP(10,6)
SMALLINTSMALLINT(2)
SMALLMONEYDECIMAL(10,4)
SYSNAME(30)VARCHAR(30)
TIMETIMESTAMP(10,6)
TIMESTAMPVARCHAR(16) FOR BIT DATA
TINYINTSMALLINT(2)
TEXTCLOB(2147483647)
UNICAHR(20)CHARACTER(20)
UNITEXTCLOB(1073741823)
UNIVARCHAR(20)VARCHAR(20)
UNSIGNEDBIGINT BIGINT(8)
UNSIGNED SMALLINTSMALLINT(2)
UNSIGNED INTEGERINTEGER(4)
VARBINARY(20)VARCHAR(40) FOR BIT DATA
VARCHAR(20)VARCHAR(20)

Data type formatting issues

FLOAT in Sybase maps to DOUBLE in DB2

When migrating the Sybase tables columns to DB2 FLOAT data type, Sybase maps to DOUBLE in DB2, which produces the same result while querying the data. But during the validation process, the procedure produces different results. The example below shows the incorrect results after execution of procedure.

Listing 3. Sample code in Sybase
create table discounts  
(
discounttype varchar(40) not null , 
stor_id char(4)  null , 
lowqty smallint   null ,
highqty smallint   null , 
discount float   not null
)
Listing 4. Equivalent code in DB2
create table discounts
( 
discounttype varchar(40) not null,
stor_id vchar(4), 
lowqty smallint,
highqty smallint,   
discount double not null
)

Figure 1 depicts the execution of a Sybase procedure with the FLOAT data type.

Figure 1. Executing the procedure in Sybase
Image shows Sybase procedure

Figure 2 depicts the execution of a DB2 procedure with DOUBLE data type.

Figure 2. Executing the procedure in DB2
Image shows DB2 procedure

Workaround: Since the FLOAT data type of Sybase maps to the DOUBLE data type in DB2, you can redefine the column and replace DOUBLE with DECFLOAT(34) in DB2.

TIMESTAMP

Because TIMESTAMP is an auto-generated column in SYBASE, columns defined as data type TIMESTAMP will automatically get updated when insert/update occurs on that column. You need to declare the column with the similar property as TIMESTAMP in DB2. This Sybase example illustrates the steps to redefine TIMESTAMP on target server DB2: create table tab3 ( c1 char(20) not null , c2 timestamp not null ).

Equivalent DB2 example with workaround:

  1. Redefine the table in DB2 as follows:
    • db2 create table tab3 (c1 char(20),c2 timestamp)
    • Alter table tab3 to add column ts2 as varchar(20), and this column should be always generated, and the format of this column should then be reformatted into the type TIMESTAMP in the existing column. During this entire process, you need to ensure integrity.
      • db2 set integrity for tab3 off
      • db2 alter table tab3 add column c3 varchar(20) generated always as (to_char(c2,'yyyymmddhh24mmssnnnnnn'))
      • db2 set integrity for tab3 immediate checked force generated
Listing 5. Verification
db2 insert into tab3(c1,c2) values('Amol',current timestamp)
db2 select * from tab3
                
C1       C2                       	  C3
------  --------------------------	 ---------------------
Amol     2012-09-26-16.59.30.751000  20120926160930751000

Or:

Listing 6. Verification 2
db2 create table timetest(c1 char(20), c2 timestamp not null generated always
for each row on update as row change timestamp)
                
db2 insert into timetest(c1) values('AMOL')
db2 select * from timetest1
                
                
C1         C2
---------- --------------------------
AMOL       2012-10-11-16.41.21.368000

Function-based index

Sybase supports the function-based index concept, where as in DB2 there is no direct conversion. The workaround for creating the function-based index is to add a column into the target DB2 table as generated always, then create the index on it:

  • Index definition in Sybase (source code)
    • create table tab1(no integer,name varchar(20))
    • create index fb1 on tab1(LOWER(name))
  • Redefine table and index in DB2 (target code)
    • create table tab1(no integer,name varchar(20),Lname generated always as (LOWER(name)))
    • create index fb1 on tab1(Lname ASC)

Or:

  • db2 set integrity for tab1 off
  • db2 alter table tab1 add column Lname varchar(20) generated always as (LOWER(name))
  • db2 set integrity for tab1 immediate checked force generated
  • db2 create index indx1 on tab1(Lname ASC)

Index creation on temporary and global temporary table

Sybase and DB2 both support temporary and global temporary tables to create indices. Migration tools does not directly convert the Sybase temporary and global temporary table The way of defining these tables in Sybase and DB2 are different and described in the example below.

Case 1: Defining temporary table and index

  • Source code in Sybase
    • create table #tmp_tbl(id numeric, name varchar(30), type char)
    • create clustered index indx_tmp1 on #tmp_tbl (id)
  • Equivalent code in DB2
    • db2 declare global temporary table session.tmp_tbl (id int, name varchar(30), type char(20))
    • db2 create index session.indx_tmp1 on session.tmp_tbl(id)

Case 2: defining global temporary table and index

  • Source code in Sybase
    • create table tempdb..gl_tmp_tbl(id numeric, name varchar(30), type char)
    • create clustered index indx_tmp on tempdb..gl_tmp_tbl (id)
  • Equivalent code in DB2
    • create global temporary table gl_tmp_tbl (id int, name varchar(30), type char(20))
    • create index indx_tmp on gl_tmp_tbl(id)

patindx function

The patindx function returns the starting position of the first occurrence of a specified pattern. The patindx function in Sybase supports a maximum of three parameters. The third parameter defines the search pattern in patindx and takes the CHARACTER type as the default search parameter. In DB2, patindx can be replaced by INSTR to achieve the same result as illustrated in the below example:

  • Sybase syntax: PATINDEX(pattern-string,string-to-search)
  • DB2 syntax: INSTR(string-to-find, string-to-search)
Listing 7. patindx function source code in Sybase
create table patindx ( no int , name char(20))
insert into patindx values(1,'amol')
insert into patindx values(2,'prince')
insert into patindx values(3,'rachna')
select patindex('%a%',name) from patindx
1
-----------
          1
          0
          2
Listing 8. patindx function source code in DB2
db2 create table patindx ( no int , name char(20))
db2 insert into patindx values(1,'amol')
db2 insert into patindx values(2,'prince')
db2 insert into patindx values(3,'rachna')
select INSTR(name, 'a') from patindx
                
1
-----------
          1
          0
          2

Pattern search in Sybase and DB2

Sybase has a useful and strong pattern search feature. Sybase not only supports the wildcard % but also _, [ ], and [^] options in order to search strings. The [^] wildcard is used to search for strings not containing a specific character set.

Listing 9. Pattern search source code in Sybase
create table tab1 (c1 char(10) not null ,c2 char(20) not null)
insert into c1 values('abc','def')
insert into c1 values('ghi','jkl')
select * from tab1 where c2 like '[dj]%'
                
                
C1         C2
------- --------
abc        def
Listing 10. Pattern search source code in DB2
db2 create table tab1(c1 char(10),c2 char(20))
db2 insert into tab1 values('abc','def')
db2 insert into tab1 values('ghi','jkl')
db2 select * from tab1 where c2 like 'd%' or c2 like 'j%'
                
C1         C2
---------- ----------
abc        def
ghi        jkl

Mapping of Sybase and DB2 DATE function

dateadd

A date produced by adding date parts to another date and can be used to return a new date by adding a value to the year, month, or day component.

Listing 11. dateadd example in Sybase
select dateadd (dd, 1, "2012/10/8")
                
Oct 9,2012 12:00:00 AM
Listing 12. dateadd example in DB2
db2 select current date,current date+1 days from sysibm.dual
                
1			    2
----------		----------
10/08/2012		10/09/2012

datediff

The amount of time between the second and first of two dates, converted to the specified date part (for example, months, days, hours).

Listing 13. datediff example in Sybase
select datediff (yy, "2000", "2012")
                
12
                
select datediff (mm, "2000", "2012")
                
144
                
select datediff (dd, "2000", "2012")
                
4383
Listing 14. datediff example in DB2
db2 select MONTHS_BETWEEN  (to_date('2000-10-08','YYYY-MM-DD') ,current date), 
current date  from sysibm.dual
                
1                         2
--------------------	-------------
-144.000000000000000	 10/08/2012
                
db2 select timestampdiff(256, char(to_date('2000-10-08','YYYY-MM-DD') - current date)), 
current date  from sysbm.dual
                
1				 2
------			---------
-12				10/08/2012

datename

This function is used to return the name of the specified part from the datetime expression. It can return month, date and year using as follows.

Listing 15. datename example in Sybase
select datename (mm, "2012/10/19")
                
October
                
select datename (dd, "2012/10/19")
                
19
                
select datename (yy, "2012/10/19")
                
2012
Listing 16. datename example in DB2
db2 select dayname(current date)  from sysibm.dual
                
1
-------
Monday
                
db2 select monthname(current date), current date from  sysibm.dual
                
1             2
---------    ----------
October      10/08/2012

datepart

This function is used to return an integer value for the specified part of a DATETIME value. datepart returns a number that follows ISO standard 8601, which defines the first day of the week and the first week of the year. Depending on whether the datepart function includes a value for calweekofyear, calyearofweek, or caldayofweek, the date returned may be different for the same unit of time. For example, the listing below shows if Adaptive Server is configured to use U.S. English as the default language.

Listing 17. datepart example in Sybase
select datepart (day, "2012/10/19")
                
19
                
select datepart (month, "2012/10/19")
                
10
                
select datepart (year, "2012/10/19")
                
2012
Listing 18. datepart example in DB2
db2 select day(current date), current date from  sysibm.dual
                
1		 2
-----	----------
8 		10/08/2012
                
db2 select month(current date), current date from  sysibm.dual
                
1		 2
------	----------
10 		10/08/2012
                
db2 select year(current date), current date from  sysibm.dual
                
1		  2
-------   ----------
2012		10/08/2012

Conclusion

This article describes various features of Sybase and its equivalent mapping in DB2. While migrating from Sybase to DB2, this article provides the workaround for various data types, functions, date time formatting, and more.


Acknowledgement

I would like to thank to Dan A. Simchuk (simchuk@us.ibm.com) for his expertise and review comments.

Resources

Learn

Get products and technologies

  • Download the IBM DB2 10.1 for Linux, UNIX, and Windows evaluation version.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=852426
ArticleTitle=Sybase to DB2 migration, Part 4: Issues and resolutions
publish-date=12202012