Monitoring log usage

[martynh]

For a database with LOGRETAIN set to RECOVERY, is it possible to find out:

1) The name of the most recently created log file (without looking at the directory containing the log files)?

2) How much of the most recently created log file has been used?

3) Which log file contains the oldest, active transaction?

Thanks

Martyn Hodgson

 

[scotdb]

Answer to all three is NO.

But here are some notes -

When you take a backup then information is written to the history file, and can be retrieved with the LIST HISTORY command, about what logfiles are needed to allow recovery from that point. This can serve as a useful guideline about what log files can be removed.

Since DB2 is a multi-user system, different processes could be using different logfiles at the same point in time. There may be long running transactions (for example) which need older log files than the current one being written to in the case of a rollback being needed.

When a log file is put into "archived" status DB2 can call a user exit (which you have to write) and one of the pieces of information passed to the user exit is the name of the log file. You have to be very careful with user exits however. If your user exit finishes with a return code 0 then DB2 assumes that you have successfully moved to log file somewhere else and will delete the file from the main log directory. Don't return RC=0 unless you are absolutely sure that you have copied the log.

I'd suggest that the easiest way to find the "current log" (if such a thing really exists) is to sort by creation date and time in the current log path. The size of the log files is specified as a DB CFG parameter LOGFILSIZ, and the size of the log file in the directory is how full it is.

Not that unless you activate a database active log files are closed and archived when all users disconnect from a database. When the database is next accessed a new set of active log files are created. Hence you may see lots of small log files (less than the LOGFILSIZ).

HTH

Phil Nelson
ScotDB Limited

 

[sathyarams]

Have a look at the database snapshot ...

db2 get snapshot for database on <dbname>

Te output has a lot of useful information on the logs

Cheers

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[ThomsonDBA]

To find answer to your question

-- which log file contains the oldest active transaction....

Execute &quot;db2 get db cfg for <dbname> |grep First&quot;

The result is a single line from the db cfg output giving the logfile number which holds the &quot;oldest&quot; active transaction.

First active log file = S0000629.LOG


Regards,

Syed Hassan

 

[leploep]

I also saw in the &quot;control center&quot; when you right click a database, select &quot;configure...&quot;. On the &quot;log&quot; tab there's some info on logfile currently in use etc.