Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    62

    Unanswered: Delete logs archived to TSM by date ?

    Hello,

    db2 v9.7 FP5

    I mean "delete logs older than" backed up natively to tsm using option logarchmeth1=tsm.

    I was writing backup+log delete script. And used tool db2adutl for this purpose.
    When I mean "delete logs older than" I mean by date - all logs archived earlier than given point in time.
    But db2adutl "older than" option means not older timestamp but smaller log number.
    db2adutl query example (which works the same way as db2adutl delete):

    Code:
    db2adutl query logs older than S0077914
       Log file: S0077911.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-29-23.02.48
       Log file: S0077912.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-30-01.41.59
       Log file: S0077913.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-30-04.24.38
       Log file: S0077914.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-30-08.31.20
       Log file: S0000000.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-11-09.59.32
       Log file: S0000001.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-11-10.08.21
       Log file: S0000002.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-11-10.24.49
       Log file: S0000003.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-11-14.17.40
       Log file: S0000004.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-11-16.37.15
       Log file: S0000005.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-11-20.46.01
       Log file: S0000006.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2015-01-12-00.56.36
    In this example new log sequence was started and since db2adutl "older than" operates on log numbers not date command
    Code:
    db2adutl delete logs older than S0077914
    will delete all logs listed above while the intention was to delete:
    Code:
       Log file: S0077911.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-29-23.02.48
       Log file: S0077912.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-30-01.41.59
       Log file: S0077913.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-30-04.24.38
       Log file: S0077914.LOG, Chain Num: 6, DB Partition Number: 0, Taken at: 2014-11-30-08.31.20
    Only "db2adutl query logs" without any additional arguments lists logs by archive date.
    Only way I see to delete by date is to select logs from "db2adutl query logs" by own logic and then execute multiple "db2adutl delete logs between" commands. But that is a bit complex.

    Is there any simpler way to do it ?

    Thanks
    Vilius M.
    Last edited by vilius; 06-18-15 at 06:20.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    with db2adutl there is also an option

    -OLDER- '-THAN-' '-n--days---'
    see : http://www-01.ibm.com/support/knowle...2-6-11&lang=en
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2007
    Posts
    62
    That option is obviously for backups not for logs.

  4. #4
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    you actually have to delete by log number as you have to determine the oldest log required for the oldest full backup. if you delete by date you may remove a required log.
    a quick and dirty way to determine the oldest log is
    db2adutl query full nonincremental db "$DB_name" | grep "Time" | cut -c42-48 | sort -n -r
    and pick the first off the list and then subtract 1 from it
    or you can interrogate sysibmadm.db_history to dtermine the oldest log associated with the oldest backup once you have pruned the backups and know the oldest backup date
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  5. #5
    Join Date
    Nov 2007
    Posts
    62
    I have no problem determining oldest log, I have problem deleting logs "older" than that log.
    Lets take my example: lets say oldest log for last backup to keep is S0077914.LOG so command:
    Code:
    db2adutl delete logs older than S0077913 without prompting
    will delete logs S0077913.LOG-S0077911.LOG and latest logs S0000000.LOG-S0000006.LOG.
    I just blasted latest logs of my test db because of this - of course new log sequence start is not frequent event but you never know.
    Last edited by vilius; 06-18-15 at 20:32.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    but as indicated in doc : chain is also supported
    LOGS--+----------------------------+--+----------+-------------------------'
    '-+-BETWEEN--sn1--AND--sn2-+-' '-CHAIN--n-'
    '-OLDER THAN--sn1--------'
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Nov 2007
    Posts
    62
    I'm talking about single chain here.
    All my examples(and db I lost logs) had only one chain but new log sequence was started somewhere in the middle.

  8. #8
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    As "przytula_guy" is suggesting get rid of the old chain in one-time single operation with the given command - if those are not required anymore.

    For periodic cleanup (from new chain) I use what "tafster" is suggesting.

    hope that helps.
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

Posting Permissions

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