470,641 Members | 1,574 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,641 developers. It's quick & easy.

sql question

hi,

if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29
million records and i divide table into small tables each having data
for one year like table1_2005 only contains records with year(c2) =
2005.

how can i create view on these tables so that from view it appears like
single table and there has to be no change in middleware code( java ).

Nov 28 '06 #1
15 1172
i am sorry for replying to my own message but do not know how to edit
it

i know that union of all table will work but that will not use any
index on tables.
On Nov 28, 1:16 pm, "db2admin" <jag...@gmail.comwrote:
hi,

if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29
million records and i divide table into small tables each having data
for one year like table1_2005 only contains records with year(c2) =
2005.

how can i create view on these tables so that from view it appears like
single table and there has to be no change in middleware code( java ).
Nov 28 '06 #2
"db2admin" <ja****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
>i am sorry for replying to my own message but do not know how to edit
it

i know that union of all table will work but that will not use any
index on tables.
I don't know why you say it will not use an index. Normally it would use an
index. But you may need to post all of your DDL to look at what you are
doing.
Nov 28 '06 #3
Right-- a UNION ALL view should be exactly what you need. Are you
saying that you've defined indexes on the base tables but they're not
being used, or that it's your understanding that such indexes *won't*
be used (i.e. it's a DB2 thing, not a circumstantial thing)?

--Jeff

Mark A wrote:
"db2admin" <ja****@gmail.comwrote in message
news:11**********************@h54g2000cwb.googlegr oups.com...
i am sorry for replying to my own message but do not know how to edit
it

i know that union of all table will work but that will not use any
index on tables.

I don't know why you say it will not use an index. Normally it would use an
index. But you may need to post all of your DDL to look at what you are
doing.
Nov 28 '06 #4
Your best bet *is* a UNION ALL view, with indexes defined on the base
tables. Why do you say that no indexes will be used?

--Jeff

db2admin wrote:
i am sorry for replying to my own message but do not know how to edit
it

i know that union of all table will work but that will not use any
index on tables.
On Nov 28, 1:16 pm, "db2admin" <jag...@gmail.comwrote:
hi,

if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29
million records and i divide table into small tables each having data
for one year like table1_2005 only contains records with year(c2) =
2005.

how can i create view on these tables so that from view it appears like
single table and there has to be no change in middleware code( java ).
Nov 28 '06 #5
Ian
db2admin wrote:
hi,

if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29
million records and i divide table into small tables each having data
for one year like table1_2005 only contains records with year(c2) =
2005.

how can i create view on these tables so that from view it appears like
single table and there has to be no change in middleware code( java ).
Another option is to use MDC (V8 and V9) or Range Partitioning (V9
only). Then you really have only 1 table.
Nov 28 '06 #6
db2admin wrote:
hi,

if i have one table like table1(c1 int,c2 date,c3 varchar) and has 29
million records and i divide table into small tables each having data
for one year like table1_2005 only contains records with year(c2) =
2005.
If you are on V9, you can make use of the table partitioning feature, i.e.
range partitioning. Then you don't have to worry about views etc.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 28 '06 #7
Knut Stolze wrote:
If you are on V9, you can make use of the table partitioning feature, i.e.
range partitioning. Then you don't have to worry about views etc.
To the best of my knowledge the table partitioning feature is only
available in the enterprise edition of db2 v9. So, this is only a
viable solution if you are targetting the enterprise edition.

Nov 29 '06 #8


On Nov 29, 2:14 am, "Otto Carl Marte" <Otto.Ma...@gmail.comwrote:
Knut Stolze wrote:
If you are on V9, you can make use of the table partitioning feature, i.e.
range partitioning. Then you don't have to worry about views etc.To the best of my knowledge the table partitioning feature is only
available in the enterprise edition of db2 v9. So, this is only a
viable solution if you are targetting the enterprise edition.
i did not test it yet but i am assumming that if i create view like

view abc as select * from table1_2005 union all select * from
table1_2006 and
then query like select * from abc where c2 = '05062005' , it will not
use existing index on both table if all underlying tables have index on
column c2 which is a date column

i will also test this today but please share with me if someone already
know the answer

Nov 29 '06 #9
"db2admin" <ja****@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
>

On Nov 29, 2:14 am, "Otto Carl Marte" <Otto.Ma...@gmail.comwrote:
>Knut Stolze wrote:
If you are on V9, you can make use of the table partitioning feature,
i.e.
range partitioning. Then you don't have to worry about views etc.To
the best of my knowledge the table partitioning feature is only
available in the enterprise edition of db2 v9. So, this is only a
viable solution if you are targetting the enterprise edition.

i did not test it yet but i am assumming that if i create view like

view abc as select * from table1_2005 union all select * from
table1_2006 and
then query like select * from abc where c2 = '05062005' , it will not
use existing index on both table if all underlying tables have index on
column c2 which is a date column

i will also test this today but please share with me if someone already
know the answer
It will use the index.

You should probably read this article to understand UNION ALL views:

http://www-128.ibm.com/developerwork...202zuzarte.pdf
Nov 29 '06 #10

db2admin wrote:
On Nov 29, 2:14 am, "Otto Carl Marte" <Otto.Ma...@gmail.comwrote:
Knut Stolze wrote:
If you are on V9, you can make use of the table partitioning feature, i.e.
range partitioning. Then you don't have to worry about views etc.To the best of my knowledge the table partitioning feature is only
available in the enterprise edition of db2 v9. So, this is only a
viable solution if you are targetting the enterprise edition.

i did not test it yet but i am assumming that if i create view like

view abc as select * from table1_2005 union all select * from
table1_2006 and
then query like select * from abc where c2 = '05062005' , it will not
use existing index on both table if all underlying tables have index on
column c2 which is a date column

i will also test this today but please share with me if someone already
know the answer

Rule number one, never use SELECT *.
Rule number two, see rule number one.

Does a query on each of the underlying TABLEs use that INDEX for the
same query?

B.

Nov 29 '06 #11
"Mark A" <no****@nowhere.comwrote in message
news:ib******************************@comcast.com. ..
>
It will use the index.

You should probably read this article to understand UNION ALL views:

http://www-128.ibm.com/developerwork...202zuzarte.pdf
Just to clarify, if the C2 column is defined as a DATE in DB2, then Where
clause should look like this:
WHERE C2 = '2005-06-05'

If the column is defined as CHAR(8), then you could use
WHERE C2 = '05062005'

A DATE column only takes up 4 bytes of storage in DB2.
Nov 29 '06 #12


On Nov 29, 9:57 am, "Mark A" <nob...@nowhere.comwrote:
"Mark A" <nob...@nowhere.comwrote in messagenews:ib******************************@comca st.com...
It will use the index.
You should probably read this article to understand UNION ALL views:
http://www-128.ibm.com/developerwork...0202zuz...Just to clarify, if the C2 column is defined as a DATE in DB2, then Where
clause should look like this:
WHERE C2 = '2005-06-05'

If the column is defined as CHAR(8), then you could use
WHERE C2 = '05062005'

A DATE column only takes up 4 bytes of storage in DB2.

unfortunately, it is a character column and i will convert this to date
soon
thanks for the article on partitioning using union all view
everyone in this group is awesome
i will try implement all this and will get back to you

Nov 29 '06 #13
Raj
We use the union all view and indexes are used without any issues...

Nov 29 '06 #14
Once you've converted that field to a date, you'll want to make sure
you include the date range within the view definition for each of the
sub-tables.
For example:

create view .....
select c1, c2, date_col
from table1
where date_col between '01/01/2004' and '12/31/2004'
union all
select c1, c2, date_col
from table2
where date_col between '01/01/2005' and '12/31/2005'
union all
select c1, c2, date_col
from table3
where date_col between '01/01/2006' and '12/31/2006'

If you do this, then any queries off the view that are for a specific
date range should hit only the qualifying tables.

Good Luck,
Greig Wise
db2admin wrote:
On Nov 29, 9:57 am, "Mark A" <nob...@nowhere.comwrote:
"Mark A" <nob...@nowhere.comwrote in messagenews:ib******************************@comca st.com...
It will use the index.
You should probably read this article to understand UNION ALL views:
>http://www-128.ibm.com/developerwork...0202zuz...Just to clarify, if the C2 column is defined as a DATE in DB2, then Where
clause should look like this:
WHERE C2 = '2005-06-05'

If the column is defined as CHAR(8), then you could use
WHERE C2 = '05062005'

A DATE column only takes up 4 bytes of storage in DB2.


unfortunately, it is a character column and i will convert this to date
soon
thanks for the article on partitioning using union all view
everyone in this group is awesome
i will try implement all this and will get back to you
Nov 29 '06 #15
unfortunately, it is a character column and i will convert this to date
soon
If the column C2 is CHAR(8) and value is 'mmddyyyy', you can change it
to DATE by the following expression.
DATE(TRANSLATE('EFGH-AB-CD', C2, 'ABCDEFGH'))

Nov 30 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Mohammed Mazid | last post: by
3 posts views Thread by Stevey | last post: by
10 posts views Thread by glenn | last post: by
53 posts views Thread by Jeff | last post: by
56 posts views Thread by spibou | last post: by
2 posts views Thread by Allan Ebdrup | last post: by
3 posts views Thread by Zhang Weiwu | last post: by
1 post views Thread by Korara | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.