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 rows of all the tables.

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

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Wed Nov 18, 2009 11:55 am    Post subject: Count rows of all the tables.
Reply with quote

Hi All,

I have a question. Is there any system table in DB2 (like SYSTABLES in SYSIBM), where in I can get the count of rows in each table in one particular region. For example there are 3 tables in region XYZ with creator ABC, and I require to know the number of rows in each of three tables without giving a count(*) query for each table.

Thanks,
Muffi
Back to top
View user's profile Send private message

LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Wed Nov 18, 2009 1:34 pm    Post subject:
Reply with quote

Hi,
You can try real time statistics.
Query the table SYSIBM.SYSTABLESPACESTATS...in which count of rows is mantained..

Else you can try writing a simple procedure selecting table name as variable from SYSIBM.SYSTABLES.
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Wed Nov 18, 2009 2:50 pm    Post subject:
Reply with quote

Try this:

Code:


select sum(card)
          from sysibm.systables
    where creator = 'ABC' ;

Back to top
View user's profile Send private message
muffirulz

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Wed Nov 18, 2009 3:52 pm    Post subject: Reply to: Count rows of all the tables.
Reply with quote

bauer,

the query gave me a result -1036.

regards,
Muffi
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2422
Location: Netherlands, Amstelveen

PostPosted: Wed Nov 18, 2009 3:58 pm    Post subject:
Reply with quote

I think its CARDF.
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 59
Location: Atlanta

PostPosted: Wed Nov 18, 2009 4:38 pm    Post subject:
Reply with quote

just a FYI.. i would be running the query on the SYSIBM tables only if RUNSTATS have been run on the above region and nothing is loaded after that on the same region.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Nov 18, 2009 4:48 pm    Post subject:
Reply with quote

To sum up the previous answers :

It all depends on how accurate it has to be :

If you're happy with whatever was in CARDF at the time of the last runstats , a
select name,cardf from sysibm.systables where creator = 'ABC'
is sufficient.

If you're happy with an value that could be 30 minutes old and possibly completely wrong (when some utility failed or some obscure third party utility ran or a failure in data sharing), a select from sysibm.systablespacestats is OK.
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Thu Nov 19, 2009 1:57 am    Post subject: Reply to: Count rows of all the tables.
Reply with quote

have a look here:

http://www.ibmmainframes.com/about35155.html
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1238
Location: Richfield, MN, USA

PostPosted: Thu Nov 19, 2009 10:54 am    Post subject:
Reply with quote

That link directs you to yoururl.com
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: Thu Nov 19, 2009 11:05 am    Post subject: Reply to: Count rows of all the tables.
Reply with quote

Link corrected. . .

Always a good idea to Preview and test a link that is being posted.

d
Back to top
View user's profile Send private message
muffirulz

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Thu Nov 19, 2009 12:40 pm    Post subject: Reply to: Count rows of all the tables.
Reply with quote

Hi,

Thanks all for your help. It seems that runstats is necessary to obtain the CARD value from SYSTABLES. In my case the region 'ABC' is a new test region created and I am given a task to load all the tables with the load files given.

Hence runstats is executed on the database the value will not be populated in CARD field.

Thanks,
Muffi
Back to top
View user's profile Send private message
bauer

New User


Joined: 03 Mar 2009
Posts: 26
Location: germany

PostPosted: Thu Nov 19, 2009 1:31 pm    Post subject:
Reply with quote

Ups, sorry for the incorrect link. No idea, why the link was incorrect.
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: Thu Nov 19, 2009 9:03 pm    Post subject:
Reply with quote

Hello,

Quote:
No idea, why the link was incorrect
Because when the URL tag is used, it genereates [url=http://yoururl.com]Type_Description_Here giving the poster the ability to put the url "under" a description. If it is not changed, the link winds up being http://yoururl.com . . .

If the url itself is shown in the post, it is not necessary to use the url tag (see the previous sentence).

A bit of experimenting with Preview will show this better than i can describe it icon_wink.gif
Back to top
View user's profile Send private message
muffirulz

New User


Joined: 14 Sep 2005
Posts: 74
Location: Kentucky, USA

PostPosted: Fri Nov 20, 2009 2:57 pm    Post subject: Reply to: Count rows of all the tables.
Reply with quote

Hi,

Thanks a lot all.. I did a runstats on the database and was able to generate the counts of all the tables in a single go.

Thanks,
Muffi
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
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 Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am


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