Link to home
Start Free TrialLog in
Avatar of ITsolutionWizard
ITsolutionWizardFlag for United States of America

asked on

As400 Db2 to MS Sql server (data/schema) only

I am looking for a way or tool (if I have to purchase) to export database schema / data (AS 400 / DB2) to MS SQL Server 2012

Anyone has idea? Or a tool used to work with? We can buy but with low budget.

Thanks
Avatar of Member_2_2484401
Member_2_2484401
Flag of United States of America image

So, what's different about this question than the last one you asked?

https://www.experts-exchange.com/questions/28566876/AS400-DB2-to-MS-SQL-Server-2012.html
Most of the time you can just do this right from SQL server using the SSIS Import/Export wizard.  You just install the IBM i Access for Windows OLE DB driver or .NET driver on your SQL server system, and then use it to connect to the AS/400 (iSeries / IBM i) DB2 database.

http://msdn.microsoft.com/en-us/library/ms140052%28v=sql.110%29.aspx

The drivers you need come on the IBM i Access for Windows media that shipped with your system.  Depending on your Windows version, you may need a newer version of IBM i Access for Windows.

http://www-03.ibm.com/systems/power/software/i/access/windows/os.html

You can obtain newer versions of IBM i Access for Windows from IBM:

http://www-03.ibm.com/systems/power/software/i/access/resources/orderupgrade.html

As with all cross-system type projects, you may run into complications depending on how your source database is set up, AS/400 OS version, version of IBM i Access, etc.  There are a lot of different ways (with no tools, and with just a bit of scripting) to accomplish this task, so it you have trouble post back - I've done a lot of these.

- Gary
Avatar of ITsolutionWizard

ASKER

We are looking for out of box tool. Since there is no business logic involved. It is table schema and data only.
I know I can 't see it is easy but it should not be so complex. So I assume there is some tools  I can do it smoothly.

Thanks
Not aware of any cheap tools to do this "out of the box", but then I've never bothered to look.  Any tool will need a database driver.  Once you have the database driver loaded, you can do most of these types of tasks with just the SSIS import wizard, which most SQL Server DBAs already know how to use, so there really isn't much demand or incentive to build a third-party tool.

Don't get me wrong, there are lots of ETL tools that can do this task, or assist with it, but they are expensive and more complicated to get installed and configured than to just use the tool you already have built into SQL Server.

Feel like I'm missing something here.  Have you tried the SSIS approach and had problems?
Avatar of Member_2_276102
Member_2_276102

It's difficult for anyone to answer since we have no experience with the source system. There are three general possibilities that can make a difference.

First, you might be looking at a reasonably pure SQL-defined database. One or more schemas might contain well defined tables, views, indexes, etc. Such a setup would be relatively easy to migrate. It's also perhaps the least likely of the three, since it indicates some good development work with an eye to the future. It might be less likely that migrations would happen from this variant.

Second, you probably would find at least some and possibly many native database objects (non-SQL-defined) consisting of numerous interconnected or unrelated libraries, physical files and logical files. File keys may or may not make any good relational sense where (and if) they exist, possibly having been developed over almost the past 40 years depending on past history. Files might be multi-member or even multi-format. Some platform-specific features might have no translation to SQL Server without re-architecting the database and possibly even applications that use them. Almost all migrations will involve a significant part of this variant.

Third, it's possible that some files will come from a System/36 environment. Hard to guess how any of it could work in SQL Server, and hard to guess how likely it is. Such systems are often very stable, particularly in the sense that the businesses want very few changes to happen in their applications.

All of those environments can provide data for consumption by SQL-based apps. Migration to SQL is not as simple for some elements.

Technically, it's also possible for other variants; but the 'big 3' cover enough ground.

Without knowing basic details about the structures that you are actually migrating from, it will be difficult to know what tools might be useful.

Tom
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial