Retrieving data from DB2 tables 2

[pmcdaniel]

What is the best way for a SQL Server database to receive data from a DB2 environment? Currently we receive feeds from mainframe people on another floor in the same building.

As far as I'm concerned this is inefficient since we have to have meetings and send emails back and forth to determine how these feeds are supposed to hold data. Seems to me we should be able to set something up where we can mirror their data to our own tables but they say it won't work.

FYI - We currently use SQL Server 2000 but rumor has it we'll be moving to 2008. We're still using VB 6 and the company is not planning on providing us .NET.

 

[simian336]

3 common ways are

data pulls - which it sounds like your already doing. These can be somewhat automated.

ODBC Data pulls with SSIS and downloading DB2 database staight to SQL Server tables.

ODBC quearies using a 3 party product or programming language to import the data you need.

There are probably more but those are the ones I use the most.

Simi

 

[pmcdaniel]

Thanks Simi, The data pulls are the mainframe people doing the SQL code on their side and providing us the feeds. I personally don't understand why this is done since we can do the SQL ourselves and cut out so much overhead like constant communication over what data to pull and formatting.

Their reason for not using ODBC Data pulls is they claim it is "very risky to duplicate data stores – it ultimately results in out of sync conditions". Is this valid? Seems to me they're just didn't do it correctly and don't want to try to do it correctly.

Can you (or anyone else) recommend ODBC quearies using a 3 party product?

 

[fredericofonseca]

use a dts package with a linked server. or ssia when you move to v2008.
Only thing you may need on the SQL Server is DB2 Connect, but this will depend on the OS where you have the DB2 database. if it is a AS400 then you already have licenses to use the DB2 personal connect on that machine

If they are concerned with duplicated data, then have them put the extracts on a temporary table, and then SQL server gets the info from those tables only.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[pmcdaniel]

Thanks Frederico, Please excuse my potentially elementary questions, I am VB programmer and have only written stored procedures and created my own DBs and tables and such on my PC at home.

So the mainframe people can move the data to a temporary table (or view) and then move that data to SQL Server using one of the methods mentioned by you and Simi?

 

[simian336]

Sure, they have all the same SQL tools as any DB.

I am still not sure I understand the data duplication issue. You should only be passing back related data (if you intend to pass data back at all).

Simi

 

[pmcdaniel]

I'm not exactly sure what they mean by the duplication issue either. In my opinion they tried and failed to mirror the data and now think it can't be done.

So as the developer on the software side I'm now limited to receiving feeds from them consisting of the exact data I'm supposed to use. It's pretty frustrating since I'm perfectly capable of writing my own stored procedures.