Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1567
  • Last Modified:

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

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.
  • 4
  • 2
4 Solutions
Kent OlsenData Warehouse Architect / DBACommented:
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.   :)


k_murli_krishnaAuthor Commented:
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?

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Kent OlsenData Warehouse Architect / DBACommented:
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.

Kent OlsenData Warehouse Architect / DBACommented:


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

k_murli_krishnaAuthor Commented:
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.
Kent OlsenData Warehouse Architect / DBACommented:

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now