By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

db2 snapshot database log information

P: n/a
Lew
Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)

2) the log space used by the system is 5779868 which is less than a
single logs worth of space. If that is so why is the first active at
55850 and all the ones after that have timestamps after 55850? It would
seem that there would be only a single log in use.
Thanks in advance for any clarification provided.

Lew
Nov 12 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Ian
Lew wrote:
Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)
The first active log file is the oldest log file associated with your
database that has active transactions (i.e. a transaction that has not
been committed or rolled back).

While 1 connection may have transactions that span these old log files,
other connections doing work will generate log records that fill up
newer log files.

I'm curious, though, why there aren't log files 55861-55899, unless
you just don't show these files in your listing.

2) the log space used by the system is 5779868 which is less than a
single logs worth of space. If that is so why is the first active at
55850 and all the ones after that have timestamps after 55850? It would
seem that there would be only a single log in use.


Log space used shows how much of the total log space is currently
allocated by active transactions, which is different from how much
available log space you have.

Total available log space = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096

In your case, (59 + 60) * 8000 * 4096 = 3,899,392,000 bytes

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Nov 12 '05 #2

P: n/a
What are the value of LOGRETAIN and USEREXIT?

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)

2) the log space used by the system is 5779868 which is less than a
single logs worth of space. If that is so why is the first active at
55850 and all the ones after that have timestamps after 55850? It would
seem that there would be only a single log in use.
Thanks in advance for any clarification provided.

Lew

Nov 12 '05 #3

P: n/a
Lew
Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@127.0.0.1>...
Lew wrote:
Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)
The first active log file is the oldest log file associated with your
database that has active transactions (i.e. a transaction that has not
been committed or rolled back).

While 1 connection may have transactions that span these old log files,
other connections doing work will generate log records that fill up
newer log files.

I'm curious, though, why there aren't log files 55861-55899, unless
you just don't show these files in your listing.


The list of log files I included was not all inclusive. I just
listed the log files that had timestamps greater than or equal to the
the first actve log S0055850.LOG to show the situation. Therefore the
log files you were looking for 55907,55908, etc had lower file
timestamps than 55850. How is this possible? Does DB2 perform
updates to a log file even after it has filled up?
2) the log space used by the system is 5779868 which is less than a
single logs worth of space. If that is so why is the first active at
55850 and all the ones after that have timestamps after 55850? It would
seem that there would be only a single log in use.
Log space used shows how much of the total log space is currently
allocated by active transactions, which is different from how much
available log space you have.

Total available log space = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096

In your case, (59 + 60) * 8000 * 4096 = 3,899,392,000 bytes


I understand that. Yet according to the log space used number the
database is using 57MB of space, less than 2 log files worth. That
doesn't jive with the number of logs being updated.

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

Nov 12 '05 #4

P: n/a
Lew
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<Mb*****************@newssvr17.news.prodigy.c om>...
What are the value of LOGRETAIN and USEREXIT?

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)

2) the log space used by the system is 5779868 which is less than a
single logs worth of space. If that is so why is the first active at
55850 and all the ones after that have timestamps after 55850? It would
seem that there would be only a single log in use.
Thanks in advance for any clarification provided.

Lew

Nov 12 '05 #5

P: n/a
Ian
Lew wrote:
Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@127.0.0.1>...
Lew wrote:
Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)


The first active log file is the oldest log file associated with your
database that has active transactions (i.e. a transaction that has not
been committed or rolled back).

While 1 connection may have transactions that span these old log files,
other connections doing work will generate log records that fill up
newer log files.

I'm curious, though, why there aren't log files 55861-55899, unless
you just don't show these files in your listing.

The list of log files I included was not all inclusive. I just
listed the log files that had timestamps greater than or equal to the
the first actve log S0055850.LOG to show the situation. Therefore the
log files you were looking for 55907,55908, etc had lower file
timestamps than 55850. How is this possible? Does DB2 perform
updates to a log file even after it has filled up?


Yes, because it depends on the transactions present in the log file.

And I'll add that log files can also remain open even with committed
transactions if the affected pages in the bufferpool have not yet been
written out to disk (this is necessary for crash recovery).
Log space used shows how much of the total log space is currently
allocated by active transactions, which is different from how much
available log space you have.

Total available log space = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096
In your case, (59 + 60) * 8000 * 4096 = 3,899,392,000 bytes


I understand that. Yet according to the log space used number the
database is using 57MB of space, less than 2 log files worth. That
doesn't jive with the number of logs being updated.


Each log file has a number of log records (identified by LSN, "log
sequence number"). For example, S0000001.LOG could have LSNs 1-100,
S0000002.LOG has LSNs 101-200, S0000003.LOG has 201-300, etc.

A transaction is associated with a series of LSNs, but these LSNs
aren't necessarily sequential (because of other activity in the
database).

Therefore, 1 long-running transaction could easily have log records
spread across multiple log files. If that transaction doesn't generate
many log records, then you can have many log files open, but not much
active log space.

Back to my example, say that txn1 is associated with LSN 2, 203, 204,
205, and everything else is committed. This keeps S0000001.LOG and
S0000003.LOG open, even though S0000002.LOG is closed. And, with only
4 log records, the active log space in the database will be very small.

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Nov 12 '05 #6

P: n/a
When you turn on log retain - either by set LOGRETAIN or USEREXIT or both.
DB2 will try to reuse those log files when they are no longer the active log
files. Because the creating of a new log file is expensive.
However, this doesn't mean db2 only reinitialized the log file with the next
sequent number.
For example, you defined 10 primary log files, and 2 secondary log files.
You ran "ls -ltr" and got
S0000445.LOG
S0000444.LOG
S0000443.LOG
S0000442.LOG
S0000449.LOG
S0000448.LOG
S0000447.LOG
S0000446.LOG
S0000451.LOG
S0000450.LOG
The current log file is 442. You can see db2 initialized 446 to 451 at this
moment. Because before the latest update of current log file (#442),
LOG443 - LOG445 is already ready to be reused. Reinitialized earlier (when
there were three log files closed and also archived)
Sorry for my English.

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<Mb*****************@newssvr17.news.prodigy.c om>...
What are the value of LOGRETAIN and USEREXIT?

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Hi,

I am a bit confused about what I'm seeing on my systems. I am trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000 Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and 55860 all these other files have been updated. I can understand it for the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)

2) the log space used by the system is 5779868 which is less than a
single logs worth of space. If that is so why is the first active at
55850 and all the ones after that have timestamps after 55850? It would seem that there would be only a single log in use.
Thanks in advance for any clarification provided.

Lew

Nov 12 '05 #7

P: n/a
Lew
I think I understand. I was aware that DB2 would reuse the log files.
I thought it would reuse them just prior to requiring them. I guess
this is not so. It seesm what it might be doing is guaranteeing that
there is always at least the primary number of logs available for use.
So in my example list of files what I'm seeing is the renaming of
earlier log files. The log files ending in 9xx are reused log files.
It makes sense since they pretty much coincide with the update (and I
am assuming filling up of) a log file ending in 8xx. Is my
understanding correct?
"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<Oq*****************@newssvr17.news.prodigy.c om>...
When you turn on log retain - either by set LOGRETAIN or USEREXIT or both.
DB2 will try to reuse those log files when they are no longer the active log
files. Because the creating of a new log file is expensive.
However, this doesn't mean db2 only reinitialized the log file with the next
sequent number.
For example, you defined 10 primary log files, and 2 secondary log files.
You ran "ls -ltr" and got
S0000445.LOG
S0000444.LOG
S0000443.LOG
S0000442.LOG
S0000449.LOG
S0000448.LOG
S0000447.LOG
S0000446.LOG
S0000451.LOG
S0000450.LOG
The current log file is 442. You can see db2 initialized 446 to 451 at this
moment. Because before the latest update of current log file (#442),
LOG443 - LOG445 is already ready to be reused. Reinitialized earlier (when
there were three log files closed and also archived)
Sorry for my English.

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<Mb*****************@newssvr17.news.prodigy.c om>...
What are the value of LOGRETAIN and USEREXIT?

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
> Hi,
>
> I am a bit confused about what I'm seeing on my systems. I am > trying to develop a script that will determine what percentage of the
> total log space is being used. If it is too high (80%) the on-call DBA > will be paged. First some system information:
>
> get db cfg for ip0
>
> First active log file = S0055850.LOG
> Log file size (4KB) (LOGFILSIZ) = 8000 > Number of primary log files (LOGPRIMARY) = 59
> Number of secondary log files (LOGSECOND) = 60
>
> get snapshot for db ip0
>
> Log space used by the database (Bytes) = 5779868
>
> ls -ltr log_dir
>
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
>
>
> Now the questions:
>
> 1) from the ls -ltr log_dir you can see that between log files 55850 and > 55860 all these other files have been updated. I can understand it for > the files between 55850-55860 but what about the others (e.g.
> 55907,55908, etc.)
>
> 2) the log space used by the system is 5779868 which is less than a
> single logs worth of space. If that is so why is the first active at
> 55850 and all the ones after that have timestamps after 55850? It would > seem that there would be only a single log in use.
>
>
> Thanks in advance for any clarification provided.
>
> Lew

Nov 12 '05 #8

P: n/a
Lew
Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@127.0.0.1>...
Lew wrote:
Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@127.0.0.1>...
Lew wrote:

Hi,

I am a bit confused about what I'm seeing on my systems. I am
trying to develop a script that will determine what percentage of the
total log space is being used. If it is too high (80%) the on-call DBA
will be paged. First some system information:

get db cfg for ip0

First active log file = S0055850.LOG
Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 59
Number of secondary log files (LOGSECOND) = 60

get snapshot for db ip0

Log space used by the database (Bytes) = 5779868

ls -ltr log_dir

-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
Now the questions:

1) from the ls -ltr log_dir you can see that between log files 55850 and
55860 all these other files have been updated. I can understand it for
the files between 55850-55860 but what about the others (e.g.
55907,55908, etc.)

The first active log file is the oldest log file associated with your
database that has active transactions (i.e. a transaction that has not
been committed or rolled back).

While 1 connection may have transactions that span these old log files,
other connections doing work will generate log records that fill up
newer log files.

I'm curious, though, why there aren't log files 55861-55899, unless
you just don't show these files in your listing.
The list of log files I included was not all inclusive. I just
listed the log files that had timestamps greater than or equal to the
the first actve log S0055850.LOG to show the situation. Therefore the
log files you were looking for 55907,55908, etc had lower file
timestamps than 55850. How is this possible? Does DB2 perform
updates to a log file even after it has filled up?


Yes, because it depends on the transactions present in the log file.

And I'll add that log files can also remain open even with committed
transactions if the affected pages in the bufferpool have not yet been
written out to disk (this is necessary for crash recovery).
Log space used shows how much of the total log space is currently
allocated by active transactions, which is different from how much
available log space you have.

Total available log space = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096
In your case, (59 + 60) * 8000 * 4096 = 3,899,392,000 bytes


I understand that. Yet according to the log space used number the
database is using 57MB of space, less than 2 log files worth. That
doesn't jive with the number of logs being updated.


Each log file has a number of log records (identified by LSN, "log
sequence number"). For example, S0000001.LOG could have LSNs 1-100,
S0000002.LOG has LSNs 101-200, S0000003.LOG has 201-300, etc.

A transaction is associated with a series of LSNs, but these LSNs
aren't necessarily sequential (because of other activity in the
database).

Therefore, 1 long-running transaction could easily have log records
spread across multiple log files. If that transaction doesn't generate
many log records, then you can have many log files open, but not much
active log space.

Back to my example, say that txn1 is associated with LSN 2, 203, 204,
205, and everything else is committed. This keeps S0000001.LOG and
S0000003.LOG open, even though S0000002.LOG is closed. And, with only
4 log records, the active log space in the database will be very small.


Yes, I understand. Thanks for the info. Let me pursue this further
if you will. This whole situation is due to a script I'm trying to
develop. I want to determine if
1 - the db is using secondary logs
2 - the db is using over 80% of the total log space

For number 1 I can't just check the number of log files in the log
directory since the db may have used secondary logs in the past but
not be using them currently. Still they will be allocated. For
number 2 I can't just count the log files between the first active and
the last touched (55850 and 55860) since it is possible that a reused
log could have a later timestamp and erroneously overstate the number
of logs currently used. (Correct me if I am wrong with these
statements)

I had intended to use two snapshot measurements:

Log space available to the database (Bytes)= 3854508838
Log space used by the database (Bytes) = 29651162

But based on what you have stated above the db could conceivably run
out of log files well before it runs out of log space. An earlier
uncommitted transaction may be holding a small amount of log space
(29MB above) while all subsquent transactions have committed. Since
the earlier transaction has not yet commmitted many log files can be
tied up. 11 in my original example. It seems those two snapshot
measurements are not usable for my script. Any ideas on how I can
determine those 2 items ?

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

Nov 12 '05 #9

P: n/a
Ian
Lew wrote:
I think I understand. I was aware that DB2 would reuse the log files.
I thought it would reuse them just prior to requiring them. I guess
this is not so. It seesm what it might be doing is guaranteeing that
there is always at least the primary number of logs available for use.
So in my example list of files what I'm seeing is the renaming of
earlier log files. The log files ending in 9xx are reused log files.
It makes sense since they pretty much coincide with the update (and I
am assuming filling up of) a log file ending in 8xx. Is my
understanding correct?


When USEREXIT is enabled, the log file is renamed as soon as it has been
succesfully archived by the user exit.

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Nov 12 '05 #10

P: n/a
Ian
Lew wrote:
Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@127.0.0.1>...
Lew wrote:
Ian <ia*****@mobileaudio.com> wrote in message news:<41**********@127.0.0.1>...
Lew wrote:
>Hi,
>
> I am a bit confused about what I'm seeing on my systems. I am
>trying to develop a script that will determine what percentage of the
>total log space is being used. If it is too high (80%) the on-call DBA
>will be paged. First some system information:
>
>get db cfg for ip0
>
>First active log file = S0055850.LOG
> Log file size (4KB) (LOGFILSIZ) = 8000
>Number of primary log files (LOGPRIMARY) = 59
>Number of secondary log files (LOGSECOND) = 60
>
>get snapshot for db ip0
>
>Log space used by the database (Bytes) = 5779868
>
>ls -ltr log_dir
>
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
>-rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
>
>
>Now the questions:
>
>1) from the ls -ltr log_dir you can see that between log files 55850 and
>55860 all these other files have been updated. I can understand it for
>the files between 55850-55860 but what about the others (e.g.
>55907,55908, etc.)

The first active log file is the oldest log file associated with your
database that has active transactions (i.e. a transaction that has not
been committed or rolled back).

While 1 connection may have transactions that span these old log files,
other connections doing work will generate log records that fill up
newer log files.

I'm curious, though, why there aren't log files 55861-55899, unless
you just don't show these files in your listing.

The list of log files I included was not all inclusive. I just
listed the log files that had timestamps greater than or equal to the
the first actve log S0055850.LOG to show the situation. Therefore the
log files you were looking for 55907,55908, etc had lower file
timestamps than 55850. How is this possible? Does DB2 perform
updates to a log file even after it has filled up?
Yes, because it depends on the transactions present in the log file.

And I'll add that log files can also remain open even with committed
transactions if the affected pages in the bufferpool have not yet been
written out to disk (this is necessary for crash recovery).

Log space used shows how much of the total log space is currently
allocated by active transactions, which is different from how much
available log space you have.

Total available log space = (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4096
In your case, (59 + 60) * 8000 * 4096 = 3,899,392,000 bytes

I understand that. Yet according to the log space used number the
database is using 57MB of space, less than 2 log files worth. That
doesn't jive with the number of logs being updated.


Each log file has a number of log records (identified by LSN, "log
sequence number"). For example, S0000001.LOG could have LSNs 1-100,
S0000002.LOG has LSNs 101-200, S0000003.LOG has 201-300, etc.

A transaction is associated with a series of LSNs, but these LSNs
aren't necessarily sequential (because of other activity in the
database).

Therefore, 1 long-running transaction could easily have log records
spread across multiple log files. If that transaction doesn't generate
many log records, then you can have many log files open, but not much
active log space.

Back to my example, say that txn1 is associated with LSN 2, 203, 204,
205, and everything else is committed. This keeps S0000001.LOG and
S0000003.LOG open, even though S0000002.LOG is closed. And, with only
4 log records, the active log space in the database will be very small.

Yes, I understand. Thanks for the info. Let me pursue this further
if you will. This whole situation is due to a script I'm trying to
develop. I want to determine if
1 - the db is using secondary logs


See the snapshot elements:

Maximum secondary log space used (Bytes) = 0
Secondary logs allocated currently = 0

This will make your life a lot easier! Your goal should be to
keep these two at zero - allocating secondary logs is expensive.
2 - the db is using over 80% of the total log space
I had intended to use two snapshot measurements:

Log space available to the database (Bytes)= 3854508838
Log space used by the database (Bytes) = 29651162

But based on what you have stated above the db could conceivably run
out of log files well before it runs out of log space. An earlier
uncommitted transaction may be holding a small amount of log space
(29MB above) while all subsquent transactions have committed. Since
the earlier transaction has not yet commmitted many log files can be
tied up. 11 in my original example. It seems those two snapshot
measurements are not usable for my script. Any ideas on how I can
determine those 2 items ?


I wouldn't worry too much about this, I wouldn't expect this to happen
very frequently. I would rather figure out what's going on and resolve
the problem.

If you've got long running transactions that don't commit, Either find
the app that's causing holding the logs open (see: 'Appl id holding the
oldest transaction') or check on the DB config parameters that affect
page cleaners (NUM_IOCLEANERS, SOFTMAX) to make them a little more
aggressive.

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---
Nov 12 '05 #11

P: n/a
Dear Fan, do you mean the archive action only does with the eld than
the current log file #442? The sequence number which are newer than
the current log file #442, such as as you mentioned #443-445 which are
not active any more will be reused? I am a little bit confused. If
this happens, why does not db2 do as the following?
(1) first use up the LOG443-LOG445, then initialize 10 new primary
logs LOG446-LOG455?
or
(2) first use up the LOG443-LOG445, then initialize 2 new secondary
logs LOG446-LOG447, after this, initalize 10 new primary logs
LOG448-LOG457?

How does DB2 trigger the log initizlization?

Thanks

Duoduo

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message news:<Oq*****************@newssvr17.news.prodigy.c om>...
When you turn on log retain - either by set LOGRETAIN or USEREXIT or both.
DB2 will try to reuse those log files when they are no longer the active log
files. Because the creating of a new log file is expensive.
However, this doesn't mean db2 only reinitialized the log file with the next
sequent number.
For example, you defined 10 primary log files, and 2 secondary log files.
You ran "ls -ltr" and got
S0000445.LOG
S0000444.LOG
S0000443.LOG
S0000442.LOG
S0000449.LOG
S0000448.LOG
S0000447.LOG
S0000446.LOG
S0000451.LOG
S0000450.LOG
The current log file is 442. You can see db2 initialized 446 to 451 at this
moment. Because before the latest update of current log file (#442),
LOG443 - LOG445 is already ready to be reused. Reinitialized earlier (when
there were three log files closed and also archived)
Sorry for my English.

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<Mb*****************@newssvr17.news.prodigy.c om>...
What are the value of LOGRETAIN and USEREXIT?

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
> Hi,
>
> I am a bit confused about what I'm seeing on my systems. I am > trying to develop a script that will determine what percentage of the
> total log space is being used. If it is too high (80%) the on-call DBA > will be paged. First some system information:
>
> get db cfg for ip0
>
> First active log file = S0055850.LOG
> Log file size (4KB) (LOGFILSIZ) = 8000 > Number of primary log files (LOGPRIMARY) = 59
> Number of secondary log files (LOGSECOND) = 60
>
> get snapshot for db ip0
>
> Log space used by the database (Bytes) = 5779868
>
> ls -ltr log_dir
>
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12 S0055850.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG
> -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG
>
>
> Now the questions:
>
> 1) from the ls -ltr log_dir you can see that between log files 55850 and > 55860 all these other files have been updated. I can understand it for > the files between 55850-55860 but what about the others (e.g.
> 55907,55908, etc.)
>
> 2) the log space used by the system is 5779868 which is less than a
> single logs worth of space. If that is so why is the first active at
> 55850 and all the ones after that have timestamps after 55850? It would > seem that there would be only a single log in use.
>
>
> Thanks in advance for any clarification provided.
>
> Lew

Nov 12 '05 #12

P: n/a
For my example, the current log file is LOG442. Let's say db2 wrote some
before image into LOG442, then there are some log files closed and also
archived. Then db2 might initialized them (LOG443-LOG445), means they are
ready to be reused. And then some new transactions are comming, db2
continually write to the current log file #442, so you will see the last
updated time of log#442 is later than LOG443-LOG445, since db2 haven't
filled up the current log file, doesn't need to switch to LOG#443. Then
another 6 log files are closed and archived, again db2 will initialized
them. At this moment, I run "ls -ltr".

"duoduo" <du*********@gmail.com> wrote in message
news:d3**************************@posting.google.c om...
Dear Fan, do you mean the archive action only does with the eld than
the current log file #442? The sequence number which are newer than
the current log file #442, such as as you mentioned #443-445 which are
not active any more will be reused? I am a little bit confused. If
this happens, why does not db2 do as the following?
(1) first use up the LOG443-LOG445, then initialize 10 new primary
logs LOG446-LOG455? or
(2) first use up the LOG443-LOG445, then initialize 2 new secondary
logs LOG446-LOG447, after this, initalize 10 new primary logs
LOG448-LOG457?

How does DB2 trigger the log initizlization? =========
Once the log file is closed and archived, then they are ready to be
initialized. This doesn't mean db2 will initialized it immediately. But db2
must do it before db2 switch the current log file to this file.

Thanks

Duoduo

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<Oq*****************@newssvr17.news.prodigy.c om>...
When you turn on log retain - either by set LOGRETAIN or USEREXIT or both. DB2 will try to reuse those log files when they are no longer the active log files. Because the creating of a new log file is expensive.
However, this doesn't mean db2 only reinitialized the log file with the next sequent number.
For example, you defined 10 primary log files, and 2 secondary log files. You ran "ls -ltr" and got
S0000445.LOG
S0000444.LOG
S0000443.LOG
S0000442.LOG
S0000449.LOG
S0000448.LOG
S0000447.LOG
S0000446.LOG
S0000451.LOG
S0000450.LOG
The current log file is 442. You can see db2 initialized 446 to 451 at this moment. Because before the latest update of current log file (#442),
LOG443 - LOG445 is already ready to be reused. Reinitialized earlier (when there were three log files closed and also archived)
Sorry for my English.

"Lew" <se*****@yahoo.com> wrote in message
news:53**************************@posting.google.c om...
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

"Fan Ruo Xin" <fa*****@sbcglobal.net> wrote in message

news:<Mb*****************@newssvr17.news.prodigy.c om>...
> What are the value of LOGRETAIN and USEREXIT?
>
> "Lew" <se*****@yahoo.com> wrote in message
> news:53**************************@posting.google.c om...
> > Hi,
> >
> > I am a bit confused about what I'm seeing on my systems. I
am
> > trying to develop a script that will determine what percentage of
the > > total log space is being used. If it is too high (80%) the on-call DBA
> > will be paged. First some system information:
> >
> > get db cfg for ip0
> >
> > First active log file =
S0055850.LOG > > Log file size (4KB) (LOGFILSIZ) = 8000
> > Number of primary log files (LOGPRIMARY) = 59
> > Number of secondary log files (LOGSECOND) = 60
> >
> > get snapshot for db ip0
> >
> > Log space used by the database (Bytes) = 5779868
> >
> > ls -ltr log_dir
> >
> > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:12
S0055850.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055851.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055901.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:14 S0055900.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:32 S0055852.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:42 S0055902.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 20:46 S0055853.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055903.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:02 S0055854.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055904.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:32 S0055855.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:33 S0055905.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 21:47 S0055856.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:07 S0055906.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:11 S0055857.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:12 S0055907.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:13 S0055858.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055859.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:15 S0055908.LOG > > -rw------- 1 db2ip0 dbip0adm 32776192 Oct 08 22:22 S0055860.LOG > >
> >
> > Now the questions:
> >
> > 1) from the ls -ltr log_dir you can see that between log files 55850 and
> > 55860 all these other files have been updated. I can understand
it for
> > the files between 55850-55860 but what about the others (e.g.
> > 55907,55908, etc.)
> >
> > 2) the log space used by the system is 5779868 which is less

than a > > single logs worth of space. If that is so why is the first active at > > 55850 and all the ones after that have timestamps after 55850? It

would
> > seem that there would be only a single log in use.
> >
> >
> > Thanks in advance for any clarification provided.
> >
> > Lew

Nov 12 '05 #13

P: n/a

"Ian" <ia*****@mobileaudio.com> wrote in message
news:41**********@127.0.0.1...
Lew wrote:
I think I understand. I was aware that DB2 would reuse the log files.
I thought it would reuse them just prior to requiring them. I guess
this is not so. It seesm what it might be doing is guaranteeing that
there is always at least the primary number of logs available for use.
So in my example list of files what I'm seeing is the renaming of
earlier log files. The log files ending in 9xx are reused log files.
It makes sense since they pretty much coincide with the update (and I
am assuming filling up of) a log file ending in 8xx. Is my
understanding correct?
When USEREXIT is enabled, the log file is renamed as soon as it has been
succesfully archived by the user exit.

=========
From the logfile timestamp, I can see the log files S0055861 - S0055908 are
ready to be reused. (I rememberd you said the timestamp of S0055861-S0055889
is earlier than S0055850).

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= East/West-Coast Server Farms - Total Privacy via Encryption =---

Nov 12 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.