In addition to Philip's excellent list, I would like to share my
For 4 years, I've been giving Advanced DB2 for z/OS Support and
a major bank. They've been a loyal IMS Customer for several years.
they decided to move DB2 for z/OS with a new corebank package
backed by DB2
for z/OS. In one year brand new 4-way DB2 for z/OS Data Sharing
is established. New package (SQLJ Based) implementation started
coexistance features with IMS and existing Cobol Applications.
alternatives are also evaluated such as DL/2 and other proposed
from the world with pros and cons.
The most challenging part was Storing data in Unicode and moving
(EBCDIC) to DB2 for z/OS in Unicode Tables is something really
there is a lot of things you must consider. Modifying existing
programs accessing IMS EBCDIC data and DB2 Unicode data at the same
near same time is really challenging. New Cobol functions and
SQL Functions, zParms, Table Declarations and BIND parameters are
need to be
coordinated very carefully. Even if you incorrectly use one of them
go thru all the checklist to find the problem.
DB2 Utilities really helps a lot. We mostly used Cobol programs to
data in delimited format, ready for DB2 Load input. Nothing
Stored Procedures helped a lot from Application Architecture point
External Cobol SPs help to modify IMS data if the *new* application
IMS data. Other than that brand new Native SQL Procedures were
outside world. Currently some applications are modifying both IMS
New applications are written for DB2 for z/OS and if they need IMS
use Cobol External SPs.
The most painful area was EBCDIC and UNICODE coexistence.
From the skills point of view, obviously DB2 and RDBMS skills are
easier to find then IMS skills. Semi-DBAs are created in
to help design of the applications. Their primary task was "do not
obvious and well known issues". We created a very detailed
design and coding. That eliminated very obvious errors.
Finally, the project is still going on. IMS and DB2 working
will not be forever but it's very hard to estimate how long will it
shutdown IMS completely.
From: Philip Nelson [mailto:[login to unmask email]
Sent: Saturday, July 21, 2012 7:21 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: IMS to DB2 migration
Sorry I'm a bit late in replying, but I was on vacation ...
The first thing I'd say is that before you start any migration
is critical that you understand your data from a logical / design
view. That probably requires you getting into the DBDs and
certainly) the COBOL copybooks for your segments, as I would doubt
there are very few large IMS sites with a totally comprehensive
model approximating to reality. Not only will that help to identify
critical databases you would need to migrate but also you'll start
to get an
idea of the level of complexity involved in a migration.
There has been mention made of some tools and facilities which may
Just be aware that all tools have limitations, especially those
a translation or emulation layer. These limitations can range from
won't work" in your situation to some degree of performance
It's been a while since I looked at some of the tools in this
last time I did there were enough restrictions to make them
use in our situation : in particular I can think of a case where a
database has so many redefinitions understood only by custom
code that no tool stood a chance !!!
You also should have a clear understanding of WHY you want to do
migration. I can think of a number of reasons including -
1) IMS is seen as old and inflexible. If you dig into some of the
features you might find that some of these impressions might not
justified. Certainly IMS isn't going away any time soon !!!
2) You want to save money on the IMS licenses. The key point to
is that you only save this money if you migrate EVERYTHING (and
of the tool support I mentioned above), which is no small
3) IMS skills are hard to find. IBM is well aware of this (indeed
believe it suffers from this itself). However look at some of the
features which reduce the need for core IMS skills (e.g. DL/I and
before deciding to jump.
If the desire to migrate is simply to get data in relational format
by "downstream users" rather than the core transactional facilities
there may be other things you can do more easily, perhaps as a
first step in
the migration direction.
The following might even be useful if you are doing a full scale
and you have a lot of data to move quickly. We have found that a
of getting relational clones of IMS databases for query purposes (1
to 1 table) is to use a combination of IMS High Performance Unload
unload tools are available with similar capabilities) and DB2 LOAD.
trick is to find the unload format which includes the segment name
concatenated key of the segment into the unload record. In HPU its
*FMT3 (from memory). You can then use a DB2 LOAD command with a
clause for the segment name (if using HPU this is found in
positions 3 -->
10) to drop the data into the appropriate table. You are still
have to take the time to define tables matching the data in the
And there are certain circumstances where you are going to have to
something other than standard DB2 LOAD (i.e. a third party LOAD
as BMC LOADPLUS or equivalent from other vendors) : these include
length segments (where the position of the concatenated key moves
based on the segment length) or heavily redefined segments (where
to specify multiple conditions in the WHEN clause to drop the data
segment into multiple tables). You can then define views on top of
"raw" data to help with interpretation. Of course this approach
that it is fine from business perspective for the "relational
clone" to be
some time behind the IMS databases : we have found that even very
databases can be cloned nightly without any problem.
If you need a relational copy which is near real time then you are
looking at some replication technology (what used to be called
DPropNR but I
think is now called "Classic Federation" ?).
You probably are going to need some of these tools and techniques
it is very unlikely that you are going to migrate everything in one
bang". You will probably find that some of the core databases in
environment are tightly coupled into many applications and you are
have to use refactoring and Master Data Management techniques to
those couplings to a degree where it is finally possible to cut
completely. I'd recommend a couple of books to get you up to speed
some of the techniques -
"Refactoring Databases : Evolutionary Database Design" :
: while the examples are relational the
principles underlying them are relevant
"Enterprise Master Data Management : An SOA Approach to Managing
: this has
a really good discussion of MDM principles and the different
involved. (It's also quite impressive that a book from IBM Press
mentions IBM's MDM Server once !!!)
Hopefully some of that information has been useful to you.
As a final note, just make sure that you are doing this migration
right reasons and that you have an understanding of the amount of
potentially involved. But whether you decide to migrate or not (for
try to take small steps which will make any future migration easier
least not any harder). In particular try to introduce a degree
abstraction into new code (and code being changed) based on SOA
to start loosening the tight coupling. If you don't want to go for
blown services at least try to get well defined interfaces to your
place, even at the COBOL subroutine level, that can be swapped for
complete services in the future.
Don't hesitate to drop me an note if you want to discuss this, as
covered this ground a number of times in the past ...
On Wed, Jul 18, 2012 at 5:38 AM, Aurobinda Pratihari
<[login to unmask email]> wrote:
Hi All - We have 1800 databases in IMS (Information management
database and we are looking forward ways to migrate this to DB2
would like to know below details
A.How effective it is doing conversion from IMS database to DB2.How
complexity is involved based on cost,effort,licence and other
B.Any case study /Some body whom I can refer to who has done such
transformation in their organization
C.Challenges associated with database consolidation
E.High level summary/approach
It would be great if I can get some inputs on this
-----End Original Message-----
-----End Original Message-----