DFSORT versus ORDER BY

Jose Antonio

DFSORT versus ORDER BY
Hi folks!


I would like to find any piece of information related to DFSORT versus ORDER BY under DB2. Does anyone know if there is any redbook
or article?!

Thanksss!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
José

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Adam Baldwin

Re: DFSORT versus ORDER BY
(in response to Jose Antonio)
Jose, as usual....it depends! An external sort can often out perform an order by if the result set is large. I presume that you're referring to an order by that requires a sort - an order by supported by an appropiate index can actually avoid a sort. Some years ago Chuck Hoover wrote a paper on the DB2 Sort and, if I remember correctly, he included a comparison with DFSORT. I'm not sure how/where you can find a copy of his paper. Maybe another lister has a soft copy?

With order by, you want to make sure that you're sorting the minimum possible. Sorting redundant columns, especially if they are in the sort key is an avoidable overhead. For example in a statement like:
SELECT COL1, COL2, COL3, COL4
FROM MYTABLE
WHERE COL1 = :hv
ORDER BY COL1, COL2

COL1 is redundant - it's a known value but it is being selected and is in both the sort key and the sorted data.

For unloads of large amounts of data an external sort may well out perform an order by. Also, by externalizing the sort you can alleviate possible problems with temporary workfiles in DB2 - I've seen large batch order by causing problems with online processes.

Does anyone have a copy of the Chuck Hoover paper?

Cheers, Adam

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Michael Turner

Re: DFSORT versus ORDER BY
(in response to Adam Baldwin)
I have a copy of Chuck's paper from Share 2002 in PDF format. Contact me
off-list if you want a copy.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk
Michael Turner Ltd. Registered in England & Wales No. 1433722. Registered
Office: 131 King Street, Knutsford, Cheshire, WA16 6EJ. VAT No. GB 338 4159
44.
----- Original Message -----
From: "Adam Baldwin" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Wednesday, November 10, 2010 10:53 AM
Subject: Re: [DB2-L] DFSORT versus ORDER BY


Jose, as usual....it depends! An external sort can often out perform an
order by if the result set is large. I presume that you're referring to an
order by that requires a sort - an order by supported by an appropiate index
can actually avoid a sort. Some years ago Chuck Hoover wrote a paper on the
DB2 Sort and, if I remember correctly, he included a comparison with DFSORT.
I'm not sure how/where you can find a copy of his paper. Maybe another
lister has a soft copy?

With order by, you want to make sure that you're sorting the minimum
possible. Sorting redundant columns, especially if they are in the sort key
is an avoidable overhead. For example in a statement like:
SELECT COL1, COL2, COL3, COL4
FROM MYTABLE
WHERE COL1 = :hv
ORDER BY COL1, COL2

COL1 is redundant - it's a known value but it is being selected and is in
both the sort key and the sorted data.

For unloads of large amounts of data an external sort may well out perform
an order by. Also, by externalizing the sort you can alleviate possible
problems with temporary workfiles in DB2 - I've seen large batch order by
causing problems with online processes.

Does anyone have a copy of the Chuck Hoover paper?

Cheers, Adam

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2
Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Max Scarpa

Re: DFSORT versus ORDER BY
(in response to Michael Turner)
Yes there was a point where it was more convenient to use DFSORT with
respect RSD SORT. The number of rows where RDS SORT is good is low so for
many rows DFSORT is better (there's always a winner in a DFSORTed file)
but I suspect it depends even on row length.

I did some test many years ago and a sorted file as input (mainly for
LOAD) in most of utilities had cpu savings but consider now DFSORT is even
more better and DB2 changed (I did tests in Db2 V 6 I think). I'm
planning to do more tests in next future, if I'll have access to a
sandbox.

For now I recommend:

- Mr. Boxwell's excellent paper about DFSORT
- B14 - All you Ever Wanted to Know about DB2 and DFSORT by D.
Goethals IDUG EMEA 2010.
- Recommendations for Tuning Large DFSORT Tasks by
Trevor Kingsbury (a little old, anyway).

I'm sure I've more recent papers in my Db2 directory but not here :-(

Max 'See you in Prague ?' Scarpa






_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jose Antonio

Re: DFSORT versus ORDER BY
(in response to Max Scarpa)
Thank you very much!

I attend SpDug yesterday, it was impossible for me going to IDUG EMEA 2010, next time may be!

Regard
José




________________________________
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Max Scarpa
Enviado el: lunes, 15 de noviembre de 2010 10:53
Para: [login to unmask email]
Asunto: Re: [DB2-L] DFSORT versus ORDER BY

Yes there was a point where it was more convenient to use DFSORT with respect RSD SORT. The number of rows where RDS SORT is good is low so for many rows DFSORT is better (there's always a winner in a DFSORTed file) but I suspect it depends even on row length.

I did some test many years ago and a sorted file as input (mainly for LOAD) in most of utilities had cpu savings but consider now DFSORT is even more better and DB2 changed (I did tests in Db2 V 6 I think). I'm planning to do more tests in next future, if I'll have access to a sandbox.

For now I recommend:

- Mr. Boxwell's excellent paper about DFSORT
- B14 - All you Ever Wanted to Know about DB2 and DFSORT by D. Goethals IDUG EMEA 2010.
- Recommendations for Tuning Large DFSORT Tasks by Trevor Kingsbury (a little old, anyway).

I'm sure I've more recent papers in my Db2 directory but not here :-(

Max 'See you in Prague ?' Scarpa





________________________________

[Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org/db2-conferences-list.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Max Scarpa

Re: DFSORT versus ORDER BY
(in response to Jose Antonio)
Greets to SpDUG President Manuel Gomez Burriel, a dear friend !

Max Scarpa



IDUG DB2-L <[login to unmask email]> wrote on 17/11/2010 11.51.26:

> From: Jose Antonio <[login to unmask email]>
> To: [login to unmask email]
> Date: 17/11/2010 11.53
> Subject: Re: [DB2-L] DFSORT versus ORDER BY
> Sent by: IDUG DB2-L <[login to unmask email]>
>
> Thank you very much!
>
> I attend SpDug yesterday, it was impossible for me going to IDUG
> EMEA 2010, next time may be!
>
> Regard
> José
>
>
>
>
>
>
> De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Max Scarpa
> Enviado el: lunes, 15 de noviembre de 2010 10:53
> Para: [login to unmask email]
> Asunto: Re: [DB2-L] DFSORT versus ORDER BY
>
> Yes there was a point where it was more convenient to use DFSORT
> with respect RSD SORT. The number of rows where RDS SORT is good is
> low so for many rows DFSORT is better (there's always a winner in a
> DFSORTed file) but I suspect it depends even on row length.
>
> I did some test many years ago and a sorted file as input (mainly
> for LOAD) in most of utilities had cpu savings but consider now
> DFSORT is even more better and DB2 changed (I did tests in Db2 V 6 I
> think). I'm planning to do more tests in next future, if I'll have
> access to a sandbox.
>
> For now I recommend:
>
> - Mr. Boxwell's excellent paper about DFSORT
> - B14 - All you Ever Wanted to Know about DB2 and DFSORT by
> D. Goethals IDUG EMEA 2010.
> - Recommendations for Tuning Large DFSORT Tasks
> by Trevor Kingsbury (a little old, anyway).
>
> I'm sure I've more recent papers in my Db2 directory but not here :-(
>
> Max 'See you in Prague ?' Scarpa
>
>
>
>

>
> [image removed]
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you are not already an IDUG member, please register here.
>
> Este correo electronico es confidencial. Si lo ha recibido por
> error, por favor
> contacte con el remitente y destruya su contenido. Toda la
> informacion relativa a
> la Proteccion de Datos de Caracter Personal, se encuentra a su
> disposicion en la
> pagina web www.cam.es , en el apartado Aviso legal
>
> This e-mail is confidential. If you have received this e-mail in
> error, please contact
> the sender and delete it from your system. All information about
Personal Data
> Protection can be found on the website www.cam.es , in the Legal
> Disclaimer section
>
>

> [image removed]
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you are not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Jose Antonio

Re: DFSORT versus ORDER BY
(in response to Max Scarpa)
and congratulations to Manuel!!!

Have you got Mr. Boxwell's paper?

José




________________________________
De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Max Scarpa
Enviado el: miércoles, 17 de noviembre de 2010 12:05
Para: [login to unmask email]
Asunto: Re: [DB2-L] DFSORT versus ORDER BY

Greets to SpDUG President Manuel Gomez Burriel, a dear friend !

Max Scarpa



IDUG DB2-L <[login to unmask email]> wrote on 17/11/2010 11.51.26:

> From: Jose Antonio <[login to unmask email]>
> To: [login to unmask email]
> Date: 17/11/2010 11.53
> Subject: Re: [DB2-L] DFSORT versus ORDER BY
> Sent by: IDUG DB2-L <[login to unmask email]>
>
> Thank you very much!
>
> I attend SpDug yesterday, it was impossible for me going to IDUG
> EMEA 2010, next time may be!
>
> Regard
> José
>
>
>
>
>
>
> De: IDUG DB2-L [mailto:[login to unmask email] En nombre de Max Scarpa
> Enviado el: lunes, 15 de noviembre de 2010 10:53
> Para: [login to unmask email]
> Asunto: Re: [DB2-L] DFSORT versus ORDER BY
>
> Yes there was a point where it was more convenient to use DFSORT
> with respect RSD SORT. The number of rows where RDS SORT is good is
> low so for many rows DFSORT is better (there's always a winner in a
> DFSORTed file) but I suspect it depends even on row length.
>
> I did some test many years ago and a sorted file as input (mainly
> for LOAD) in most of utilities had cpu savings but consider now
> DFSORT is even more better and DB2 changed (I did tests in Db2 V 6 I
> think). I'm planning to do more tests in next future, if I'll have
> access to a sandbox.
>
> For now I recommend:
>
> - Mr. Boxwell's excellent paper about DFSORT
> - B14 - All you Ever Wanted to Know about DB2 and DFSORT by
> D. Goethals IDUG EMEA 2010.
> - Recommendations for Tuning Large DFSORT Tasks
> by Trevor Kingsbury (a little old, anyway).
>
> I'm sure I've more recent papers in my Db2 directory but not here :-(
>
> Max 'See you in Prague ?' Scarpa
>
>
>
>

>
> [image removed]
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you are not already an IDUG member, please register here.
>
> Este correo electronico es confidencial. Si lo ha recibido por
> error, por favor
> contacte con el remitente y destruya su contenido. Toda la
> informacion relativa a
> la Proteccion de Datos de Caracter Personal, se encuentra a su
> disposicion en la
> pagina web www.cam.es , en el apartado Aviso legal
>
> This e-mail is confidential. If you have received this e-mail in
> error, please contact
> the sender and delete it from your system. All information about Personal Data
> Protection can be found on the website www.cam.es , in the Legal
> Disclaimer section
>
>

> [image removed]
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
> you are not already an IDUG member, please register here.
________________________________

[Independent, not-for-profit, User Run - the IDUG difference! ] < http://www.idug.org/db2-conferences-list.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

Este correo electronico es confidencial. Si lo ha recibido por error, por favor
contacte con el remitente y destruya su contenido. Toda la informacion relativa a
la Proteccion de Datos de Caracter Personal, se encuentra a su disposicion en la
pagina web www.cam.es , en el apartado Aviso legal

This e-mail is confidential. If you have received this e-mail in error, please contact
the sender and delete it from your system. All information about Personal Data
Protection can be found on the website www.cam.es , in the Legal Disclaimer section

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv