Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Understanding DB2 Logs and sizing

    Hi,

    I have a DB2 9.7 Database and I try to size the file system I need in order to have sufficient place in my db, logs, mirrored logs and archive logs file system.

    The active logs are in a file system DB/logs
    The mirror logs are in a file system DB/mirror/logs

    I have configured the logarchmeth1 parameter of the database to:
    DISK:/ArchiveLogs/ then my archive logs are in this file system

    I have a backup plan as the following :
    Code:
    (sun)		OFFLINE	FULL BACKUP 
    (mon)(thu)	ONLINE	INCREMENTAL DELTA BACKUP
    (wen)		ONLINE	INCREMENTAL BACKUP
    (tur)(fri)	ONLINE	INCREMENTAL DELTA BACKUP
    (sat)		ONLINE	INCREMENTAL BACKUP
    My backups are saved in a /backup/ file system.


    I am trying to size all my file system :
    db/logs/
    db/mirror/logs/
    /ArchiveLogs/
    /backup/


    I don't understand how does the logs works.
    Then, I have some questions about that

    I've seen the active logs are monitored in the CFG command :
    Code:
    Log file size (4KB)                         (LOGFILSIZ) = 10000
    Number of primary log files                (LOGPRIMARY) = 10
    Number of secondary log files               (LOGSECOND) = 12
    1 - if I'm right, this means that I can have only 10 files of 40MB in my db/log file system.
    2 - What are the "secondary log files" then ?

    3 - I have seen when I do a FULL OFFLINE BACKUP, every usefull active logs are moved in the Archive logs path. Is that Right ?
    4 - Does the FULL OFFLINE BACKUP save the active logs ?
    5 - Does the FULL OFFLINE BACKUP save the archive logs ?

    6 - When I do an INCREMENTAL OR DELTA BACKUP, a part of the "active" active logs referenced in the line below is saved in a log file with the same name (N60 here) but none the same size . Then the line below is incremented (here goes to 61) an other log of 40MB is created in order to have 10 files of active logs (here N70 : 61 to 69 already created and 70 is created now).
    Code:
     First active log file                                   = S0000060.LOG

    Then, in my active log file system, I got 10 files of 40MB AND the new file created by the INCREMENTAL or DELTA backup.

    How does I know what is the max size of this file system ?

    So, what must I save in order to be able to come back at every moment in case of disaster
    Every backup and logs?
    Only backup and active logs ?
    Only backup ?
    FULL OFFLINE Backup, other backup and some logs from the last full offline backup ?

    Thank you for your help.

    Alex
    Last edited by fta; 06-23-11 at 05:25.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Data changes to tables in DB2 are usually applied in memory only and written to disk at a later point in time. Since memory is not very reliable in case of a power outage, DB2 logs all those changes additionally (and guarantees that those logs are written to disk when a transaction is committed). On the side, logs are also used to roll back transactions. So logs are very important (and they should be written to a fast device.)

    In order to apply incremental/delta backups, you need a full backup before. Additionally, you'll need the logs to recover to a point between backups. Thus, you'll need at least:
    (0) use archive logging (not circular)
    (1) at least one full backup
    (2) incremental/delta backups since (1)
    (3) logs since (2)
    If you want to recover before the full backup, you need more before that. I would probably keep more stuff around.

    The maximum size you'll need depends on the amount of activity (whether lots of log records need to be written or not) and the size of the database.

    Regarding the number of log files: you'll have active 10 primary log files (which are created when the database is started) and there may be up to 12 additional log files (which are created when needed). Active log files are those that contain log records of transactions that are not yet committed/rolled back. There will be older log files, which contain log records from already finished transactions.

    If you have a full backup, you have a (consistent) snapshot of the database/tablespace. No logs before the backup are needed. (All the logged changes are in the backup image already.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    Hi, thank you for your reply !

    However, I need some small explanations
    Quote Originally Posted by stolze View Post
    In order to apply incremental/delta backups, you need a full backup before. Additionally, you'll need the logs to recover to a point between backups. Thus, you'll need at least:
    (0) use archive logging (not circular)
    (1) at least one full backup
    I've read that I need a full OFFLINE backup in order to be able to do ONLINE backup then.
    I've also read that for the online full backup, I need to keep the logs (because some other actions are done during the backup routine by users)

    this (1) full backup is an offline or online backup ?
    Do I need a full offline backup in order to recover a full online backup ?

    Quote Originally Posted by stolze View Post
    Regarding the number of log files: you'll have active 10 primary log files (which are created when the database is started) and there may be up to 12 additional log files (which are created when needed).
    When is this "When needed" ? Because if an active log is full, then it is moved to the archive log location. I don't see when more logs can be needed.
    This means I have at maximum 10 files of 40MB (primary logs) and 12 files of 40Mb (secondary logs) in my active logs file system ?

    Quote Originally Posted by stolze View Post
    Active log files are those that contain log records of transactions that are not yet committed/rolled back. There will be older log files, which contain log records from already finished transactions.
    Then, If I want to keep the relevant active log, I need to keep every active logs or only those with the date of modification after the (2) incremental/delta backup ?

    Quote Originally Posted by stolze View Post
    If you have a full backup, you have a (consistent) snapshot of the database/tablespace. No logs before the backup are needed. (All the logged changes are in the backup image already.)
    This is an offline backup right ?

    Thank you for your help,

    Alex

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by fta View Post
    I've read that I need a full OFFLINE backup in order to be able to do ONLINE backup then.
    I'm not sure about that. What does the manual say? (I can't be bothered to look it up right now myself.) If it states to do an offline backup first, you'll have to do it. If the manual does not say so, you don't need to.

    I've also read that for the online full backup, I need to keep the logs (because some other actions are done during the backup routine by users)
    That's right. But you can use the INCLUDE LOGS option on the command: BACKUP DATABASE

    this (1) full backup is an offline or online backup ?
    Doesn't matter. Why should it? A full backup is a full backup and contains everything in the database.

    When is this "When needed" ? Because if an active log is full, then it is moved to the archive log location. I don't see when more logs can be needed.
    A log file is only archived if it doesn't contain stuff from a transaction that is not yet committed/rolled back. Such a log file is active. Log files that don't contain stuff from running transactions anymore are not active and can be archived. Now, if I have a long running transaction that did some INSERTs that were logged in the first log file, that file cannot be archived. Let's assume that some more transactions (or even the same one) cause log files 2..10 to be used. Even more operations requiring to be logged give us the "when needed" situation: another log file is needed and then a secondary log file is created.

    Then, If I want to keep the relevant active log, I need to keep every active logs or only those with the date of modification after the (2) incremental/delta backup ?
    See above. From the perspective of recovery, you don't care whether a log file is active or not. What matters is when the log file was created. If the log file was active after the backup, it is needed because it may contain committed transactions. If the log file was archived after the backup, it is also needed for the same reason. The transactions in the archived log file are not in the backup image, of course.


    This is an offline backup right ?
    Doesn't matter. Offline/online are concepts orthogonal to full vs. incremental/delta backups. They just describe whether operations were going on in the database while the backup was taken or not. Full vs. incremental/delta describes the content of the backup (everything or just changes since a certain previous event).


    I suggest that you read up a but on logging, backups and recovery in the DB2 manuals. Those concepts are described there pretty well as far as I recall.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2011
    Posts
    4
    I've read a lot of manuals, I did understand we must keep the log files in order to do a rollforward operation for the "point-in-time" backup.
    But I didn't understand if the active/archive logs were included in the backup files (after some test with the include logs tag, I've got the same size of backup, this is strange) or which active/archive log were usefull to save.

    That why I didn't success to size my file systems, I didn't know what file were needed.

    Those replies were very helpfull, thank you for your time.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by fta View Post
    I've read a lot of manuals, I did understand we must keep the log files in order to do a rollforward operation for the "point-in-time" backup.
    But I didn't understand if the active/archive logs were included in the backup files (after some test with the include logs tag, I've got the same size of backup, this is strange) or which active/archive log were usefull to save.

    That why I didn't success to size my file systems, I didn't know what file were needed.

    Those replies were very helpfull, thank you for your time.
    Logs that are included in a backup file are only those logs that were created or updated during the online backup. It does not inlcude any log files created in-between online backups.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Marcus is right. But if you take a backup (online or offline), the logs written before the backup are not needed because the corresponding data changes are included in the backup image itself. (The logs are still needed in case you want to roll back to a point in time that is before the backup.)

    I think that logs and backups are pretty simple:
    1. when a backup is taken, you get a snapshot of the database (assuming the complete database is backed up and not just a single tablespace or so)
    2. if the backup was incremental/delta, you need a full backup from some point before as a base line because the backup only contains changes since the last backup (for delta since any previous backup, for incremental since the last full backup)
    3. logs fill the gap between backups
    4. end of story.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jun 2011
    Posts
    4
    It's ok thank you

    I understood very well.

    Finally a do a the db2 backup as mentionned in my first post.
    I do a backup of all the db2 backup in an other file system
    I do a backup of all the logs (archive and active)
    I delete every backup or log after twenty-two days retention.

    With this process I can rollback at every point in time for three week

    Then I've sized
    my backup file system,
    my log and mirror file system
    my archive log file system
    my global backup

    thank you,

    Alex

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by fta View Post
    I do a backup of all the logs (archive and active)
    May be there's no need to copy active logs; any one of them can be written to at any time until they become archived, so your copy may be outdated. Also, the OS may not let you copy files that are open.

    If you set LOGARCHMETH1 and ..2 to the correct destinations, logs will be copied there automatically as they are archived.

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by fta View Post
    I delete every backup or log after twenty-two days retention.
    Tell DB2 to clean up those files by means of a "db2 prune" command. Less risk.

Posting Permissions

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