RES: IDMS to DB2 conversion

Walter Trovijo Jr

RES: IDMS to DB2 conversion
It´s not so simple as one have said. There are lots of diferences between
network model and relational model (obviously) and they are almost
incompatible, so changing just database access commands is not possible.

I don´t know how you are using IDMS (network only? relational? both?
COBOL/DC? ADS?) but if you are network only or almost network databases, i
would suggest you to start redesigning your databases and rewriting your
programs after that.

If you have IDMS SQL up and running, IDMS itself can help you to first
convert your programs
to SQL and only after that you can convert your data:
IDMS SQL can map network records to relational tables (and perform good) if
you make use of
views (to reference record-sets and avoid area sweeps) and table procedures.
There´s a tool which is part of IDMS SQL that can help you to write
procedures to do complex network
navigation and return it as a relational table for
select/insert/update/delete;

Another issue is IDMS/DC to CICS conversion; for this there are tools (i
know they exist, but i never saw myself or tested) to convert ADS to cobol.
If you´re planning to keep ADS running there are tools (i never tested it)
that allow ADS to access DB2.


hth,
Walter Trovijo.
Salvador, Brasil.

[login to unmask email]

Re: IDMS to DB2 conversion
(in response to Walter Trovijo Jr)
Oh yah, James point about the multi column indexes is a good one I forgot about. We do a lot of SELECTing and sorting on a 3 column key. In the old system we could define 'group' names on the 3 columns (telephone numbers) so that we could
deal with one variable rather than 3. In DB2 you might want to define a concatenation in a view to simplify coding. Using James example

CREATE VIEW view-1 (col_123)

AS SELECT col_1||col_2||col_3

FROM table;

that allows you to simplify, James example from this
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

to

SELECT ... FROM ...
WHERE col_123 >= :hv123
...
ORDER BY col_123, ...

We haven't tried it yet, so I would be interested in to hear if anyon would have concerns about performance with this type of column defined in a view.





Campbell James <[login to unmask email]> on 99/10/07 01:19:44 AM

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

To: [login to unmask email]
cc: (bcc: Rohn Solecki/MTSCommunications/MTS)
Subject: Re: IDMS to DB2 conversion




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