How can I use sql db2 update function?

Question

I have a sql db2 code that get information on a existing data table and I want to modify it to not download the data starting from 2020-02-01, instead just update this table everyday. Please help me to modify this script not to retrieve the whole data over and over again just to update but keeps on updating only the new data everyday.

create table public.fc_TDPMean_By_DIM2_EtchDate as 
select DIM2_EtchDate, Model, tool_id, avg(TDPower) as TDPower_Mean, count(slider_id)
from (select distinct a.slider_id, trunc(a.test_date_time), left(a.product_id,2) as Model, a.wafer_id, a.row_number, 
a.column_number, a.x_coordinate, a.y_coordinate, a.error_code, a.grade, a.bin,
a.TFCTDPWR as TDPower, b.job_number, trunc(c.transaction_date_time) as DIM2_EtchDate, c.tool_id
from ah.param_jade_wide a left join ah.param_lap_summary b on a.wafer_id = b.wafer_id and a.row_number = b.row_number
left join ah.his_job c on c.job_number = b.job_number
where c.transaction_date_time > '2020-02-01'
and left(a.product_id,2) in ('L2','L3','L8','C3','C2','V8')
and b.source_system_code in ('MFG2.SLDR.LAPRUN')
and c.operation_id in ('545600')
and a.retest_number = 0
and a.class_description in ('PROD')
and not c.tool_id = 0 and not c.tool_id in ('') )
group by DIM2_EtchDate, Model, tool_id;
commit;

What are the exact rules for "update this table everyday"? An ability to do this depends on data change nature for all these 3 tables. Are you sure, that that your select with, let's say, date(c.transaction_date_time) = some_previous_date condition returns always the same data, disregarding on when you run it after this some_previous_date date? In other words, do you have any backdated updates to these tables? — Mark Barinstein
– Mark Barinstein, Commented May 4, 2020 at 14:12
I'm just running it everyday to update my data since it always get the greater than date. Since the table public.fc_TDPMean_By_DIM2_EtchDate is being created every day, I just want to get the data today and updated it to the table public.fc_TDPMean_By_DIM2_EtchDate. I am newbie with db2 and I don't know how can I just updated it and not create the table everyday. — Julius Benn Sabeniano
– Julius Benn Sabeniano, Commented May 4, 2020 at 15:58

Pavol Adam · Accepted Answer · 2020-05-05 11:58:40Z

As You want to add a new "day" entry/entries every day (as DIM2_EtchDate == DATE(c.transaction_date_time) is a part of the summary table), and if You are OK with not selecting "today's" entries, You can just use a simple INSERT for "yesterdays and before" entries, run every day like this:

INSERT INTO public.fc_TDPMean_By_DIM2_EtchDate
  SELECT DIM2_EtchDate, Model, tool_id, avg(TDPower) AS TDPower_Mean, count(slider_id)
    FROM (SELECT DISTINCT a.slider_id, LEFT(a.product_id,2) as Model, 
a.TFCTDPWR AS TDPower, TRUNC(c.transaction_date_time) as DIM2_EtchDate, c.tool_id
-- (I got rid of not used columns.)
            FROM ah.param_jade_wide a
              LEFT JOIN ah.param_lap_summary b ON (a.wafer_id, a.row_number) = (b.wafer_id, b.row_number)
              LEFT JOIN ah.his_job c ON c.job_number = b.job_number
            WHERE c.transaction_date_time > '2020-02-01' --MAX(DIM2_EtchDate)
              AND c.transaction_date_time < CURRENT DATE
              AND LEFT(a.product_id,2) IN ('L2','L3','L8','C3','C2','V8')
              AND b.source_system_code IN ('MFG2.SLDR.LAPRUN')
              AND c.operation_id IN ('545600')
              AND a.retest_number = 0
              AND a.class_description IN ('PROD')
              AND NOT c.tool_id = 0 AND NOT c.tool_id in ('')
         )
    GROUP BY DIM2_EtchDate, Model, tool_id
-- WITH appropriate isolation
;

(I haven't tested it. This should work if c.transaction_date_time is DATE. If it is TIMESTAMP, then c.transaction_date_time > '2020-02-01' changes to c.transaction_date_time >= TIMESTAMP('2020-02-01', '00:00') + 1 DAY.)

If You would want to update it more often than once per day, having also incomplete data for today, it could be done using MERGE. If so, just tell me, I could manage to write a MERGE for it. (Or You could, that would be better.)

Hi Sir Pavol, this is great. If not too much, yes, please write me merge code.

Collectives™ on Stack Overflow

How can I use sql db2 update function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related