I'm working towards migrating an IBM DB2 database to SQL Server, however the DB2 database sits in a Data Warehouse environment.
As I am very new to Data Warehousing, DB2 and DB Migration between different types of databases, I would like to ask about how to best approach this and understand what components are involved (listing or linking elsewhere is fine - happy to read a bunch of things or learn something new).
- The ETL tool in use, and to be kept in use, is Informatica
- The only application that queries the DB is Cognos
- There are 4 DB2 DBs in use on 2 (non-windows) hosts, one of these is 'H1' which has a 'DB2-DW-PROD' DB used as part of the Data Warehouse
- I am interested in migrating the 'DB2-DW-PROD' DB to some space I have on an existing (Windows) SQL Server host, 'H2' under the name 'SQL-DW-PROD'. This host currently has several DBs on it for other purposes (in one instance) and can have SSRS/SSIS or other services/resources installed or configured if needed.
- I would like to clean up the poorly maintained DB a little bit during the migration, if possible, based off what has not been used/updated in the past couple of years.
What I would like to know, directly or indirectly:
- Should I create a new instance on H2 or is a single new database enough?
- Does the new instance/DB need particular resources? If so, where from?
- Should I use a particular Microsoft tool or use some type of import/export to get the data I need? (Is Informatica involved in this process?)
- Someone mentioned 'metadata tables' for Informatica and/or Cognos, should I be wary of something?
- Another mentioned maintaining sequence values -How is this best achieved; is it needed?
- Any changes on the Informatica side are not my personal concern but the work of another, as such, Is there anything I should provide this person (apart from the address of the new db)?
I'm very new in this field, so anything dumbed down is greatly appreciated. That being said, anything complicated but necessary or good to know is as well. It's a steep learning curve for me but I'm kind of stumped right now and need a hand.
I have no control over the process - Informatica and Cognos must be kept as they are. Also, at this stage, I am not able to view the DB2 DB but I know it is not too large. At the very, very most, assume it's 1 TB for the purpose of this question.
At the moment I am simply trying to understand the process I will follow and what I will need to look out for when the time comes.
EDIT: Have received plenty of feedback on the instance vs database part of the question which I greatly appreciate. However, I am still confused as to how I should approach migrating the data accross. I.e. 'how, or what should I use, to migrate such that I have everything I need to support Informatica and cognos?'
The most pressing issue is that I don't understand any of informatica's/cognos's dependencies or know how db2 works. I am not confident that simply copying user table data accross is enough and would really like some confirmation or pointers to know exactly what to do.