Topic
  • 5 replies
  • Latest Post - ‏2012-12-13T17:16:54Z by mwandishi
jb_genier
jb_genier
3 Posts

Pinned topic Problem with backup deletation

‏2012-12-13T14:39:23Z |
Hello,
I use DB2 server 9.7 on Windows and I have an issue.
I backup my database with something like that:

CONNECT TO WAREHOUS; QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS; CONNECT RESET; BACKUP DATABASE WAREHOUS TO 
"D:\DB2\NODE0000\SQL00003\DB2BACKUPS" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING; CONNECT TO WAREHOUS; UNQUIESCE DATABASE; CONNECT RESET;


But after when I use the PRUNE command with the option : AND DELETE, the file is not deleted (even if it's not the last one) in the folder.

PRUNE HISTORY 20121212 AND DELETE;

The backup history is well cleaned but not the folder.
May be someone can help me ?

Take care,
JB GENIER
Updated on 2012-12-13T17:16:54Z at 2012-12-13T17:16:54Z by mwandishi
  • mwandishi
    mwandishi
    47 Posts

    Re: Problem with backup deletation

    ‏2012-12-13T14:48:14Z  
    Hi JB,

    What are AUTO_DEL_REC_OBJ & NUM_DB_BACKUPS set too?

    Regards,
    Stephen
  • jb_genier
    jb_genier
    3 Posts

    Re: Problem with backup deletation

    ‏2012-12-13T15:13:06Z  
    • mwandishi
    • ‏2012-12-13T14:48:14Z
    Hi JB,

    What are AUTO_DEL_REC_OBJ & NUM_DB_BACKUPS set too?

    Regards,
    Stephen
    Hi Stephen,
    I'm a newbie and unfortunately I don't know how to check with control center.
    would you like helping me ?

    Regards,
    JB GENIER
  • mwandishi
    mwandishi
    47 Posts

    Re: Problem with backup deletation

    ‏2012-12-13T16:04:18Z  
    • jb_genier
    • ‏2012-12-13T15:13:06Z
    Hi Stephen,
    I'm a newbie and unfortunately I don't know how to check with control center.
    would you like helping me ?

    Regards,
    JB GENIER
    Hi JB,

    The simplest method would be to check the db cfg via the CLP, as follows:

    'Start' - 'Programs' - 'IBM DB2' - Select the 'copy name'. Then there should be a command line option, I am afraid I don't have a windows server in front of me so I can't be more exact. The next step would be to select a 'command window'.

    Note: A 'command window' looks just like an OS cmd prompt. I use this in preference to the full blown CLP which has the 'db2 =>' prompt. It just means you can utilise DB2 & OS commands, which I personally prefer.

    Once you have this you can run: db2 get db cfg for WAREHOUS
    Then page through the output looking for: AUTO_DEL_REC_OBJ & NUM_DB_BACKUPS
    Or run: db2 get db cfg for WAREHOUS > dbcfg.out

    This is a Unix ( AIX ) example for reference:

    db2v10: db2 get db cfg for sample10| egrep -i "NUM_DB_BACKUPS|AUTO_DEL_REC_OBJ"
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

    I suspect AUTO_DEL_REC_OBJ = OFF ( default ) which means we don't physically delete the corresponding physical log files, backup images, and load copy images when we prune the history file.

    If you can post the output into this thread or attach it ( might be preferable ) then I can check it over.

    Best regards,
    Stephen.
  • jb_genier
    jb_genier
    3 Posts

    Re: Problem with backup deletation

    ‏2012-12-13T16:17:32Z  
    • mwandishi
    • ‏2012-12-13T16:04:18Z
    Hi JB,

    The simplest method would be to check the db cfg via the CLP, as follows:

    'Start' - 'Programs' - 'IBM DB2' - Select the 'copy name'. Then there should be a command line option, I am afraid I don't have a windows server in front of me so I can't be more exact. The next step would be to select a 'command window'.

    Note: A 'command window' looks just like an OS cmd prompt. I use this in preference to the full blown CLP which has the 'db2 =>' prompt. It just means you can utilise DB2 & OS commands, which I personally prefer.

    Once you have this you can run: db2 get db cfg for WAREHOUS
    Then page through the output looking for: AUTO_DEL_REC_OBJ & NUM_DB_BACKUPS
    Or run: db2 get db cfg for WAREHOUS > dbcfg.out

    This is a Unix ( AIX ) example for reference:

    db2v10: db2 get db cfg for sample10| egrep -i "NUM_DB_BACKUPS|AUTO_DEL_REC_OBJ"
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

    I suspect AUTO_DEL_REC_OBJ = OFF ( default ) which means we don't physically delete the corresponding physical log files, backup images, and load copy images when we prune the history file.

    If you can post the output into this thread or attach it ( might be preferable ) then I can check it over.

    Best regards,
    Stephen.
    Hi Stephen,
    Thanks for your help. You were right AUTO_DEL_REC_OBJ was OFF.
    I set it to ON and the two others parameters REC_HIS_RETENTN = 25 and NUM_DB_BACKUPS = 2.
    And now it works fine.

    Thanks a lot and take care.

    JB Genier.
  • mwandishi
    mwandishi
    47 Posts

    Re: Problem with backup deletation

    ‏2012-12-13T17:16:54Z  
    • jb_genier
    • ‏2012-12-13T16:17:32Z
    Hi Stephen,
    Thanks for your help. You were right AUTO_DEL_REC_OBJ was OFF.
    I set it to ON and the two others parameters REC_HIS_RETENTN = 25 and NUM_DB_BACKUPS = 2.
    And now it works fine.

    Thanks a lot and take care.

    JB Genier.
    Hi JB,

    Excellent, no problem at all.

    Regards,
    Stephen Levett.