|
View previous topic :: View next topic
|
| Author |
Message |
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
Hi All,
I need to have a select query which can retrieve record count in Each Partition.
I tried something like
| Code: |
| SELECT COUNT(*) FROM creator.table PARTITION (PARTITION1) WITH UR: |
But this gives me an error.
I tried to display the record counts in Platinum too but it does not show me in RC Query Option.
Can someone Suggest any other method to get the record count in each partition. I have Products like Platinum, FILD & QMF for DB2.
Thanks in advance. |
|
| Back to top |
|
 |
tarun_bhardwaj
New User
.jpg)
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
Hi Hufeza,
I tried this query in SPUFI and it works for me:
| Code: |
000100 SELECT PART_NR,COUNT(*) FROM creator.DEPARTMENTS
000200 GROUP BY PART_NR; |
The results are:
| Code: |
---------+---------+---------+---------+---------+---------+
PART_NR
---------+---------+---------+---------+---------+---------+
1. 177
2. 175
3. 171
4. 172
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 |
Best wishes,
Tarun |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
| Quote: |
000100 SELECT PART_NR,COUNT(*) FROM creator.DEPARTMENTS
000200 GROUP BY PART_NR;
|
I am not sure how this will retrieve the total number of records in a particular partition
If your requirement is to obtain the total record count of a particular partitionion you should something like
Select card from sysibm.systablepart where PARTITION='your partion number'
PS:Statitics should be gathered for the table else the value would be -1 |
|
| Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
| I forgot to mention that in our system, the partition number happens to be one of the columns in the table. |
|
| Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
For systems where the partition number is not a part of the table, the query can be re-written as:
| Code: |
SELECT PARTITION, CARD FROM SYSIBM.SYSTABLEPART
WHERE TSNAME='xxxxx' AND DBNAME='yyyyyyyy'; |
Here, CARD contains the count of rows in that partition. |
|
| Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003
Posts: 39
Location: delhi
|
|
|
|
| You also need to make sure that the table has been reorg'ed in order to get the correct and accurate count(that matches with the actual number of rows in that tablespace) else you would get the previous count of the time whenever reorg was last run on that table. |
|
| Back to top |
|
|
Huzefa
New User
Joined: 05 Dec 2006
Posts: 83
Location: Bangalore
|
|
|
|
| Hey thanks all for the reply. I got the results from the query above. |
|
| Back to top |
|
|
|
|
