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
13  3537 
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 =---
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
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 =---
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
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 =---
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
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
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 =---
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 =---
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 =---
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
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
"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 =--- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bernard Segonnes |
last post by:
Hi
I'm a bit lost with snapshot creation, so can I have an easy (but with
all details : *.ora, user grants, *.sql, db links, ...) example of
snapshot between :
Server1 :
instance : I1...
|
by: Bernhard Krautschneider |
last post by:
hello group,
is it possible to do a storage snapshot of a running ms-sql database
without losing transactions?
What tasks must be done before such a snapshot.
thanks in advance,
Bernhard
|
by: Jean-Marc Blaise |
last post by:
Dear all,
It seems there is a problem with some snapshot table functions, if you try
to divide by some element that is unset or equal to 0. This generates a trap
file with the db2fmp.exe...
|
by: hype |
last post by:
Hi,
1) If I need to monitor a resource eg, bufferpools at the database
level across all users, how can I do this ?
If snapshot monitoring needs to be done wouldn't enabling the switch
at the DBM...
|
by: Ina Schmitz |
last post by:
Hello,
I would like to get the logical and physical reads for every sql statement
executed.
Thatfore, I used the command "db2 get snapshot for dynamic sql on
<mydatabase>". There, I could see...
|
by: Tejas |
last post by:
Hi,
I have gone through the snapshot API. I need to use it for finding
location of database files, tablespace containers etc on a remote
machine. These parameters are present in the response....
|
by: hsimon |
last post by:
Hi,
I have no DB2 or SAP knowledge, but would like to know if someone has
done Quickshadow Space optimised snapshot of SAP/DB2 and did a restore
from the snapshots.
We try to backup SAP/DB2, by...
|
by: steingold |
last post by:
Hi all
I'm using DB2 UDB v8.2 on linux.
After minimizing the code in order to isolate the problem, I have a
simple jdbc client that executes the following SQL statement in loop :
SELECT * FROM...
|
by: MPD |
last post by:
Hi
How can I create a job in sql agent to create a new snapshot every hour?
I have, for eg a T-SQL that does it manually.
create database Snapshotter_snap_20070418_1821 on
( name =...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
