[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

View to look at current table

Posted on 2011-10-06
24
Medium Priority
?
261 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:sam2929
  • 12
  • 10
  • +1
24 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36925836
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
 

Author Comment

by:sam2929
ID: 36926243
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36926259
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:sam2929
ID: 36926320
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36926371
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
 

Author Comment

by:sam2929
ID: 36926373
or lets say if sumone is running query aginst indicator a will tht not cause lock if we want to
update indicator b
0
 

Author Comment

by:sam2929
ID: 36926406
other thing is
how you join table_a/b & load_control_table ?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36926531
just like in the query i posted earlier
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 36926813

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
 

Author Comment

by:sam2929
ID: 36927670
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
 
LVL 27

Expert Comment

by:tliotta
ID: 36928058
...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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36930698
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
 

Author Comment

by:sam2929
ID: 36931545
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36931874
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
 

Author Comment

by:sam2929
ID: 36933748
ok i will test your solution first
0
 

Author Comment

by:sam2929
ID: 37027961
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37029089
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
 

Author Comment

by:sam2929
ID: 37029534
how can i check which table is active and which one to load?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37031120
you have the control table with a single row indicating the active one
so just use the control table
0
 

Author Comment

by:sam2929
ID: 37032474
can you please provide sql
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37033542
i don't understand which code you want me to provide
0
 

Author Comment

by:sam2929
ID: 37034142
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
 

Author Comment

by:sam2929
ID: 37039642
any thoughts?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 37045943
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question