Database transaction log switch history

[Eugene F]

The database is currently in the circular logging mode (no log
archiving). I want to track the database transaction log switch history
in form of (switch-timestamp, active-log-sequence-number) to eventualy
assess on the database logging volume during the database operations
(OLTP). I wasn't able to find a relevant event monitor elemen for that.
Any other method?

Thanks
-Eugene

[Knut Stolze]

Eugene F wrote:

The database is currently in the circular logging mode (no log
archiving). I want to track the database transaction log switch history
in form of (switch-timestamp, active-log-sequence-number) to eventualy
assess on the database logging volume during the database operations
(OLTP). I wasn't able to find a relevant event monitor elemen for that.
Any other method?

Stupid question: What is a "database transaction log switch history"?

Do you want to track whenever the logging behavior is changed from circular
to archiving or vice versa?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Eugene F]

No, I want to track the event when DB2 closes the current log file as
it gets full and start writing (switches) into the next log file that
becomes the active one, and so forth.

-Eugene
Knut Stolze wrote:

Eugene F wrote:

The database is currently in the circular logging mode (no log
archiving). I want to track the database transaction log switch history
in form of (switch-timestamp, active-log-sequence-number) to eventualy
assess on the database logging volume during the database operations
(OLTP). I wasn't able to find a relevant event monitor elemen for that.
Any other method?

Stupid question: What is a "database transaction log switch history"?

Do you want to track whenever the logging behavior is changed from circular
to archiving or vice versa?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Prasad]

When you enable user exit for log archiving, the database manager calls
the db2uext2 program. You can use this program(db2uext2) to capture the
timestamp into a file.

Hope this helps,
Prasad.

[Eugene F]

As I mentioned, the database is not in log archiving mode so that's not
a solution.

Prasad wrote:

When you enable user exit for log archiving, the database manager calls
the db2uext2 program. You can use this program(db2uext2) to capture the
timestamp into a file.

Hope this helps,
Prasad.

[Knut Stolze]

Eugene F wrote:

As I mentioned, the database is not in log archiving mode so that's not
a solution.

Now I'm utterly confused. I would say this is just a development or toy
system. Otherwise, you would use log archiving in the first place. So
what would you need the information about closing one log file and starting
with another one?

Besides, DB2 does not have to close a log file in circular logging. If all
transactions (UOWs) are completed, it could happily start at the first log
file again. Likewise, it could very well happen that all primary +
secondary log files are currently open because the first log file still has
entries for a not-yet-committed transaction. Therefore, you are probably
only interested in the timestamp when a new log file becomes active - not
when another one is closed.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Eugene F]

Not getting into details whether log files are getting closed or not
after they are full in circular mode, I still want to track the
timestamp when the database starts writing into the next log file in
the circular logging mode.


Knut Stolze wrote:

Eugene F wrote:

As I mentioned, the database is not in log archiving mode so that's not
a solution.

Now I'm utterly confused. I would say this is just a development or toy
system. Otherwise, you would use log archiving in the first place. So
what would you need the information about closing one log file and starting
with another one?

Besides, DB2 does not have to close a log file in circular logging. If all
transactions (UOWs) are completed, it could happily start at the first log
file again. Likewise, it could very well happen that all primary +
secondary log files are currently open because the first log file still has
entries for a not-yet-committed transaction. Therefore, you are probably
only interested in the timestamp when a new log file becomes active - not
when another one is closed.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Knut Stolze]

Eugene F wrote:

Not getting into details whether log files are getting closed or not
after they are full in circular mode, I still want to track the
timestamp when the database starts writing into the next log file in
the circular logging mode.

I don't know if that is possible - besides monitoring activity on the log
files/directory with some external tool.

But still I'm curious: what do you need that for?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Eugene F]

To make an estimate on the volume of transaction logs generated and the
database activity pattern (distribution) in time. Do you know any
better way? I am thinking to turn on the archival logging to, at
least, be able to use database history for logs for that.
Knut Stolze wrote:

Eugene F wrote:

Not getting into details whether log files are getting closed or not
after they are full in circular mode, I still want to track the
timestamp when the database starts writing into the next log file in
the circular logging mode.

I don't know if that is possible - besides monitoring activity on the log
files/directory with some external tool.

But still I'm curious: what do you need that for?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Pierre Saint-Jacques]

The following comwes from a normal db snapshot. Taking snapshots at
interval, can you make use of this?

Log space available to the database (Bytes)= 20397527
Log space used by the database (Bytes) = 2473
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 3082
Secondary logs allocated currently = 0
Log pages read = 0
Log read time (sec.ns) = 0.000000004
Log pages written = 1
Log write time (sec.ns) = 0.000000004
Number write log IOs = 1
Number read log IOs = 0
Number partial page log IOs = 1
Number log buffer full = 0
Log data found in buffer = 0
Appl id holding the oldest transaction = 7
Log to be redone for recovery (Bytes) = 2473
Log accounted for by dirty pages (Bytes) = 2473

File number of first active log = 18
File number of last active log = 20
File number of current active log = 18
File number of log being archived = Not applicable

The last entry means the db is in circular logging.

Regars, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Eugene F" <ro********@yahoo.coma écrit dans le message de news:
11*********************@j72g2000cwa.googlegroups.c om...

To make an estimate on the volume of transaction logs generated and the
database activity pattern (distribution) in time. Do you know any
better way? I am thinking to turn on the archival logging to, at
least, be able to use database history for logs for that.
Knut Stolze wrote:

>Eugene F wrote:

Not getting into details whether log files are getting closed or not
after they are full in circular mode, I still want to track the
timestamp when the database starts writing into the next log file in
the circular logging mode.

I don't know if that is possible - besides monitoring activity on the log
files/directory with some external tool.

But still I'm curious: what do you need that for?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany