Home The Company Publications Products Links Tips

ADABAS vs. DB2

Last update: 30 June 2004

Laura Dennis asked:
I'm sure there have been threads on this before, but we are being threatened with a study to see what it would take to convert our files from ADABAS to DB2.

Can someone tell me a good source of statistics for a comparison of efficiencies? We have relatively large files (around 20 million records a piece) which were designed to use ADABAS features meaning we're using PEs and MUs, etc.

Lance Maguire responded:
This issue presents itself quite frequently. Normally with managers in the systems software arena having lunch with an IBM sales rep.

SAGA had provided the attached Power Point. It is quite useful. The list server rejected the PPT when I attempted including in a response. I may have the ability to send it directly .

Bring it on and show the comparisons in your shop.

"An Evaluative Study by Butler Bloor Group
from May 27, 1995"

Left mouse-click to view, right mouse-click to download the PPT presentation

Remarks to the PowerPoint presentation from June 2004:

Isaac Yassin wrote:
The PowerPoint presentation has many "incorrect" information.

COMPRESSION – built into DB2 since V4 (COMPRESS YES on tablespace definition) , you can decide what to compress and what not to compress. HARDWARE COMPRESSION is part of hardware architecture, before it existed in hardware it was possible in software. Open the POP and see the op-code for yourself.

When you drop a tablespace – the VSAM file behind it goes away (IDCAMS DELETE CLUSTER) , the connection to space reclaim is wrong. If you drop a table in a segmented tablespace then the segments are immediately available.

If you drop a table in a multi-table simple tablespace then the space is not reclaimed till reorg – and the ROT is not to use simple (non-segmented) tablespace.

Rows do not span a page. If you have a 32kb row it's in a 32kb page tablespace.

REOEG is done at tablespace level , not at DB level. (Usually you put 1 table in a tablespace)

REORG can be done while you are still updating the data (since V5)

Filed length – at V7 you may change character field length for VARCHAR (make it longer, not shorter)

In V8 you can enlarge CHAR/ VARCHAR / DECIMAL / etc. AND YOU CAN DO IT ONLINE !

MU & PE – it least something is correct here – DB2 does not have them (as of V8)

As for performance – it totally depends on design. When you take an ADABAS design and "moves" it into DB2 without re-design, you'll pay in performance. There things that are much faster in ADABAS but it goes the other way as well.

At least if you take the PPT and use – know that it contains wrong information

In DB2 you can use triggers / CONSTRIANTS / RI rules / UDF as part of the DBMS engine. You can LOAD data into a table while it is still being updated by other users (no need for exclusive use) - as of V7

I forgot to mention that as of DB2 V6 you may COPY your indexes and RECOVER them (or still REBUILD them from the data - it's your decision). As for another question - UNIQUE index is defined as UNIQUE in creation. The information is recorded in the DB2 catalog.

Lew Levy responded:
I think where the biggest difference in DBA support comes in for DB2 is with tuning SQL, tuning table information (runstats, partitions, indexes, etc.), and doing reorgs. Adabas doesn't require as much effort in these aspect of DBMS support.

Where Adabas DBA's get more time chewed up is helping in the design phase. In DB2 people tend to implement a 3rd normal form design and the developers don't worry about defining indexes. In Adabas developers need to spend more time defining keys and working out when to use (or not use) MU and PE constructs.

Hubert Phillips II responded:
My experience was DB2 required a lot more DBA activity to support it. Perhaps 3 to 1 for my situation. But this was not a conversion, we had two major systems that interacted, one with ADABAS and one with DB2. I am comparing these two (worked on both by the way).
Isaac Yassin responded:
From my experience about DBA, it's usually the same unless you take an experienced ADABAS DBA and tell him to be a DB2 DBA, in this case you'll need more. (BTW -it's true the other way around)

To be a DBA, never mind the DB, is something that takes time. Don't base you ratio on "experienced" vs. "non-experienced".

Bill Proctor responded:
I think that 5 to 1 is a little high. I have been handling Adabas for 4 years by myself. We now have two physical DBA's and 1 application DBA.
Laura Dennis responded and asked:
Our application is the vehicle title and registration system for the state of Texas. It is client/server and heavy transaction/concurrent usage, heavy updates, no record expansions. Not many MUs or PEs, and whether we handle with multiple fields or records will vary from file to file.

I have another question. Could someone relate their experiences with using Natural Engineer or other products to do a mass conversion of programs from Natural/ADABAS to Natural/DB2.

Also, I've heard that the number of DBAs needed to handle DB2 over ADABAS is 5 to 1. Any experiences there?

Lew Levy responded:
One point of contention on the COMPRESSION topic. DB2 does support compression, but it is nothing like Adabas compression nor does it achieve close to the same DASD or performance gains that Adabas compression does. Last time I worked with DB2, the compression was record level, not field level. As far as I know, Adabas field level compression is unmatched by any other DBMS.
John Gregory responded:
A little something I just remembered because someone just asked be about finding out if an Index is unique in ADABAS. An ADABAS file does not have to have a unique index because it has ISN's(not recommended) but DB2 requires a unique index. I think most files do have a unique index even if it is not defined as unique to ADABAS. Just something else to think about.

Not to encourage the use of DB2 But it does have index's that use more than one field. Should never define every field as a descriptor. Also it is how you word your query to the data base that determines how the table is accessed. If the index's are not correct then yes DB2 will do its own thing in getting the data. I changed a program that ran for 5 hrs to run in under 5 min by changing the sequence of the query fields. So training will be needed big time if you go to DB2.

Hubert Phillips II responded:
There were no superdescriptors in DB2 (version 6?) either. The DBAs simply made every alpha field a descriptor (which is not the same as an ADABAS descriptor efficiency wise).

Also, DB2 decided how to handle the order of using descriptors dynamically, which made it hard to design the order of descriptor WITH and WHERE selection for efficiency.

Natural gives the programmers much more control, which can be very good or very bad.

John C. Gregory responded:
Good Luck, I have used both and like ADABAS better. If you do convert part of the decision will be are you going to stay with Natural or not. Also over the years I have heard that DB2 may have its own version of MU's and PE's but so far I have not seen that yet. Please let us know if someone makes the decision to go to DB2 I can not see any gain myself.
James Gallegos responded:
Your going to have to convert those groups into separate tables as opposed to a separate row for each occurence (which would increase the size of your file(table)which would affect performance. Set up a relation to the new tables from the Parent table.

Top Page

Back to ADABAS Tips, Tricks, Techniques -- Overview