Solved

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

Posted on 2014-12-12
6
340 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
ID: 40496514
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 35

Expert Comment

by:Gary Patterson
ID: 40496546
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
ID: 40496696
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 35

Expert Comment

by:Gary Patterson
ID: 40496766
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
ID: 40497454
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 48

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40499805
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

789 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