Results 1 to 13 of 13
  1. #1
    Join Date
    May 2011
    Posts
    27

    Unanswered: DB2 - Archive Mode - on what values to set LOGPRIMARY,LOGSECOND,LOGFILSIZ?

    we must set our database from CIRCULAR to ARCHIVE mode in order to enable ONLINE backups. Backups will be done every 1 day. Database is now almost 30GB. Database grows approximately 1GB per month. For online backup we will use Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option. Time to restore is not so important parameter, it is important to everything restore from previous day when everything was OK.

    Can you please assist me to set those three parameters on appropriate value? This is production database with important data.

    LOGPRIMARY and LOGSECOND are set to values 13 and 15 by default but I think it is not enough? Is it to large if I set them to 50 for example? What can be negative effect?

    Current values are: 13, 15, 4096. I run Configuration Advisor and it gives me those values: 27, 7, 1024. But for many I used default values because I do not know exact answers (for example Transactions per minute,or Average number of connected remote applications..) Why it put the lower value for LOGFILSIZ?

    Can someone additionally suggest me are those good values or not ? Thank you!!!!

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    When in doubt and do not know enough why to deviate just trust the config advisor. In case you are planning to use the 'oracle' isolation level of cur-commit you might consider to add some space to logbuffer IBM DB2 9.7 for Linux, UNIX and Windows Information Center
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  3. #3
    Join Date
    May 2011
    Posts
    27
    Hello,

    thank you for reply.
    So you think it will be ok to set those values?
    Can there be some negative effects if I put higher values for LOGPRIMARY and LOGFILSIZ? Just in case...

    Also I will have two questions:
    how do you differentiate LOGSECOND from LOGPRIMARY?
    Because they are in the same folder only LOGSECOND is not preallocated immediately.
    But how do you differentiate what is primary and what second log in that same folder?

    Also I do not understand completely how LOGPRIMARY works with Online backups:
    For example if I switch to Archive Mode at 1st January and LOGPRIMARY is set to 10.
    I made online backup at 5th January and in that moment for example there are 6 arhive logs created from active logs.
    Then at 10th January I want to make new online backup and new 6 arhive logs are created.
    What will happen in that situation?
    5+6=11 so I will have 10 PRIMARY logs and 1 new LOGSECOND log was created
    Or Database will start to count from from beginning on 5th January (when online backup was performed) so I will have just 6 new PRIMARY logs and previous 5 will be deleted?

    Please give me the answer on this to understand the concept

    thank you in advance

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    The outcome of ((logprimay + logsecondary) * logfilsiz) indicates how many transactions you can process without a commit (e.g. when you update all the rows of a very large table with 1 SQL statement). When you make that calculation make sure you have that space on disk

    About the correlation between an online backup vs your LOG file configuration you are hunting ghosts. There is none.

    btw: I recommend this: Understanding DB2: Learning Visually with Examples (2nd Edition): Raul Chong, Xiaomei Wang, Michael Dang, Dwaine Snow: 9780131580183: Amazon.com: Books . Contains all the answers to your questions and is not hard to read.
    Last edited by dr_te_z; 02-19-14 at 07:13.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  5. #5
    Join Date
    May 2011
    Posts
    27
    Thank you very much for response.
    What else can create such large transactions (except update table.. or delete from table when large number of rows are affected?)
    Is there some additional scenarios which can make such kind of "problems" so both primary and secondary logs will be fulfilled?

    Additionally we are using IBM Maximo (Trouble Ticketing) application which by default are doing committed transactions.
    And also when we are doing update on database we do it with commit by default.
    As I can see we cannot run into "uncommitted" transactions or I am wrong?

    Thank you again
    Last edited by cankovicv; 02-19-14 at 11:47.

  6. #6
    Join Date
    Apr 2012
    Posts
    156
    If this is a IBM Maximo only system you should go with their recommendations and you will most likely be fine. Keep an eye and if you see secondary logs being created then you will want to either increase your log file size or the number of primary logs. Maximo is a pretty light weight db so you should be fine. Where are you archiving your logs to? Make sure you back those up periodically if you are sending them to disk.

  7. #7
    Join Date
    May 2011
    Posts
    27
    Thank you for response.
    Logs are archived on Veritas Netbackup.

    I run Configuration Advisor and it gives me different results depending on Tranasactions per minute. I cannot estimate that value.

    Can you help me with that? How to find calculate estimated value? Using GET SNAPSHOT? Using which parameters?

  8. #8
    Join Date
    Apr 2012
    Posts
    156
    Point in time will not really help, what I would do is run the following:
    db2 get snapshot all databases | grep -i log
    The look at the following:
    Log space available to the database (Bytes)= 19523107914
    Log space used by the database (Bytes) = 41692086
    Maximum secondary log space used (Bytes) = 0
    Maximum total log space used (Bytes) = 833657124
    Secondary logs allocated currently = 0

    As you can see I do not have any secondary log space used or allocated. The used amounts are for that point in time, so in your case I would focus on the "Secondary logs allocated currently" , "Maximum secondary log space used" and "Log space available to the database ". If you are seeing secondary logs, increase log size or number of primary logs.

  9. #9
    Join Date
    May 2011
    Posts
    27
    OK thank you,
    is this statistics only shows for the time when you turned on/off monitor switches for the get snapshot?

  10. #10
    Join Date
    Apr 2012
    Posts
    156
    The Maximum total log space used and Maximum secondary log space used will be since the database was started. Go ahead and run the command and paste your results

  11. #11
    Join Date
    May 2011
    Posts
    27
    Maximum secondary log space used (Bytes) = 0
    Maximum total log space used (Bytes) = 104263661
    Secondary logs allocated currently = 0

    and also:
    [ctginst1@tt-db-02 NODE0000]$ db2 get snapshot for database on maxdb71 | grep "Log space"
    Log space available to the database (Bytes)= 466439444
    Log space used by the database (Bytes) = 1028844

    What shows the last parameter "Log space used by the database"?
    Current ACTIVE logs usage (logs which are not archived currently) and total usage of Log space since the database start?



    Currenlt it is in CIRCULAR mode (if it is relevant)

  12. #12
    Join Date
    Apr 2012
    Posts
    156
    Since startup you have not used any secondary logs, which is always good.
    The maximum percentage of the log space available that you have used is:
    104263661 / 466439444 * 100, which comes out to be around 22%, which is good as well, assuming you have been running typical workloads on the system.
    The "Log space used by the database", is how much of you primary logs is currently in use. This will fluctuate some.

    If you are nervous about some rogue process filling up log space you can create a script to run at a regular interval and alert at say 50%, 75% etc. The following command will give you the percentage of log space being used:

    select log_utilization_percent from sysibmadm.log_utilization

  13. #13
    Join Date
    May 2011
    Posts
    27
    Thank you very much you were very helpful.
    All the best!

Posting Permissions

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