IDMS to DB2 conversion

Eugenia Huang

IDMS to DB2 conversion
Hello friends:

We're interested in any shops that have experienced IDMS to DB2
conversion. Could you please let me know on the following from your
experience on this kind of conversion projects:

1. Is there any third party tool(s) that handle IDMS to DB2 conversion?
Their PROs and CONs?
2. What should we watch out for?

Your valuable experiences, tips and hints are highly appreciated.
Thanks in advance!

Eugenia

[login to unmask email]

Re: IDMS to DB2 conversion
(in response to Eugenia Huang)
Hello Huang,
As per my knowledge, there are two ways of implementing the IDMS to DB2
conversion, I'm not sure of the name of the tools that are avaiable, but the
approach would be -

1. Map the IDMS structure to DB2 and replace all the IDMS calls with SQL
calls in the programs. The changes are minimal but the application doesn't make
use of the relational power. The only advantage I see is the reduced development
life cycle time.

2. The second approach requires remodelling of the IDMS database to relational
model, which increases the developement life cycle but the application can be
optimized.

Hope this helps...

regards,
Sushil






"Huang, Eugenia" <[login to unmask email]> on 10/06/99 10:25:48 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc: (bcc: SHILKMJ SushilKumarJaiswal/Phoenix Home Life Mutual Insurance)
bcc: SHILKMJ SushilKumarJaiswal/Phoenix Home Life Mutual Insurance
Subject: IDMS to DB2 conversion




Hello friends:

We're interested in any shops that have experienced IDMS to DB2
conversion. Could you please let me know on the following from your
experience on this kind of conversion projects:

1. Is there any third party tool(s) that handle IDMS to DB2 conversion?
Their PROs and CONs?
2. What should we watch out for?

Your valuable experiences, tips and hints are highly appreciated.
Thanks in advance!

Eugenia

Mike Ockenfels

Re: IDMS to DB2 conversion
(in response to SHILKMJ_SushilKumarJaiswal@PHL.COM)
There is a third party tool called DARS which converts IDMS to DB2. It's made
by a company called International Software which is located in Canada. Their
phone number is 1-800-295-7609. I'm sure they would be happy to work with you.

We started a project to convert an old IDMS system to DB2 but have put in on the
back burner for now. We worked through several problems with the DARS code but,
when we postponed the project, we were getting pretty close to a workable
solution. Interbational Software is a small company but is very responsive to
any issues that come up.

Mike Ockenfels
MCI-Worldcom DBA








"Huang, Eugenia" <[login to unmask email]> on 10/06/99 09:25:48 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Mike Ockenfels/CDR/BSM/MCI)
Subject: IDMS to DB2 conversion




Hello friends:

We're interested in any shops that have experienced IDMS to DB2
conversion. Could you please let me know on the following from your
experience on this kind of conversion projects:

1. Is there any third party tool(s) that handle IDMS to DB2 conversion?
Their PROs and CONs?
2. What should we watch out for?

Your valuable experiences, tips and hints are highly appreciated.
Thanks in advance!

Eugenia

[login to unmask email]

Re: IDMS to DB2 conversion
(in response to Mike Ockenfels)
We went from a Non IBM environment to DB2 so I can't answer #1.

#2 - If you hire an external company to do conversion, keep control of the project. Don't just send them the code and let them go crazy.

Don't get me started ... !
OK, here is a 'short list' of things to consider. Most of them are so "common sense" that we didn't think about them ahead of time.h

- try very hard to avoid the "Quck and Cheap" fix. The logic required to access data form IMS is somewhat different than for DB2. For example be willing to modify old code to take advantage of new features, rather than leaving old code
like data types and converting to/from new DB2 format. Changing code to use 2 or 3 table joins rather than separate table access or adding non indexed WHERE clause conditions rather than checking for them in application code.
- with this point you run into the discussion short term cost (ie minimum change for minimum conversion cost) v.s long term cost (ie CPU inefficient code, excessive lines of code that make future analysis and maintenance more
difficult)
- if your programmers are not experienced SQL coders, a common tendancy is to model from existing (converted) code. This can lead to bad SQL coding practices being perpetuated in the name of 'coding standards'. If they are
experienced with SQL then they are more likely to do the 'right thing' rather than blindly following bad coding examples. (see next point)

- review the DB2/SQL coding standards that will be implemented, before they are implemented. An automated code conversion utility can introduce some really dumb coding conventions in the name of expediency. We were depending on the
conversion project to define DB2 coding standards and as a result we ended up with some really CPU extravagant 'standards'. For example it was "too manpower intensive" to analyze where in the code to place a cursor close, so instead they
created 'cursor-open indicator flags' and coded cursors like this ...

if cursor-open flag is true
close cursor
else
open cursor
end

fetch (first row
loop conditions including check if a row was retrieved
application logic
fetch next row
end loop

the cursors are all left open until next open is performed or the end of program is reached!

- another coding standard, where do you put the cursor declare. For example, in COBOL the DECLARE is non executeable code, that has to be physically (not just logically) located before first reference to the cursor. Do you put it:
- in working storage?
- in separate 'Declare cursor(s)" paragraph(s)?
- in a separate paragraph with the OPEN?

- Seemingly small things can have large impacts that a conversion team may not consider. Here is another coding standards question
SELECT COLA , or SELECT COLA
COLB , , COLB
COLC , COLC
FROM.... FROM ...
They look alike, and both return the same results, but when doing a 'difference report' to compare "old" and "new" versions of code (to identify code changes) in the first example, adding a new column to a select looks like a 3 line change:
Delete line "COLC"
add line "COLC ,"
add line "COLD"

compared to a single line change (Add ", COLD" in second example.

- document (and keep easily available for post project access) DB2 and code design decisions AND the rational for them. Most of these decisions will be made by external DB2 'experts' who won't be around later. DB2 is constantly changing,
and it is easier to decide when it becomes appropriate to use new features if you know why things were defined as they were in the original system . i.e. in V2.3 it was decided to define full table views, but no one in the company now
knows exactly why. Another is were defined with certain FREESPACE/FREEPAGE amounts but looking at them now it does not appear appropriate (freepages on a static lookup table?), they probably used system defaults.

- consider the option of creating generic modules or objects to do common types of I/O requests

- consider adding more than just SQLCODE to error reporting, ie SQLERRMC, SQLERD(3) and SQLSTATE

- depending on the length of the project, try to involve as many of your staff as possible in the conversion project to give them hands on experience before going 'live'

- design the naming standards and structure you will use for your subsequent development environment. We just 'took over' and expanded on the environment created by the conversion team. They created names and naming conventions that made
sense to them for the conversion, but that do not really make sense for long term use by development team.

- make sure all of your development staff is well trained in SQL. SQL thinking & design is somewhat different than for IMS, much more flexible. SQL has a lot more functionality available than IMS. Anyone can model from existing SQL to
create new simple single table SQL. But it takes time and knowledge to take full advantage of the features available in SQL. Ideally a follow up 'intermediate' level course after you've been on DB2 for a while (6 months to a year?) will
allow the developers to pick up more advanced knowledge once they have intelligent questions to ask instructor.

- one of the 'not obvious' issues we ran into was that our old system allowed code to start in column 1 and run all the way to column 232! As a result we had really deeply nested (and indented) IFs coded, just because we could (and END-IF
was not available when code was first written). Just image what the code looks like when you move it to IBM COBOL standard Column 7 to 71 line limits!

I guess the bottom line is, if they say things like 'simple fix ... ' or '... absolutely no problem for our auto converter program ...' then I suggest you be prepared run, don't walk to the nearest exit. Just like any project, no matter
how much you prepare there is always something that comes up with a "GOTCH-YA!"

Good luck
Rohn





"Huang, Eugenia" <[login to unmask email]> on 99/10/06 09:25:48 AM



Hello friends:

We're interested in any shops that have experienced IDMS to DB2
conversion. Could you please let me know on the following from your
experience on this kind of conversion projects:

1. Is there any third party tool(s) that handle IDMS to DB2 conversion?
Their PROs and CONs?
2. What should we watch out for?

Your valuable experiences, tips and hints are highly appreciated.
Thanks in advance!

Eugenia

James Campbell

Re: IDMS to DB2 conversion
(in response to Rohn.Solecki@MTS.MB.CA)
Eugenia

Some years ago we (or rather, since it was before my time - I'm
innocent, the business whose IT systems we manage) used some software
houses to convert from DMS II (which is also a network dbms) to DB2.
Some of the blunders that were made include:

- Converting "GET <record>" to "SELECT * FROM <table>".

- not converting all fields into columns. Some grouped fields were
converted to a single column. Hence we have DB2 columns that might be
defined as CHAR(5), but the first two bytes are character and the
remaining 3 bytes are really DECIMAL(5).

- (This is probably related to the fact the conversion was off a
Burroughs system) The group that converted the LINK programs used quotes
as text delimiters, the COBOL programs were converted to use
apostrophes.

- Use of multi-field indexes were not properly re-designed.
Non-relational dbmses allow you to position on a record using multi
level indexes, and then read the remaining records in index sequence.
In DB2/SQL this is done using something like:
SELECT .... FROM ....
WHERE col1 >= :hv1
AND (COL1 > :hv1
OR (col1 = :hv1 AND col2 > :hv2)
OR (col1 = :hv1 AND col2 = :hv2 AND col3 > :hv3)
...
OR (col1 = :hv1 AND ... AND colm = :hvm AND coln >= :hvn)
)
ORDER BY col1, col2, ... colm, coln

The problem occurs because the non-rdbms can, usually, do the equivalent
of a direct index probe to find (:hv1, :hv2,..., :hvn), but DB2 does a
probe to find (:hv1) and then scans the index looking at each entry to
see if it satisfies (col1 > :hv1) or (col1 = :hv1 AND col2 > :hv2) or
.... If you have low cardinality on col1 and retrieve only a few rows
(eg a screen full), this scan, in an on-line system, can be a
performance killer. The simple alternative is to split the select into
multiple selects:

SELECT .... FROM ....
WHERE col1 > :hv1
ORDER BY col1, col2, ... colm, coln

...

SELECT .... FROM ....
WHERE col1 = :hv1 AND ... AND colm > :hvm
ORDER BY col1, col2, ... colm, coln

SELECT .... FROM ....
WHERE col1 = :hv1 AND ... AND colm = :hvm AND coln >= :hvn
ORDER BY col1, col2, ... colm, coln

So you open the last of these, fetch to the end, close that cursor, open
the previous select, fetch to the end etc. You then have a) multiple
SQL statements to maintain and b) the performance overhead of all the
open/closes.

What you really want is to get a set of selects that:
- usually, don't have a long index scan (occasionally they will), and
- not so many selects that you have too many open/closes. something
like:
SELECT .... FROM ....
WHERE col1 >= :hv1
AND (COL1 > :hv1
OR (col1 = :hv1 AND col2 > :hv2)
OR (col1 = :hv1 AND col2 = :hv2 AND col3 > :hv3)
)
ORDER BY col1, col2, ... colm, coln

SELECT .... FROM ....
WHERE col1 = :hv1 AND col2 = :hv2 AND col3 = :hv3 and col4 >= :hv4
AND (col4 > :hv4
OR (col4 = :hv4 AND col5 > :hv5)
...
OR (col4 = :hv4 AND ... AND colm = :hvm AND coln >= :hvn)
)
ORDER BY col1, col2, ... colm, coln

So the second select will have a short index scan and will, hopefully,
satisfy most screenfulls. For the odd time it doesn't you have to use
the first, but you live with the occasional long scan.

The COBOL programs were converted to use a single select, the LINK
programs were converted to have a cursor for each column in the index.

(apologies for the length and detail of this point, but I think it's
necessary to properly describe the point)

- DMS II allows sparse indexes. These were converted to separate
tables.

- failing to set conversions standards that allowed the above problems.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Tooronga
+61 3 9261 1802
[login to unmask email]
-----Original Message-----
From: Huang, Eugenia [mailto:[login to unmask email]
Sent: Thursday, October 07, 1999 12:26 AM
To: [login to unmask email]
Subject: IDMS to DB2 conversion


Hello friends:

We're interested in any shops that have experienced IDMS to DB2
conversion. Could you please let me know on the following from your
experience on this kind of conversion projects:

1. Is there any third party tool(s) that handle IDMS to DB2
conversion?
Their PROs and CONs?
2. What should we watch out for?

Your valuable experiences, tips and hints are highly appreciated.
Thanks in advance!

Eugenia