|
View previous topic :: View next topic
|
| Author |
Message |
David Beckham
New User
Joined: 04 Apr 2008
Posts: 28
Location: Chennai
|
|
|
|
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 |
|
 |
UmeySan
Active Member
Joined: 22 Aug 2006
Posts: 771
Location: Germany
|
|
|
|
Hi !
Could it be possible, that this depends on the WhereClause ??? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Where clause? Which Where clause? If the OP truely wants a row count, the sql should contain NO where clause. |
|
| Back to top |
|
|
am_ne
New User
Joined: 24 Mar 2007
Posts: 25
Location: Bangalore
|
|
|
|
| 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 |
|
|
David Beckham
New User
Joined: 04 Apr 2008
Posts: 28
Location: Chennai
|
|
|
|
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 |
|
|
am_ne
New User
Joined: 24 Mar 2007
Posts: 25
Location: Bangalore
|
|
|
|
| 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 |
|
|
David Beckham
New User
Joined: 04 Apr 2008
Posts: 28
Location: Chennai
|
|
|
|
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 |
|
|
prakash271082
New User
Joined: 09 Sep 2005
Posts: 53
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
David Beckham
New User
Joined: 04 Apr 2008
Posts: 28
Location: Chennai
|
|
|
|
After running Rebulid Index, Count Query is working fine.
Thanks for your support.
______
David |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| such BS! |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| agreed |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| What is BS here..? (Basic Stuff..by any chance..?) |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hi Dick,
Yeah, I got a PM explaing the meaning..that was lil different..but was intresting..  |
|
| Back to top |
|
|
|
|
