How to find Log File usage ?

[hype]

Hi,
How can I find out how much space has been used in the log file
and how much of it is free or yet to be used ?
Thanks,
Hype

[PM \(pm3iinc-nospam\)]

db2 command : Get snapshot for database ...
Log space available to the database (Bytes)
Log space used by the database (Bytes)
Maximum secondary log space used (Bytes)
Maximum total log space used (Bytes)
Secondary logs allocated currently
Log pages read
Log pages written

db2 sql udf : SNAPSHOT_DATABASE table function

PM

"hype" <hy********@yahoo.com> a écrit dans le message de
news:71**************************@posting.google.c om...

Hi,
How can I find out how much space has been used in the log file
and how much of it is free or yet to be used ?
Thanks,
Hype

[Joachim Klassen]

I'm not aware of a easy way to gather this information, but what
should work is the following:
do a "snapshot for database" for the particular database. look for
these information:
Log space available to the database (Bytes)= 55185
Log space used by the database (Bytes) = 5044815

Divide "log space used" by the log file size for your database and you
will get a number which you must add to your "First active log file"
(you get this through the DB CFG). The resulting number should be the
logfile where DB2 actually writes in.

HTH
Joachim

PS: in V8 you can get the snapshot information via a SQL Table
function

hy********@yahoo.com (hype) wrote in message news:<71**************************@posting.google. com>...

Hi,
How can I find out how much space has been used in the log file
and how much of it is free or yet to be used ?
Thanks,
Hype

[hype]

Thanks for the replies.
However,

with Circular logging, I see the following output always
despite running a few thousand insert statements.

Log space available to the database (Bytes)= 20400000
Log space used by the database (Bytes) = 0
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 0
Secondary logs allocated currently = 0
Log pages read = 1
Log pages written = 0
Appl id holding the oldest transaction = 0

But when I changed to archival logging, I see,

Log space available to the database (Bytes)= 20400000
Log space used by the database (Bytes) = 0
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 197575
Secondary logs allocated currently = 0
Log pages read = 1
Log pages written = 2372
Appl id holding the oldest transaction = 0

The "Log space used by the database (Bytes)" does not increase ?
What is the reason behind this, should I turn on some counters ?

Thanks,
Hype

[PM \(pm3iinc-nospam\)]

Log pages written = 0
vs
Log pages written = 2372

If no log pages were yet written, there is not currently any used space in
the log files. (still in the buffer(s))

DB CFG LOGBUFSZ ?
mincommit may imply a 1 sec delay
auto commit is off?
Your update transaction commited? (Commit statements attempted and similar
elements in the snapshot data)

Some shapshot data require the timestamp switch on. (i don't think it's the
case here.)
You may use the database system monitor to determine how much of the log
buffer space

is used for a particular transaction (or unit of work).

Refer to the log_space_used (unit of work log space used) monitor element.

In v8 and maybe prior versions, you can write event monitor data to sql
tables.

I'm pretty sure both values would be the same. (sql snapshot vs get snapshot
command vs event monitor data)

Personnaly, i have no problems.

DB SALES_DB
Log retain for recovery enabled (LOGRETAIN) = OFF

+-1000 inserts...

db2 get snapshot for database on ***
Log space available to the database (Bytes)= 4852796
Log space used by the database (Bytes) = 247204
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 247204
Secondary logs allocated currently = 0
Log pages read = 0
Log pages written = 55

db2 get snapshot for database on ***
Commit statements attempted = 4
Internal commits = 5
PM

"hype" <hy********@yahoo.com> a écrit dans le message de
news:71**************************@posting.google.c om...

Thanks for the replies.
However,

with Circular logging, I see the following output always
despite running a few thousand insert statements.

Log space available to the database (Bytes)= 20400000
Log space used by the database (Bytes) = 0
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 0
Secondary logs allocated currently = 0
Log pages read = 1
Log pages written = 0
Appl id holding the oldest transaction = 0

But when I changed to archival logging, I see,

Log space available to the database (Bytes)= 20400000
Log space used by the database (Bytes) = 0
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 197575
Secondary logs allocated currently = 0
Log pages read = 1
Log pages written = 2372
Appl id holding the oldest transaction = 0

The "Log space used by the database (Bytes)" does not increase ?
What is the reason behind this, should I turn on some counters ?

Thanks,
Hype