Create View

LearningDb2 · New User Joined: 19 Jun 2009 Posts: 40 Location: India

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.

guptae · Posted: Tue Oct 27, 2009 12:21 pm

Hello There,

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

GuyC · Posted: Tue Oct 27, 2009 1:30 pm

Although you don't run runstats regularly, maybe you could still use Real Time Statistics.

LearningDb2 · New User Joined: 19 Jun 2009 Posts: 40 Location: India

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....

GuyC · Posted: Tue Oct 27, 2009 2:03 pm

You can use literals.

LearningDb2 · New User Joined: 19 Jun 2009 Posts: 40 Location: India

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.