Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2012
    Posts
    12

    Unanswered: How to schedule automatic db2 backup

    Hi,
    Windows 2003 sp2, db2 v9.5

    In our system, Auto-backup of db2 is already taken place every day, it was set last time by vendor but I observed there is something missing or wrong that backup is not being taken properly by db2 system.

    I found there is some difference in back up files (create every day) name such as;

    [*] MAXPROD.0.CTGINST1.NODE0000.CATN0000.2012031523010 0.001 –
    this backup file created on 03/15/2012 11:11 PM with size 3,860,400 KB
    [*]MAXPROD.0.CTGINST1.NODE0000.CATN0000.2012040403045 2.001 –
    this backup file created on 04/04/2012 3:14 AM with size 3,878,856 KB
    [*]MAXPROD.0.MAXPROD.NODE0000.CATN0000.20120409062020 .001 –
    this backup file created on 04/09/2012 6:33 AM with size 4,995,444 KB
    [*]MAXPROD.0.MAXPROD.NODE0000.CATN0000.20120410054402 .001 -
    this backup file created on 04/10/2012 5:58 AM with size 5,001,596 KB
    [*] Another thing I have noticed the size of backup files varies, meaning some
    times next day backup file size is lesser than previous day file size,
    for example;
    today backup file is;
    MAXPROD.0.MAXPROD.NODE0000.CATN0000.20120411052104 .001 -
    this backup file created on 04/11/2012 5:35 AM with size 4,998,520 KB
    [*] My concerns ;
    ------------
    >> you will notice in above first 2 files names are same like ‘CTGINST1’ appeared in the middle of file, but in last 2 files names are different and ‘MAXPROD’ appeared instead of ‘CTGINST1’ in the middle of files.
    >> Daily Backup time is not constant, I want auto-backup should be taken at night 04:00 AM
    >> There is no weekly and monthly backup at database level
    >> In Oracle there are redo logs files option is available and can be configured which helps system not to lose any seconds/minute of data as user punches in the system, How to configure same thing in db2 database?

    I will be happy any one helps me step by step.

    Regards
    Shah

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For the following file:
    MAXPROD.0.CTGINST1.NODE0000.CATN0000.2012031523010 0.001

    MAXPROD is the database
    CTGINST1 is the instance

    So looking at the other backup files, you have more than one instance on the database server. A DB2 instance is not the same as an Oracle instance.

    I would not recommend that automatic DB2 backups be used. Write your own backup scripts and run them via a scheduler of some type.

    If you want rollforward recovery after a restore of backup, you will need to activate LOGARCHMETH1 in db config. You need to do some reading in the manual about logging for recovery. The default is circular logging (not sure which you have).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2012
    Posts
    12

    How to schedule automatic db2 backup

    Hi Marcus_A,

    Thanks for giving prompt reply.

    Yes, you are right there are 2 instances in db2. Does it mean there are two automatic backup one for 'CTGINST1' and other one is for 'MAXPROD', but how can I see CTGINST1 related backup procedure in db2?

    Questions:
    ---------
    Q1. Why last file size is lesser than previous file size? See as Ref. 04/10/2012
    and 04/11/2012 backup files.

    Q2. As you raised the point 'Write your own backup scripts and run them via a
    scheduler of some type', I do not know how write backup scripts for
    MAXPROD(db) and how to run/configure scripts by scheduler? Can you
    help me in this regards?

    Q3. Could you provide me manual about logging for recovery or help me where
    can I find it?

    Q4. How to find default log status meaning circular or not?

    Q5. Where can I find db config and how to activate LOGARCHMETH1 in db
    config?

    I will appreciate your prompt and positive response.

    Regards
    Shah

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You will find everything here :

    IBM DB2 9.7 Information Center


    Quote Originally Posted by db2mx View Post
    Q1. Why last file size is lesser than previous file size? See as Ref. 04/10/2012
    and 04/11/2012 backup files.
    how much different is it ? not clear from your previous post

    Q2. As you raised the point 'Write your own backup scripts and run them via a
    scheduler of some type', I do not know how write backup scripts for
    MAXPROD(db) and how to run/configure scripts by scheduler? Can you
    help me in this regards?
    On windows, you can write batch scripts that use dos commands.

    An Introduction to DB2 UDB Scripting on Windows

    should be a starting point. Scheduler - no idea what native scheduler windows has . I am sure you will be able to figure this out

    Q3. Could you provide me manual about logging for recovery or help me where
    can I find it?
    An Overview of Transactional Logging in DB2 Universal Database

    Q4. How to find default log status meaning circular or not?
    dev works article of Q3

    Q5. Where can I find db config and how to activate LOGARCHMETH1 in db
    config?
    See the info centre link above. Basically, it is similar to the user exit described in the developerworks article. But using a different configuration parameter.

    I will appreciate your prompt and positive response.
    prompt? What? Are you paying for the service

    I will be happy any one helps me step by step.
    Well, again ... Dont expect spoon feeding or step-by-step hand holding on a free forum.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Apr 2012
    Posts
    12

    How to schedule automatic db2 backup

    Hi sathyaram_s,

    Thanks for reply. All answers of the questions were good enough to understand. Actually I am a beginner in db2, I did not mean to consume your time by asking questions or irritate you. I would clarify the following Quotes as you answered.

    Quote:
    I will appreciate your prompt and positive response.
    prompt? What? Are you paying for the service

    [Clarify]: it was a request with advance appreciation, prompt meaning a
    response in time if it is possible. I know it is a free forum for db2
    help.

    Quote:
    I will be happy any one helps me step by step.
    Well, again ... Dont expect spoon feeding or step-by-step hand holding on a free forum.

    [Clarify]: I joined your forum for db2 help. I am new on your forum and today
    is my first day I do not know what rules you have set for
    communication on your forum. I think the word 'Spoon Feeding' is
    inappropriate on the forum. It might discourage the people and hurt
    their feelings. It seemed to me you minded.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    this discussion is not finished.. it will open many reactions..
    what I think of this : I completely agree with the moderator
    many companies have their db2 db outsourced to different countries and let the db be handled by people not knowing DB2 at all. whenever they have a problem - before looking at the message guide or any other doc - they open a thread in the forum and expect other people to give them a complete solution to resolve their problem.
    I believe the forum is a place to share ideas and ask your questions but at least we expect people to have a look at the doc or at least try to understand db2..
    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
    Apr 2012
    Posts
    12
    There is slight confusion occurring and you people perceiving me wrong.

    Let me introduce myself I have been dba and developer in oracle 8i, 9i, 10g, and 11g for years. I move into db2 database six months back, I know how to write procedure/trigger/sql statements. As far as db2 database is concerned I have explored new things as a dba which I had no idea about them earlier, that is why I am saying I am new db2. Now a days I am facing db2 issues, some of them I resolved by myself and rest are still are out of my knowledge. Even I appreciated and happy moderator reply who at the begin of his message helped me by saying "You will find everything here ". That was quite sufficient statement to me dig out further related topics. But when I went down of the message I got surprised by his quotes like 'Spoon Feeding'. Might be the way I wrote my request could be not up to the mark as you people deal on forums. Meanwhile, you stepped in conversation by saying which is irrelevant to me I do not belong to that category which you specify. I am involved in development where I am using 2 databases oracle and db2, also supervising database administration.
    Kindly stop this conversation which is going on wrong direction which I did not mean to say. I welcome suggestion and always appreciate people who really help people like me. I accept you would follow your given information more properly so that I could be good asset on this forum. Kindly let me in on this forum for discussing further db2 issues.
    I hope you people would not mind anymore allowing me for further discussion.

    Regards

  8. #8
    Join Date
    Dec 2009
    Posts
    62
    Shah,

    I'd say you should turn off automatic backup and configure a new backup task on Task Center.
    I use it with plenty of success.
    Follows the command line on mine:

    BACKUP DATABASE mydb ONLINE TO "F:\BACKUP" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 COMPRESS COMPRLIB "C:\Program Files\IBM\SQLLIB\BIN\db2compr.dll" EXCLUDE INCLUDE LOGS WITHOUT PROMPTING;

    The "COMPRLIB" statement use the default compress library of DB2 to make smaller backup files. Remember though that if in case of a restore the same compress library will have to be indicated.

    If you don't have the TOOLSDB database installed, you may use the following command line to create it. This is necessary if you want to use tools like the Task Center:

    db2 create tools catalog systools create new database toolsdb

    Open the Task Center utility and you may right click to open a [NEW] task. You'll have to choose or create a Task Category before you can save it. The screens then are almost self explanatory. The main tabs though are "Task", "Command Script" and the "Schedule" tab. Others are optional. I'd say you keep "Notification" and "Task Actions" off for now.

    Also, it'd be good to configure archival logging, instead of circular logging. You may use the Command Line Processor to accomplish this.
    Usually you "get database configuration for mydb" first, so you see what the current configuration is.
    Then you should change a cfg value with "update database configuration using PARAMETER VALUE". The parameter name is showed at the right of the complete name, followed by the current value, when you get db cfg.

    BTW, it's also the way you change automatic maintenance items, at the bottom of the list.


    So you should have the following items changed to have archive logging:

    LOGRETAIN recovery
    LOGARCHMETH1 disk:c:\path

    Note that "c:\path" is an existing folder on a disk of your server, or storage device. For tape devices there must be another option that I actually don't know right now.

    The "Path to log files" cannot be changed directly. If you want to change it, you'll have to update NEWLOGPATH with the complete path to the chosen location.

    The LOGARCHMETH1 parameter indicates where older log files are stored. So it's good to keep an eye on disk space and clean it up from time to time.

    Remember, some parameters of db cfg are not really updated until you reinitiate the DB2 service. The automatic maintenance parameters are updated immediately.

    I have Windows server 2008 running a DB2 9.5 here.

    I hope it helps a little. I'm not the super topper hyper experienced with no time to help the others kind of DB2 specialist. That's why I try to give you details of how it works for me, so I can fix and learn with it too ;-)

    If there are any doubts in the way I explain things (English is not my first language...) please feel free to ask.

    BTW, I have Windows server 2008 running DB2 9.5 here.

    cheers

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by zevang View Post
    So you should have the following items changed to have archive logging:

    LOGRETAIN recovery
    LOGARCHMETH1 disk:c:\path
    Only need to change LOGARCHMETH1.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Dec 2009
    Posts
    62
    Yes indeed, Marcus. Thanks.

    But if I don't change LOGRETAIN, am I still able to make ROLL FORWARD after doing a restore?

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by zevang View Post
    Yes indeed, Marcus. Thanks.

    But if I don't change LOGRETAIN, am I still able to make ROLL FORWARD after doing a restore?
    Yes. You will be able to. Setting logarchmeth1 enables linear logging and hence rollforward recovery.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Dec 2009
    Posts
    62
    Quote Originally Posted by sathyaram_s View Post
    Yes. You will be able to. Setting logarchmeth1 enables linear logging and hence rollforward recovery.
    Thanks :-)

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2mx,
    Didn't mean to offend you. Apologies if it has.

    But as Guy said above, you will be able to make the best use of the forum if you do your basic research.

    Don't expect step-by-step instructions on how to do something. You are certain to get pointers/links that will help you with your question. You can always come back for more info or clarification.

    Asking for "urgent help" or "prompt response" is generally considered unsuitable on public forums, and this board is no exception.

    Hope you will continue to find this forum useful.

    Cheers

    Sathyaram




    Quote Originally Posted by db2mx View Post
    There is slight confusion occurring and you people perceiving me wrong.

    Let me introduce myself I have been dba and developer in oracle 8i, 9i, 10g, and 11g for years. I move into db2 database six months back, I know how to write procedure/trigger/sql statements. As far as db2 database is concerned I have explored new things as a dba which I had no idea about them earlier, that is why I am saying I am new db2. Now a days I am facing db2 issues, some of them I resolved by myself and rest are still are out of my knowledge. Even I appreciated and happy moderator reply who at the begin of his message helped me by saying "You will find everything here ". That was quite sufficient statement to me dig out further related topics. But when I went down of the message I got surprised by his quotes like 'Spoon Feeding'. Might be the way I wrote my request could be not up to the mark as you people deal on forums. Meanwhile, you stepped in conversation by saying which is irrelevant to me I do not belong to that category which you specify. I am involved in development where I am using 2 databases oracle and db2, also supervising database administration.
    Kindly stop this conversation which is going on wrong direction which I did not mean to say. I welcome suggestion and always appreciate people who really help people like me. I accept you would follow your given information more properly so that I could be good asset on this forum. Kindly let me in on this forum for discussing further db2 issues.
    I hope you people would not mind anymore allowing me for further discussion.

    Regards
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  14. #14
    Join Date
    Apr 2012
    Posts
    12
    Agreed, and I am thankful to welcome me on forum. Thanks for advice I will follow.

Posting Permissions

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