DB2 vs. VSAM benefit vs. true cost

Jim Tonchick

DB2 vs. VSAM benefit vs. true cost
You're missing the point of converting to DB2 vs. VSAM file structures for
containing application data.

The reason for converting is twofold.

1) DB2 is a true database management system, VSAM is just a file access
method.
2) The ability to use SQL and the flexibility it gives an application
programmer in manipulating data.

Did your test compare a DB2 SELECT of only two columns in the clustering
index vs. the overhead needed to code the same logic in the application program
and reading the entire VSAM file? Did you test two jobs simultaneously
accessing the same data? Did you test running an update job while the data was
allocated to an on-line? What type of dynamic backout does VSAM supply if a batch
application program abends? And try adding an index to a VSAM file and having
application programs use it WITHOUT re-coding the application programs! How
many CPU cycles are used editing the source, recompiling the program and
retesting the application? How much is that worth?

How much are all these benefits worth to application design and data
integrity? Are a couple of MIPS worth it? I'd say yes.

Jim Tonchick

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bernd Oppolzer

Re: DB2 vs. VSAM
(in response to Jim Tonchick)
You always get misleading results, if you compare VSAM to DB2 used in the
manner you described. You cannot, in my opinion, simply replace VSAM accesses
by singleton SELECTs and expect acceptable performance. You have to use the
"set oriented" features of DB2, that is, joins, group by, tablespace scan
with sequential prefetch, and so on.

Yes, this means, that you will have to change your applications, at least the
heavy ones, to make full use of the DB2 features.

But, if you do that, you will get better response times with DB2 compared to
VSAM in many situations.

Kind regards

Bernd



Am Mittwoch, 12. Januar 2005 05:09 schrieben Sie:
> I know this is a loaded question but.... Does anyone know of any
> benchmarks that compare DB2 CPU usage to VSAM. I know this all depends on
> if VSAM programs are converted to properly use DB2, the tables are properly
> designed, etc. but is there any 'rule of thumb' for DB2 overhead when
> comparing to VSAM? The reason I ask is that we are getting a lot of grief
> about how much CPU overhead DB2 adds to processing. We did some benchmarks
> doing sequential and random reads against a VSAM file and then simply
> converted the reads to DB2 singleton selects for comparison. DB2 uses
> between 2 and 4 times the CPU. Any feedback would be appreciated.
>
> Thanks
> Dave.
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Churn

Re: DB2 vs. VSAM
(in response to Bernd Oppolzer)
Dave,

You will not be able to compare DB2 to VSAM on straight I/O access.
If you start to add features and note that it is easier to do write
and change code with DB2 then you can start to show where the database
shines. DB2 allows the programs to be written for just the rows that
you want to process vs. figuring out if the row should be processed.
Perhaps you could put together a benchmark showing how a calculated
fields are handled something like the average account balances grouped
by state (or country). Also present the actual programs that produced
the similar results.

I teach a short section of a class that quickly compares the two. If
you would like to discuss this more, please contace me off-line at
[login to unmask email] And we start off that section of the class with
a picture of an apple and an orange....


On Tue, 11 Jan 2005 23:09:18 EST, [login to unmask email] <[login to unmask email]> wrote:
>
> I know this is a loaded question but.... Does anyone know of any
> benchmarks that compare DB2 CPU usage to VSAM. I know this all depends on
> if VSAM programs are converted to properly use DB2, the tables are properly
> designed, etc. but is there any 'rule of thumb' for DB2 overhead when
> comparing to VSAM? The reason I ask is that we are getting a lot of grief
> about how much CPU overhead DB2 adds to processing. We did some benchmarks
> doing sequential and random reads against a VSAM file and then simply
> converted the reads to DB2 singleton selects for comparison. DB2 uses
> between 2 and 4 times the CPU. Any feedback would be appreciated.
>
> Thanks
> Dave.
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


--
David Churn

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Bob Galeotti

Re: DB2 vs. VSAM
(in response to David Churn)
Hi Dave,
In response to your question, " but is there any 'rule of thumb' for DB2 overhead when comparing to VSAM?"

Yes there is a rule of thumb:

Short Version: If your business will never change and your costs are static, then fine tune that VSAM system and stick with it.

Long Version:

If you have no DB2 DBAs and a large, inexpensive resource of developers, change control analysts, round the clock application support staff, mission critical applications that are 1960s vintage, and don't mind them being in a constant state of code maintenance/enhancement flux in an attempt to work in today's business world, and users that don't mind waiting the traditional "man week" for a report (and paying that much for it), then you should go with VSAM.

If you have a few good DBAs, exorbitant developer and support staff costs (or few developers and supporters), users that need applications that can be easily integrated with the web on one end, outside business partners in the middle, and the warehouse on the other end, and users that constantly want information immediately in unimaginable ways for who knows what (marketing?), and if you think things might change constantly in your business, then you should go with DB2.

HTH,

Bob Galeotti
SoftBase Systems, Inc.
800.669.7076 x372
[login to unmask email]
http://www.softbase.com/

----- Original Message -----
From: [login to unmask email]
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, January 11, 2005 11:09 PM
Subject: [DB2-L] DB2 vs. VSAM


I know this is a loaded question but.... Does anyone know of any benchmarks that compare DB2 CPU usage to VSAM. I know this all depends on if VSAM programs are converted to properly use DB2, the tables are properly designed, etc. but is there any 'rule of thumb' for DB2 overhead when comparing to VSAM? The reason I ask is that we are getting a lot of grief about how much CPU overhead DB2 adds to processing. We did some benchmarks doing sequential and random reads against a VSAM file and then simply converted the reads to DB2 singleton selects for comparison. DB2 uses between 2 and 4 times the CPU. Any feedback would be appreciated.

Thanks
Dave.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Myron Miller

Re: DB2 vs. VSAM
(in response to Bob Galeotti)
Dave,
As many have already suggested a comparison directly using DB2 singleton
selects versus VSAM is a poor comparison. Why not use BDAM and QSAM as a
comparison, it makes a much sense?

One of the single biggest issues that I see with programmers going from VSAM to
DB2 is getting rid of the VSAM record concept and using SET logic. The read
table A get value then read table B with that value is a notoriously poor way
of using DB2. Sure it works. But it's phenomenally inefficient and a very
poor usage of resources. Doing a SELECT from TABLE A and B where B.col = a.col
is much more efficient.

Similarly, run your batch update programs and CICS update programs at the same
time without something like SHRLEVEL4 product with VSAM. With Db2 it's
trivial. CICS and VSAM are completely different animals. It's like comparing
a volkswagen with a Peterbilt. Yes, they are both vehicles but they serve
totally different purposes. Both have their place and are excellent in what
they do. And both can be misused and abused, DB2 probably more than VSAM
because it requires a paradigm shift in thinking. I've seen efficient VSAM
app's and phenomenally inefficient ones, same as DB2.

The bigger issue is the other factors rather than CPU cost. Flexibility,
recoverability, extended hours, extended accessability all are in DB2's favor.
Are these worth it to your business? That's the real key. CPU cost is
actually such a minor portion of the overall factors as to be in many cases a
non-factor.

Just my opinion.

Myron
--- [login to unmask email] wrote:

> I know this is a loaded question but.... Does anyone know of any
> benchmarks that compare DB2 CPU usage to VSAM. I know this all depends on if
> VSAM
> programs are converted to properly use DB2, the tables are properly designed,
> etc.
> but is there any 'rule of thumb' for DB2 overhead when comparing to VSAM?
> The reason I ask is that we are getting a lot of grief about how much CPU
> overhead DB2 adds to processing. We did some benchmarks doing sequential and
> random reads against a VSAM file and then simply converted the reads to DB2
> singleton selects for comparison. DB2 uses between 2 and 4 times the CPU.
> Any
> feedback would be appreciated.
>
> Thanks
> Dave.
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
> The IDUG List Admins can be reached at [login to unmask email] Find
> out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Jackson

Re: DB2 vs. VSAM
(in response to Myron Miller)
This should have gone out as a memo to all IT personnel 10 years ago, at least. The issue is not CPU cycles, but getting usefull
information from data to those who need it as quickly as possible. VSAM served it's purpose 30 - 40 years ago, but that is longer the case.
A RDBMS of any kind is much more suited to today's world.

If the VP of Sales wants to know the average sale amount from District X, in the VSAM world he might wait for a program to be written and put into production maybe a week later. With DB2 or any other RDBMS he could know in a few minutes, with a well-crafted database.

thoughts,

Phil J.




Hi Dave,
In response to your question, " but is there any 'rule of thumb' for DB2 overhead when comparing to VSAM?"

Yes there is a rule of thumb:

Short Version: If your business will never change and your costs are static, then fine tune that VSAM system and stick with it.

Long Version:

If you have no DB2 DBAs and a large, inexpensive resource of developers, change control analysts, round the clock application support staff, mission critical applications that are 1960s vintage, and don't mind them being in a constant state of code maintenance/enhancement flux in an attempt to work in today's business world, and users that don't mind waiting the traditional "man week" for a report (and paying that much for it), then you should go with VSAM.

If you have a few good DBAs, exorbitant developer and support staff costs (or few developers and supporters), users that need applications that can be easily integrated with the web on one end, outside business partners in the middle, and the warehouse on the other end, and users that constantly want information immediately in unimaginable ways for who knows what (marketing?), and if you think things might change constantly in your business, then you should go with DB2.

HTH,

Bob Galeotti
SoftBase Systems, Inc.
800.669.7076 x372
[login to unmask email]
http://www.softbase.com/

----- Original Message -----
From: [login to unmask email]
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, January 11, 2005 11:09 PM
Subject: [DB2-L] DB2 vs. VSAM


I know this is a loaded question but.... Does anyone know of any benchmarks that compare DB2 CPU usage to VSAM. I know this all depends on if VSAM programs are converted to properly use DB2, the tables are properly designed, etc. but is there any 'rule of thumb' for DB2 overhead when comparing to VSAM? The reason I ask is that we are getting a lot of grief about how much CPU overhead DB2 adds to processing. We did some benchmarks doing sequential and random reads against a VSAM file and then simply converted the reads to DB2 singleton selects for comparison. DB2 uses between 2 and 4 times the CPU. Any feedback would be appreciated.

Thanks
Dave.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm