[DB2-L] Any comparison Read/write SAM file vs DB2 ?

Joseph Fatula

[DB2-L] Any comparison Read/write SAM file vs DB2 ?
Billy:

I don't know of a hard and fast benchmark comparison between VSAM and
DB2. Unless someone comes up with the numbers, I would suggest a simple
benchmark for your installation's typically sized files. This is hard
because of the variables involved, but the fairest apples to apples
comparison might be a single table application in DB2 compared to a one
file VSAM application, with the LRL being the same as the row size. Do
sequential reads, sequential updates, random reads, and random updates.
Larger LRLs would be more fair to VSAM. I am not a DBA, but as I recall,
DB2 manages data by pages (4K) and probably has additional I/O batching
capabilities; therefore, a LRL of less than 4K would be a handicap for
VSAM.
There are many variables which can affect this comparison. The
sequential operations will probably lead to the most valid comparisons
since DB2's buffer pools are probably much larger than most installation's
typical LSRPOOLs. For VSAM, ensure the CICS sysprog uses an adequate
number of index buffers--this is something which is still sometimes missed
(CICS split the data and the index buffers a number of years ago; shared
buffers don't perform anywhere nearly as well as split buffers because the
data buffers tend to push the index buffers out because of their much lower
hit rates).
As far as DB2 possibly seeming significantly faster than VSAM, I
agree with that. However, the CPU consumption of an equivalent, properly
designed VSAM application, will still be lower due to less overhead, since
DB2 uses VSAM under the hood anyway. The big performance limitation with
VSAM is of course the "hardening" of the physical design of the data into
the application. It is easy for a growing VSAM application to become I/O
bound and difficult to "fix" this situation by splitting files and
modifying/testing programs. Also, DB2 can more easily exploit multiple CPU
engines for that really large application.
Finally, don't forget the ability to easily and flexibly query a DB2
database.

Joe Fatula

----- Forwarded by Joseph Fatula/PHEAA on 01/07/05 01:10 PM -----

"Dingman, Randy"
<[login to unmask email]
HIGAN.COM> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] Any comparison
Read/write SAM file vs DB2 ?

01/07/05 12:39 PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-L.ORG>






While it may take more resources to perform DB2 selects or inserts, it is
somewhat like comparing apples to oranges. In terms of speed realized by
many applications, DB2 may appear to be significantly faster due to it's
many asynchronous capabilities.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Billy Larsen
Sent: Friday, January 07, 2005 11:05 AM
To: [login to unmask email]
Subject: [DB2-L] Any comparison Read/write SAM file vs DB2 ?


Dear all,

We all know that read/write are much more faster then select/insert. Does
any one has some measurements on this ? (SAM or VSAM )

I always tell my developers about this , but would be interested to know
what is the ration.

thanks

----------------------------------------------------------------------------

-----
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

Joel Goldstein

Re: Fw: [DB2-L] Any comparison Read/write SAM file vs DB2 ?
(in response to Joseph Fatula)
A "same fruit family" comparison is difficult, since you're trying to
compare file access itself.
You still need an application to perform the accesses.

Now, an I/O itself for the Vsam part, to access one block will be the same,
but there are many many variables
that make benchmarks difficult..... and part of this is repeatability. If
you can't run the same job, with the same elapsed
and cpu times, then you don't have a comparison. You need a dedicated
machine for accuracy - unless you're just aiming for
a ballpark number. Ballparks vary depending on the wind and weather.....

DB2 will be much faster for sequential access because of 32 page prefetch.

I think your only real comparison point will be for very random I/O, where
the data is NEVER found in a buffer.

Regards,
Joel

----- Original Message -----
From: <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Friday, January 07, 2005 1:31 PM
Subject: [DB2-L] Fw: [DB2-L] Any comparison Read/write SAM file vs DB2 ?


> Billy:
>
> I don't know of a hard and fast benchmark comparison between VSAM and
> DB2. Unless someone comes up with the numbers, I would suggest a simple
> benchmark for your installation's typically sized files. This is hard
> because of the variables involved, but the fairest apples to apples
> comparison might be a single table application in DB2 compared to a one
> file VSAM application, with the LRL being the same as the row size. Do
> sequential reads, sequential updates, random reads, and random updates.
> Larger LRLs would be more fair to VSAM. I am not a DBA, but as I recall,
> DB2 manages data by pages (4K) and probably has additional I/O batching
> capabilities; therefore, a LRL of less than 4K would be a handicap for
> VSAM.
> There are many variables which can affect this comparison. The
> sequential operations will probably lead to the most valid comparisons
> since DB2's buffer pools are probably much larger than most installation's
> typical LSRPOOLs. For VSAM, ensure the CICS sysprog uses an adequate
> number of index buffers--this is something which is still sometimes missed
> (CICS split the data and the index buffers a number of years ago; shared
> buffers don't perform anywhere nearly as well as split buffers because the
> data buffers tend to push the index buffers out because of their much
> lower
> hit rates).
> As far as DB2 possibly seeming significantly faster than VSAM, I
> agree with that. However, the CPU consumption of an equivalent, properly
> designed VSAM application, will still be lower due to less overhead, since
> DB2 uses VSAM under the hood anyway. The big performance limitation with
> VSAM is of course the "hardening" of the physical design of the data into
> the application. It is easy for a growing VSAM application to become I/O
> bound and difficult to "fix" this situation by splitting files and
> modifying/testing programs. Also, DB2 can more easily exploit multiple
> CPU
> engines for that really large application.
> Finally, don't forget the ability to easily and flexibly query a DB2
> database.
>
> Joe Fatula
>
> ----- Forwarded by Joseph Fatula/PHEAA on 01/07/05 01:10 PM -----
>
> "Dingman, Randy"
> <[login to unmask email]
> HIGAN.COM> To
> Sent by: DB2 Data [login to unmask email]
> Base Discussion cc
> List
> <[login to unmask email] Subject
> ORG> Re: [DB2-L] Any comparison
> Read/write SAM file vs DB2 ?
>
> 01/07/05 12:39 PM
>
>
> Please respond to
> DB2 Database
> Discussion list
> at IDUG
> <[login to unmask email]
> 2-L.ORG>
>
>
>
>
>
>
> While it may take more resources to perform DB2 selects or inserts, it is
> somewhat like comparing apples to oranges. In terms of speed realized by
> many applications, DB2 may appear to be significantly faster due to it's
> many asynchronous capabilities.
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> Behalf Of Billy Larsen
> Sent: Friday, January 07, 2005 11:05 AM
> To: [login to unmask email]
> Subject: [DB2-L] Any comparison Read/write SAM file vs DB2 ?
>
>
> Dear all,
>
> We all know that read/write are much more faster then select/insert. Does
> any one has some measurements on this ? (SAM or VSAM )
>
> I always tell my developers about this , but would be interested to know
> what is the ration.
>
> thanks
>
> ----------------------------------------------------------------------------
>
> -----
> 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
>

---------------------------------------------------------------------------------
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