Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

Thanks for that Jan (and Madhusudan),

I will look into setting up this table and checking it out.

The reason I haven't already spotted it is because the Event Monitor was created just to collect the DDLSTMTEXEC data:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1) AUTOSTART";

So, if I change the definition (and re-create the Ev.Mon.) as:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1), CHANGESUMMARY (TABLE DB2INST1.DDLSTMT_HISTORY_SUMMARY) AUTOSTART";

That should give me the info that I need, right?

I will give it a go A.S.A.P. and report back the result!

Kind regards, Damir

Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

Keep in mind that each change-related event is uniquely identified by the following three key fields:

Event timestamp

The time that the event occurred.

Event ID

A numeric token that ensures uniqueness in cases where the event timestamp is common.

Member

The database manager process where the event occurred.

Thanks for that Jan (and Madhusudan),

I will look into setting up this table and checking it out.

The reason I haven't already spotted it is because the Event Monitor was created just to collect the DDLSTMTEXEC data:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1) AUTOSTART";

So, if I change the definition (and re-create the Ev.Mon.) as:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1), CHANGESUMMARY (TABLE DB2INST1.DDLSTMT_HISTORY_SUMMARY) AUTOSTART";

That should give me the info that I need, right?

I will give it a go A.S.A.P. and report back the result!

Kind regards, Damir

Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

Hi Jan,

Just to confirm: after updating the Ev.Mon. for Change History (to include the event group CHANGESUMMARY), I can see the USERID and the remote client's IP address in the DDLSTMT_HISTORY_SUMMARY table!

Regarding the unique identification of monitoring events, the Db2 documentation goes even one step further, specifying four fields:

Change history event monitor record

The ID is recycled at database activation time. The uniqueness of events is guaranteed by the combination of event_timestamp, event_id, member, and event-type.

...and one would naively believe that using just the EVENT_ID would be sufficient for the purpose :-)

Kind regards, Damir

Keep in mind that each change-related event is uniquely identified by the following three key fields:

Event timestamp

The time that the event occurred.

Event ID

A numeric token that ensures uniqueness in cases where the event timestamp is common.

Member

The database manager process where the event occurred.

Thanks for that Jan (and Madhusudan),

I will look into setting up this table and checking it out.

The reason I haven't already spotted it is because the Event Monitor was created just to collect the DDLSTMTEXEC data:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1) AUTOSTART";

So, if I change the definition (and re-create the Ev.Mon.) as:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1), CHANGESUMMARY (TABLE DB2INST1.DDLSTMT_HISTORY_SUMMARY) AUTOSTART";

That should give me the info that I need, right?

I will give it a go A.S.A.P. and report back the result!

Kind regards, Damir

Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

Hi Damir, 

V12.1 documentation states only three fields:
https://www.ibm.com/docs/en/db2/12.1.x?topic=monitoring-data-generated-by-change-history-event-monitors

but if you found that four fields will give you "more unique" selection :-)  than a/m three fields giving "only unique" :-)
- better for you. Where you found those 4 fields mentioned?

Hi Jan,

Just to confirm: after updating the Ev.Mon. for Change History (to include the event group CHANGESUMMARY), I can see the USERID and the remote client's IP address in the DDLSTMT_HISTORY_SUMMARY table!

Regarding the unique identification of monitoring events, the Db2 documentation goes even one step further, specifying four fields:

Change history event monitor record

The ID is recycled at database activation time. The uniqueness of events is guaranteed by the combination of event_timestamp, event_id, member, and event-type.

...and one would naively believe that using just the EVENT_ID would be sufficient for the purpose :-)

Kind regards, Damir

Keep in mind that each change-related event is uniquely identified by the following three key fields:

Event timestamp

The time that the event occurred.

Event ID

A numeric token that ensures uniqueness in cases where the event timestamp is common.

Member

The database manager process where the event occurred.

Thanks for that Jan (and Madhusudan),

I will look into setting up this table and checking it out.

The reason I haven't already spotted it is because the Event Monitor was created just to collect the DDLSTMTEXEC data:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1) AUTOSTART";

So, if I change the definition (and re-create the Ev.Mon.) as:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1), CHANGESUMMARY (TABLE DB2INST1.DDLSTMT_HISTORY_SUMMARY) AUTOSTART";

That should give me the info that I need, right?

I will give it a go A.S.A.P. and report back the result!

Kind regards, Damir

Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

Hi Jan,

I was looking at the v11.5 docs, here's the link:

  https://www.ibm.com/docs/en/db2/11.5.x?topic=reference-e#r0060168

However, I can see the same description in the v12.1 docs:

  https://www.ibm.com/docs/en/db2/12.1.x?topic=reference-e#r0060168

But the page you sent indeed shows only three fields (omitting the EVENT_TYPE), so this is probably just a case of partially outdated documentation...

In any case, I am sure I will get uniqueness by only using the first two fields (as not using DPF anywhere): EVENT_TIMESTAMP and EVENT_ID.

Kind regards, Damir

Hi Damir, 

V12.1 documentation states only three fields:
https://www.ibm.com/docs/en/db2/12.1.x?topic=monitoring-data-generated-by-change-history-event-monitors

but if you found that four fields will give you "more unique" selection :-)  than a/m three fields giving "only unique" :-)
- better for you. Where you found those 4 fields mentioned?

Hi Jan,

Just to confirm: after updating the Ev.Mon. for Change History (to include the event group CHANGESUMMARY), I can see the USERID and the remote client's IP address in the DDLSTMT_HISTORY_SUMMARY table!

Regarding the unique identification of monitoring events, the Db2 documentation goes even one step further, specifying four fields:

Change history event monitor record

The ID is recycled at database activation time. The uniqueness of events is guaranteed by the combination of event_timestamp, event_id, member, and event-type.

...and one would naively believe that using just the EVENT_ID would be sufficient for the purpose :-)

Kind regards, Damir

Keep in mind that each change-related event is uniquely identified by the following three key fields:

Event timestamp

The time that the event occurred.

Event ID

A numeric token that ensures uniqueness in cases where the event timestamp is common.

Member

The database manager process where the event occurred.

Thanks for that Jan (and Madhusudan),

I will look into setting up this table and checking it out.

The reason I haven't already spotted it is because the Event Monitor was created just to collect the DDLSTMTEXEC data:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1) AUTOSTART";

So, if I change the definition (and re-create the Ev.Mon.) as:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1), CHANGESUMMARY (TABLE DB2INST1.DDLSTMT_HISTORY_SUMMARY) AUTOSTART";

That should give me the info that I need, right?

I will give it a go A.S.A.P. and report back the result!

Kind regards, Damir

Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

I am also assuming that you are not using PureScale - as in such case member-id may be needed...

As for docs - I am always using online documentation as this is updated "almost" on time as opposed to static links to PDF documents.
 

Hi Jan,

I was looking at the v11.5 docs, here's the link:

  https://www.ibm.com/docs/en/db2/11.5.x?topic=reference-e#r0060168

However, I can see the same description in the v12.1 docs:

  https://www.ibm.com/docs/en/db2/12.1.x?topic=reference-e#r0060168

But the page you sent indeed shows only three fields (omitting the EVENT_TYPE), so this is probably just a case of partially outdated documentation...

In any case, I am sure I will get uniqueness by only using the first two fields (as not using DPF anywhere): EVENT_TIMESTAMP and EVENT_ID.

Kind regards, Damir

Hi Damir, 

V12.1 documentation states only three fields:
https://www.ibm.com/docs/en/db2/12.1.x?topic=monitoring-data-generated-by-change-history-event-monitors

but if you found that four fields will give you "more unique" selection :-)  than a/m three fields giving "only unique" :-)
- better for you. Where you found those 4 fields mentioned?

Hi Jan,

Just to confirm: after updating the Ev.Mon. for Change History (to include the event group CHANGESUMMARY), I can see the USERID and the remote client's IP address in the DDLSTMT_HISTORY_SUMMARY table!

Regarding the unique identification of monitoring events, the Db2 documentation goes even one step further, specifying four fields:

Change history event monitor record

The ID is recycled at database activation time. The uniqueness of events is guaranteed by the combination of event_timestamp, event_id, member, and event-type.

...and one would naively believe that using just the EVENT_ID would be sufficient for the purpose :-)

Kind regards, Damir

Keep in mind that each change-related event is uniquely identified by the following three key fields:

Event timestamp

The time that the event occurred.

Event ID

A numeric token that ensures uniqueness in cases where the event timestamp is common.

Member

The database manager process where the event occurred.

Thanks for that Jan (and Madhusudan),

I will look into setting up this table and checking it out.

The reason I haven't already spotted it is because the Event Monitor was created just to collect the DDLSTMTEXEC data:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1) AUTOSTART";

So, if I change the definition (and re-create the Ev.Mon.) as:

db2 "CREATE EVENT MONITOR EVMON_CHANGE_HISTORY FOR CHANGE HISTORY WHERE EVENT IN (DDLALL) WRITE TO TABLE DDLSTMTEXEC (TABLE DB2INST1.DDLSTMT_HISTORY IN USERSPACE1), CHANGESUMMARY (TABLE DB2INST1.DDLSTMT_HISTORY_SUMMARY) AUTOSTART";

That should give me the info that I need, right?

I will give it a go A.S.A.P. and report back the result!

Kind regards, Damir

Perhaps the table CHANGESUMMARY_evmon-name has the info you are looking for? 

When you created Change history event monitor - did you look which this event monitor related tables were modified when you issued for example ALTER TABLE ... ADD/DROP FOREIGN KEY?

I use the statement event monitor to capture this (and all data manipulations).

Hi Lars,

Can you please elaborate on this - what exactly do you mean by "statement event monitor"?
I'm curious because I can't find it in the (online) docs, so just wondering if you are using the "event monitor for activities" or the Audit category "execute" or something else that I missed?
(both of these would be very unsuitable in my case, as I need to monitor a very lively transactional database with quite a lot of activities, which would put too much strain on the whole server)

I use the statement event monitor to capture this (and all data manipulations).

Hi Damir,

Let's just say that the documentation in this area is not IBM's finest...
Here's the actual syntax:

create event monitor stmts for statementswrite to tablestmt(table tabschema.tabname1 in tbspace        trunc includes(agent_id, start_time, stop_time, stmt_operation, stmt_type, stmt_text, sqlcode)),connheader(table tabschema.tabname2 in tbspace        includes(agent_id, auth_id, appl_id, conn_time));

You join the stmt and connheader tables on agent_id.
I was never able to find the rules for when an agent_id can be reused, so to play it safe, I merge and save the data hourly.
The first part of appl_id contains the IP address followed by port and timestamp (unless it's a local connection).

Hi Lars,

Can you please elaborate on this - what exactly do you mean by "statement event monitor"?
I'm curious because I can't find it in the (online) docs, so just wondering if you are using the "event monitor for activities" or the Audit category "execute" or something else that I missed?
(both of these would be very unsuitable in my case, as I need to monitor a very lively transactional database with quite a lot of activities, which would put too much strain on the whole server)

I use the statement event monitor to capture this (and all data manipulations).

Hi Lars,

Understood, thanks on the into!

All the info is actually right there, in the DB2 docs, only I haven't seen it at once as the Statement Ev.mon. is - so to speak - hidden within the "general" CREATE EVENT MONITOR statement, amongst about 8 different type-of-event categories (database, tables, deadlocks, ...).

And I wrongly thought this whole Ev.Mon. definition was deprecated (whereas only the Deadlock category is), so didn't give it too much attention :-)

In any case, the Statement Ev.Mon. does not work very well for my requirements, as the database is far too busy (too many SQL stmts executed per second) to keep this Ev.Mon. active for extended periods of time, so I will stick with the Ev.Mon. for Change History, as discussed previously.

Hi Damir,

Let's just say that the documentation in this area is not IBM's finest...
Here's the actual syntax:

create event monitor stmts for statementswrite to tablestmt(table tabschema.tabname1 in tbspace        trunc includes(agent_id, start_time, stop_time, stmt_operation, stmt_type, stmt_text, sqlcode)),connheader(table tabschema.tabname2 in tbspace        includes(agent_id, auth_id, appl_id, conn_time));

You join the stmt and connheader tables on agent_id.
I was never able to find the rules for when an agent_id can be reused, so to play it safe, I merge and save the data hourly.
The first part of appl_id contains the IP address followed by port and timestamp (unless it's a local connection).

Hi Lars,

Can you please elaborate on this - what exactly do you mean by "statement event monitor"?
I'm curious because I can't find it in the (online) docs, so just wondering if you are using the "event monitor for activities" or the Audit category "execute" or something else that I missed?
(both of these would be very unsuitable in my case, as I need to monitor a very lively transactional database with quite a lot of activities, which would put too much strain on the whole server)