Unload/Load vs. DSN1COPY?

Robert Galambos

Unload/Load vs. DSN1COPY?
a quick question. While I 'believe' that DSN1COPY is far better in 'cloning' a DB

the question is does anyone have a idea of the percentage difference between the two techniques?

I understand there are a lot of variables but a 'gut feel' (mine is 40-50% difference)


thanks


Robert Galambos CIPP/C

Compuware Senior Technical Specialist
IBM Certified Solutions Expert -
DB2 UDB for OS/390 Database Administration
Certified Information Privacy Professional/Canada
[login to unmask email]


Tel: +1 905 886 7000
Toll Free: +1 800 263 7189
Fax: +1 905 886 7023
Quebec: +1 877-281-1888

Compuware Canada<file:///C:/Canada.gif>

Service is our best product
Les renseignements contenus dans le présent message électronique sont confidentiels et concernent exclusivement le(s) destinataire(s) désigné(s). Il est strictement interdit de distribuer ou de copier ce message. Si vous avez reçu ce message par erreur, veuillez répondre par courriel à l'expéditeur et effacer ou détruire toutes les copies du présent message.
The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: Unload/Load vs. DSN1COPY?
(in response to Robert Galambos)
Robert,
In my view this is almost the same issue as the one discussed in IBM Utilities
Vs BMC.

I think your estimate for savings with DSN1COPY is low

If we make the assumptions that
the unload comes from the live active table and not an image copy
The DSN1COPY vsam DDs have AMP='BUFND=value' where value is between
2* CIs per track + 1 and CIs per Cyl +1
Then

Cost of locking, logging, will double the DB2 VSAM expense or increase total
CPU by about 1/4 (assumption 1/2 DB2 VSAM processing 1/2 flat file
processing so if DB2 is twice as expensive as vsam the total net savings is
1/4th)

DB2 costs will increase depending on Row size and number of columns ...
smaller rows = increased CPU
greater number of columns = increased CPU
In both cases one needs to loop through SQL code more times

Compression would be a cost for the live DB2 option

The odds are DSN1COPY would require both less VSAM I/O and less copy file
I/O if the JCL is correctly coded but mesurements are very difficult as the
cost of prefetch I/O and the cost of defered writes in the DB2 world are not
mesured by application accounting.

It is useful to point out that DSN1COPY can totally eliminate the middle man
flat file processing in most configurations as it supports VSAM to VSAM copies.
With DSN1COPY going VSAM to VSAM and optomized JCL I would guess that
less than 1/10 the CPU and elapsed time would be needed compared to
unload / reload.

Hope you dont mind the 'geeky' reply
Avram Friedman

On Wed, 19 Dec 2007 20:56:34 -0500, Galambos, Robert
<[login to unmask email]> wrote:

>a quick question. While I 'believe' that DSN1COPY is far better in 'cloning' a DB
>
>the question is does anyone have a idea of the percentage difference
between the two techniques?
>
>I understand there are a lot of variables but a 'gut feel' (mine is 40-50%
difference)
>
>
>thanks
>
>
>Robert Galambos CIPP/C
>
>Compuware Senior Technical Specialist
>IBM Certified Solutions Expert -
>DB2 UDB for OS/390 Database Administration
>Certified Information Privacy Professional/Canada
>[login to unmask email]
>
>
>Tel: +1 905 886 7000
>Toll Free: +1 800 263 7189
>Fax: +1 905 886 7023
>Quebec: +1 877-281-1888
>
>Compuware Canada<file:///C:/Canada.gif>
>
>Service is our best product
> Les renseignements contenus dans le pr?sent message ?lectronique sont
confidentiels et concernent exclusivement le(s) destinataire(s) d?sign?(s). Il
est strictement interdit de distribuer ou de copier ce message. Si vous avez
re?u ce message par erreur, veuillez r?pondre par courriel ? l'exp?diteur et
effacer ou d?truire toutes les copies du pr?sent message.
>The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately and
then destroy it.
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: Unload/Load vs. DSN1COPY?
(in response to Avram Friedman)
So there's all the "pluses".....

Of course the big advantage of the unload/reload process is that it's
independent of the structures at either end of the copy process

The more ways there are for source and target to be different, then the
harder a DSN1COPY strategy is to manage

It used to be a manageable pain (have you ALTER ADDed any columns
with/without subsequent reorgs?) but now you also have to worry about on
line schema change history (another reason for doing the "advisory"
reorg), rebalancing, rotating etc etc

And it'll only get worse from here

So, make sure you do benchmark the options but also factor in
manageability


Phil Grainger
CA
Product Management Director
Phone: +44 (0)1753 577 733
Mobile: +44 (0)7970 125 752
eMail: [login to unmask email]

Ditton Park
Riding Court Road
Datchet
Slough
SL3 9LL

CA plc a company registered in England and Wales under company
registration number 1282495 with its registered office at the address
set out above. VAT number 697904179.



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Avram Friedman
Sent: 20 December 2007 02:33
To: [login to unmask email]
Subject: Re: [DB2-L] Unload/Load vs. DSN1COPY?

Robert,
In my view this is almost the same issue as the one discussed in IBM
Utilities Vs BMC.

I think your estimate for savings with DSN1COPY is low

If we make the assumptions that
the unload comes from the live active table and not an image copy The
DSN1COPY vsam DDs have AMP='BUFND=value' where value is between
2* CIs per track + 1 and CIs per Cyl +1
Then

Cost of locking, logging, will double the DB2 VSAM expense or increase
total CPU by about 1/4 (assumption 1/2 DB2 VSAM processing 1/2 flat file
processing so if DB2 is twice as expensive as vsam the total net savings
is
1/4th)

DB2 costs will increase depending on Row size and number of columns ...
smaller rows = increased CPU
greater number of columns = increased CPU In both cases one needs to
loop through SQL code more times

Compression would be a cost for the live DB2 option

The odds are DSN1COPY would require both less VSAM I/O and less copy
file I/O if the JCL is correctly coded but mesurements are very
difficult as the cost of prefetch I/O and the cost of defered writes in
the DB2 world are not mesured by application accounting.

It is useful to point out that DSN1COPY can totally eliminate the middle
man flat file processing in most configurations as it supports VSAM to
VSAM copies.
With DSN1COPY going VSAM to VSAM and optomized JCL I would guess that
less than 1/10 the CPU and elapsed time would be needed compared to
unload / reload.

Hope you dont mind the 'geeky' reply
Avram Friedman

On Wed, 19 Dec 2007 20:56:34 -0500, Galambos, Robert
<[login to unmask email]> wrote:

>a quick question. While I 'believe' that DSN1COPY is far better in
>'cloning' a DB
>
>the question is does anyone have a idea of the percentage difference
between the two techniques?
>
>I understand there are a lot of variables but a 'gut feel' (mine is
>40-50%
difference)
>
>
>thanks
>
>
>Robert Galambos CIPP/C
>
>Compuware Senior Technical Specialist
>IBM Certified Solutions Expert -
>DB2 UDB for OS/390 Database Administration Certified Information
>Privacy Professional/Canada [login to unmask email]
>
>
>Tel: +1 905 886 7000
>Toll Free: +1 800 263 7189
>Fax: +1 905 886 7023
>Quebec: +1 877-281-1888
>
>Compuware Canada<file:///C:/Canada.gif>
>
>Service is our best product
> Les renseignements contenus dans le pr sent message lectronique sont
confidentiels et concernent exclusivement le(s) destinataire(s) d sign
(s). Il est strictement interdit de distribuer ou de copier ce message.
Si vous avez re u ce message par erreur, veuillez r pondre par courriel
l'exp diteur et effacer ou d truire toutes les copies du pr sent
message.
>The contents of this e-mail are intended for the named addressee only.
>It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or
disclose it to anyone else. If you received it in error please notify us
immediately and then destroy it.
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L

>list
archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms
>

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services at http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms