[Best Practice] Sync data from iSeries DB2 to SQL server (for cognos staging DB)

thanosgr
thanosgr used Ask the Experts™
on
hi all

I am looking for best practice to create a staging environment for COGNOS.
our situation is like this:
The main database is located on as AS400 DB2 (developed with synon). We do not want Cognos to read directly from this DB, so we want to create an intermediate db on SQL server for cognos to read.
The question is what is the best practice to get all necessary tables from DB2 on sql server (IDS, program, stored procedures, cognos framework, etc) considering that during that process we need to apply several rules & formulas creating new "calculated fields". Also some tables are quite big more than 20mil rows, whats is the best way to keed this tables synced on SQL server (changes might occur on existing data). Finaly the field names on DB2 are something like FDSERTD and they need to be renamed to something that makes sense (keep in mind that the description (in DB2) of the fields  works fine)

million thanx in advance
George
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
You Need an ETL tool to help you achieve this

{get all necessary tables from DB2 on sql server}

Microsoft has SSIS on sql 2005 and 2008

or if you are using sql 2000 you can use DTS.

all you need is to install the necessary drivers to help you read DB2 data and using these ETL tools you can transfer data from DB2 to SQL.

there are many ETL tools out there DTS and SSIS are free shiped with microsoft sql, if you want more powerful and stronger ones you might need to search for your best fit.

check this article

ETL Method – Fastest Way To Get Data from DB2 to Microsoft SQL Server

http://sqlserverpedia.com/blog/sql-server-bloggers/etl-method-%E2%80%93-fastest-way-to-get-data-from-db2-to-microsoft-sql-server/
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
> Establish a linked server pointing to the recent data on DB2 from SQL Server
> Set up a local SQL Server copy of the data ,using SQL Server
> Do stuff in SQL Server
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist

Commented:
Note that the proposed solution is simply an alternative that requires less work than ETL for smaller volumes.  Depending on the volume of data to be transferred daily pick one or the other.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2010

Commented:
just incase you want to try Racimo:'s suggestion

http://www.mssqltips.com/tip.asp?tip=2151&home

Author

Commented:
Any ETL tools that u used before
and suggest?

Thanx again
Top Expert 2010

Commented:
i used SSIS, its powerful and can get your job done

http://www.mssqltips.com/tip.asp?tip=1923

http://www.bidn.com/blogs/PatrickLeBlanc/ssis/700/connecting-to-db2-using-ssis


and the best data driver i used was with SSIS, that is very efficient in reading different data sources is
http://web.datadirect.com/products/other/ssis/index.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial