IMS to DB2 migration

Aurobinda Pratihari

IMS to DB2 migration

Hi All - We have 1800 databases in IMS (Information management system) database and we are looking forward ways to migrate this to DB2 database.I would like to know below details

A.How effective it is doing conversion from IMS database to DB2.How much complexity is involved based on cost,effort,licence and other parameters
B.Any case study /Some body whom I can refer to who has done such a transformation in their organization
C.Challenges associated with database consolidation
D.Painful areas
E.High level summary/approach

It would be great if I can get some inputs on this

Aurobinda Pratihari

Enrico Haak

IMS to DB2 migration
(in response to Aurobinda Pratihari)
Hi Aurobinda,

as mentioned some weeks before,
we partner with Circle Group.
Their product DL/2 offers a lot of features from key/data migration up
to sophisticated data transformation.

If you like to get more information please send me a PM.

BTW: why do you hide yourself behind a gmail account?
LinkedIn is everywhere.
;-)

Mit freundlichen Grüßen / Best Regards / Meilleures Salutations

Enrico Haak

___________________________________________________________________

InSoft Software GmbH, Derendorfer Str. 70, 40479 Düsseldorf / Germany
Tel. +49 211 44 03 166, Fax +49 211 48 80 33
Geschäftsführer: Colin Oakhill, Jean-Marie Meyer
HRB 20486 DÜSSELDORF * UST-IDNR:DE 119253977 * www.insoft-software.com
http://www.insoft-software.com


Am 18.07.2012 06:38, schrieb Aurobinda Pratihari:
>
> Hi All - We have 1800 databases in IMS (Information management system)
> database and we are looking forward ways to migrate this to DB2
> database.I would like to know below details
>
> A.How effective it is doing conversion from IMS database to DB2.How
> much complexity is involved based on cost,effort,licence and other
> parameters
> B.Any case study /Some body whom I can refer to who has done such a
> transformation in their organization
> C.Challenges associated with database consolidation
> D.Painful areas
> E.High level summary/approach
>
> It would be great if I can get some inputs on this
>
> Aurobinda Pratihari
>
>
> -----End Original Message-----

Tim Wilkins

RE: IMS to DB2 migration
(in response to Aurobinda Pratihari)

An IMS support person mentioned to me that you should maybe look at this Redbook below and he said that they can now do SQL calls directly into IMS V11 with OPEN DATABASE, without having to change any data on the IMS side.  Better yet, NO CICS or IMS/DC is needed for this, just IMS Connect.  In addition you retain the performance offered by IMS.

 

 

www.redbooks.ibm.com/abstracts/sg247856.html

Philip Nelson

IMS to DB2 migration
(in response to Aurobinda Pratihari)
Aurobinda,

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 exercise it
is critical that you understand your data from a logical / design point of
view. That probably requires you getting into the DBDs and (almost
certainly) the COBOL copybooks for your segments, as I would doubt that
there are very few large IMS sites with a totally comprehensive information
model approximating to reality. Not only will that help to identify the
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 help
you. Just be aware that all tools have limitations, especially those that
provide a translation or emulation layer. These limitations can range from
"just won't work" in your situation to some degree of performance
degradation. It's been a while since I looked at some of the tools in
this space, but last time I did there were enough restrictions to make them
impossible to use in our situation : in particular I can think of a case
where a critical database has so many redefinitions understood only by
custom application code that no tool stood a chance !!!

You also should have a clear understanding of WHY you want to do this
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 newer
features you might find that some of these impressions might not be
justified. Certainly IMS isn't going away any time soon !!!

2) You want to save money on the IMS licenses. The key point to make
here is that you only save this money if you migrate EVERYTHING (and
without any of the tool support I mentioned above), which is no small
undertaking !!!

3) IMS skills are hard to find. IBM is well aware of this (indeed I
believe it suffers from this itself). However look at some of the new
features which reduce the need for core IMS skills (e.g. DL/I and COBOL)
before deciding to jump.

If the desire to migrate is simply to get data in relational format for use
by "downstream users" rather than the core transactional facilities then
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 migration
and you have a lot of data to move quickly. We have found that a good way
of getting relational clones of IMS databases for query purposes (1 segment
to 1 table) is to use a combination of IMS High Performance Unload (other
unload tools are available with similar capabilities) and DB2 LOAD. The
trick is to find the unload format which includes the segment name and the
concatenated key of the segment into the unload record. In HPU its format
*FMT3 (from memory). You can then use a DB2 LOAD command with a WHEN
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 going to
have to take the time to define tables matching the data in the segments.
And there are certain circumstances where you are going to have to look to
something other than standard DB2 LOAD (i.e. a third party LOAD utility
such as BMC LOADPLUS or equivalent from other vendors) : these include
variable length segments (where the position of the concatenated key moves
about based on the segment length) or heavily redefined segments (where you
want to specify multiple conditions in the WHEN clause to drop the data
from a segment into multiple tables). You can then define views on top
of the "raw" data to help with interpretation. Of course this approach
assumes 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 large databases can be cloned nightly without any problem.

If you need a relational copy which is near real time then you are probably
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 anyway,
as it is very unlikely that you are going to migrate everything in one "big
bang". You will probably find that some of the core databases in your
environment are tightly coupled into many applications and you are going to
have to use refactoring and Master Data Management techniques to loosen
those couplings to a degree where it is finally possible to cut over
completely. I'd recommend a couple of books to get you up to speed with
some of the techniques -

"Refactoring Databases : Evolutionary Database Design" :
http://databaserefactoring.com/ : while the examples are relational the
principles underlying them are relevant

"Enterprise Master Data Management : An SOA Approach to Managing Core
Information" :
http://www.ibmpressbooks.com/bookstore/product.asp?isbn=0132366258&w_ptgrevartcl=Master+Data+Management%3a+An+Introduction_1226929
:
this has a really good discussion of MDM principles and the different
patterns involved. (It's also quite impressive that a book from IBM Press
never 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 for the
right reasons and that you have an understanding of the amount of effort
potentially involved. But whether you decide to migrate or not (for now)
try to take small steps which will make any future migration easier (or at
least not any harder). In particular try to introduce a degree of
abstraction into new code (and code being changed) based on SOA principles
to start loosening the tight coupling. If you don't want to go for full
blown services at least try to get well defined interfaces to your data in
place, even at the COBOL subroutine level, that can be swapped for more
complete services in the future.

Don't hesitate to drop me an note if you want to discuss this, as I've
covered this ground a number of times in the past ...

HTH

Phil Nelson








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 system)
> database and we are looking forward ways to migrate this to DB2 database.I
> would like to know below details
>
> A.How effective it is doing conversion from IMS database to DB2.How much
> complexity is involved based on cost,effort,licence and other parameters
> B.Any case study /Some body whom I can refer to who has done such a
> transformation in their organization
> C.Challenges associated with database consolidation
> D.Painful areas
> E.High level summary/approach
>
> It would be great if I can get some inputs on this
>
> Aurobinda Pratihari
>
> -----End Original Message-----
>

Cuneyt Goksu

IMS to DB2 migration
(in response to Philip Nelson)


In addition to Philip's excellent list, I would like to share my latest
experience.



For 4 years, I've been giving Advanced DB2 for z/OS Support and Services to
a major bank. They've been a loyal IMS Customer for several years. In 2008
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 environment
is established. New package (SQLJ Based) implementation started with some
coexistance features with IMS and existing Cobol Applications. Other
alternatives are also evaluated such as DL/2 and other proposed solutions
from the world with pros and cons.



The most challenging part was Storing data in Unicode and moving from IMS
(EBCDIC) to DB2 for z/OS in Unicode Tables is something really important and
there is a lot of things you must consider. Modifying existing Cobol
programs accessing IMS EBCDIC data and DB2 Unicode data at the same time or
near same time is really challenging. New Cobol functions and declarations,
SQL Functions, zParms, Table Declarations and BIND parameters are need to be
coordinated very carefully. Even if you incorrectly use one of them you must
go thru all the checklist to find the problem.



DB2 Utilities really helps a lot. We mostly used Cobol programs to unload
data in delimited format, ready for DB2 Load input. Nothing special.



Stored Procedures helped a lot from Application Architecture point of view.
External Cobol SPs help to modify IMS data if the *new* application request
IMS data. Other than that brand new Native SQL Procedures were enough from
outside world. Currently some applications are modifying both IMS and DB2.
New applications are written for DB2 for z/OS and if they need IMS data, we
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 much
easier to find then IMS skills. Semi-DBAs are created in Application Teams
to help design of the applications. Their primary task was "do not allow
obvious and well known issues". We created a very detailed checklist for
design and coding. That eliminated very obvious errors.



Finally, the project is still going on. IMS and DB2 working together. It
will not be forever but it's very hard to estimate how long will it take to
shutdown IMS completely.



Best regards,

Cuneyt



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



Aurobinda,



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 exercise it
is critical that you understand your data from a logical / design point of
view. That probably requires you getting into the DBDs and (almost
certainly) the COBOL copybooks for your segments, as I would doubt that
there are very few large IMS sites with a totally comprehensive information
model approximating to reality. Not only will that help to identify the
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 help you.
Just be aware that all tools have limitations, especially those that provide
a translation or emulation layer. These limitations can range from "just
won't work" in your situation to some degree of performance degradation.
It's been a while since I looked at some of the tools in this space, but
last time I did there were enough restrictions to make them impossible to
use in our situation : in particular I can think of a case where a critical
database has so many redefinitions understood only by custom application
code that no tool stood a chance !!!



You also should have a clear understanding of WHY you want to do this
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 newer
features you might find that some of these impressions might not be
justified. Certainly IMS isn't going away any time soon !!!



2) You want to save money on the IMS licenses. The key point to make here
is that you only save this money if you migrate EVERYTHING (and without any
of the tool support I mentioned above), which is no small undertaking !!!



3) IMS skills are hard to find. IBM is well aware of this (indeed I
believe it suffers from this itself). However look at some of the new
features which reduce the need for core IMS skills (e.g. DL/I and COBOL)
before deciding to jump.



If the desire to migrate is simply to get data in relational format for use
by "downstream users" rather than the core transactional facilities then
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 migration
and you have a lot of data to move quickly. We have found that a good way
of getting relational clones of IMS databases for query purposes (1 segment
to 1 table) is to use a combination of IMS High Performance Unload (other
unload tools are available with similar capabilities) and DB2 LOAD. The
trick is to find the unload format which includes the segment name and the
concatenated key of the segment into the unload record. In HPU its format
*FMT3 (from memory). You can then use a DB2 LOAD command with a WHEN
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 going to
have to take the time to define tables matching the data in the segments.
And there are certain circumstances where you are going to have to look to
something other than standard DB2 LOAD (i.e. a third party LOAD utility such
as BMC LOADPLUS or equivalent from other vendors) : these include variable
length segments (where the position of the concatenated key moves about
based on the segment length) or heavily redefined segments (where you want
to specify multiple conditions in the WHEN clause to drop the data from a
segment into multiple tables). You can then define views on top of the
"raw" data to help with interpretation. Of course this approach assumes
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 large
databases can be cloned nightly without any problem.



If you need a relational copy which is near real time then you are probably
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 anyway, as
it is very unlikely that you are going to migrate everything in one "big
bang". You will probably find that some of the core databases in your
environment are tightly coupled into many applications and you are going to
have to use refactoring and Master Data Management techniques to loosen
those couplings to a degree where it is finally possible to cut over
completely. I'd recommend a couple of books to get you up to speed with
some of the techniques -



"Refactoring Databases : Evolutionary Database Design" :
http://databaserefactoring.com/ : while the examples are relational the
principles underlying them are relevant



"Enterprise Master Data Management : An SOA Approach to Managing Core
Information" :
http://www.ibmpressbooks.com/bookstore/product.asp?isbn=0132366258
<http://www.ibmpressbooks.com/bookstore/product.asp?isbn=0132366258&w_ptgrev
artcl=Master+Data+Management%3a+An+Introduction_1226929>
&w_ptgrevartcl=Master+Data+Management%3a+An+Introduction_1226929 : this has
a really good discussion of MDM principles and the different patterns
involved. (It's also quite impressive that a book from IBM Press never
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 for the
right reasons and that you have an understanding of the amount of effort
potentially involved. But whether you decide to migrate or not (for now)
try to take small steps which will make any future migration easier (or at
least not any harder). In particular try to introduce a degree of
abstraction into new code (and code being changed) based on SOA principles
to start loosening the tight coupling. If you don't want to go for full
blown services at least try to get well defined interfaces to your data in
place, even at the COBOL subroutine level, that can be swapped for more
complete services in the future.



Don't hesitate to drop me an note if you want to discuss this, as I've
covered this ground a number of times in the past ...



HTH



Phil Nelson

















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 system)
database and we are looking forward ways to migrate this to DB2 database.I
would like to know below details

A.How effective it is doing conversion from IMS database to DB2.How much
complexity is involved based on cost,effort,licence and other parameters
B.Any case study /Some body whom I can refer to who has done such a
transformation in their organization
C.Challenges associated with database consolidation
D.Painful areas
E.High level summary/approach

It would be great if I can get some inputs on this

Aurobinda Pratihari



-----End Original Message-----





-----End Original Message-----

Scott Quillicy

RE: IMS to DB2 migration
(in response to Philip Nelson)

Hi Aurobinda,

You're not alone...although IMS is a great technology as far as speed and reliability goes, we are seeing more companies making an effort to migrate from IMS to a relational platform for a variety of reasons.

If your plan is to migrate all of your 1,800 IMS databases to DB2, along with the application code, be prepared for a long journey.  Our experience is that the majority of the effort will be spent in the overall planning/coordination, design and testing/validation of the migrated application against the original version...most companies significantly underestimate the amount of effort required for the latter.  The actual conversion itself, assuming you use a decent data migration toolset(s)...and (more importantly) have access to SMEs who understand the IMS side, is one of the more predictable phases.

A note of caution....I've seen it happen numerous times...if your existing IMS transaction volume is on the high side and you are barely meeting throughput service level objectives for a particular application(s), a move to a relational platform is probably not going to end on a happy note for the business.

There are a handful of reputable companies who provide IMS to relational application conversion tools/services and there are a couple of good IMS data migration / changed data capture products available to assist you on your journey.  When selecting a tool, you need to make sure that the vendor has the capability to provide assistance during your migration, not just sell you a product and hope things work out for you (or not).  I would be happy to discuss this in more detail with you offline.

From a data perspective, you will find the primary challenges to be:

  • Having access to Subject Matter Experts (SMEs) - arguably the most critical element for success
  • Redefined segments - must understand which field(s) / logic identifies a specific redefine
  • Repeating groups/occurs - typically normalized into separate tables, but not always
  • Non-Keyed segments
  • SDEPs
  • Dates - we've seen some creative methods of how customers dealt with Y2K
  • Invalid data values (i.e. spaces in numeric fields) - a good tool should be able to take care of this type of cleansing automatically

Again, please let me know if you would like to discuss further.

Scott

Sam Christopher

RE: IMS to DB2 migration
(in response to Scott Quillicy)

Hi All, 

With reference to IMS to DB2 conversion, I am working with a leading rental industry and we have initiated the process to convert IMS databases to DB2 tables.

The applications are in COBOL with IMS and DB2 databases. We are in the initial step for database conversion and my responsibilities are to convert/identify the DB2 queries with mapping IMS function codes. 

I am looking forward for your valuable suggestions to get the mapping SQL functions for different IMS function codes. 

GU - Select query
GHU - Select and Update query
GN - Cursor select
ISRT - Insert or Update
REPL - Update
DLET - Delete
SYNC - ???
CODE - ???
PURG - ???
GHN - ???
GNP - ???
GHNP - ???

Looking for valuable suggestions to initiate my analysis and upon this confirmation, i am thinking to validate for each program logic. 

 

 

Philip Nelson

IMS to DB2 migration
(in response to Sam Christopher)
To assist you a little bit -

https://www.ibm.com/support/knowledgecenter/en/SSEPH2_13.1.0/com.ibm.ims13.doc.apr/ims_dlicallsfordbsysservices.htm


SYNC is a bit like commit, but you also want to look at CHKP..

GNP and GHNP are Get (Hold) Next Within Parent ... they retrieve child
segments sequentially.

Some of these calls relate to IMS/TM (transaction management) rather than
Database Management. Some can work for either (e.g. ISRT).

You can't convert an IMS system to DB2 simply by replacing DL/I calls with
SQL. Even for simple cases where it might be possible, the performance
would likely be poor. You need to understand the differences between a
hierarchical and relational database. You want to look at how IMS DBDs
are structured, and how calls using SSAs to locate information and then to
navigate through it. You'll want to understand how different DBD clauses
make the various DL/I calls operate in different ways. You also will want
to understand how IMS logical relationships and secondary indexes work.

You are likely to find that many of the data items you want to put into DB2
columns are only specified in COBOL copybooks, and you should be prepared
to handle such things as COBOL redefinitions and variable length segments.


We've had some success in moving data from IMS to DB2 using IMS High
Performance Unload (Format *FMT3 is useful, since it appends the
concatenated key of the segment, if available, to the end of the record).
We can then normally formulate a load job for DB2 which will handle this
data, although if you have redefinitions and other complexities you are
probably going to need more than the standard DB2 load to give you more
options (e.g. multiple WHEN clauses).

Typically you can approximate each IMS segment to a DB2 table (maybe more
than one DB2 table if there are redefinitions). But I'd suggest that this
is only a transitional step towards a DB2 database design normalized and
optimized.

HTH

Phil Nelson


On 14 November 2016 at 18:18, Sam Christopher <[login to unmask email]> wrote:

> Hi All,
>
> With reference to IMS to DB2 conversion, I am working with a leading
> rental industry and we have initiated the process to convert IMS databases
> to DB2 tables.
>
> The applications are in COBOL with IMS and DB2 databases. We are in the
> initial step for database conversion and my responsibilities are to
> convert/identify the DB2 queries with mapping IMS function codes.
>
> I am looking forward for your valuable suggestions to get the mapping SQL
> functions for different IMS function codes.
>
> GU - Select query
> GHU - Select and Update query
> GN - Cursor select
> ISRT - Insert or Update
> REPL - Update
> DLET - Delete
> SYNC - ???
> CODE - ???
> PURG - ???
> GHN - ???
> GNP - ???
> GHNP - ???
>
> Looking for valuable suggestions to initiate my analysis and upon this
> confirmation, i am thinking to validate for each program logic.
>
>
>
>
>
> -----End Original Message-----
>

Sam Christopher

RE: IMS to DB2 migration
(in response to Philip Nelson)

Hi Phil,

Thanks for your note. Our current shop works on both IMS and DB2 databases. This is the initial phase of the project to migrate the existing IMS databases to DB2. The IMS database conversion will be a phased approach to convert few and followed by others.

The programmers opted to convert each IMS segments to a new table.Though the normalization would be a better approach for efficiency, due to various contraints, we had to agree to take this approach to meet the expectations. Based on segment size, the tablespaces are to be determined. 

Now, started analysis on how to replace the existing IMS. First step is to replace few databases with the IMS calls GU,GHU,GN,ISRT,REPL,DLET,GHN,GNP/GHNP,FLD and make it as DB2 statements. Though GU, GHU, ISRT, DLET, REPL, FLD will be a quicker migration; the GN, GHN, GNP, GHNP will be hard to perform in DB2 with IMS efficiency. 

Looking forward for options to migrate with multiple options. 

1. Parent and Child segments - Multiple tables with same table space. - Will a cursor helps or other possible options?

2. Parent and Child segments - Multiple tables with multiple table space.- Will a cursor helps or other possible options? 

3. Parent and Child segments - 1 table with 1 table space. - One cursor to replace GN call? 

Thanks! 

Joel Goldstein

IMS to DB2 migration
(in response to Sam Christopher)
You may want to prepare for a major performance hit using that conversion approach.

Both CPU and IO.





Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Sam Christopher [mailto:[login to unmask email]
Sent: Tuesday, November 15, 2016 1:27 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: IMS to DB2 migration



Hi Phil,

Thanks for your note. Our current shop works on both IMS and DB2 databases. This is the initial phase of the project to migrate the existing IMS databases to DB2. The IMS database conversion will be a phased approach to convert few and followed by others.

The programmers opted to convert each IMS segments to a new table.Though the normalization would be a better approach for efficiency, due to various contraints, we had to agree to take this approach to meet the expectations. Based on segment size, the tablespaces are to be determined.

Now, started analysis on how to replace the existing IMS. First step is to replace few databases with the IMS calls GU,GHU,GN,ISRT,REPL,DLET,GHN,GNP/GHNP,FLD and make it as DB2 statements. Though GU, GHU, ISRT, DLET, REPL, FLD will be a quicker migration; the GN, GHN, GNP, GHNP will be hard to perform in DB2 with IMS efficiency.

Looking forward for options to migrate with multiple options.

1. Parent and Child segments - Multiple tables with same table space. - Will a cursor helps or other possible options?

2. Parent and Child segments - Multiple tables with multiple table space.- Will a cursor helps or other possible options?

3. Parent and Child segments - 1 table with 1 table space. - One cursor to replace GN call?

Thanks!



-----End Original Message-----

Sam Christopher

RE: IMS to DB2 migration
(in response to Joel Goldstein)

Hi Joel,

Agreed on your statement for a major performance hit. But, what would be the options available for my queries? We had to provide the lists of options?  

Looking forward for options to migrate.

1. Parent and Child segments - Multiple tables with same table space. - Will a cursor helps or other possible options?

2. Parent and Child segments - Multiple tables with multiple table space.- Will a cursor helps or other possible options?

3. Parent and Child segments - 1 table with 1 table space. - One cursor to replace GN call?

Thanks! 

Joel Goldstein

IMS to DB2 migration
(in response to Sam Christopher)
Hi Sam,



While tuning approaches always help a bit, it’s like putting on a band-aid when you need a tourniquet



Bad design costs dearly on the cost and elapsed time areas.



Sorry I don’t have any good approaches here.

I’ve seen the results of these same design decisions before.

Not pretty.





Today’s multi-gigabyte memory machines, and SSD will help a lot compared to where we were more than a decade ago.



Good luck !



Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Sam Christopher [mailto:[login to unmask email]
Sent: Tuesday, November 15, 2016 2:52 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: IMS to DB2 migration



Hi Joel,

Agreed on your statement for a major performance hit. But, what would be the options available for my queries? We had to provide the lists of options?

Looking forward for options to migrate.

1. Parent and Child segments - Multiple tables with same table space. - Will a cursor helps or other possible options?

2. Parent and Child segments - Multiple tables with multiple table space.- Will a cursor helps or other possible options?

3. Parent and Child segments - 1 table with 1 table space. - One cursor to replace GN call?

Thanks!



-----End Original Message-----

Paul Ogborne

IMS to DB2 migration
(in response to Sam Christopher)
Hi Sam,


I am been involved with a number of IMS to DB2 conversions in the past. Mostly fixing poorly performing bad conversions!


As you have stated, conversion of individual segments to single DB2 tables maybe a reasonable approach but some normalisation is always advisable (as you know).


IMS relies on different hierarchical segments being stored logically adjacent and hence a GNP for instance, will work efficiently.
DB2 data in different tables is certainly not likely to be adjacent. Therefore, DO NOT be tempted to convert DL/1 to DB2 calls one for one. You may be under pressure to do a conversion in this way as a 'quick win', but as stated by others the performance 'hit' cannot be understated.


Perhaps some GU database calls may be candidates for conversion to single DB2 SELECTs. However, a GU path call for instance, would not. This and many of the other calls to retrieve data will almost certainly need to be converted to joins. This highlights a basic difference between hierarchical and relational database management systems. DB2 is of course once such RDMS and these thrive on joins and so do not be afraid of them!


In addition, careful consideration of indexes is also important. First of all look at the existing indexing as a starting point. Maintaining entity integrity (uniqueness) in DB2 is vital and so you will want unique indexes on primary and other unique keys. As you look to convert DL/1 calls to joins as suggested, then look at access paths and join predication to determine whether extra indexing may be required (remembering also that each additional index is also an overhead). Analysis of your most used access paths will also be an indicator of how to cluster your DB2 data. Don't just fall into the trap of clustering on your primary key as a unique SELECT does not require an index which is clustered, it just needs a unique one.


Ideally, your project team will include people who are experience with IMS and DB2. If not then seek advice from within your organisation.


Please feel free to contact me off list if you would like to discuss further; (I am on LinkedIn).


I wish you well with this.


Regards,
Paul Ogborne.





-----Original Message-----
From: Sam Christopher <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Mon, 14 Nov 2016 17:18
Subject: [DB2-L] - RE: IMS to DB2 migration



Hi All,
With reference to IMS to DB2 conversion, I am working with a leading rental industry and we have initiated the process to convert IMS databases to DB2 tables.
The applications are in COBOL with IMS and DB2 databases. We are in the initial step for database conversion and my responsibilities are to convert/identify the DB2 queries with mapping IMS function codes.
I am looking forward for your valuable suggestions to get the mapping SQL functions for different IMS function codes.
GU - Select query
GHU - Select and Update query
GN - Cursor select
ISRT - Insert or Update
REPL - Update
DLET - Delete
SYNC - ???
CODE - ???
PURG - ???
GHN - ???
GNP - ???
GHNP - ???
Looking for valuable suggestions to initiate my analysis and upon this confirmation, i am thinking to validate for each program logic.





Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
** ** ** Attend the 2016 IDUG EMEA DB2 Tech Conference** ** **
---> Brussels, Belgium November 13-17, 2016 <---
http://www.idug.org/emea2016


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Larry Kirkpatrick

RE: IMS to DB2 migration
(in response to Paul Ogborne)

I found this thread (somewhat old, but rather interesting to our shop).

I am wondering about the experience of shops that have set a direction for using the DL/2 product. 

specifically:

1)  Have you been successful in eliminating your IMS/DB license charges?

2)  How long from start to finish (approximately) did it take you to accomplish the complete elimination of IMS/DB?  (and, about how many physical IMS databases did you have to work with?)

 

I appreciate you input on this (and if you want to e-mail me a separate comment on this, I welcome that - My e-mail is [login to unmask email] )

 

Larry 

Philip Nelson

IMS to DB2 migration
(in response to Larry Kirkpatrick)
I must say that we looked at DL/2 a long while ago, but didn't move forward
with it since there were situations it could not handle at that time
(things will have moved on, but I doubt it would still cope well with our
business rules database with about 1000 redefinitions).

To be honest, I think if you are serious about doing this, and your IMS
systems is anything other than low volume and / or very low complexity,
then you really are going to have to look at a rewrite rather than putting
a piece of software in between to act as a translator.

Phil

On 16 April 2018 at 16:23, Larry Kirkpatrick <[login to unmask email]> wrote:

> I found this thread (somewhat old, but rather interesting to our shop).
>
> I am wondering about the experience of shops that have set a direction for
> using the DL/2 product.
>
> specifically:
>
> 1) Have you been successful in eliminating your IMS/DB license charges?
>
> 2) How long from start to finish (approximately) did it take you to
> accomplish the complete elimination of IMS/DB? (and, about how many
> physical IMS databases did you have to work with?)
>
>
>
> I appreciate you input on this (and if you want to e-mail me a separate
> comment on this, I welcome that - My e-mail is [login to unmask email]
> )
>
>
>
> Larry
>
> -----End Original Message-----
>

Isaac Yassin

IMS to DB2 migration
(in response to Larry Kirkpatrick)
Hi,
The best results were achieved with rewrite / redesign without DL/2 (at 3
different locations).
The one place that used DL/2 is still using it and paying for both IMS and
DL/2 (over 10 years now).


*Isaac Yassin *
*dbX-Consulting Services*
*IBM Gold Consultant*
*IBM Champion for Analytics #ibmchampion*
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS 9, 10, 11
IBM Certified System Administrator - DB2 10, 11 for z/OS
IBM Certified Database Administrator - DB2 LUW 10.1
IBM Certified Specialist - PureData System for Analytics v7.1
IDUG Israel RUG co-Chair


On Mon, Apr 16, 2018 at 6:23 PM, Larry Kirkpatrick <[login to unmask email]>
wrote:

> I found this thread (somewhat old, but rather interesting to our shop).
>
> I am wondering about the experience of shops that have set a direction for
> using the DL/2 product.
>
> specifically:
>
> 1) Have you been successful in eliminating your IMS/DB license charges?
>
> 2) How long from start to finish (approximately) did it take you to
> accomplish the complete elimination of IMS/DB? (and, about how many
> physical IMS databases did you have to work with?)
>
>
>
> I appreciate you input on this (and if you want to e-mail me a separate
> comment on this, I welcome that - My e-mail is [login to unmask email]
> )
>
>
>
> Larry
>
> -----End Original Message-----
>