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
 

 

Number of tables in a given database

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

New User


Joined: 27 Feb 2008
Posts: 10
Location: bangalore

PostPosted: Wed Mar 05, 2008 5:42 pm    Post subject: Number of tables in a given database
Reply with quote

Hi,
How to know the number of TABLEs in a DB2 DATABASE...?
Is there any QUERY for that.?
Back to top
View user's profile Send private message

acevedo

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Wed Mar 05, 2008 6:10 pm    Post subject:
Reply with quote

search in SYSIBM.SYSTABLES
Back to top
View user's profile Send private message
senthil1711
Warnings : 1

New User


Joined: 27 Feb 2008
Posts: 10
Location: bangalore

PostPosted: Thu Mar 06, 2008 10:19 am    Post subject:
Reply with quote

hi,

thanks.
could u plz tell where to search for SYSIBM.SYSTABLES. I tried but i can't access some of the options.
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 Mar 06, 2008 11:52 am    Post subject:
Reply with quote

senthil1711 wrote:
I tried but..
Hi,

Where did You try..? If the next question is..
Quote:
could u plz tell where to search for SYSIBM.SYSTABLES
Back to top
View user's profile Send private message
senthil1711
Warnings : 1

New User


Joined: 27 Feb 2008
Posts: 10
Location: bangalore

PostPosted: Thu Mar 06, 2008 2:27 pm    Post subject:
Reply with quote

sorry..

the statement : "I tried but i can't access some of the options"--> is for...when i am trying to find where to look for "SYSIBM.SYSTABLES".. I tried some options where SPUFI and similar options are listed. Ex. for
"BMC ADMINISTRATIVE TOOLS FOR DB2" option I could not access it.

So, sorry for the confusion. If u know where to look for SYSIBM.SYSTABLES, plz write.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Thu Mar 06, 2008 7:37 pm    Post subject:
Reply with quote

SELECT xxx from SYSIBM.SYSTABLES WITH UR;
Back to top
View user's profile Send private message
senthil1711
Warnings : 1

New User


Joined: 27 Feb 2008
Posts: 10
Location: bangalore

PostPosted: Fri Mar 07, 2008 10:49 am    Post subject:
Reply with quote

Hi Steve

thank you
Back to top
View user's profile Send private message
jitendersinghbhiwani

New User


Joined: 15 Jun 2007
Posts: 2
Location: Chennai

PostPosted: Fri Mar 07, 2008 6:19 pm    Post subject:
Reply with quote

Hi,

Use the query
SELECT COUNT(*) From SYSIBM.SYSTABLES
WHERE CREATOR = 'XXXX'

in SPUFI or QMF. USE the DB2 region in place of XXXX where you want to find the tables.

If you want to get the list of tables then use SELECT * in place of SELECT COUNT(*)

ok
Back to top
View user's profile Send private message
senthil1711
Warnings : 1

New User


Joined: 27 Feb 2008
Posts: 10
Location: bangalore

PostPosted: Mon Mar 10, 2008 10:47 am    Post subject:
Reply with quote

hi jitender,

i used the query..

SELECT COUNT(*) From SYSIBM.SYSTABLES;

and it listed all the table names.

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

Senior Member


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

PostPosted: Mon Mar 10, 2008 10:55 am    Post subject:
Reply with quote

senthil1711 wrote:
SELECT COUNT(*) From SYSIBM.SYSTABLES;

and it listed all the table names.
Hello,

This will return the count (number of) tables & not the list. For that
Code:
SELECT * From SYSIBM.SYSTABLES
with ur;
would work.
Back to top
View user's profile Send private message
senthil1711
Warnings : 1

New User


Joined: 27 Feb 2008
Posts: 10
Location: bangalore

PostPosted: Wed Mar 12, 2008 10:05 am    Post subject:
Reply with quote

hi anuj,

thanks
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10274
Location: italy

PostPosted: Wed Mar 12, 2008 12:36 pm    Post subject: Reply to: Number of tables in a given database
Reply with quote

Quote:
SELECT xxx from SYSIBM.SYSTABLES WITH UR;
will display the xxx column

Quote:
SELECT count(*) from SYSIBM.SYSTABLES WITH UR;
will display the TOTAL number of the tables in the DB2 instance irrelevant of the database

Quote:
SELECT DISTINCT DBNAME from SYSIBM.SYSTABLES ORDER BY DBNAME WITH UR;
will display all the DATABASES defined in the DB2 instance

Quote:
SELECT DBNAME count(*) FROM SYSIBM.SYSTABLES GROUP BY DBNAME ORDER BY DBNAME WITH UR;
will return a list of all the databases with the number of tables defined in each database

Quote:
SELECT DBNAME,TBNAME FROM SYSIBM.SYSTABLES ORDER BY DBNAME,TBNAME WITH UR;
will return a list of all the databases with the tables defined in each database
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 Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts SMTP the current generation number of... Jyothi Kulunde JCL & VSAM 4 Thu May 04, 2017 4:08 pm
No new posts Edit large number of datasets (QSAM) zh_lad TSO/ISPF 3 Tue Apr 04, 2017 6:08 pm
No new posts how to mask the phone number kumarinfy DB2 4 Mon Apr 03, 2017 5:23 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


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