Hello, i'm new to DB2. I'd like to know how I can automate a DELETE statement to run on a daily or weekly basis? My delete statement is below:
DELETE FROM "schema".table
where c_id in
(SELECT x.c_id FROM "schema".table2 as x
where x.END_TS < current date - 160 days);
can someone advise me on what the best/easiest way is to wrap this into a job that can run on a weekly basis? thanks - C
thanks for the quick reply. I'm on a Linux server so I think the easiest thing will be a cronjob. When I posed my question, I was thinking more of using the Data Studio tool to schedule a job. thanks -
maybe you can help with another question. I'm trying to run the delete from the CLI but I keep getting an error. There's some sort of syntax error. Can you check below please?
[db2inst1@db2 ~]$ db2 'DELETE FROM "BELLMO".recipient y where exists (SELECT 'x' from FROM campaign as x where y.campaign_id = x.campaign_id and x.END_TS < current date - 170 days)'
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "as" was found following "x from FROM campaign".
Expected tokens may include: ",". SQLSTATE=42601
hi thanks for adding the escape characters. I added a couple more. but now I get a bash error as you can see below. So there's still something wrong with the syntax. Can you see anything? thanks again
[db2inst1@db2 ~]$ db2 DELETE FROM \"BELLMO\".recipient where campaign_id in \(SELECT x.campaign_id FROM \"BELLMO\".campaign as 'x' where x.END_TS < current date - 160 days\);
-bash: current: No such file or directory