March 27, 2014 at 6:37 am
Hi,
Need a help in Migrating a database of 100 GB from DB2 to Sql server.
Client would extract the data from DB2 into a delimited file and then we need to export that file to sql server.
Can any one tell me the best possible way to that...
You help is much appreciated.
Thanks in advance.
March 27, 2014 at 7:20 am
Hi There,
You could add the DB2 server as a linked server and then select the data you want direct into SQL Server.
http://support.microsoft.com/kb/222937
Or export all the data into flat files and use BCP or BULK COPY to bring the data in.
March 27, 2014 at 7:51 am
Thanks for update.
I guess the DB2 syntax and data structure is total different.i think i need to modify the data structure according to SQL server.
Is there any free tool or any way to convert the structure from DB2 to MSSQL.
i am not much aware of development side....any suggest would be much help full for executing in this case...
March 27, 2014 at 9:06 am
I don't know DB2 at all
I have just had a quick look and the DB2 create table syntax is not too far off SQL Server and you can get the DDL scripts for the tables as shown in this post.
http://stackoverflow.com/questions/10014278/db2-control-center-show-sql-for-existing-table
You can then create the same table sturctures in the new SQL Server and populate them
March 27, 2014 at 9:28 am
Have you tried the import/export wizard? You could use it to create the SSIS package and review it to make sure your destination has the correct structure.
March 27, 2014 at 10:55 am
bala2 (3/27/2014)
Thanks for update.I guess the DB2 syntax and data structure is total different.i think i need to modify the data structure according to SQL server.
Is there any free tool or any way to convert the structure from DB2 to MSSQL.
i am not much aware of development side....any suggest would be much help full for executing in this case...
What's your requirement ? Converting whole DB or just importing data from DB2 to SQL Server ?
--
SQLBuddy
March 28, 2014 at 7:18 am
When we did this I used the Import/Export wizard. You have to install the DB2 client on the server and config the dbs but... it works great.
March 28, 2014 at 10:01 am
bala2 (3/27/2014)
Hi,Need a help in Migrating a database of 100 GB from DB2 to Sql server.
Client would extract the data from DB2 into a delimited file and then we need to export that file to sql server.
Can any one tell me the best possible way to that...
You help is much appreciated.
Thanks in advance.
bala2, I live in both the DB2 and MS SQL worlds, so I do such loads almost daily. First question, is your DB2 environment DB2 LUW (and if so which OS) or is it DB2 on z/OS or z/Linux? And which version of DB2?
Either way you could unload the data into CSV files then load it into MS SQL using BCP, but one caveat I've always ran into are dates given DB2 can go all the way down to year 0000 but MS SQL can only go down to 1753. For this you need to do a replace in DB2 timestamp columns to a date above or equal to 1753 or you'll get conversion issues.
I've honesty had good luck with SSIS, which if so I'd suggest getting the MS OLEDB Provider for DB2 v4.0 - http://www.microsoft.com/en-us/download/details.aspx?id=29100 - which will allow you to create an OLE DB connection into DB2 from SSIS. Once this is done you can use a Data Load Task with DB2 as the source and MS SQL as the destination and use standard ETL processes to pull your data over. Again the date will be an issue so unless you're 100% sure no dates in DB2 are below 1753 you'd probably want to use a Query to select your data from DB2 and use case statements to check the date change it to say 1900-01-01 if it's less than that in DB2.
Another option is creating a database link to DB2, which again you can use the MS OLEDB Provider for DB2 v4.0 or if you have the IBM DB2 client installed on the MS SQL server use ODBC which is slower but it works. Then use openquery to select your data from DB2. For example:
select * from openquery(dblink,'select * from DB2Table');
The BIGGEST issue here is the entire results of the table get loaded into your Log file before it moves it into your MS SQL table, so if you have very large tables this may not be an option else you'll fill-up your log file very quickly.
Anyway just some options, but send back more details on your DB2 environment and I'll see if I can give other options.
Take care,
Sam
April 2, 2014 at 9:50 am
Thanks for replying all
Hi samalex
,
Below is db2 and os deatils.
DB2 verion and OS is db2/6000 8.2.7 on AIX.
We will not be having access to DB2 server for security reasons , then i think we will not be able to use the methods you mention above.
Is any other way to do.Please suggest.
Many Thanks..
February 1, 2017 at 6:15 am
Hi Sam,
I know it is an old thread but could you confirm if SSIS can be used to migrate the DB2 views and other such PL\SQL objects to SQL Server database? Would SSIS be able to convert the DB2 syntax to SQL Server syntax?
Thanks for the help.
February 20, 2017 at 7:39 am
samalex - Friday, March 28, 2014 10:01 AMbala2 (3/27/2014)
Hi,Need a help in Migrating a database of 100 GB from DB2 to Sql server.Client would extract the data from DB2 into a delimited file and then we need to export that file to sql server.Can any one tell me the best possible way to that...You help is much appreciated.Thanks in advance.bala2, I live in both the DB2 and MS SQL worlds, so I do such loads almost daily. First question, is your DB2 environment DB2 LUW (and if so which OS) or is it DB2 on z/OS or z/Linux? And which version of DB2?Either way you could unload the data into CSV files then load it into MS SQL using BCP, but one caveat I've always ran into are dates given DB2 can go all the way down to year 0000 but MS SQL can only go down to 1753. For this you need to do a replace in DB2 timestamp columns to a date above or equal to 1753 or you'll get conversion issues.I've honesty had good luck with SSIS, which if so I'd suggest getting the MS OLEDB Provider for DB2 v4.0 - http://www.microsoft.com/en-us/download/details.aspx?id=29100 - which will allow you to create an OLE DB connection into DB2 from SSIS. Once this is done you can use a Data Load Task with DB2 as the source and MS SQL as the destination and use standard ETL processes to pull your data over. Again the date will be an issue so unless you're 100% sure no dates in DB2 are below 1753 you'd probably want to use a Query to select your data from DB2 and use case statements to check the date change it to say 1900-01-01 if it's less than that in DB2.Another option is creating a database link to DB2, which again you can use the MS OLEDB Provider for DB2 v4.0 or if you have the IBM DB2 client installed on the MS SQL server use ODBC which is slower but it works. Then use openquery to select your data from DB2. For example:select * from openquery(dblink,'select * from DB2Table');The BIGGEST issue here is the entire results of the table get loaded into your Log file before it moves it into your MS SQL table, so if you have very large tables this may not be an option else you'll fill-up your log file very quickly. Anyway just some options, but send back more details on your DB2 environment and I'll see if I can give other options.Take care,Sam
February 20, 2017 at 7:39 am
samalex - Friday, March 28, 2014 10:01 AMbala2 (3/27/2014)
Hi,Need a help in Migrating a database of 100 GB from DB2 to Sql server.Client would extract the data from DB2 into a delimited file and then we need to export that file to sql server.Can any one tell me the best possible way to that...You help is much appreciated.Thanks in advance.bala2, I live in both the DB2 and MS SQL worlds, so I do such loads almost daily. First question, is your DB2 environment DB2 LUW (and if so which OS) or is it DB2 on z/OS or z/Linux? And which version of DB2?Either way you could unload the data into CSV files then load it into MS SQL using BCP, but one caveat I've always ran into are dates given DB2 can go all the way down to year 0000 but MS SQL can only go down to 1753. For this you need to do a replace in DB2 timestamp columns to a date above or equal to 1753 or you'll get conversion issues.I've honesty had good luck with SSIS, which if so I'd suggest getting the MS OLEDB Provider for DB2 v4.0 - http://www.microsoft.com/en-us/download/details.aspx?id=29100 - which will allow you to create an OLE DB connection into DB2 from SSIS. Once this is done you can use a Data Load Task with DB2 as the source and MS SQL as the destination and use standard ETL processes to pull your data over. Again the date will be an issue so unless you're 100% sure no dates in DB2 are below 1753 you'd probably want to use a Query to select your data from DB2 and use case statements to check the date change it to say 1900-01-01 if it's less than that in DB2.Another option is creating a database link to DB2, which again you can use the MS OLEDB Provider for DB2 v4.0 or if you have the IBM DB2 client installed on the MS SQL server use ODBC which is slower but it works. Then use openquery to select your data from DB2. For example:select * from openquery(dblink,'select * from DB2Table');The BIGGEST issue here is the entire results of the table get loaded into your Log file before it moves it into your MS SQL table, so if you have very large tables this may not be an option else you'll fill-up your log file very quickly. Anyway just some options, but send back more details on your DB2 environment and I'll see if I can give other options.Take care,Sam
February 20, 2017 at 7:41 am
amitprang - Monday, February 20, 2017 7:39 AMsamalex - Friday, March 28, 2014 10:01 AMbala2 (3/27/2014)
Hi,Need a help in Migrating a database of 100 GB from DB2 to Sql server.Client would extract the data from DB2 into a delimited file and then we need to export that file to sql server.Can any one tell me the best possible way to that...You help is much appreciated.Thanks in advance.bala2, I live in both the DB2 and MS SQL worlds, so I do such loads almost daily. First question, is your DB2 environment DB2 LUW (and if so which OS) or is it DB2 on z/OS or z/Linux? And which version of DB2?Either way you could unload the data into CSV files then load it into MS SQL using BCP, but one caveat I've always ran into are dates given DB2 can go all the way down to year 0000 but MS SQL can only go down to 1753. For this you need to do a replace in DB2 timestamp columns to a date above or equal to 1753 or you'll get conversion issues.I've honesty had good luck with SSIS, which if so I'd suggest getting the MS OLEDB Provider for DB2 v4.0 - http://www.microsoft.com/en-us/download/details.aspx?id=29100 - which will allow you to create an OLE DB connection into DB2 from SSIS. Once this is done you can use a Data Load Task with DB2 as the source and MS SQL as the destination and use standard ETL processes to pull your data over. Again the date will be an issue so unless you're 100% sure no dates in DB2 are below 1753 you'd probably want to use a Query to select your data from DB2 and use case statements to check the date change it to say 1900-01-01 if it's less than that in DB2.Another option is creating a database link to DB2, which again you can use the MS OLEDB Provider for DB2 v4.0 or if you have the IBM DB2 client installed on the MS SQL server use ODBC which is slower but it works. Then use openquery to select your data from DB2. For example:select * from openquery(dblink,'select * from DB2Table');The BIGGEST issue here is the entire results of the table get loaded into your Log file before it moves it into your MS SQL table, so if you have very large tables this may not be an option else you'll fill-up your log file very quickly. Anyway just some options, but send back more details on your DB2 environment and I'll see if I can give other options.Take care,Sam
Hi,
I am running with the same issue. Could you please provide me your mobile number so we can talk regarding it as it is very urgent request from the client.
You can send me your number on amitprang@gmail.com.
Regards,
Amit
February 20, 2017 at 8:21 am
You have got to be kidding.
February 22, 2017 at 11:38 am
Divine Flame - Wednesday, February 1, 2017 6:15 AMHi Sam,I know it is an old thread but could you confirm if SSIS can be used to migrate the DB2 views and other such PL\SQL objects to SQL Server database? Would SSIS be able to convert the DB2 syntax to SQL Server syntax?
Thanks for the help.
I'm a little confused.... you ask if SSIS can be used to migrate the DB2 views and then state "and other such PL\SQL objects"... DB2 does NOT use PL\SQL. That's an Oracle designation for their SQL syntax. You CAN migrate the DATA from a DB2 view using SSIS, but unless you have a way to query DB2 for the text that represents the actual SELECT statement for that view, you don't even have a way to capture that information, which you MAY then have to manually convert the syntax on in order to be able to create the view. An automated process, it's NOT. Best of my knowledge, SQL Server is not able to convert DB2 syntax in any way. What allows these disparate database systems to work together is ODBC, which only supports a certain subset of SQL syntax. Alternately, SQL Server supports an OPENQUERY clause, that allows you to specify the exact SQL to send to a linked server, of whatever variety, whether it's DB2, Oracle, Teradata, Postgre/SQL, etc...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply