Solved

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

Posted on 2008-06-16
7
1,494 Views
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
migrations.

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
involved?

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.
0
Comment
Question by:k_murli_krishna
  • 4
  • 2
7 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 200 total points
Comment Utility
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.   :)


Kent


0
 
LVL 17

Author Comment

by:k_murli_krishna
Comment Utility
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?


0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 50 total points
Comment Utility
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 200 total points
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Kdo
Comment Utility

Oops.

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


Kent
0
 
LVL 17

Author Comment

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

Assisted Solution

by:Kdo
Kdo earned 200 total points
Comment Utility

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.


Kent
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

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

8 Experts available now in Live!

Get 1:1 Help Now