DB2 Migration

[pkr]

Hi,

In my shop, we have database in diverse platforms like mainframe,
Linux, Windows 2000. We are planning to consolidate all iDB in a single
platform and build one big monster DB, this monster DB is going be a
DB2 UDD V8, running on Linux server.
As a first step we are planning to migrate mainframe DB2 (v7) to Linux
DB2 UDB (V8).
I want to know the following
1. Are there any tools available for migrating the database from
mainframe to Linux box, there is not going to be any functionality
change during the migration?
2. What are the pit falls in migrating database from mainframe to Linux
(we have factored the processing power & storage size)

Thanks
R

[grofaty]

Hi,
what kind of mainfraime do you have? Is this an IBM zSeries mainframe?
What kind of operating system do you have on mainframe? zOS, VM, VSE,
Linux390?
In our company we have Linux390 (zSeries) and DB2v8.2fp10. We also have
DB2/VSE v7.2 and we are also planing to migrate to DB2/Linux390. I have
made general tests and on DB2/Linux it works about 5-times faster than
DB2/VSE. I haven't discovered out the feature that is supported on
DB2/VSE and is not supported on DB2/Linux390. The only thing I have
encountered to is the default settings for date definitions. On DB2/VSE
is ISO format of date, for example first of February 2006 is written as
2006-02-01, but on DB2/Linux computer there is date written as local
format. In Europe the default format is day-month-year example first of
February 2006 is written as 01.02.2006. So all the SQLs that are
executing against the date data fields must be checked. The general
'select date_column from table' isn't problematic, but 'select
substr(date_column,1,4) from table' returns different results on this
two databases. We are planing to solve the problem by rewritting SQLs
to 'select substr(char(date_column,1,4),ISO) from table' so all SQLs
that are using somekind of data field transformation will be converted
to ISO date format.

Hope this helps,
Grofaty

[Larry]

grofaty wrote:

Hi,
what kind of mainfraime do you have? Is this an IBM zSeries mainframe?
What kind of operating system do you have on mainframe? zOS, VM, VSE,
Linux390?
In our company we have Linux390 (zSeries) and DB2v8.2fp10. We also have
DB2/VSE v7.2 and we are also planing to migrate to DB2/Linux390. I have
made general tests and on DB2/Linux it works about 5-times faster than
DB2/VSE. I haven't discovered out the feature that is supported on
DB2/VSE and is not supported on DB2/Linux390. The only thing I have
encountered to is the default settings for date definitions. On DB2/VSE
is ISO format of date, for example first of February 2006 is written as
2006-02-01, but on DB2/Linux computer there is date written as local
format. In Europe the default format is day-month-year example first of
February 2006 is written as 01.02.2006. So all the SQLs that are
executing against the date data fields must be checked. The general
'select date_column from table' isn't problematic, but 'select
substr(date_column,1,4) from table' returns different results on this
two databases. We are planing to solve the problem by rewritting SQLs
to 'select substr(char(date_column,1,4),ISO) from table' so all SQLs
that are using somekind of data field transformation will be converted
to ISO date format.

Hope this helps,
Grofaty

Be careful about using performance comparisons based on this migration.
This is Linux on z and Linux on Intel/UNIX runs on different processors.

Larry Edelstein

[pkr]

Hi,

Thanks Larry & Grofaty.
We re migrating from mainframe platform to Intel platform. I hope DB2
UDB is same across platforms.
We need to migrate 100+ tables and 60+ stored proc, is there any tool
available?
Few of our tables have more than 50 million rows and taking a dump of
the table and migrating it to Intel platform would be a CPU intensive
activity. Is there any other efficient way of moving data.

Migrating SP wouldn't be a problem, we would copy the SP in Linux
platform and recompile it, but migrating data is a major concern.

Thanks,
R
Larry wrote:

grofaty wrote:

Hi,
what kind of mainfraime do you have? Is this an IBM zSeries mainframe?
What kind of operating system do you have on mainframe? zOS, VM, VSE,
Linux390?
In our company we have Linux390 (zSeries) and DB2v8.2fp10. We also have
DB2/VSE v7.2 and we are also planing to migrate to DB2/Linux390. I have
made general tests and on DB2/Linux it works about 5-times faster than
DB2/VSE. I haven't discovered out the feature that is supported on
DB2/VSE and is not supported on DB2/Linux390. The only thing I have
encountered to is the default settings for date definitions. On DB2/VSE
is ISO format of date, for example first of February 2006 is written as
2006-02-01, but on DB2/Linux computer there is date written as local
format. In Europe the default format is day-month-year example first of
February 2006 is written as 01.02.2006. So all the SQLs that are
executing against the date data fields must be checked. The general
'select date_column from table' isn't problematic, but 'select
substr(date_column,1,4) from table' returns different results on this
two databases. We are planing to solve the problem by rewritting SQLs
to 'select substr(char(date_column,1,4),ISO) from table' so all SQLs
that are using somekind of data field transformation will be converted
to ISO date format.

Hope this helps,
Grofaty

Be careful about using performance comparisons based on this migration.
This is Linux on z and Linux on Intel/UNIX runs on different processors.

Larry Edelstein

[pkr]

Sorry I forgot to mention, we are presently running on IBM zSeries
mainframe using Z/OS, we are migrating to Linux on Intel platform.

[Larry]

pkr wrote:

Sorry I forgot to mention, we are presently running on IBM zSeries
mainframe using Z/OS, we are migrating to Linux on Intel platform.

This might help:

http://www.redbooks.ibm.com/abstract...6905.html?Open

Larry Edelstein

[s.sathyaram]

>>>>> I hope DB2 UDB is same across platforms.

I'm afraid, not ... DB2 has 3 different flavours - DB2 zOS, DB2 LUW,
DB2 iSeries. Each one of these have their own feature set. Examples of
differences between LUW and ZOS :
a) The way you define tablespace and index space
b) The tablespace definitions
c) LOB columns on mainframe go on a table on their own. On LUW, they
are on the same table
d) ROWID is not a supported column type
e) Range Partitioning for Performance (partitioned tablespaces) on zOS
is not available in LUW
f) Some column options like COMPRESS etc is not available on LUW
g) LOAD is different

there are many more...

IMHO, moving data is the simplest among the lot. and believe me, 50
million is not very big.

Migrating SP wouldn't be a problem, we would copy the SP in Linux platform and recompile it, but migrating data is a major concern.
You will have to put in a bit of effort on Stored Procs as well ... It
is definitely not as easy as recompiling ... Some CREATE PROCEDURE
options may have to be removed .... (I have migrated SPs from LUW to
ZOS, which was a quiet a pain!! I guess zOS to LUW will be bit easier)

In short, do not underestimate the ease of migration from zOS to LUW!!
Few of our tables have more than 50 million rows and taking a dump of the table and migrating it to Intel platform would be a >>>CPU intensive activity. Is there any other efficient way of moving data.

For smaller tables (not more than a few hundred thousand records), I
would consider using LOAD from CURSOR. Even for larger ones, you may
test LOAD from CURSOR .. Though dependent on various factos, I would
think a 50 million record table in LOAD from CURSOR should be loaded
easily in an hour.

Cheers
Sathyaram

[Jean-Marc Blaise]

"pkr" <pk*****@yahoo.com> a écrit dans le message de
news:11**********************@g14g2000cwa.googlegr oups.com...

Sorry I forgot to mention, we are presently running on IBM zSeries
mainframe using Z/OS, we are migrating to Linux on Intel platform.

What version are you planning to use ? ESE or WSE ?
What would be the estimated total volume of your DB ? You might consider
using ESE with DPF, speaking of a huge DB.

HTH,

Jean-Marc