Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

COUNT is not working

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
David Beckham

New User


Joined: 04 Apr 2008
Posts: 26
Location: Chennai

PostPosted: Tue Jul 01, 2008 11:39 am    Post subject: COUNT is not working
Reply with quote

Dear all,

In our Db2 environment Count(*) SQL Query is not working properly. For some tables it's giving the actual no rows. for some tables it's showing 0 rows. It's not retriving the actual no of rows present in the table.

Cound Anyone help me to resolve this problem?

Regards,
David.
Back to top
View user's profile Send private message

UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 743
Location: Germany

PostPosted: Tue Jul 01, 2008 1:15 pm    Post subject:
Reply with quote

Hi !

Could it be possible, that this depends on the WhereClause ???
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jul 01, 2008 1:39 pm    Post subject:
Reply with quote

Where clause? Which Where clause? If the OP truely wants a row count, the sql should contain NO where clause.
Back to top
View user's profile Send private message
am_ne

New User


Joined: 24 Mar 2007
Posts: 25
Location: Bangalore

PostPosted: Tue Jul 01, 2008 3:14 pm    Post subject: Re: COUNT is not working
Reply with quote

David Beckham wrote:

In our Db2 environment Count(*) SQL Query is not working properly. For some tables it's giving the actual no rows. for some tables it's showing 0 rows. It's not retriving the actual no of rows present in the table.


It depends on the access path DB2 optimizer chooses to get the desired result. If it goes for tablespace scan then you will get correct result. If it goes for index scan then you will get result according to the state of index. To get good result you have to make sure that the index is not corrupted. You can run one CHECK INDEX utility on the index of the table for which you are getting incorrect results.

Thanks,
Amit
Back to top
View user's profile Send private message
David Beckham

New User


Joined: 04 Apr 2008
Posts: 26
Location: Chennai

PostPosted: Tue Jul 01, 2008 3:16 pm    Post subject:
Reply with quote

Hi,

Actually i don't give any WHERE clause in the COUNT Query. I am just executing a simple query SELECT COUNT(*) FROM <table name>. The rows are there in table, still it showing 0 rows in the result. When i execute SELECT * FROM <table name>. It's retriving the rows. Anything is there to do with Reorg or Rebuild Index?

Thank you,
Regards,
David
Back to top
View user's profile Send private message
am_ne

New User


Joined: 24 Mar 2007
Posts: 25
Location: Bangalore

PostPosted: Tue Jul 01, 2008 5:12 pm    Post subject:
Reply with quote

David Beckham wrote:

Anything is there to do with Reorg or Rebuild Index?


Both utilities will build the index and that will solve your prblem.

Thanks,
Amit
Back to top
View user's profile Send private message
David Beckham

New User


Joined: 04 Apr 2008
Posts: 26
Location: Chennai

PostPosted: Wed Jul 02, 2008 10:12 am    Post subject:
Reply with quote

Hi,

Is there any way to identify which access path the COUNT Query is choosing? How to make the COUNT Query to do Tablespace scan.

Thanks for your help.

Regards,
David.
Back to top
View user's profile Send private message
prakash271082

New User


Joined: 09 Sep 2005
Posts: 53

PostPosted: Wed Jul 02, 2008 12:30 pm    Post subject:
Reply with quote

Hi,

One simple thing is that you can drop any indexes present in the table. So that it does Tablespace scan by default.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jul 02, 2008 1:11 pm    Post subject:
Reply with quote

indexes have nothing to do with a query that has no where clause.

suggest your problem is elsewhere. besides, we have not seen the sql.
Back to top
View user's profile Send private message
David Beckham

New User


Joined: 04 Apr 2008
Posts: 26
Location: Chennai

PostPosted: Tue Jul 08, 2008 2:58 pm    Post subject:
Reply with quote

After running Rebulid Index, Count Query is working fine.

Thanks for your support.
______
David
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Jul 08, 2008 3:01 pm    Post subject:
Reply with quote

such BS!
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 08, 2008 6:44 pm    Post subject:
Reply with quote

agreed
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Jul 09, 2008 1:23 pm    Post subject:
Reply with quote

What is BS here..? (Basic Stuff..by any chance..?)
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 09, 2008 8:03 pm    Post subject:
Reply with quote

Hi Anuj,

Quote:
(Basic Stuff..by any chance..?)
Ah. . . No. . .

More like Bull Stuff. . . That stuff which leaves the south end of a north-bound bull. . .
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Jul 10, 2008 12:00 pm    Post subject:
Reply with quote

Hi Dick,

Yeah, I got a PM explaing the meaning..that was lil different..but was intresting.. icon_biggrin.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts SCRNAME not working on my mainframe? mrsidhu12 TSO/ISPF 7 Thu Sep 01, 2016 2:17 pm
No new posts 2 same CLIST pgm but with different n... boyti ko CLIST & REXX 5 Fri Jun 03, 2016 4:55 pm
No new posts Update the Sortout file with record c... karthik_sripal SYNCSORT 8 Tue May 17, 2016 8:52 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us