Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


DB Migrate from AS/400 DB2 to SUSE Linux x8664 DB2 UDB

Posted on 2008-06-16
Medium Priority
Last Modified: 2012-06-27
Database migration from AS/400 DB2 to SUSE Linux 10.2 x8664 DB2
1) We have DB2 on AS/400 mainframes. The schema has no foreign keys & check constraints. But
there are DEFAULT, PK, UK & Indexes.

2) Same schema bar a few tables will be created using converted to Linux specific DDL. Hence,
the column mapping will be little complex. Transformations are unlikely.

3) What are the best ways that data can be migrated from AS/400 DB2 to Linux DB2?

4) Consider both cases i.e. it is a one time migration OR there maybe repeated incremental

5) Both existing/source database in AS/400 and new/target database in Linux are/will be OLTP
ones & for some period both will be running with existing/new application connecting to them.

6) Will DB2 on AS/400 be much different than DB2 UDB on Linux? Are there any features that may
affect any route of migration?

7) In Linux, we can have shell scripts embedding DB2 scripts with error handling and scheduling
to do the job smoothly. What can there if migration is run/scheduled/controlled from AS/400?
This is unlikely though since AS/400 DB2 will be production one.

8) How will DB2Connect client/gateway i.e. server play a role in migration?

9) Can we use nicknames i.e. distributed computing to do migration? What are the pros & cons

10) There are 215 tables in all, 5 having 60-70 lakh records, 5 having 30-40 lakh records, 15
having 5-10 lakh records and most having less than 1 lakh records?

11) Client is unlikely to go for any migration/ETL tools except if existing DTS & 
SSIS(SQL Server).

12) Please post links if required.
Question by:k_murli_krishna
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 46

Accepted Solution

Kent Olsen earned 800 total points
ID: 21797223
Hi k_murli_krishna,

1)  These shouldn't cause any real headaches during conversion.  If you discover that there are foreign keys, you'll find a significant performance boost to the load process if you'll disable integrity checking until the tables are loaded, then re-enable it.

2)  You're moving the database anyway, might was well reorganize, too.  :)  (See below.)

3)  If the data volume is modest the easiest way to migrate the data is to federate the systems, create the databases and schemas on the new one, and load the new database via the federated environment.  Use LOAD FROM CURSOR!  It is by far the fastest load process.

4)  Incremental migrations will be modest.  Federate.

5)  Running one or both is a business decision that your management will have to resolve.  I would be very cautious about a migration environment where production applications with the ability to change or append data could be connected to either system.

6)  There are some differences between the two.  The SQL is 95% the same, with most of the differences being in the IBM provided functions and procedures.  Administration of them is quite a bit different as the AS/400 tool set is a lot different than the linux tool set.  The DBA will still need to do most of the same things, he'll just need to go about it differently.

7)  I don't see this as much of an issue.  It is easier to run the load from the Linux side, but not a requirement.

8)  I would think not at all.  At least that would be my intent if this were my migration.

9)  "Nickname" has several possible meanings and is related to an "alias".  To the federated environment a nickname is the name of an object on another server.  An "alias" is another name for something on the same server.  A database often has a nickname and would be required in a federated environment to differentiate database_1 on server A from database_1 on server B.  There are no cons that I'm aware of.

10)  That's pretty modest by today's database standards.  If these tables do not include LOB, BLOB, or CLOB data the storage needs should be modest and performance should be quite good.

11)  DTS is a SQL Server tool.  If you're migrating from DB2 on an AS/400 to UDB/LUW, how does SQL Server come into play?  Oh -- and I'd do it with Linux shell scripts anyway.   :)


LVL 17

Author Comment

ID: 21800115
Thanks, kent.
3) Federated computing(different RDBMS) & distributed computing(same RDBMS) in our case can be used to migrate data as insert into <schema>.table select * from <nickname> OR insert into <nickname> select * from <schema>.<table>. But can we export from a nickname and load into a nickname?

6) Will DB2 on AS/400 be much different than DB2 UDB on Linux? Are there any features that may
affect any route of migration? The first you answered but not the second.

7) In Linux, we can have shell scripts embedding DB2 scripts with error handling and scheduling
to do the job smoothly. What can there if migration is run/scheduled/controlled from AS/400? Question remains unanswered.

12) How can .IXF files let us say be transferred from AS/400 to Linux and LInux to AS/400?

LVL 37

Assisted Solution

momi_sabag earned 200 total points
ID: 21802341
3) you can't export in the "native" way since you will have to go through the federated link, so even if it allows you to "export", behind the scene it will be implemented as select
6) the main differences are in the dba's prespective, not the application programmer's.
i'm pretty sure that every thing you can do in db2 on as400 programmer wise, you can do on linux as well
7) db2 on as400 does not have a built in scheduling mechanism, you can either you a 3rd party tool to schedule or you can use the linux envrionment to trigger the operation on as400 - it will require some creativity
12) they can't. this format is only known to linux udb, not to as400 db2.
when loading the data on as400 db2 will automatically rebuild the indexes from scratch without using the ixf files
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 800 total points
ID: 21802479
3)  Yes, you can use the nicknames in the export and load processes.  Part of their charm is that they look and feel just like a "native" object.  :)

6)  Hard to tell.  If your SQL uses functions or procedures that are specific to the iSeries, they will obviously have to be converted.  The only one that knows the answer to this is you.  With this as the only significant exception, the application people shouldn't see much impact on their daily routine.  The DBAs will see a significant impact as they will need to be versed in the LUW tools.  Still, the database is small enough that the defaults and the suggestions available from the Control Center tools should get you a very good foundation.

7)  Don't control the migration from the source system.  The only advantage to doing so is that the conversion may be dependent on certain data items (perhaps an updated flag indicating that the day's processing is concluded).  And that can actually be queried from the destination system.  If the Linux side is allowed to control its own destiny, this issue goes away.  If you go to the DWE product, a full scheduling mechanism is built into DB2, but it's not cheap.  At least not yet.

12)  Don't know.  I've not dealt with this.

LVL 46

Expert Comment

by:Kent Olsen
ID: 21802489


Hi Momi....   Didn't mean to repeat your answers.

LVL 17

Author Comment

ID: 21805922
Thanks, momi & kent. One last point which I did not copy paste in the first place from text editor.
Does AS/400 DB2 also has to be 64 bit if the OS itself is? I am asking since existing target Linux is 64 bit i.e. x8664 i.e. will 32 bit data from 32 bit OS + DB2 go into target 32 bit OS + DB2 & same is the case with 64 bit or there is a bit conversion possible just as DB2Connect converts z-series & i-series mainframes EBCDIC Encoding to Unix, Linux & Windows ASCII Encoding and the other way round as well.
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 800 total points
ID: 21806139

If you use the federated hooks, the 32-bit vs 64-bit issue goes away.  Any necessary conversions are handled by the drivers.

The same is true of queries against either database.  The ODBC/JDBC drivers or DB2 client will handle any necessary conversions.

That said, you should strive to use the 64-bit DBMS if you have a 64-bit O/S.  There's some speed improvement, better handling of large datasets, etc.  There may be an issue with certain 32-bit APIs no longer being supported in the 64-bit world.  That may not be an issue today, but it will be.


Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

596 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