[V7 z/OS 1.7] CPU overhead -305 versus null indicator

Adam Baldwin

[V7 z/OS 1.7] CPU overhead -305 versus null indicator
Happy New Year.

Does anyone have a comparison of the relative CPU costs of processing a -
305 sqlcode against coding and processing a null indicator? We have a CPU
hungry SUM which is coded without a null indicator. Although we are going to
make index changes to support the query I would like to know how DB2
returing the -305 shapes up against populating a null indicator. Any thoughts?

Thanks in advance.

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: [V7 z/OS 1.7] CPU overhead -305 versus null indicator
(in response to Adam Baldwin)
Adam,
I would guess from the stated information that the SQLCODE305 is not the
problem CPU time wise. Unless there are lots of invald values you are better
of using DB2s built in NULL support or eat the -305s in the SUM function as
oposed to additional testing in the SQL via additional WHERE conditions

SUM is not expensive, nor is discarding a few rows because they contain
invalid values. What tends to be expensive includes

A) RESULT set processing The larger the number of rows to be processed or
the more unusual the selection process the more CPU. You seem to be aware
of this given your reference to adjusting indexes

B) Decompression (Are the objects compressed)

C) ENCODING does the character set for the table(s) mach each other and
the encoding for the plan / package?

D) Column types ... Are the Columns being summed
all data types that support arithmatic like INT DEC FLOAT etc
All the same data types SUM(A+B) are A and B the same type?

E) are the Columns defined with NOT NULL? This is a bit confusing if NULLs
are allowed then SUM should automaticlly exclude them as there is already a
null indicator that is stored with the column on for each row. If NOT NULL is
specified I wonder how the invalid valules got introduced ... was the Table/
Column ALTERED after creation? As item D suggests are you CASTING a non
numeric field?


Regards
Avram Friedman

On Fri, 4 Jan 2008 10:12:06 +0000, Adam Baldwin
<[login to unmask email]> wrote:

>Happy New Year.
>
>Does anyone have a comparison of the relative CPU costs of processing a -
>305 sqlcode against coding and processing a null indicator? We have a CPU
>hungry SUM which is coded without a null indicator. Although we are going to
>make index changes to support the query I would like to know how DB2
>returing the -305 shapes up against populating a null indicator. Any thoughts?
>
>Thanks in advance.
>
>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

Kate Kavanaugh

Re: [V7 z/OS 1.7] CPU overhead -305 versus null indicator
(in response to Avram Friedman)
The only time that a SUM will be NULL is if there are no rows that qualify for
the result set.

You could use the COALESCE function with the SUM to assign the value that
you want to use if the SUM should be NULL: COALESCE(SUM(xxx),0). This
way you only need to deal with the value of the SUM instead of both the SUM
and the NULL Indicator. You can use any value in the COALESCE if you could
have rows that meet the WHERE clause, but still give you a SUM of 0.

Kate Kavanaugh

On Fri, 4 Jan 2008 17:24:34 +0000, Avram Friedman <[login to unmask email]
SITES.COM> wrote:

>Adam,
>I would guess from the stated information that the SQLCODE305 is not the
>problem CPU time wise. Unless there are lots of invald values you are better
>of using DB2s built in NULL support or eat the -305s in the SUM function as
>oposed to additional testing in the SQL via additional WHERE conditions
>
>SUM is not expensive, nor is discarding a few rows because they contain
>invalid values. What tends to be expensive includes
>
>A) RESULT set processing The larger the number of rows to be processed or
>the more unusual the selection process the more CPU. You seem to be aware
>of this given your reference to adjusting indexes
>
>B) Decompression (Are the objects compressed)
>
>C) ENCODING does the character set for the table(s) mach each other and
>the encoding for the plan / package?
>
>D) Column types ... Are the Columns being summed
> all data types that support arithmatic like INT DEC FLOAT etc
> All the same data types SUM(A+B) are A and B the same type?
>
>E) are the Columns defined with NOT NULL? This is a bit confusing if NULLs
>are allowed then SUM should automaticlly exclude them as there is already a
>null indicator that is stored with the column on for each row. If NOT NULL is
>specified I wonder how the invalid valules got introduced ... was the Table/
>Column ALTERED after creation? As item D suggests are you CASTING a non
>numeric field?
>
>
>Regards
>Avram Friedman
>
>On Fri, 4 Jan 2008 10:12:06 +0000, Adam Baldwin
><[login to unmask email]> wrote:
>
>>Happy New Year.
>>
>>Does anyone have a comparison of the relative CPU costs of processing a -
>>305 sqlcode against coding and processing a null indicator? We have a CPU
>>hungry SUM which is coded without a null indicator. Although we are going
to
>>make index changes to support the query I would like to know how DB2
>>returing the -305 shapes up against populating a null indicator. Any
thoughts?
>>
>>Thanks in advance.
>>
>>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