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
 

 

Create View

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

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Tue Oct 27, 2009 11:51 am    Post subject: Create View
Reply with quote

Hi,

I am using DB2 for z/os 9.1
I need the count of some tables.Since we do not runstat our system at regular interval ,i cannot use the catalog tables.So, i thought to create a view like ---create view schema.sample(count) as (select count(*) from tab1 Union select count(*) from tab2 Union select count(*) from tab3......).

i expected the result to come in the order i am giving the count in create view stmt.
But the output was random and for some table it dint even give the count.
I cant understand wat the reason can be.
Kindly correct my knowledge in this.
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Oct 27, 2009 12:21 pm    Post subject:
Reply with quote

Hello There,

I think if you give union all then it will show output for all the table .You can use
Code:

create view schema.sample(count) as (select count(*) as table1_count from tab1 Union All select count(*) as table2_count from tab2 Union All select count(*) as table3_count  from tab3......).


Please let me know if it solves your problem
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Oct 27, 2009 1:30 pm    Post subject:
Reply with quote

Although you don't run runstats regularly, maybe you could still use Real Time Statistics.
Back to top
View user's profile Send private message
LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Tue Oct 27, 2009 1:41 pm    Post subject: Reply to: Create View
Reply with quote

Hi guptae,

Thanx for ur reply ...
By Using Union all ..it helped..and it was giving the count in order and for all tables...
at present i m getting output like (for 3 tables)----
COUNT
-----------
105
105
4

i used --------
CREATE VIEW VW_SAMPLE1(COUNT) AS (SELECT COUNT(*) FROM TABLE_2 UNION ALL SELECT COUNT(*) FROM TABLE_3 UNION ALL SELECT COUNT(*) FROM TABLE);
SELECT * FROM VW_SAMPLE1;

i just want to know if i can add one more column containing the respective table names in the above create view statement.
i tried creating it but i got error....

Pls correct if i am rong....
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Tue Oct 27, 2009 2:03 pm    Post subject:
Reply with quote

You can use literals.

Code:

CREATE VIEW VW_SAMPLE1(tbname,CNT) as (
select 'a', count(*) from sysibm.sysdummy1
union all
select 'b', count(*) from sysibm.sysdatabase)
Back to top
View user's profile Send private message
LearningDb2

New User


Joined: 19 Jun 2009
Posts: 40
Location: India

PostPosted: Tue Oct 27, 2009 2:54 pm    Post subject:
Reply with quote

Hi GuyC,
Thanx for ur reply and suggestion.
i used the literals as u said and it is working fine..

it is giving output like ---
TABLENAME CNT
--------- -----------
a 4
b 106
c 106

Thanx again.
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. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Help Needed with View Direct shailesh_do CA Products 2 Wed Jul 13, 2016 10:39 am
No new posts how to find clist's or view library Andrew Hsia CLIST & REXX 4 Tue Jun 28, 2016 6:07 pm
No new posts Report view in Mobile devices Robert Sample All Other Mainframe Topics 0 Thu Jun 23, 2016 7:05 pm


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