Fetching data from IBM DB2 to SQL Server 2005

Hi,

I am trying to fetch data from IBM DB2 to SQL Server 2005.

The problem I am facing is when I create the OLE DB Connection (I am using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider") and see the "Preview", I get "System.Byte[]" in a couple of columns for all the rows, instead of the actual data.

The datatype of the original field is "Byte Stream".

I have tried all options, but, failed. I believe there is something in the "Force Translate" property of the OLE DB Connection. Right now it is set to "65535". I am not sure if that needs to be changed.

I was earlier using a DTS package, where I used ODBC for connecting to the same database. In ODBC, there is a "Translation" tab where there is a check box labelled: "Convert binary text (CCSID 65535) to text".

When I check this box, I am able to see the data correctly.

But, now I have moved to SSIS and I am facing the same problem as I am not using the ODBC connection.

Please help.

Thanks and Regards,

B@.ns

I don't knot if this would resolve your problem; but you can still use the ODBC connection in SSIS by using a Data Reader source component.|||

Hi,

Thank you for the reply.

I will try and use the same ODBC connection and get back.

Thanks and Regards,

B@.ns

|||

Thank you Rafael!

This has indeed solved my problem.

Thanks and Regards,

B@.ns

|||

If you still having problems.

1. You can try to create linkserver

2. Create a view of the target table

3. Access it like a SQL server table

Federation/replication - DB2 & Microsoft SQL Server 2000

Hi all,

I'm new to this forum, though not new to sql server am new to DB2.

We are running Microsoft SQL Server 2000 but we have recently acquired similar business in another country which runs IBM DB2 database system. We are interested in incorporating the data from DB2 in our system. The tables are slightly different in DB2.
Q. what is the best way to integrate the two systems? Should we take the route of a federated system or replication of data?
We are interested in knowing what is happening in the other business (DB2) but we what the two systems to operate in parallel for about a year, before we come up with the best solution.

The company is after a short term and long term solution.

Can any one please help or point me to the right place?

Regards,
Chloe.What you are describing is a consulting engagement. I would STRONGLY suggest that you consider paying someone with an appropriate level of expertise to come examine your systems and give you a professional opinion. While you can get good ideas from the internet, you really need something more solid than a good idea, and you really need someone with the experience to know what questions you didn't ask and the time and energy to both ask and answer them for you. I'm not trying to sell you anything, and I'm not volunteering for the engagement, I'm just trying to keep you from hurting yourself!

With that said, I'd suggest you consider using sp_addlinkedserver (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp). It will allow your SQL Server to "see" the AS400 data, and that will be a stepping stone to allowing you to build reports, etc from the merged companies combined perspective.

Don't give up on using the Internet to get good ideas, it is great for that. As your business grows, don't "bet the farm" on what you get from the Internet until you get to the point that you can critically examine the information and know at least enough to know if you've asked all of the relevant questions.

As a side note, you might consider one of the firms that helps firms like yours grow. There are several of them, and while they aren't cheap they can easily save 100 times what they cost. I work for one of those firms (I don't do client engagements, just IT), and I see examples of mergers that went well (and poorly) every day.

Find the appropriate expertise, even if it is expensive. As the old saying goes: If you think education is expensive, you should consider the cost of ignorance!

-PatP|||Hi Pat,

Thanks a lot for your advice. What you said is right we should get a professional to do the job for us and as you rightly pointed out it will probably be expensive.
I want to point out that our business is very small and we can't afford to pay a professional, however, I and my business pattern studied IT and designed the current system we are using. But that was a long time ago and we don't have much experience in integrating data from different databases plus new technologies come up every day. And its for that reason/s, that I asked the question/s on this forum to find out what is out there that we can use. Or probably some body has integrated data from the two systems mentioned and would like to give us some advice.

Thanks again for sharing your wisdom.

Any more advice on the mentioned issue will be highly appreciated.

Best regards,

Chloe

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restor
ed
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok an
d
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I go
t
> at the end told me that DB1 had been restored...when in fact it was DB2 th
at
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
Andre
Andre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

feature?

I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
DB1, then moved on to DB2. I was restoring from a file, so I added the
file, forgot to change the db name in the dropdown, so it still said DB1,
changed the file paths, and clicked ok. So of course when I clicked ok and
it started the restore, I got an error telling me the file contained a db
other than DB1. Realizing my mistake, I then selected DB2 from the
dropdown, and clicked ok. The db restored just fine, but the message I got
at the end told me that DB1 had been restored...when in fact it was DB2 that
was restored.
AndreAndre wrote:
> I was setting up a new SQL 2005 sp2 (x64) box and restoring dbs. I restored
> DB1, then moved on to DB2. I was restoring from a file, so I added the
> file, forgot to change the db name in the dropdown, so it still said DB1,
> changed the file paths, and clicked ok. So of course when I clicked ok and
> it started the restore, I got an error telling me the file contained a db
> other than DB1. Realizing my mistake, I then selected DB2 from the
> dropdown, and clicked ok. The db restored just fine, but the message I got
> at the end told me that DB1 had been restored...when in fact it was DB2 that
> was restored.
> Andre
>
I think it's just a matter of a missing "refresh" of the GUI. I'm not
using the GUI for restores myself so I'm not familiar with how it
appears, but you can be quite sure that it is DB2 you've restored - and
not DB1. You'd only be able to restore DB2 on top of DB1 if you explicit
had told it to replace the database.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1Are these two boxes in the same location?
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:533A6B9900AD9@.SQLMonster.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via SQLMonster.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via db2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200508/1
Are these two boxes in the same location?
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:533A6B9900AD9@.droptable.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1
|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via droptable.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via db2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1

Fastest way to transfer data from DB2 on host to SQL Server

Hello all,
I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
via DB2 connect and ODBC to transfer the data. The data size is 30GB. It
would take more than 2 day. It abended two times.
What is the fastest way to transfer data other than this way?
How can I make faster in this case?
any comments?
Thanks in advance,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200508/1Are these two boxes in the same location?
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:533A6B9900AD9@.droptable.com...
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via DB2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1|||StarQuest has a very fast replication solution, they should be able to
help you reduce considerably the transfer window:
http://www.starquest.com/Productfolder/infoSQDR.html
Good luck,
Bob
Do Park via droptable.com wrote:
> Hello all,
> I am transfering data from DB2 on z/OS (MVS) to SQL Server 2000. I use DTS
> via DB2 connect and ODBC to transfer the data. The data size is 30GB. It
> would take more than 2 day. It abended two times.
> What is the fastest way to transfer data other than this way?
> How can I make faster in this case?
> any comments?
> Thanks in advance,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1