Solved

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

Posted on 2014-12-12
6
314 Views
Last Modified: 2016-02-18
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
0
Comment
Question by:ITsolutionWizard
6 Comments
 
LVL 18

Expert Comment

by:daveslash
Comment Utility
So, what's different about this question than the last one you asked?

http://www.experts-exchange.com/Database/DB2/Q_28566876.html
0
 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
0
 

Author Comment

by:ITsolutionWizard
Comment Utility
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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?
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
I already told you in your other similar question to use SSIS (SQL Server Integration Services). You will hardly find a better tool to migrate data to SQL Server.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now