Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Get record Counts in Each Partition

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

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Thu Nov 20, 2008 6:06 pm    Post subject: Get record Counts in Each Partition
Reply with quote

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
View user's profile Send private message

tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Fri Nov 21, 2008 10:16 am    Post subject: Reply to: Get record Counts in Each Partition
Reply with quote

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
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Nov 21, 2008 11:10 am    Post subject: Reply to: Get record Counts in Each Partition
Reply with quote

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
View user's profile Send private message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Fri Nov 21, 2008 11:16 am    Post subject: Reply to: Get record Counts in Each Partition
Reply with quote

I forgot to mention that in our system, the partition number happens to be one of the columns in the table.
Back to top
View user's profile Send private message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Fri Nov 21, 2008 11:26 am    Post subject: Reply to: Get record Counts in Each Partition
Reply with quote

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
View user's profile Send private message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Fri Nov 21, 2008 11:29 am    Post subject: Reply to: Get record Counts in Each Partition
Reply with quote

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
View user's profile Send private message
Huzefa

New User


Joined: 05 Dec 2006
Posts: 85
Location: Bangalore

PostPosted: Sat Nov 22, 2008 1:03 am    Post subject: Reply to: Get record Counts in Each Partition
Reply with quote

Hey thanks all for the reply. I got the results from the query above.
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 query to fetch record which has only ... maxsubrat DB2 2 Mon Dec 11, 2017 5:03 pm
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Squeeze record left so that zeroes ar... Vignesh Sid SYNCSORT 5 Fri Nov 10, 2017 1:40 pm
No new posts Formatting multiple records to a sing... Vignesh Sid SYNCSORT 3 Tue Nov 07, 2017 12:22 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us