Migrating DB2 to SQL Server 2005

Hi,
I want to migrate my data from IBM DB2 to SQL Server 2005. What drivers
are needed to do the same? Where will I get all those drivers. Could
any one help me?
Is there any other ways to migrate data from DB2?
Thanks and regards,Hi
I think you are going to install CLIENT ACCESS on the SQL Server to
communicate with DB2/AS400 .We've done it on SQL Server 2000 and by using
DTS packages transfered the data from AS400.
As well you can write a small VB.NET app that usein OLEDB driver (search on
internet) to work against DB2
"renil" <renilakader@.gmail.com> wrote in message
news:1152853349.316893.157290@.m79g2000cwm.googlegroups.com...
> Hi,
> I want to migrate my data from IBM DB2 to SQL Server 2005. What drivers
> are needed to do the same? Where will I get all those drivers. Could
> any one help me?
> Is there any other ways to migrate data from DB2?
> Thanks and regards,
>

Migrating DB2 to SQL Server 2005

Hi,
I want to migrate my data from IBM DB2 to SQL Server 2005. What drivers
are needed to do the same? Where will I get all those drivers. Could
any one help me?
Is there any other ways to migrate data from DB2?
Thanks and regards,Hi
I think you are going to install CLIENT ACCESS on the SQL Server to
communicate with DB2/AS400 .We've done it on SQL Server 2000 and by using
DTS packages transfered the data from AS400.
As well you can write a small VB.NET app that usein OLEDB driver (search on
internet) to work against DB2
"renil" <renilakader@.gmail.com> wrote in message
news:1152853349.316893.157290@.m79g2000cwm.googlegroups.com...
> Hi,
> I want to migrate my data from IBM DB2 to SQL Server 2005. What drivers
> are needed to do the same? Where will I get all those drivers. Could
> any one help me?
> Is there any other ways to migrate data from DB2?
> Thanks and regards,
>sql

Migrating data from DB2 to SQL Server

Hello,

I am trying to migrate data from a DB2 database to SQL Server 2005
database. Does anyone know about any migration tool that does that? I
have heard about DB2 Migration Tool kit, but I think you can only
migrate data to a DB2 database with that. Thank you.

Sincerely,

Eldhose Cyriaccontact1981 wrote:

Quote:

Originally Posted by

Hello,
>
I am trying to migrate data from a DB2 database to SQL Server 2005
database. Does anyone know about any migration tool that does that? I
have heard about DB2 Migration Tool kit, but I think you can only
migrate data to a DB2 database with that. Thank you.

Yes, of course.
To you push data regularly or is this a once of.
If you have to do it often you could consider Websphere Information
Integegrator. It allows you to push/pull data through normal SQL from
and to DB2.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/on...iness/conf2006/|||Hi

There is migration information at
http://www.microsoft.com/sql/prodin...bm/default.mspx, migration
tools exist for Oracle, Access and Sybase but there does not seem to be a
released version for DB2
http://www.microsoft.com/sql/soluti...on/default.mspx I suggest you
contact the SQL Server Migration team at the email address under the related
links on that page.

John

"contact1981" <contact1981@.gmail.comwrote in message
news:1154637593.696660.274720@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hello,
>
I am trying to migrate data from a DB2 database to SQL Server 2005
database. Does anyone know about any migration tool that does that? I
have heard about DB2 Migration Tool kit, but I think you can only
migrate data to a DB2 database with that. Thank you.
>
>
Sincerely,
>
Eldhose Cyriac
>

migrating data from DB2 to MS-SQL

hi,
i am a novice in DB2 and am looking for a step by step procedure to migrate data from DB2 version7 to MS-SQL. i need immediate help..
thanks
jackieI think you could use a linked server to do that ... and if i remember correctly, there is also a whitepaper availible on MS Knowledge Base for the same ...|||thanks...let me check it out...|||From the Holy Book

OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2, distributed with Microsoft Host Integration Server 2000, allows Microsoft SQL Server 2000 distributed queries to query data in DB2 databases.

To create a linked server to access a DB2 database
Install the Windows NT Client for Host Integration Server 2000 or the Windows 9x Client for Host Integration Server 2000 on a computer running an instance of SQL Server. Select the options to install the OLE DB Provider for DB2 and the network components needed to communicate with an IBM computer running in an SNA network.
Determine the connection string the OLE DB Provider for DB2 needs to access the DB2 data source you want to query. The best way to determine a connection string is to build a Data Link file using the Host Integration Server New OLE DB Data Source application. For more information, see the Microsoft Host Integration Server 2000 documentation.
Execute sp_addlinkedserver to create a linked server, specifying DB2OLEDB as the provider_name, the name of the DB2 catalog containing the data you want to access as catalog, and the connection string from Step 2 as provider_string. This example shows how to use sp_addlinkedserver to create a linked server definition accessing a DB2 database:

EXEC sp_addlinkedserver @.server = 'DB2SRV', @.srvproduct = 'Microsoft OLE DB Provider for DB2', @.catalog = 'SEATTLE', @.provider = 'DB2OLEDB', @.provstr = 'NetLib=SNA;NetAddr=;NetPort=;RemoteLU=SEATTLE;Loc alLU=LOCAL; ModeName=QPCSUPP;InitCat=SEATTLE; Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES; IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsC har=NO; Data Source=Seattle_WNW3XX'
Execute sp_addlinkedsrvlogin to create login mappings from SQL Server 2000 logins to DB2 logins. This example maps the SQL Server 2000 login SQLJoe to DB2 login DB2Joe:

EXEC sp_addlinkedsrvlogin 'DB2SRV', false, 'SQLJoe', 'DB2Joe', 'JoePwd'
After completing these steps, you can use the linked server name DB2SRV as the server name in four part names and as linked_server in the OPENQUERY function. For example:

SELECT *FROM DB2SRV.SEATTLE.WNW3XX.DEPARTMENTOr

SELECT *FROM OPENQUERY(DB2SRV, 'SELECT * FROM SEATTLE.WNW3XX.EMP_ACT')When the distributed queries against DB2 data sources involve NULL comparisons, use IS NULL or IS NOT NULL rather than comparison operators, such as =, <, or >. In addition, INSERT statements should supply values for all columns in a table even if certain columns in the table can be NULL or have default values.|||hey that was great...i am going to try that...|||OS/390 or Client Server?

With the mainframe, you'll need a gateway I believe...have yet to get that working..

If it is the mainframe, you could unload all the data and ftp it down the bcp it in...

The deal with conversion issues...wonder if linked servers handle DB2 Dates?

Migrating AS/400 DB2 to SQL Server

The client I'm working at right now is currently moving from their current
ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
doing so they are migrating 2 physical AS/400's with 4 business units (2 on
each 400) over to SYSPRO. They have moved one out of the four and a
preparing to move the the second. When migrating over to SYSPRO they only
moved the needed information to go live on the new system hence leaving years
of sales, orders, customer and product information.
So my question is there anything just blazingly important that I should know
when using DTS to pull all of this data over to SQL Server?
I know that the structure of the 400 DB2 isn't exactly the same as SQL
Server. From what I understand multiple Libraries make up a database in the
400. Each of those contain files and logicals. Files look to be the same as
a tables and logicals look to be the same as views.
Thx
You're pretty much on the ball with the file vs table and logical vs view
deal, for what you're trying to do, they're affectively equivalent. I ran
into some trouble with this in a past life, though. There was a lot of
trailing white space in the character fields I pulled out of the 400. It was
a different ERP, and it may have been human error causing it (I'm no DB2
guru), but I wound up having to RTRIM a lot of stuff on it's way over. Only
issue I ran into, though.
"Chris Stevenson" wrote:

> The client I'm working at right now is currently moving from their current
> ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
> doing so they are migrating 2 physical AS/400's with 4 business units (2 on
> each 400) over to SYSPRO. They have moved one out of the four and a
> preparing to move the the second. When migrating over to SYSPRO they only
> moved the needed information to go live on the new system hence leaving years
> of sales, orders, customer and product information.
> So my question is there anything just blazingly important that I should know
> when using DTS to pull all of this data over to SQL Server?
> I know that the structure of the 400 DB2 isn't exactly the same as SQL
> Server. From what I understand multiple Libraries make up a database in the
> 400. Each of those contain files and logicals. Files look to be the same as
> a tables and logicals look to be the same as views.
> Thx

Migrating AS/400 DB2 to SQL Server

The client I'm working at right now is currently moving from their current
ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
doing so they are migrating 2 physical AS/400's with 4 business units (2 on
each 400) over to SYSPRO. They have moved one out of the four and a
preparing to move the the second. When migrating over to SYSPRO they only
moved the needed information to go live on the new system hence leaving years
of sales, orders, customer and product information.
So my question is there anything just blazingly important that I should know
when using DTS to pull all of this data over to SQL Server?
I know that the structure of the 400 DB2 isn't exactly the same as SQL
Server. From what I understand multiple Libraries make up a database in the
400. Each of those contain files and logicals. Files look to be the same as
a tables and logicals look to be the same as views.
ThxYou're pretty much on the ball with the file vs table and logical vs view
deal, for what you're trying to do, they're affectively equivalent. I ran
into some trouble with this in a past life, though. There was a lot of
trailing white space in the character fields I pulled out of the 400. It was
a different ERP, and it may have been human error causing it (I'm no DB2
guru), but I wound up having to RTRIM a lot of stuff on it's way over. Only
issue I ran into, though.
"Chris Stevenson" wrote:
> The client I'm working at right now is currently moving from their current
> ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
> doing so they are migrating 2 physical AS/400's with 4 business units (2 on
> each 400) over to SYSPRO. They have moved one out of the four and a
> preparing to move the the second. When migrating over to SYSPRO they only
> moved the needed information to go live on the new system hence leaving years
> of sales, orders, customer and product information.
> So my question is there anything just blazingly important that I should know
> when using DTS to pull all of this data over to SQL Server?
> I know that the structure of the 400 DB2 isn't exactly the same as SQL
> Server. From what I understand multiple Libraries make up a database in the
> 400. Each of those contain files and logicals. Files look to be the same as
> a tables and logicals look to be the same as views.
> Thx

Migrating AS/400 DB2 to SQL Server

The client I'm working at right now is currently moving from their current
ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
doing so they are migrating 2 physical AS/400's with 4 business units (2 on
each 400) over to SYSPRO. They have moved one out of the four and a
preparing to move the the second. When migrating over to SYSPRO they only
moved the needed information to go live on the new system hence leaving year
s
of sales, orders, customer and product information.
So my question is there anything just blazingly important that I should know
when using DTS to pull all of this data over to SQL Server?
I know that the structure of the 400 DB2 isn't exactly the same as SQL
Server. From what I understand multiple Libraries make up a database in the
400. Each of those contain files and logicals. Files look to be the same a
s
a tables and logicals look to be the same as views.
ThxYou're pretty much on the ball with the file vs table and logical vs view
deal, for what you're trying to do, they're affectively equivalent. I ran
into some trouble with this in a past life, though. There was a lot of
trailing white space in the character fields I pulled out of the 400. It wa
s
a different ERP, and it may have been human error causing it (I'm no DB2
guru), but I wound up having to RTRIM a lot of stuff on it's way over. Only
issue I ran into, though.
"Chris Stevenson" wrote:

> The client I'm working at right now is currently moving from their current
> ERP contained on AS/400 to a MS SQL Server based ERP known as SYSPRO. In
> doing so they are migrating 2 physical AS/400's with 4 business units (2 o
n
> each 400) over to SYSPRO. They have moved one out of the four and a
> preparing to move the the second. When migrating over to SYSPRO they only
> moved the needed information to go live on the new system hence leaving ye
ars
> of sales, orders, customer and product information.
> So my question is there anything just blazingly important that I should kn
ow
> when using DTS to pull all of this data over to SQL Server?
> I know that the structure of the 400 DB2 isn't exactly the same as SQL
> Server. From what I understand multiple Libraries make up a database in t
he
> 400. Each of those contain files and logicals. Files look to be the same
as
> a tables and logicals look to be the same as views.
> Thx

Migrate from DB2 to MSSQL

Dear All,

I am exploring the feasibility of migrating from DB2 (v7) to MSSQL server 2005. My current applications are using UDB, but the DBA wants to change to MSSQL. I believe the impact is huge. For example, how to create schema in MSSQL, how to migrate the data...

Anyone had the experience before? Could kindly suggest some reference for such a migration? Thanks!

Regards,From the database perspective, this is almost trivial.

Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.

A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.

-PatP|||String Manipulation?

Dates, OK, you are going to have to trim the DB2 dates as SQL Server only supports 3 ms...and only cpu clock speeds...I believe every .333 ms

But strings/chars?

Huh?

And what, besides sheer whim or only knowledge of sql sever does the dba want to move off DB2|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?

From the database perspective, this is almost trivial.

Use ErWin, Visio, or another modeling tool to harvest your schema from the DB2 database, then project that (empty) schema into an MS-SQL database), then use DTS or a similar tool to migrate your data. If you aren't as finicky about the schema details as I am, you can simply use DTS all by itself, which will get you pretty close.

A much, much larger issue is the difference in string and date handling. DB2 syntax for both string and date operations is significantly different than MS-SQL syntax for the same operations. Unless you've been abnormally careful to avoid any manipulation of strings and dates within your SQL, there will be a potentially enormous amount of effort needed for this conversion.

-PatP|||Sorry for my ignorance: my table in DB2 is something like "db2n1.tuser", while db2n1 is the schema and tuser is the table name. Is there such a corresponding schema name in MSSQL?Yes, there is. Your DBA can tell you for sure, but my first guess is "dbo".

You are treading very near one of the confusing points in the switch from DB2 to MS-SQL... In MS-SQL 2000 and all earlier versions, there was a logical concept called a "user" that actually spanned across many of the concepts such as schema, permissions, etc in the purely relational world. Each user could logically own objects within the database, giving them something quite close to their own schema. There was a kind of "uber user" named dbo (an acronym for DataBase Owner) that was the default user under the name resolution rules.

In MS-SQL 2005, true schemas were introduced into the product. The schemas don't behave quite the way that you are accustomed to in DB2, but they're pretty close. For the moment, I wouldn't worry too much about them unless you have to make adjustments to cope with them.

-PatP