Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

Roy Reynolds

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

'We have a lot of SELECT COUNT(*) FROM table;' statements.  Any thoughts people would like to offer on the option of replaceing with a RUNSTATS TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR;  These programs only want the row count for an entire table.  We enforce one table per tablespace.  Using DB2 V9 NFM.  I don't need all the column stats, etc.  Just the row count.  Suggestions on the fastest, least DB2 intensive way to do this?  I think I now, but sometimes I'm surprised (and humbled) by what I don't know.  Thanks,  Roy

 

Roy Boxwell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Reynolds)
if the single table tablespace rule is true then use the RTS!

SELECT SUM(COALESCE(TOTALROWS , 0 ))
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = :HOST1
AND NAME = :HOST2
GROUP BY DBNAME , NAME
WITH UR
;

Sums up the partitions as well as handles the NULL case.


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Roy Reynolds <[login to unmask email]>
An: [login to unmask email]
Datum: 28.07.2011 01:54
Betreff: [DB2-L] - Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



'We have a lot of SELECT COUNT(*) FROM table;' statements. Any thoughts
people would like to offer on the option of replaceing with a RUNSTATS
TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM
SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR; These programs
only want the row count for an entire table. We enforce one table per
tablespace. Using DB2 V9 NFM. I don't need all the column stats, etc.
Just the row count. Suggestions on the fastest, least DB2 intensive way
to do this? I think I now, but sometimes I'm surprised (and humbled) by
what I don't know. Thanks, Roy



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Mick Graley

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Reynolds)
IIRC the optimizer can chose to scan a unique index to count the RIDs
rather than scan the rows in the table space. If that is an index on a
small primary key then that is usually much smaller than the table space
and so much quicker. In that case RUNSTATS will do much more work than the
SELECT COUNT(*).
However, if absolute accuracy is not a major concern, if you already have
fairly up to date stats (or real time stats) then just query them.

Cheers,

Mick.





From: Roy Reynolds <[login to unmask email]>

To: [login to unmask email]

Date: 28/07/2011 00:52

Subject: [DB2-L] - Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?






'We have a lot of SELECT COUNT(*) FROM table;' statements. Any thoughts
people would like to offer on the option of replaceing with a RUNSTATS
TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM
SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR; These programs
only want the row count for an entire table. We enforce one table per
tablespace. Using DB2 V9 NFM. I don't need all the column stats, etc.
Just the row count. Suggestions on the fastest, least DB2 intensive way to
do this? I think I now, but sometimes I'm surprised (and humbled) by what
I don't know. Thanks, Roy






Site Links: View post online View mailing list online Send new post via
email Unsubscribe from this mailing list Manage your subscription Use
of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


David Bretz

RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Boxwell)

I found this solution interesting, particularly in light of the fact that the original post asked for the least DB2 intensive way to find the number of rows.

I am no DB2 expert by any means, but I used SPUFI to conduct an unscientific test by executing a SELECT COUNT(*) and the query from the SYSTABLESPACESTATS. I received a sub-second response from the SELECT COUNT and had to way almost 3 seconds for a response from the SYSTABLESPACESTATS query. It would appear that the SYSTABLESPACESTATS query is more DB2 intensive.

I am curious whether this outcome would be different if the queries were run from within a program? 

Thanks

DB



In Reply to Roy Boxwell:

if the single table tablespace rule is true then use the RTS!

SELECT SUM(COALESCE(TOTALROWS , 0 ))
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = :HOST1
AND NAME = :HOST2
GROUP BY DBNAME , NAME
WITH UR
;

Sums up the partitions as well as handles the NULL case.


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Roy Reynolds <[login to unmask email]>
An: [login to unmask email]
Datum: 28.07.2011 01:54
Betreff: [DB2-L] - Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



'We have a lot of SELECT COUNT(*) FROM table;' statements. Any thoughts
people would like to offer on the option of replaceing with a RUNSTATS
TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM
SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR; These programs
only want the row count for an entire table. We enforce one table per
tablespace. Using DB2 V9 NFM. I don't need all the column stats, etc.
Just the row count. Suggestions on the fastest, least DB2 intensive way
to do this? I think I now, but sometimes I'm surprised (and humbled) by
what I don't know. Thanks, Roy



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Roy Boxwell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to David Bretz)
I guess you have never reorged and runstated the RTS then!! It is *way*
faster than counting anything!!!

Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: David Bretz <[login to unmask email]>
An: [login to unmask email]
Datum: 28.07.2011 12:31
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



I found this solution interesting, particularly in light of the fact that
the original post asked for the least DB2 intensive way to find the number
of rows.
I am no DB2 expert by any means, but I used SPUFI to conduct an
unscientific test by executing a SELECT COUNT(*) and the query from the
SYSTABLESPACESTATS. I received a sub-second response from the SELECT COUNT
and had to way almost 3 seconds for a response from the SYSTABLESPACESTATS
query. It would appear that the SYSTABLESPACESTATS query is more DB2
intensive.
I am curious whether this outcome would be different if the queries were
run from within a program?
Thanks
DB


In Reply to Roy Boxwell:
if the single table tablespace rule is true then use the RTS!

SELECT SUM(COALESCE(TOTALROWS , 0 ))
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = :HOST1
AND NAME = :HOST2
GROUP BY DBNAME , NAME
WITH UR
;

Sums up the partitions as well as handles the NULL case.


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Roy Reynolds <[login to unmask email]>
An: [login to unmask email]
Datum: 28.07.2011 01:54
Betreff: [DB2-L] - Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



'We have a lot of SELECT COUNT(*) FROM table;' statements. Any thoughts
people would like to offer on the option of replaceing with a RUNSTATS
TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM
SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR; These programs
only want the row count for an entire table. We enforce one table per
tablespace. Using DB2 V9 NFM. I don't need all the column stats, etc.
Just the row count. Suggestions on the fastest, least DB2 intensive way
to do this? I think I now, but sometimes I'm surprised (and humbled) by
what I don't know. Thanks, Roy



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Phil Grainger

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Reynolds)
Hi Roy (Roy Reynolds that is)

You've had a few suggestions on the best way to do this, but I still find myself asking "Why?"

As in "Why do people want to count the rows in the table?" - especially when you say "We have a lot of ......."

As Roy B has pointed out, using the RTS tables will certainly be faster than doing the actual counts - but still I'd like to know WHY

Thanks
Phil Grainger
Cogito Ltd.
[login to unmask email]<mailto:[login to unmask email]>
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk<blocked::http://www.cogito.co.uk>

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia<http://www.idug.org/db2-australasia-conference/index.html>, 14-16 September, Melbourne, Australia
EMEA<http://www.idug.org/emea>, 14-18 November, Prague Czech Republic

From: Roy Reynolds [mailto:[login to unmask email]
Sent: 28 July 2011 00:54
To: [login to unmask email]
Subject: [DB2-L] - Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?


'We have a lot of SELECT COUNT(*) FROM table;' statements. Any thoughts people would like to offer on the option of replaceing with a RUNSTATS TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR; These programs only want the row count for an entire table. We enforce one table per tablespace. Using DB2 V9 NFM. I don't need all the column stats, etc. Just the row count. Suggestions on the fastest, least DB2 intensive way to do this? I think I now, but sometimes I'm surprised (and humbled) by what I don't know. Thanks, Roy



-----End Original Message-----

Mauro Moschelli

R: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Boxwell)
Roy (B.), that is generally true but the query you provided does a tablespace scan on SYSIBM.TABLESPACESTATS unless you created a new index on the table. The sample provided by IBM creates only an index on DBID/PSID/PARTITION (at least up to V8...), and I couldn't find on the manual the IBM position on creating additional user indexes on it.

In all my automated processes I always join TABLESPACESTATS with catalog tables in order to get a matching index access on the RTS table.

Said that, if the index used to count Is smaller than the RTS tablespace itself then the count(*) MAY be better, but I agree with you that reading catalog or RTS is generally better than counting. And I agree even more with Phil about the reason WHY you (Roy R.) need the number of rows of the table so often.

HTH
Mauro Moschelli

Intesa Sanpaolo Group Services

IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS

Da: Roy Boxwell [mailto:[login to unmask email]
Inviato: giovedì 28 luglio 2011 12.33
A: [login to unmask email]
Oggetto: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

I guess you have never reorged and runstated the RTS then!! It is *way* faster than counting anything!!!

Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.dehttp://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: David Bretz <[login to unmask email]<mailto:[login to unmask email]>>
An: [login to unmask email]<mailto:[login to unmask email]>
Datum: 28.07.2011 12:31
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
-----End Original Message-----

Prima di stampare, pensa all'ambiente ** Think about the environment before printing

________________________________
Il presente messaggio, inclusi gli eventuali allegati, ha natura aziendale e potrebbe contenere informazioni confidenziali e/o riservate. Chiunque lo ricevesse per errore, è pregato di avvisare tempestivamente il mittente e di cancellarlo.
E' strettamente vietata qualsiasi forma di utilizzo, riproduzione o diffusione non autorizzata del contenuto di questo messaggio o di parte di esso.
Pur essendo state assunte le dovute precauzioni per ridurre al minimo il rischio di trasmissione di virus, si suggerisce di effettuare gli opportuni controlli sui documenti allegati al presente messaggio. Non si assume alcuna responsabilità per eventuali danni o perdite derivanti dalla presenza di virus.

Per lo svolgimento delle attività di investimento nel Regno Unito, la società è autorizzata da Banca d'Italia ed è soggetta alla vigilanza limitata della Financial Services Authority. Maggiori informazioni in merito ai poteri di vigilanza della Financial Services Authority sono a disposizione previa richiesta..

Nel Regno Unito Intesa Sanpaolo S.p.A. opera attraverso la filiale di Londra, sita in 90 Queen Street, London EC4N 1SA, registrata in Inghilterra & Galles sotto No.FC016201, Branch No.BR000036

***

________________________________
This email (including any attachment) is a corporate message and may contain confidential and/or privileged and/or proprietary information. If you have received this email in error, please notify the sender immediately, do not use or share it and destroy this email. Any unauthorised use, copying or disclosure of the material in this email or of parts hereof (including reliance thereon) is strictly forbidden.
We have taken precautions to minimize the risk of transmitting software viruses but nevertheless advise you to carry out your own virus checks on any attachment of this message. We accept no liability for loss or damage caused by software viruses.

For the conduct of investment business in the UK, the Company is authorised by Banca d'Italia and subject to limited regulation in the UK by the Financial Services Authority. Details about the extent of our regulation by the Financial Services Authority are available from us on request.

In the UK Intesa Sanpaolo S.p.A. operates through its London Branch, located at 90 Queen Street, London EC4N 1SA. Registered in England & Wales under No.FC016201, Branch No.BR000036

Roy Boxwell

R: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Mauro Moschelli)
true indeed! Forgot that little detail.....I also join to the catalog to
then use the DBID and OBIDs to get better access.

Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Moschelli Mauro <[login to unmask email]>
An: "[login to unmask email]" <[login to unmask email]>
Datum: 28.07.2011 14:09
Betreff: [DB2-L] - R: RE: Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



Roy (B.), that is generally true but the query you provided does a
tablespace scan on SYSIBM.TABLESPACESTATS unless you created a new index
on the table. The sample provided by IBM creates only an index on
DBID/PSID/PARTITION (at least up to V8…), and I couldn’t find on the
manual the IBM position on creating additional user indexes on it.

In all my automated processes I always join TABLESPACESTATS with catalog
tables in order to get a matching index access on the RTS table.

Said that, if the index used to count Is smaller than the RTS tablespace
itself then the count(*) MAY be better, but I agree with you that reading
catalog or RTS is generally better than counting. And I agree even more
with Phil about the reason WHY you (Roy R.) need the number of rows of the
table so often.

HTH
Mauro Moschelli

Intesa Sanpaolo Group Services

IBM Certified Database Administrator - DB2 9 for z/OS
IBM Certified System Administrator - DB2 9 for z/OS

Da: Roy Boxwell [mailto:[login to unmask email]
Inviato: giovedì 28 luglio 2011 12.33
A: [login to unmask email]
Oggetto: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?

I guess you have never reorged and runstated the RTS then!! It is *way*
faster than counting anything!!!

Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: David Bretz <[login to unmask email]>
An: [login to unmask email]
Datum: 28.07.2011 12:31
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?




I found this solution interesting, particularly in light of the fact that
the original post asked for the least DB2 intensive way to find the number
of rows.
I am no DB2 expert by any means, but I used SPUFI to conduct an
unscientific test by executing a SELECT COUNT(*) and the query from the
SYSTABLESPACESTATS. I received a sub-second response from the SELECT COUNT
and had to way almost 3 seconds for a response from the SYSTABLESPACESTATS
query. It would appear that the SYSTABLESPACESTATS query is more DB2
intensive.
I am curious whether this outcome would be different if the queries were
run from within a program?
Thanks
DB


In Reply to Roy Boxwell:
if the single table tablespace rule is true then use the RTS!

SELECT SUM(COALESCE(TOTALROWS , 0 ))
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = :HOST1
AND NAME = :HOST2
GROUP BY DBNAME , NAME
WITH UR
;

Sums up the partitions as well as handles the NULL case.


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Roy Reynolds <[login to unmask email]>
An: [login to unmask email]
Datum: 28.07.2011 01:54
Betreff: [DB2-L] - Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



'We have a lot of SELECT COUNT(*) FROM table;' statements. Any thoughts
people would like to offer on the option of replaceing with a RUNSTATS
TABLESPACE dbname.tsname TABLE(ALL) followed by a SELECT CARDF FROM
SYSIBM.SYSTABLES WHERE NAME = :H AND CREATOR = :H WITH UR; These programs
only want the row count for an entire table. We enforce one table per
tablespace. Using DB2 V9 NFM. I don't need all the column stats, etc.
Just the row count. Suggestions on the fastest, least DB2 intensive way
to do this? I think I now, but sometimes I'm surprised (and humbled) by
what I don't know. Thanks, Roy



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


-----End Original Message-----

Prima di stampare, pensa all'ambiente ** Think about the environment
before printing

Il presente messaggio, inclusi gli eventuali allegati, ha natura aziendale
e potrebbe contenere informazioni confidenziali e/o riservate. Chiunque lo
ricevesse per errore, è pregato di avvisare tempestivamente il mittente e
di cancellarlo.
E’ strettamente vietata qualsiasi forma di utilizzo, riproduzione o
diffusione non autorizzata del contenuto di questo messaggio o di parte di
esso.
Pur essendo state assunte le dovute precauzioni per ridurre al minimo il
rischio di trasmissione di virus, si suggerisce di effettuare gli
opportuni controlli sui documenti allegati al presente messaggio. Non si
assume alcuna responsabilità per eventuali danni o perdite derivanti dalla
presenza di virus.

Per lo svolgimento delle attività di investimento nel Regno Unito, la
società è autorizzata da Banca d'Italia ed è soggetta alla vigilanza
limitata della Financial Services Authority. Maggiori informazioni in
merito ai poteri di vigilanza della Financial Services Authority sono a
disposizione previa richiesta..

Nel Regno Unito Intesa Sanpaolo S.p.A. opera attraverso la filiale di
Londra, sita in 90 Queen Street, London EC4N 1SA, registrata in
Inghilterra & Galles sotto No.FC016201, Branch No.BR000036

***

This email (including any attachment) is a corporate message and may
contain confidential and/or privileged and/or proprietary information. If
you have received this email in error, please notify the sender
immediately, do not use or share it and destroy this email. Any
unauthorised use, copying or disclosure of the material in this email or
of parts hereof (including reliance thereon) is strictly forbidden.
We have taken precautions to minimize the risk of transmitting software
viruses but nevertheless advise you to carry out your own virus checks on
any attachment of this message. We accept no liability for loss or damage
caused by software viruses.

For the conduct of investment business in the UK, the Company is
authorised by Banca d’Italia and subject to limited regulation in the UK
by the Financial Services Authority. Details about the extent of our
regulation by the Financial Services Authority are available from us on
request.

In the UK Intesa Sanpaolo S.p.A. operates through its London Branch,
located at 90 Queen Street, London EC4N 1SA. Registered in England & Wales
under No.FC016201, Branch No.BR000036


Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Roy Reynolds

RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Reynolds)

From the original poster: (Roy R) I originally asked 'why' this was being done, why so often, and who actually reviews the results. I'm still awaiting the reply.  I suspect this is code/logic from the 1960s/70s where the person bursting and decollating printed forms back then actually had to read and write these numbers on a control page to be sent to the Prod Control supervisor who would then forward it to an accountant somewhere.  Yes, it is being investigated to see if these counts can be 'retired'.  The scenario I mention is only one possibility I remember from working back in those decades.  Thanks for all your informative answers!! Cheers, Roy

FirstName LastName

RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Reynolds)

The answer as always with DBA's is, it depends.  If your statistics are up to date and not sampled then you may select card from syscat.tables where type in ('S','T') and if you want the schema and table name use select tabschema,tabname,card from syscat.tables where type in ('S','T') or do something similar with the SYSIBM catalog tables.  If your tables have billions and billions of rows, I would bet a lot of money that the select from the catalog views or tables will run much faster than the select count(*).  You'd probably have to use select count_big(*) if the number of rows is very large.  The select count(*) will be absolutely accurate at the time it is run.  The card or cardf column from the catalog view or table is only as accurate as the last time runstats was run.  The util_impact_priority of 100 can be used to help runstats run faster.  Sampling can be used to speed up runstats as well but then the card will reflect the sample number and not the actual cardinality of rows in the table so you could get a low answer.  You may be able to use the high speed unload utility to /dev/null on a UNIX server to get the output count of rows unloaded and I suspect this would execute much faster than a select count(*) or count_big(*) but you'd have to have db2hpu installed and know how to use that.  I suspect there are many other ways to get this same information that I am not aware of.  Since you sounded like you wanted some different alternatives, I thought I would brainstorm a few that come to mind.  Which one is faster really depends on the size of your tables.  As the size of your tables grows larger the answer may be different.  When you have to scale up to very large tables one solution that worked in a small environment may not in a large environment.

Adam Baldwin

RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Reynolds)

Hi Roy,
What I certainly wouldn't recommend is the very expensive count that one of my client's had. Their's was achieved via a cursor (declared with SELECT * FROM...without a WHERE) and a fetch of every row by means of a loop perform in Cobol with the program adding +1 to a counter for every row fetched. This little beauty escaped the Count Police but certainly had a high CPU cost.

Cheers, Adam

Roy Boxwell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Adam Baldwin)
That would cost the programmer who did that here a round of pizza and
drinks...on the other hand some little JAVA aps still do a SELECT COUNT(*)
FROM SYSIBM.SYSTABLES just to see if their connection is active....Someone
once said "No programming language has been invented where it is
impossible to write bad programs" and that was and still is very very
true!


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Adam Baldwin <[login to unmask email]>
An: [login to unmask email]
Datum: 29.07.2011 09:32
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



Hi Roy,
What I certainly wouldn't recommend is the very expensive count that one
of my client's had. Their's was achieved via a cursor (declared with
SELECT * FROM...without a WHERE) and a fetch of every row by means of a
loop perform in Cobol with the program adding +1 to a counter for every
row fetched. This little beauty escaped the Count Police but certainly had
a high CPU cost.

Cheers, Adam



Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Phil Grainger

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Boxwell)
I've also started coming across a worrying number (ie greater than zero!) occurrences of:

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1

If course the answer is ALWAYS 1 - but the statement, as Roy B says, is just there to "make sure DB2 is still listening"

If you have one of these, if you really can't bear to be without it, then change it to

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 WHERE 0 = 1

DB2 know this is an "always false" predicate so doesn't need to access ANY data to return +100

So, if you get +100 DB2 IS there, any other error and DB2 ISN'T :)

Oh, and this can save ridiculous amounts of CPU as these statements are typically executed millions of times per day

I know it's Friday - but this is NOT a fanciful fabrication!
Phil Grainger
Cogito Ltd.
[login to unmask email]<mailto:[login to unmask email]>
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk<blocked::http://www.cogito.co.uk>

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia<http://www.idug.org/db2-australasia-conference/index.html>, 14-16 September, Melbourne, Australia
EMEA<http://www.idug.org/emea>, 13-18 November, Prague Czech Republic

From: Roy Boxwell [mailto:[login to unmask email]
Sent: 29 July 2011 09:28
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

That would cost the programmer who did that here a round of pizza and drinks...on the other hand some little JAVA aps still do a SELECT COUNT(*) FROM SYSIBM.SYSTABLES just to see if their connection is active....Someone once said "No programming language has been invented where it is impossible to write bad programs" and that was and still is very very true!


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.dehttp://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Adam Baldwin <[login to unmask email]>
An: [login to unmask email]
Datum: 29.07.2011 09:32
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
________________________________



Hi Roy,
What I certainly wouldn't recommend is the very expensive count that one of my client's had. Their's was achieved via a cursor (declared with SELECT * FROM...without a WHERE) and a fetch of every row by means of a loop perform in Cobol with the program adding +1 to a counter for every row fetched. This little beauty escaped the Count Police but certainly had a high CPU cost.

Cheers, Adam



-----End Original Message-----

Mick Graley

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Phil Grainger)
Phil,
Would a
SET :Host-Var = Special Register
be even quicker?
No optimizer at all maybe?
Developers?

Anyway - I guess this is silly as you will get a proper SQL CODE back to
the application if DB2 isn't there when the application really wants to do
something!?
I guess I'm missing something! Pro-active monitoring? Do it through
automation not the application? i.e. DB2's ABENDed - help!
But the above question is still valid - is it quicker?

Cheers,

Mick.





From: Phil Grainger <[login to unmask email]>

To: "[login to unmask email]" <[login to unmask email]>

Date: 29/07/2011 11:46

Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?






I’ve also started coming across a worrying number (ie greater than zero!)
occurrences of:

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1

If course the answer is ALWAYS 1 – but the statement, as Roy B says, is
just there to “make sure DB2 is still listening”

If you have one of these, if you really can’t bear to be without it, then
change it to

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 WHERE 0 = 1

DB2 know this is an “always false” predicate so doesn’t need to access ANY
data to return +100

So, if you get +100 DB2 IS there, any other error and DB2 ISN’T J

Oh, and this can save ridiculous amounts of CPU as these statements are
typically executed millions of times per day

I know it’s Friday – but this is NOT a fanciful fabrication!
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG Tech Conference 2011 - the premiere event for DB2
professionals.
Australia, 14-16 September, Melbourne, Australia
EMEA, 13-18 November, Prague Czech Republic

From: Roy Boxwell [mailto:[login to unmask email]
Sent: 29 July 2011 09:28
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?

That would cost the programmer who did that here a round of pizza and
drinks...on the other hand some little JAVA aps still do a SELECT COUNT(*)
FROM SYSIBM.SYSTABLES just to see if their connection is active....Someone
once said "No programming language has been invented where it is impossible
to write bad programs" and that was and still is very very true!


Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Adam Baldwin <[login to unmask email]>
An: [login to unmask email]
Datum: 29.07.2011 09:32
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM
SYSIBM.SYSTABLES instead of SELECT COUNT(*)?




Hi Roy,
What I certainly wouldn't recommend is the very expensive count that one of
my client's had. Their's was achieved via a cursor (declared with SELECT *
FROM...without a WHERE) and a fetch of every row by means of a loop perform
in Cobol with the program adding +1 to a counter for every row fetched.
This little beauty escaped the Count Police but certainly had a high CPU
cost.

Cheers, Adam





Site Links: View post online View mailing list online Send new post via
email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


-----End Original Message-----

Site Links: View post online View mailing list online Send new post via
email Unsubscribe from this mailing list Manage your subscription Use
of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Myron Miller

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Phil Grainger)
Excellent point, Phil. These typically come from either Websphere or Weblog,
glassfish, etc. They are connection pool test SQL to test whether the
connection pool is still valid. There are usually a number of options for the
connection pool, ie before every statement, after every statement, every so
minutes. And each of these options is independent of the other, so the Weblogic
admin could actually have Weblogic testing DB2 before and after every single SQL
statement as well as every 5-10 minutes. For a moderately busy system, this can
mean millions of test per day.

As Phil mentions, these can save ridiculous amounts of CPU. I have measured
these carefully on one system and this change saves about 300 us per execution.
Doesn't seem like much. Well one moderately busy system saved an average of 15
CPU minutes per day with this change. That adds up over a month. 10 systems
provided a savings of about 140 CPU seconds per day.

Think about that amount - for doing nothing but checking to see if DB2 is
there.

Myron



________________________________
From: Phil Grainger <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Fri, July 29, 2011 6:45:59 AM
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?


I’ve also started coming across a worrying number (ie greater than zero!)
occurrences of:

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1

If course the answer is ALWAYS 1 – but the statement, as Roy B says, is just
there to “make sure DB2 is still listening”

If you have one of these, if you really can’t bear to be without it, then change
it to

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 WHERE 0 = 1

DB2 know this is an “always false” predicate so doesn’t need to access ANY data
to return +100


So, if you get +100 DB2 IS there, any other error and DB2 ISN’T J

Oh, and this can save ridiculous amounts of CPU as these statements are
typically executed millions of times per day

I know it’s Friday – but this is NOT a fanciful fabrication!
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia, 14-16 September, Melbourne, Australia
EMEA, 13-18 November, Prague Czech Republic

From:Roy Boxwell [mailto:[login to unmask email]
Sent: 29 July 2011 09:28
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?

That would cost the programmer who did that here a round of pizza and
drinks...on the other hand some little JAVA aps still do a SELECT COUNT(*) FROM
SYSIBM.SYSTABLES just to see if their connection is active....Someone once said
"No programming language has been invented where it is impossible to write bad
programs" and that was and still is very very true!



Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: Adam Baldwin <[login to unmask email]>
An: [login to unmask email]
Datum: 29.07.2011 09:32
Betreff: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?


________________________________




Hi Roy,
What I certainly wouldn't recommend is the very expensive count that one of my
client's had. Their's was achieved via a cursor (declared with SELECT *
FROM...without a WHERE) and a fetch of every row by means of a loop perform in
Cobol with the program adding +1 to a counter for every row fetched. This little
beauty escaped the Count Police but certainly had a high CPU cost.

Cheers, Adam


-----End Original Message-----

Raymond Bell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Myron Miller)
Agree with it all; what a waste of [CPU] time. But remember, these ‘kids’ grew up using [login to unmask email] systems that might be there one moment, a smoking pile of LAN cards the next. It’s in their nature not to trust the back-end ‘server’ will be there for them. And anyone who’s used Windoze will sympathise.

Never stopped me going ‘round to a Developer and beating the daft ‘are you there?’ checking code out of ‘em, though. Most satisfying.

Cheers,


Raymond

From: Myron Miller [mailto:[login to unmask email]
Sent: 29 July 2011 14:51
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

Excellent point, Phil. These typically come from either Websphere or Weblog, glassfish, etc. They are connection pool test SQL to test whether the connection pool is still valid. There are usually a number of options for the connection pool, ie before every statement, after every statement, every so minutes. And each of these options is independent of the other, so the Weblogic admin could actually have Weblogic testing DB2 before and after every single SQL statement as well as every 5-10 minutes. For a moderately busy system, this can mean millions of test per day.

As Phil mentions, these can save ridiculous amounts of CPU. I have measured these carefully on one system and this change saves about 300 us per execution. Doesn't seem like much. Well one moderately busy system saved an average of 15 CPU minutes per day with this change. That adds up over a month. 10 systems provided a savings of about 140 CPU seconds per day.

Think about that amount - for doing nothing but checking to see if DB2 is there.

Myron

-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
________________________________

-----End Original Message-----

Cuneyt Goksu

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Raymond Bell)
I’ve seen different usages of pinging DB2 thru SELECT bla bla FROM SYSIBM.SYSDUMMY1.

One of the craziest and very common one was SELECT … FROM DUAL which is the Oracle equivalent of SYSDUMMY1!!



This also gives many clues how those developer folks are developing code…



Happy Friday.

Cuneyt



From: Bell, Raymond [mailto:[login to unmask email]
Sent: Friday, July 29, 2011 4:57 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



Agree with it all; what a waste of [CPU] time. But remember, these ‘kids’ grew up using [login to unmask email] systems that might be there one moment, a smoking pile of LAN cards the next. It’s in their nature not to trust the back-end ‘server’ will be there for them. And anyone who’s used Windoze will sympathise.



Never stopped me going ‘round to a Developer and beating the daft ‘are you there?’ checking code out of ‘em, though. Most satisfying.



Cheers,





Raymond



From: Myron Miller [mailto:[login to unmask email]
Sent: 29 July 2011 14:51
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



Excellent point, Phil. These typically come from either Websphere or Weblog, glassfish, etc. They are connection pool test SQL to test whether the connection pool is still valid. There are usually a number of options for the connection pool, ie before every statement, after every statement, every so minutes. And each of these options is independent of the other, so the Weblogic admin could actually have Weblogic testing DB2 before and after every single SQL statement as well as every 5-10 minutes. For a moderately busy system, this can mean millions of test per day.



As Phil mentions, these can save ridiculous amounts of CPU. I have measured these carefully on one system and this change saves about 300 us per execution. Doesn't seem like much. Well one moderately busy system saved an average of 15 CPU minutes per day with this change. That adds up over a month. 10 systems provided a savings of about 140 CPU seconds per day.



Think about that amount - for doing nothing but checking to see if DB2 is there.



Myron



-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----



-----End Original Message-----

Phil Grainger

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Raymond Bell)
And haven’t any of these people ever seen a -927??

There’s a special SQL code for “DB2 isn’t there any more”!!!

Works on ANY statement in ANY circumstances ☺

Oh, and I can beat Myrons figures – we’ve seen people save cpu HOURS by speeding up these stupid pings
Phil Grainger
Cogito Ltd.
[login to unmask email]<mailto:[login to unmask email]>
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk<blocked::http://www.cogito.co.uk>

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia<http://www.idug.org/db2-australasia-conference/index.html>, 14-16 September, Melbourne, Australia
EMEA<http://www.idug.org/emea>, 13-18 November, Prague Czech Republic

From: Bell, Raymond [mailto:[login to unmask email]
Sent: 29 July 2011 14:57
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

Agree with it all; what a waste of [CPU] time. But remember, these ‘kids’ grew up using [login to unmask email] systems that might be there one moment, a smoking pile of LAN cards the next. It’s in their nature not to trust the back-end ‘server’ will be there for them. And anyone who’s used Windoze will sympathise.

Never stopped me going ‘round to a Developer and beating the daft ‘are you there?’ checking code out of ‘em, though. Most satisfying.

Cheers,


Raymond

From: Myron Miller [mailto:[login to unmask email]
Sent: 29 July 2011 14:51
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

Excellent point, Phil. These typically come from either Websphere or Weblog, glassfish, etc. They are connection pool test SQL to test whether the connection pool is still valid. There are usually a number of options for the connection pool, ie before every statement, after every statement, every so minutes. And each of these options is independent of the other, so the Weblogic admin could actually have Weblogic testing DB2 before and after every single SQL statement as well as every 5-10 minutes. For a moderately busy system, this can mean millions of test per day.

As Phil mentions, these can save ridiculous amounts of CPU. I have measured these carefully on one system and this change saves about 300 us per execution. Doesn't seem like much. Well one moderately busy system saved an average of 15 CPU minutes per day with this change. That adds up over a month. 10 systems provided a savings of about 140 CPU seconds per day.

Think about that amount - for doing nothing but checking to see if DB2 is there.

Myron

-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----

-----End Original Message-----

Myron Miller

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Phil Grainger)
The catch for testing the SQL code is that it's in Weblogic master rather than
application code. And weblogic commonly returns an invalid connection code to
the app rather than a SQL code. The extra coding required to test for invalid
connection is a bit and can be a bit of effort (not difficult, just
time-consuming) so much coders do not test for it. And some server products,
like SeeBeyond don't handle invalid connections at all well, like having to be
re-started completely. Thus the "need" for the KeepAlive code.

And most of the people doing this are Weblogic administrators rather than
coders. (I'm using Weblogic but it could be Websphere or other tools). They do
not understand the cost and impact on the server. Remember, if they run out of
cycles, they get a bigger breadbox, cheaply.

If people are saving "HOURS a DAY", then I would really question their machine
usage.

My example was just for going from select ... sysdummy1 to select ... sysdummy1
where 1=0. Clearly going from select count(*) from table to the sysdummy1
where 1=0 will save much more.

The default that DBAs must watch for used to be: select count(*) from
sysibm.systables. This is how it comes out of the box from Bea. And like
default passwords, many Admins don't change this. Here's your HOURS per day if
this gets into production on a busy system.

Myron



________________________________
From: Phil Grainger <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Fri, July 29, 2011 10:16:53 AM
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?


And haven’t any of these people ever seen a -927??

There’s a special SQL code for “DB2 isn’t there any more”!!!

Works on ANY statement in ANY circumstances J

Oh, and I can beat Myrons figures – we’ve seen people save cpu HOURS by speeding
up these stupid pings
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia, 14-16 September, Melbourne, Australia
EMEA, 13-18 November, Prague Czech Republic

From:Bell, Raymond [mailto:[login to unmask email]
Sent: 29 July 2011 14:57
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?

Agree with it all; what a waste of [CPU] time. But remember, these ‘kids’ grew
up using [login to unmask email] systems that might be there one moment, a smoking pile of
LAN cards the next. It’s in their nature not to trust the back-end ‘server’
will be there for them. And anyone who’s used Windoze will sympathise.

Never stopped me going ‘round to a Developer and beating the daft ‘are you
there?’ checking code out of ‘em, though. Most satisfying.

Cheers,


Raymond

From:Myron Miller [mailto:[login to unmask email]
Sent: 29 July 2011 14:51
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?

Excellent point, Phil. These typically come from either Websphere or Weblog,
glassfish, etc. They are connection pool test SQL to test whether the
connection pool is still valid. There are usually a number of options for the
connection pool, ie before every statement, after every statement, every so
minutes. And each of these options is independent of the other, so the Weblogic
admin could actually have Weblogic testing DB2 before and after every single SQL
statement as well as every 5-10 minutes. For a moderately busy system, this can
mean millions of test per day.

As Phil mentions, these can save ridiculous amounts of CPU. I have measured
these carefully on one system and this change saves about 300 us per execution.
Doesn't seem like much. Well one moderately busy system saved an average of 15
CPU minutes per day with this change. That adds up over a month. 10 systems
provided a savings of about 140 CPU seconds per day.

Think about that amount - for doing nothing but checking to see if DB2 is
there.

Myron

-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----

-----End Original Message-----
-----End Original Message-----

Phil Grainger

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Myron Miller)
OK, but what happens if DB2 goes away between the “test” and the actual execution??
Phil Grainger
Cogito Ltd.
[login to unmask email]<mailto:[login to unmask email]>
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk<blocked::http://www.cogito.co.uk>

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia<http://www.idug.org/db2-australasia-conference/index.html>, 14-16 September, Melbourne, Australia
EMEA<http://www.idug.org/emea>, 13-18 November, Prague Czech Republic

From: Myron Miller [mailto:[login to unmask email]
Sent: 29 July 2011 15:33
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?

The catch for testing the SQL code is that it's in Weblogic master rather than application code. And weblogic commonly returns an invalid connection code to the app rather than a SQL code. The extra coding required to test for invalid connection is a bit and can be a bit of effort (not difficult, just time-consuming) so much coders do not test for it. And some server products, like SeeBeyond don't handle invalid connections at all well, like having to be re-started completely. Thus the "need" for the KeepAlive code.

And most of the people doing this are Weblogic administrators rather than coders. (I'm using Weblogic but it could be Websphere or other tools). They do not understand the cost and impact on the server. Remember, if they run out of cycles, they get a bigger breadbox, cheaply.

If people are saving "HOURS a DAY", then I would really question their machine usage.

My example was just for going from select ... sysdummy1 to select ... sysdummy1 where 1=0. Clearly going from select count(*) from table to the sysdummy1 where 1=0 will save much more.

The default that DBAs must watch for used to be: select count(*) from sysibm.systables. This is how it comes out of the box from Bea. And like default passwords, many Admins don't change this. Here's your HOURS per day if this gets into production on a busy system.

Myron


-----End Original Message-----
-----End Original Message-----
________________________________

-----End Original Message-----

Myron Miller

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Phil Grainger)



Phil,

We know that will "never happen", right! At least that's the presumption they
code by. Does it make sense that it will never happen, of course not. Is it
really likely to happen, probably not.

I can't count the number of discussions I've had with Java types on these
issues. I've been able to get them to reduce the tests to before statement.
Not even on time period is considered reasonable to them.

Really, testing every so many minutes would be much more reasonable and
realistic and coding for what happens if it fails in the interim. But??

At least we've been able to improve the "KeepAlive" SQL.

Myron



________________________________
From: Phil Grainger <[login to unmask email]>
To: "[login to unmask email]" <[login to unmask email]>
Sent: Fri, July 29, 2011 10:50:05 AM
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?


OK, but what happens if DB2 goes away between the “test” and the actual
execution??
Phil Grainger
Cogito Ltd.
[login to unmask email]
+44 (0) 1298 872 148
+44 (0) 7505 266 768
www.cogito.co.uk

Attend IDUG Tech Conference 2011 - the premiere event for DB2 professionals.
Australia, 14-16 September, Melbourne, Australia
EMEA, 13-18 November, Prague Czech Republic

From:Myron Miller [mailto:[login to unmask email]
Sent: 29 July 2011 15:33
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES
instead of SELECT COUNT(*)?

The catch for testing the SQL code is that it's in Weblogic master rather than
application code. And weblogic commonly returns an invalid connection code to
the app rather than a SQL code. The extra coding required to test for invalid
connection is a bit and can be a bit of effort (not difficult, just
time-consuming) so much coders do not test for it. And some server products,
like SeeBeyond don't handle invalid connections at all well, like having to be
re-started completely. Thus the "need" for the KeepAlive code.

And most of the people doing this are Weblogic administrators rather than
coders. (I'm using Weblogic but it could be Websphere or other tools). They do
not understand the cost and impact on the server. Remember, if they run out of
cycles, they get a bigger breadbox, cheaply.

If people are saving "HOURS a DAY", then I would really question their machine
usage.

My example was just for going from select ... sysdummy1 to select ... sysdummy1
where 1=0. Clearly going from select count(*) from table to the sysdummy1
where 1=0 will save much more.

The default that DBAs must watch for used to be: select count(*) from
sysibm.systables. This is how it comes out of the box from Bea. And like
default passwords, many Admins don't change this. Here's your HOURS per day if
this gets into production on a busy system.

Myron


-----End Original Message-----
-----End Original Message-----
-----End Original Message-----
-----End Original Message-----

Aurora Emanuela Dellanno

RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Phil Grainger)

reviving this ancient (?) thread because we were just hit by a serious performance issue in this regard: an SAP-supplied piece of code, or possibly one of our own developers, thought that using "WHERE 1 < 0" would be just the same as "WHERE 1 = 0".

 

It isn't, obviously (as anyone but a developer would have known); we ended up with several threads using up to 15% of CPU each.

 

Meh.

 

Thanks.

 

Aurora

 



In Reply to Phil Grainger:

I've also started coming across a worrying number (ie greater than zero!) occurrences of:

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1

If course the answer is ALWAYS 1 - but the statement, as Roy B says, is just there to "make sure DB2 is still listening"

If you have one of these, if you really can't bear to be without it, then change it to

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 WHERE 0 = 1

DB2 know this is an "always false" predicate so doesn't need to access ANY data to return +100

So, if you get +100 DB2 IS there, any other error and DB2 ISN'T :)

Oh, and this can save ridiculous amounts of CPU as these statements are typically executed millions of times per day

I know it's Friday - but this is NOT a fanciful fabrication!
Phil Grainger

Roy Boxwell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Aurora Emanuela Dellanno)
Ah! Gotta love SQL developers sometimes… Here’s my blog all about this:



https://www.seg.de/2019-11-db2-existence-checks-select-sysibm-sysdummy1/



Nice weekend to one and all!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Aurora Emanuela Dellanno <[login to unmask email]>
Sent: Friday, May 8, 2020 9:17 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?



reviving this ancient (?) thread because we were just hit by a serious performance issue in this regard: an SAP-supplied piece of code, or possibly one of our own developers, thought that using "WHERE 1 < 0" would be just the same as "WHERE 1 = 0".



It isn't, obviously (as anyone but a developer would have known); we ended up with several threads using up to 15% of CPU each.



Meh.



Thanks.



Aurora





In Reply to Phil Grainger:

I've also started coming across a worrying number (ie greater than zero!) occurrences of:

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1

If course the answer is ALWAYS 1 - but the statement, as Roy B says, is just there to "make sure DB2 is still listening"

If you have one of these, if you really can't bear to be without it, then change it to

SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 WHERE 0 = 1

DB2 know this is an "always false" predicate so doesn't need to access ANY data to return +100

So, if you get +100 DB2 IS there, any other error and DB2 ISN'T :)

Oh, and this can save ridiculous amounts of CPU as these statements are typically executed millions of times per day

I know it's Friday - but this is NOT a fanciful fabrication!
Phil Grainger



-----End Original Message-----

Attachments

  • smime.p7s (5.1k)

James Campbell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Roy Boxwell)
<muse>
SET PATH = PATH;
</muse>

James Campbell



On 8 May 2020 at 8:32, Boxwell, Roy wrote:

> Ah! Gotta love SQL developers sometimes... Here´s my blog all about this:
>
>
>
> https://www.seg.de/2019-11-db2-existence-checks-select-sysibm-sysdummy1/
>
>
>
> Nice weekend to one and all!
>
>
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GmbH and SEGUS Inc.
> -Product Development-
>
> Vagedesstrasse 19
> 40479 Dusseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: <mailto:[login to unmask email]> [login to unmask email]
> Web http://www.seg.de http://www.seg.de
>
> https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung
>
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Ulf Heinrich
>
>
>
> From: Aurora Emanuela Dellanno <[login to unmask email]>
> Sent: Friday, May 8, 2020 9:17 AM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
>
>
>
> reviving this ancient (?) thread because we were just hit by a serious performance issue in this regard: an SAP-supplied piece of code, or possibly one of our own developers, thought that using "WHERE 1 < 0" would be just the same as "WHERE 1 = 0".
>
>
>
> It isn't, obviously (as anyone but a developer would have known); we ended up with several threads using up to 15% of CPU each.
>
>
>
> Meh.
>
>
>
> Thanks.
>
>
>
> Aurora
>
>
>
>
>
> In Reply to Phil Grainger:
>
> I've also started coming across a worrying number (ie greater than zero!) occurrences of:
>
> SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1
>
> If course the answer is ALWAYS 1 - but the statement, as Roy B says, is just there to "make sure DB2 is still listening"
>
> If you have one of these, if you really can't bear to be without it, then change it to
>
> SELECT COUNT(*) FROM SYSIBM.SYSDUMMY1 WHERE 0 = 1
>
> DB2 know this is an "always false" predicate so doesn't need to access ANY data to return +100
>
> So, if you get +100 DB2 IS there, any other error and DB2 ISN'T :)
>
> Oh, and this can save ridiculous amounts of CPU as these statements are typically executed millions of times per day
>
> I know it's Friday - but this is NOT a fanciful fabrication!
> Phil Grainger
>
>



--
This email has been checked for viruses by AVG.
https://www.avg.com

Michael Hannan

RE: Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to James Campbell)

Funny one James. Concatenate PATH with nothing. Could have commented that one out I guess. It is a sort of place marker to adding something extra to the PATH (when needed).

I had this bad habit. Because of amending my standard SQLs, commenting various predicates in and out, I wanted every useful potential predicate (boolean term predicate) to begin with an AND. Commenting out the WHERE line causes  the SQL to fail. So I therefore started making WHERE 1=1 the first of the predicates, never to be commented out, and doing absolutely nothing. DB2 should be able to trim it in the modern day. In old  days the overhead was absolutely minisicule (because it did no filtering).

I would happily give my SQL to others to use, to amend, or clone.

Then I kept getting questions.
Why did I code WHERE 1=1?
Was this something special for performance or what? Ha ha. 
Nope, just a piece of filler. Not obvious to everyone.
I then wondered if I needed to add a comment to explain that WHERE 1=1 does not do anything,
but I put it there just because I can put it there with no effect.

This tangent is not very important, but neither was the original topic.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ May 10, 2020 - 11:10 AM (Australia/Melbourne)
Michael Hannan[Organization Members] @ May 10, 2020 - 11:11 AM (Australia/Melbourne)

James Campbell

Runstats before SELECT CARDF FROM SYSIBM.SYSTABLES instead of SELECT COUNT(*)?
(in response to Michael Hannan)
I suspect (happy to be wrong) that you missed that it was a SET statement. Specifically
related to Roy's 'is Db2 still there' blog. No open/fetch/close - a single statement, nothing
changes, no locks .

I would have suggested a bare CONNECT (does even less), but Roy mentioned Java.

James Campbell


On 9 May 2020 at 18:09, Michael Hannan wrote:

> Funny one James. Concatenate PATH with nothing. Could have commented that one out I guess.
>
> I had this bad habit. Because of amending my standard SQLs, commenting various predicates in and out, I wanted every useful potential predicate (boolean term predicate) to begin with an AND. Commenting out the WHERE line causes  the SQL to fail. So I therefore started making WHERE 1=1 the first of the predicates, never to be commented out, and doing absolutely nothing. DB2 should be able to trim it in the modern day. In old  days the overhead was absolutely minisicule (because it did no filtering).
> I would happily give my SQL to others to use, to amend, or clone.
> Then I kept getting questions.
> Why did I code WHERE 1=1?
> Was this something special for performance or what? Ha ha. 
> Nope, just a piece of filler. Not obvious to everyone.
> I then wondered if I needed to add a comment to explain that WHERE 1=1 does not do anything,
> but I put it there just because I can put it there with no effect.
> Michael Hannan,
> DB2 Application Performance Specialist
>
>


--
This email has been checked for viruses by AVG.
https://www.avg.com