Alternate to Count(*) in select query

GMnaidu · New User Joined: 29 Jan 2009 Posts: 6 Location: Chennai

Hi All,

I would like to know if there is any alternate way to find out no.of records in a DB2 table apart from the Count(*) in the select query.
Count(*) consumes so much CPU utilization and not preferable.

dick scherrer · Posted: Tue Mar 31, 2009 12:51 am

Hello,

GMnaidu · New User Joined: 29 Jan 2009 Posts: 6 Location: Chennai

Hi Dick,

In my cobol program i have quey which needs the count of a DB2 table.
I am looking for an alternate method instead of using Count(*)over here.

dick scherrer · Posted: Tue Mar 31, 2009 10:28 am

Hello,

If you truly need the actual count in your progam, what you have is most likely what you'll continue to use.

dbzTHEdinosauer · Posted: Tue Mar 31, 2009 4:00 pm

GM naidu,

just out of curiosity, why does the COBOL program need to know the count of the DB2 table?

is this a temp table?

Terry Heinze · Posted: Wed Apr 01, 2009 6:49 am

The only time I've seen COUNT(*) used in a COBOL program was to determine if an internal table was large enough to hold all the rows prior to loading the table. I never really saw much sense in that. I would simply load the table until I exceeded the maximum occurrences in which case I'd cancel with an error message. The only advantage I could see in doing the COUNT first would be to know how large the internal table needed to be in case it wasn't big enough.

GMnaidu · New User Joined: 29 Jan 2009 Posts: 6 Location: Chennai

Hi,

It goes like this,

we have IMS screens.if user want any info regarding some person he will just type the name and press enter.
in the backend a program is triggered automatically which have a select count(*) query with where condition provided by user.
if 0 records then in the online screen 'no records in db' msg should be displayed
else it should retrive the info from the db2 tables and display on the screen.

Here i am looking for an alternate to count(*).

dbzTHEdinosauer · Posted: Wed Apr 01, 2009 6:25 pm

normally, I have always written the logic to access the rows,
and if zero rows are returned, appropriate advisory message is displayed.

idea being, why do a COUNT, then SELECT if count is >0?
saves an SQL call (count).
saving a DECLARE CURSOR and FETCH is not worth it,
or a ROWSET SELECT which is faster than the DECLARE CURSOR/ FETCH

or do a singleton select without the FIRST Row only clause
if you are only interested in 0,1 or more than 1 type info.

0 = 1 row
100 = no rows
-811 = more than one row.

the COUNT will process until no-more rows to interrogate,
whereas a singleton select will stop after the second row is found,
even though DB2 is 'provoked' with a forced error.

if the WHERE conditions force an index read, the effect of the COUNT is not so bad,
but if we are doing a table scan, forget it - go with the -811 or create the necessary indexes.

DBA's will debate the -811 logic (because of the error handling logic),
but the table size, design, indexes are more important considerations
than some DBA's hard and fast rule.

If your vsn of DB2 supports ROWSET Select and
you know there will never be more rows than the rowset count
throw the COUNT crap out and replace with ROWSET select.

dick scherrer · Posted: Wed Apr 01, 2009 7:39 pm

Hello,