Solved

migrate from IBM DB2 Version 8.1 to MS SQL server 2008 R2 with SP2 + CU4

Posted on 2014-01-12
21
1,182 Views
Last Modified: 2014-01-16
Dear all,

Right now we are consider migrating the DB2 V 8.1 to MS SQL server 2008 R2 with SP2 + CU4:

1) is it possible ?
2) what should we do if it is not possible ? e.g., upgrade to MS SQL server 2005 first?
3)  What should we do if it is possibe? step by step guide please.
0
Comment
Question by:marrowyung
  • 9
  • 7
  • 3
  • +2
21 Comments
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Marrowyung,

Whenever I hear someone suggest that they want to migrate a database from one vendor to another I always ask, "why?"  There's expense and risk involved, and the reasons for moving are often misguided.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
hi, how are you? thanks for the help last time. and I am happy if you can give me more "Gift" aahahha.


yeah, you are right and the reason is no one here are good at DB2 and the one is really good and the final decision is to use a cheaper one, MS SQL server 2008, but probably standard edition.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
If you're buying SQL now, you might as well get SQL 2012.  If you already have a SQL 2008 instance, you can go to it.  No reason to start with SQL 2005.

You'd need to do some more web searches for a step-by-step guide.  Best to get that from some people who have actually done it.
0
 
LVL 11

Assisted Solution

by:John_Vidmar
John_Vidmar earned 50 total points
Comment Utility
Creating tables and transferring data should be fairly simple, however, the non-standard SQL extensions will require significant effort.  I'm talking about commands and constructs that are usually found in stored-procedures, user-defined functions, triggers, and (to a lesser degree) views.  Microsoft uses Transact SQL (T-SQL), which shares syntax similarities to Sybase ASE, but DB2 has its own.  I suspect there are things you can do in DB2 where there is no equivalent in T-SQL.
0
 
LVL 45

Accepted Solution

by:
Kdo earned 250 total points
Comment Utility
Moving the data can be a pain, but there are few technical challenges to it.  Mostly just get your hands dirty and make it happen.  You may be able to link a client to both servers, or even federate your DB2 database to SQL server and move the data with INSERT statements.  The backup files from different vendors aren't compatible.  You won't be able to take a backup of the DB2 data and use that file in any SQL Server utilities to create/load a new database.  Probably the "best" solution is to EXPORT the db2 data into a column or csv file and the use BCP to load the data into SQL Server.

The SQL and DDL between DB2 and SQL Server are probably closer than between any other two vendors, but there are certainly differences that you'll need to manage.  You should be able to use your GUI client to generate the CREATE TABLE statements in your DB2 environment.  If you can live with the defaults (blocking, padding, tablespace, etc.) the DDL will "mostly" run in SQL SERVER to create the new tables there.  Check the table names and date, time, and timestamp type of fields.

You'll have to check and perhaps even modify all of your locally written stored procedures, functions, and triggers.  The SQL syntax for manipulating dates and times is completely different.  The DB2 "sometime + 2 DAYS - 4 HOURS" syntax does not exist in SQL Server.  (To me, this syntax alone is the biggest reason to not migrate applications to SQL Server.)  SQL Server uses function calls to accomplish the same thing, and the parameters aren't obvious.  You'll get used to them, but they're a nuisance.

DB2 uses the double pipe (||) syntax for string concatenation.  SQL Server uses the plus (+).  All of your SQL will need to be converted.  Also note that the two vendors have different ideas about how items are automatically converted when concatenated into a string.  Just changing double pipe to plus doesn't convert the SQL!

CTE syntax and usage is nearly identical.  Language elements (concatenation, timestamps, etc.) used in the CTE needs to conform, but the CTE structure should require little change, if any.



A word of caution before you go down this path.  Having a DB2 database without the proper resource to administer it is no different than having a SQL Server database without the proper administrator.  They're both database engines with the same need for established policies for storage, backups, replication, and other maintenance.  A significant portion of my time, even in my own shop, is spent cleaning up after SQL Server guys that think that they're qualified DBAs just because they can run the admin GUI.  All too often they find themselves ankle deep in muck, try to fix it without understanding SQL Server any better than they do DB2 or Oracle, and the project winds up suddenly waist deep in muck.  All too often the post-mortem goes like this:  "Why did you click on that button?"  "Because that fixed it last time".

If you've got a business reason to move to SQL Server, by all means do what's best for your shop.  But you'll have a significant cost and risk associated with it and unless you've got a qualified SQL Server admin, you won't really be in a better position than you are now.

If licensing costs are an issue, DB2 Express-C is free and is a fully functioning version of DB2.  It has several limitations, of course, but unless you require the federated feature, it's probably sufficient.  It allows databases of any size.  It does limit the DBMS to 4GB memory and 4 CPUs, but that's more than is generally available on any 32-bit O/S.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I think the login mapping is the first painful one as there are no root in MS SQL server, for example.

I do some research already but the point there should be:
1) Microsoft OLE DB Provider for DB2 V3.0 for SQL server 2008 R2 to connect to DB2, then we can already do some migration already. Data moving/copy for example.
2) host integration server 2010, nearly forget about this, is mainly for this purpose. but we will not buy it but probably will use as a temporary solution before cut over. Then decomposite that HIS2010.

we use it as the DB for remedy incident management application and it can use only SQL 2008 R2 with SP2. not SQL 2012.

"You should be able to use your GUI client to generate the CREATE TABLE statements in your DB2 environment.  If you can live with the defaults (blocking, padding, tablespace, etc.) the DDL will "mostly" run in SQL SERVER to create the new tables there.  Check the table names and date, time, and timestamp type of fields.
"
nice try.



kdo:

"Probably the "best" solution is to EXPORT the db2 data into a column or csv file and the use BCP to load the data into SQL Server.
"
one of the possibility. But MS SQL do not have tablespace !! that's another problem.


"Having a DB2 database without the proper resource to administer it is no different than having a SQL Server database without the proper administrator. "

we have MS SQL administrator here.

"All too often the post-mortem goes like this:  "Why did you click on that button?"  "Because that fixed it last time".
"

no one here do work in this way. thanks for telling us.

thanks for suggesting DB2 express, but we should not use it.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
"Probably the "best" solution is to EXPORT the db2 data into a column or csv file and the use BCP to load the data into SQL Server.
"
one of the possibility. But MS SQL do not have tablespace !! that's another problem.


I don't see the issue here.  The basic form of the command is:

$ db2
db2 => EXPORT TO filename OF DEL SELECT * FROM users;

It doesn't care about tablespaces or other containers.

Kent
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
Comment Utility
In case you have not seen it, I would look for Microsoft's old (but still worthwhile) article called:
Guide to Migrating from DB2 to SQL Server
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
Anthony,

"Guide to Migrating from DB2 to SQL Server " means ?

http://www.google.com.au/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&ved=0CEcQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FC%2F0%2FC%2FC0C8C15A-32AD-418E-85BF-A6FF0CCE052C%2FMigrateDB2toSQLServer_2009.pdf&ei=XJrUUoTLBMLqiAexv4DQBg&usg=AFQjCNGGrLBKAcpArRMgmNG0UFgTg2pYpA&bvm=bv.59378465,d.aGc

when I do search using this keyword is see more:

Hi,

Currently, we don't have a SSMA migration tool for DB2. The document pointed by Alberto is also recommend by Microsoft as you can see at http://www.microsoft.com/sqlserver/en/us/default.aspx.

Besides, you can use SQL Server Import and Export Data Wizard or SQL Server Integration Services (SSIS) to import data from DB2 to SQL Server. You need to install Microsoft OLEDB Provider for DB2 on the server on which SQL Server is running.

Hope this helps.

Thanks,
 Chunsong

Open in new window


but that link is not working as expected: http://www.microsoft.com/sqlserver/en/us/default.aspx.

any idea?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
No.  Not really, I could download and view the PDF just fine.  I have no idea why you cannot download it.

Just to clarify, all I did was to click on the same link you posted, downloaded the PDF document with 92 pages and then viewed it.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
one thing, the "Guide to Migrating from DB2 to SQL Server" thing I downloaded, it seems everything is manually effort, not an automatically method for that. right?

let see attached and please suggest if you guys get a CORRECT one.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
There is no magic button that you can push to convert your DB2 database and applications to SQL Server.  That's the DBA's job.

There are guidelines and advise, but you've got to do the work yourself....
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Exactly, hence the title is "Guide to Migrating from DB2 to SQL Server".

This is true of DB2 and all databases, anyone who suggests that you can migrate any non-trivial database to SQL Server with a touch of a button is to say the least confused.  This is and should be a manual process, with a lot of thought put into it.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
ok, yeah..

But I think we need to HIS 2010, this is why it comes and make it easlier for us!

the ODBC OLE 3.0 driver for MSSQL to connect to DB2 is another good choice.

the pdf just like the application migration, a lot of reserved keyword to knows.
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi Marrowyung,

Without trying to be too hard on you or the people around you, this statement has a lot of red flags to me:

"Having a DB2 database without the proper resource to administer it is no different than having a SQL Server database without the proper administrator. "

we have MS SQL administrator here.


If your SQL administrator really is as skilled as your comments suggest, he should know the amount of effort required to migrate a database from a different vendor into SQL Server.  The fact that he's not answering these questions for you indicates to me that you're seriously considering putting your project at significant risk to conform to the wishes of a junior DBA.  I certainly won't put my job and career at risk on such a proposition.


Kent
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
Dear Kdo,

thanks for that comment and as I said:

"But I think we need to HIS 2010, this is why it comes and make it easlier for us!

the ODBC OLE 3.0 driver for MSSQL to connect to DB2 is another good choice."

this already indicated that the DBA has done something and we just try to gather more information on this.

but other solution is to have the vendor of the application (We are trying to do application upgrade and therefore consider DB migration, everything is old already!) do the migration for us and we have to verify if everything is ok instead of saying , we just don't know that method is not working (at that moment).

So doing the study right now already means we have something on hands and I believe that ODBC OLE 3.0 for MS SQL already can do something.

one thing is, we shouldn't guess something we really didn't do before! good preparation and planning is all at the beginning that make a good start.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
That's also why I close this ticket so quick as the studies is pretty close!
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Hi marrowyung,

That sounds even worse to me.  Your project is paying for a study that you know will have a specific outcome and the people conducting the study don't have any idea of its impact.

Think very hard on this one, and update your resume.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
nono, I dont' think in this way.

one question, for somehting new and you don't know who to handle, what willl you do?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
that's why the EE is about as people is finding help AFTER they get into trouble.

But now, we gather information BEFORE that, good start, right?
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility
Who to trust when something new comes along is a tough question.  We deal with it every day whether it's a leaky roof, noise in the engine, or problem at work.  I don't know of a good roofer or mechanic to recommend to you, but for technology issues keep coming back to EE.  We've been able to help you out in the past and should be able to keep up the assistance even if your technology changes.  (A shameless plug, I know, but it's also true.)

And gathering information before you get into trouble is certainly better than calling the fire department later!  :)


Kent
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now