View to look at current table

Hi,
We will be have two tables table_a table_b

day1 load table_a with currentdatetimestanp don't touch table_b

day2 load table_b with currentdatetimestamp don't touch table_a


so when table_b is finshed sumhow we create a view which should be intellegent to know
now switch to table_b and ignore table_a so basic idea is view which will be union
of two tables should just look for currenttimestamp table and ignore the other table
as these tables don't have key and we don't want to distrub business.

Is this possibel?
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
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
0
sam2929Author Commented:
ok with load_control_table so  lets say we load table_a once its loaded then insert indicator
a in load table you mean
0
momi_sabagCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

sam2929Author Commented:
o yes lets think abt this if i am updating lets says update take 1 sec from a to b will that impact business
0
momi_sabagCommented:
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)
0
sam2929Author Commented:
or lets say if sumone is running query aginst indicator a will tht not cause lock if we want to
update indicator b
0
sam2929Author Commented:
other thing is
how you join table_a/b & load_control_table ?
0
momi_sabagCommented:
just like in the query i posted earlier
0
Kent OlsenData Warehouse Architect / DBACommented:

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;

Open in new window

0
sam2929Author Commented:
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' ?


0
tliottaCommented:
...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


0
momi_sabagCommented:
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'
0
sam2929Author Commented:
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
0
momi_sabagCommented:
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
0
sam2929Author Commented:
ok i will test your solution first
0
sam2929Author Commented:
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
0
momi_sabagCommented:
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
0
sam2929Author Commented:
how can i check which table is active and which one to load?
0
momi_sabagCommented:
you have the control table with a single row indicating the active one
so just use the control table
0
sam2929Author Commented:
can you please provide sql
0
momi_sabagCommented:
i don't understand which code you want me to provide
0
sam2929Author Commented:
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
0
sam2929Author Commented:
any thoughts?
0
momi_sabagCommented:
try something like:

declare active int

select active_table into active from your_control_table;

if (sqlcode = 100)
  active = 2;
end if

if (active = 1)
   insert into table_b select * from source_table
   update your_control_table set active = 2
   commit;
   truncate table table_a;
else
   insert into table_a select * from source_table
   update your_control_table set active = 1
   commit;
   truncate table table_b;
end if
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.