Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Unanswered: how to automate a delete SQL stmt

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Depends on the platform ..

    If on Linux/Unix, you can wrap this statement in a shell script - which logs deletion info, handles and reports on errors etc and use crontab to schedule.

    If on Windows, a batch script with similar functionality as above and use some kind of scheduler. Read up on db2cmd command if you go the .bat way.

    Or consdier using DB2's Task Scheduler (wouldn't recommend it)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2013
    Posts
    5
    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 -

  4. #4
    Join Date
    Feb 2013
    Posts
    5
    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
    [db2inst1@db2 ~]$

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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)"
    Last edited by sathyaram_s; 02-27-13 at 13:07.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2013
    Posts
    5
    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

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Look at my example carefully. I have used double quotes for the entire SQL statement , so don't need all the escape chars. If your stmt has double quotes, then you escape those quotes.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Feb 2013
    Posts
    5
    thanks - I got it working. I appreciate the help

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    There are double from in the exists clause

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •