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  3576 
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.goo gle.com... 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*****@mobile audio.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*****@sbcglo bal.net> wrote in message news:<Mb******* **********@news svr17.news.prod igy.com>... What are the value of LOGRETAIN and USEREXIT?
"Lew" <se*****@yahoo. com> wrote in message
news:53******** *************** ***@posting.goo gle.com... 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*****@mobile audio.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,5590 8, 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.goo gle.com... Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON
"Fan Ruo Xin" <fa*****@sbcglo bal.net> wrote in message
news:<Mb******* **********@news svr17.news.prod igy.com>... What are the value of LOGRETAIN and USEREXIT?
"Lew" <se*****@yahoo. com> wrote in message
news:53******** *************** ***@posting.goo gle.com... 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*****@sbcglo bal.net> wrote in message news:<Oq******* **********@news svr17.news.prod igy.com>... 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.goo gle.com... Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON
"Fan Ruo Xin" <fa*****@sbcglo bal.net> wrote in message
news:<Mb******* **********@news svr17.news.prod igy.com>... What are the value of LOGRETAIN and USEREXIT?
"Lew" <se*****@yahoo. com> wrote in message
news:53******** *************** ***@posting.goo gle.com...
> 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*****@mobile audio.com> wrote in message news:<41******* ***@127.0.0.1>. .. Lew wrote: Ian <ia*****@mobile audio.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,5590 8, 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 =--- 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
oracle user : user1
table : user1.table1
|
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 process, that keeps going on, until your disk is
full. I'm on W2K, DB2 UDB 8.1 FP4.
Test case:
db2start
|
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 level cause performance problems ?
2)How does one find the SQL statement that a particular user session
is executing?
TIA,
|
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 the logical and physical reads from
bufferpool for every statement monitored. Am I right?
But now, I would like to read these results out of a table so that I could
process them automatically. In which table(s) are the results of snapshot
for...
| | |
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. However, for
getting only some paramters, I get a huge response, with many
parameters that I do not need (mostly many are performance parameters).
How long can the snapshot response be, and can it considerably affect
performance?
|
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 bring the DB2 database in online backup
mode and to a Quickshadow space optimised snapshot, by issuing a HDS
Quick Shadow command, after this
we bring DB2 out of online (hot backup) mode and split the shadow copy
of and mounting it on the backup...
|
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 TABLE (SNAPSHOT_LOCK (CAST (NULL AS CHAR), -1)) AS
LOCK_INFO
After each query the client sleeps for 100 ms, and then continues the
loop, so the database machine is not under load (cpu consumption is
|
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 = Snapshotter, filename =
'c:\temp\Snapshotter_snap_20070418_1821.ss')
as snapshot of Snapshotter
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
