Solved

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

Posted on 2014-12-12
6
357 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
 
LVL 1

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 49

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 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