event monitor for statements

[Raj]

Can we create an event monitor for statements in a partitioned
environment?
CREATE EVENT MONITOR stmt_event FOR STATEMENTS WRITE TO FILE
'/home/rajm/event' ON PARTITION 0 GLOBAL
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "GLOBAL"
clause are
present. SQLSTATE=42613

Thanks,
Raj

[Serge Rielau]

Raj wrote:

Can we create an event monitor for statements in a partitioned
environment?
CREATE EVENT MONITOR stmt_event FOR STATEMENTS WRITE TO FILE
'/home/rajm/event' ON PARTITION 0 GLOBAL
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "GLOBAL"
clause are
present. SQLSTATE=42613

PARTITION 0 and GLOBAL ?

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Raj]

Thanks for the reply
write to table works fine i guess it is global by default, I tried the
following this fails too..
CREATE EVENT MONITOR stmt_event FOR statements WRITE TO FILE
'/filesys/sql/snap' GLOBAL
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "GLOBAL"
clause are
present. SQLSTATE=42613

From the sql reference..

ON DBPARTITIONNUM
Keyword that indicates that a specific database partition is specified.
db-partition-number
Specifies a database partition number where the event monitor runs
and writes the events. With the monitoring scope defined as
GLOBAL, all database partitions report to the specified database
partition number. The I/O component will physically run on the
specified database partition, writing its records to the file or pipe
specified above.
GLOBAL
The event monitor reports on all database partitions. For a partitioned
database in DB2 Universal Database Version 8, only deadlocks and
deadlocks with details event monitors can be defined as GLOBAL.
LOCAL
The event monitor reports only on the database partition that is
running.
It gives a partial trace of the database activity. This is the default.
example:
2. Specify if the event monitor data is to be collected at a local or
global
scope. To collect event monitor reports from all partitions issue the
following statement:
CREATE EVENT MONITOR dlmon FOR DEADLOCKS
WRITE TO FILE '/tmp/dlevents'
ON PARTITION 3 GLOBAL

[Shashi Mannepalli]

Raj:

The definition is clear. For STATEMENTS you cannot use GLOBAL Key word.
GLOBAL
The event monitor reports on all database partitions. For a partitioned
database in DB2 Universal Database Version 8, only deadlocks and
deadlocks with details event monitors can be defined as GLOBAL.
LOCAL
The event monitor reports only on the database partition that is
running. It gives a partial trace of the database activity. This is the
default.

I ran this and it works well.

SET EVENT MONITOR dlmon2 STATE = 0;

DROP EVENT MONITOR dlmon2;

CREATE EVENT MONITOR dlmon2 FOR STATEMENTS WRITE TO FILE '/local/udb
/SAULB022/archive/SAACCNT/event_monitor/' BUFFERSIZE 8 BLOCKED
AUTOSTART;

SET EVENT MONITOR dlmon2 STATE = 1;
Cheers...
Shashi Mannepalli

Raj wrote:

Thanks for the reply
write to table works fine i guess it is global by default, I tried the
following this fails too..
CREATE EVENT MONITOR stmt_event FOR statements WRITE TO FILE
'/filesys/sql/snap' GLOBAL
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "GLOBAL"
clause are
present. SQLSTATE=42613

From the sql reference..

ON DBPARTITIONNUM
Keyword that indicates that a specific database partition is specified.
db-partition-number
Specifies a database partition number where the event monitor runs
and writes the events. With the monitoring scope defined as
GLOBAL, all database partitions report to the specified database
partition number. The I/O component will physically run on the
specified database partition, writing its records to the file or pipe
specified above.
GLOBAL
The event monitor reports on all database partitions. For a partitioned
database in DB2 Universal Database Version 8, only deadlocks and
deadlocks with details event monitors can be defined as GLOBAL.
LOCAL
The event monitor reports only on the database partition that is
running.
It gives a partial trace of the database activity. This is the default.
example:
2. Specify if the event monitor data is to be collected at a local or
global
scope. To collect event monitor reports from all partitions issue the
following statement:
CREATE EVENT MONITOR dlmon FOR DEADLOCKS
WRITE TO FILE '/tmp/dlevents'
ON PARTITION 3 GLOBAL

[Raj]

Thanks a lot...
Shashi Mannepalli wrote:

Raj:

The definition is clear. For STATEMENTS you cannot use GLOBAL Key word.
GLOBAL
The event monitor reports on all database partitions. For a partitioned
database in DB2 Universal Database Version 8, only deadlocks and
deadlocks with details event monitors can be defined as GLOBAL.
LOCAL
The event monitor reports only on the database partition that is
running. It gives a partial trace of the database activity. This is the
default.

I ran this and it works well.

SET EVENT MONITOR dlmon2 STATE = 0;

DROP EVENT MONITOR dlmon2;

CREATE EVENT MONITOR dlmon2 FOR STATEMENTS WRITE TO FILE '/local/udb
/SAULB022/archive/SAACCNT/event_monitor/' BUFFERSIZE 8 BLOCKED
AUTOSTART;

SET EVENT MONITOR dlmon2 STATE = 1;
Cheers...
Shashi Mannepalli

Raj wrote:

Thanks for the reply
write to table works fine i guess it is global by default, I tried the
following this fails too..
CREATE EVENT MONITOR stmt_event FOR statements WRITE TO FILE
'/filesys/sql/snap' GLOBAL
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0628N Multiple or conflicting keywords involving the "GLOBAL"
clause are
present. SQLSTATE=42613

From the sql reference..

ON DBPARTITIONNUM
Keyword that indicates that a specific database partition is specified.
db-partition-number
Specifies a database partition number where the event monitor runs
and writes the events. With the monitoring scope defined as
GLOBAL, all database partitions report to the specified database
partition number. The I/O component will physically run on the
specified database partition, writing its records to the file or pipe
specified above.
GLOBAL
The event monitor reports on all database partitions. For a partitioned
database in DB2 Universal Database Version 8, only deadlocks and
deadlocks with details event monitors can be defined as GLOBAL.
LOCAL
The event monitor reports only on the database partition that is
running.
It gives a partial trace of the database activity. This is the default.
example:
2. Specify if the event monitor data is to be collected at a local or
global
scope. To collect event monitor reports from all partitions issue the
following statement:
CREATE EVENT MONITOR dlmon FOR DEADLOCKS
WRITE TO FILE '/tmp/dlevents'
ON PARTITION 3 GLOBAL