Sorry if this has been discussed before (I am sure it has). I did a search and didn't really find what I was looking for.
I am not a DB2 expert at all. Right now, I am taking full backups nightly. I am also trying to archive the logs once during the day (at noon currently). I am doing this by issuing "ARCHIVE LOG FOR DB". Then, I am looking at the DB CFG to get the first active log. I am copying all logs before the first active log to a separate machine. This way, I am thinking that I can only lose half of a day if my primary log drive fails. My problem is that issuing the "ARCHIVE LOG" at noon doesn't close all logs up to that point. I know I am misunderstanding this command. I thought it would close out ALL of it's logs (after all have committed) and start new ones, therefore giving me all transactions up until noon. Apparently, this is not how it works. I restored last night's backup and rolled the logs forward and my latest transaction was 6 a.m. I manually copied all of the logs until the truncated ones and then I got all of my transactions until noon.
So, what is the best way to close all logs until the current point in time so that I can archive those to another location. This method was handed down to me. It seems like I should probably be using the LOGARCHMETH1 parameter to just move the files when DB2 is done with them and forget the noon process or possibly use a user exit routine. I just wanted to check with you guys to see what the best way is to go about backups and log archiving.
Yes, setting LOGARCHMETH1 to the desired value is what you need to do. ARCHIVE LOG only archives the current active log file, then only according to the setting of LOGARCHMETH1. Once LOGARCHMETH1 is set, DB2 will archive logs as they are filled to the specified location.
A little off topic, but can you help me understand the active log file a little better. I am set to have 20 primary logs. When I activate the db, DB2 creates 20 logs like I expected. Now, does it use them in order? Is every transaction in a given log after (committed time wise) the log file that precedes it in number? I am just not sure that I understand having one active log, but 20 reserved (for lack of a better term) logs.
You have solved my issue. Now I am just trying to learn more about how DB2 uses it's logs. Thanks again.
Active log(s) are those that contain data for currently running transactions. DB2 allocates _primary_ log files upon startup, it does not mean that all (or any) of them is active. They are just like you said, reserved. It will use log files in sequence. If you use circular logging, older logs will be reused, so an earlier log file in sequence could have the most recent transaction. In archiving mode you could say that later logs contain later transactions, but keep in mind that transactions can overlap.
Thank you very much for the explanation. I have spent most of the day trying to learn how DB2 uses these log files. What was getting me was when I issued the archive command, it would truncate say log 105 and create another one. Just like I thought. However, when I asked what the first active log, it would still say log 103 for example. I wasn't understanding how that could be, but I think I understand now that the archive log command closes the currently active log file, but DB2 could still be considering logs before the one it archived as active, even though they are full because of uncommitted and/or not externalized data. The externalize part of that is what I think was getting me. I was looking at it as DB2 had filled them so they couldn't be active, but they can.
Again, sorry for all of the questions. Thanks so much for helping me out. I think I have a pretty good grasp on this now. I am moving to using the LOGARCHMETH1 parameter to control my logging and quit trying to do in code what DB2 is designed to do.