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
 
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: 1281
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: 1281
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
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts How do I create a mainframe monitorin... anjali.bisht All Other Mainframe Topics 4 Fri Sep 01, 2017 2:20 pm
This topic is locked: you cannot edit posts or make replies. rexx to create a tso command Bharath Vikraman CLIST & REXX 1 Tue Aug 08, 2017 3:32 pm
This topic is locked: you cannot edit posts or make replies. rexx code to create a ps file Bharath Vikraman CLIST & REXX 4 Mon Aug 07, 2017 10:30 am
No new posts How do you create Eclipse based dialogs jasorn IBM Tools 0 Thu Aug 03, 2017 5:05 pm

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