View to look at current table

possible yes
but probably not very efficient

i would think of maybe a different solution
have another table - call it load control.
that table will have 1 row with one column called indicator which will contain wither 'a' or 'b'
then your query can be

select *
from table_a, load_control_table
where load_control_table.indicator = 'a'
union all

select *
from table_b, load_control_table
where load_control_table.indicator = 'b'



once you finish to load, just update the indicator column
if you are using db2 for zos v10, you might want to consider clone tables

ok with load_control_table so  lets say we load table_a once its loaded then insert indicator
a in load table you mean

yes
but once you inserted the indicator row once, you don't insert again
you just update the existing row to point to the current copy

o yes lets think abt this if i am updating lets says update take 1 sec from a to b will that impact business

1. update does not take 1 second. much less
2. at any point in time, the row is eiter a or be, so until the update is complete, the business is getting the old values (just like during the actual load operation)

or lets say if sumone is running query aginst indicator a will tht not cause lock if we want to
update indicator b

other thing is
how you join table_a/b & load_control_table ?

just like in the query i posted earlier

Hi Sam,

I think that I would solve this with a stored procedure that creates the view(s) based on the table contents.

It needs some sanity checks, but the code below is a great start.


Kent

CREATE PROCEDURE make_views
....
  DECLARE t1_date timestamp;
  DECLARE t2_date timestamp;

  SELECT max(row_date) INTO t1_date FROM table1;
  SELECT max(row_date) INTO t2_date FROM table2;

  IF (t1_date > t2_date) THEN
    EXECUTE IMMEDIATE 'DROP VIEW table_view';
    EXECUTE IMMEDIATE 'CREATE VIEW table_view AS SELECT * FROM table1;
  ELSE
    EXECUTE IMMEDIATE 'DROP VIEW table_view';
    EXECUTE IMMEDIATE 'CREATE VIEW table_view AS SELECT * FROM table2;
  END IF;

END;

Select allOpen in new window

lets say table a
looks like table_a
col1 col2 col3 col4
100  101  201  200
101  100  201  200
100  101  201  200

and load_table
col1
a
how  can join where load_control_table.indicator = 'a' ?

...i am updating lets says update take 1 sec from a to b will that impact business

To me, simply asking a question like that implies that the view (as well as tables) will be expected to be in use essentially 24x7. And whenever anything is "always" in use, recreating it will have an impact to one active business process at least, possibly many. What the impact might be and how to handle it will depend on what those processes are.

Tom

as i wrote earlier

create view my_view as


select *
from table_a, load_control_table
where load_control_table.indicator = 'a'
union all

select *
from table_b, load_control_table
where load_control_table.indicator = 'b'

Hi Kent,
How about keeping the view in place,where the view is a union of t1 & t2 and based on the max(row_date), delete all rows from  the older table then, there's no DDL involved.

Thanks

ddl is not good because as long as someone is using the view, it can't be dropped
using max(row_date) can also be a performance impact

i don't understand why you don't like my solution

ok i will test your solution first

yes it works good now i need control table to drive table_a and table_b so how can we do this in one procedure.

Load table_a update control table flag to 'Y' don't touch table b.

Next run i guess we need some logic here to see if table b is empty or we can use timestamp to see which table needs to be run based upon timestamp so next run load table_b as it is empty and then update control table

i think control table should have timestamp and flag both

and so on...

can we do this logic in sql or procedure

i don't understand what you are trying to do

why not having a single row in the control table indicating which table is active
then, if you need to load a table, you check which one is active,
you load the second one and then update the control table to point at the one you just loaded

how can i check which table is active and which one to load?

you have the control table with a single row indicating the active one
so just use the control table

can you please provide sql

i don't understand which code you want me to provide

The code i am asking is

lets say i just have three empty tables table_a and table_b and control_table

both table_a and table_b pulls data from same table table_source (table_a and table_b are new tables so we can add
additional columns like current timestamp etc )

so how can we do all in one code which says
1) insert data in to table_a from table_source then update control_table
2) Second time insert data in table_b from table_source then update control_table and delete table_a
3) Third time insert data in to table_a from table_source then update control_table and delete table_b

and so on

any thoughts?