Tracking Usage of a View

[eloiselle]

Hello everyone,

I've been trying recently to create some sort of event monitor (not sure if this is a correct solution) in DB2 to try to keep tracks of a view's usage (each time it i used it in a SELECT statement, for instance).
I've seen multiple solutions existing for T-SQL, but can't seem to find a good answer for DB2.


------------------------------------------------------------------------------
Example of T-SQL solutions:
- Forum answers:
https://dba.stackexchange.com/questi...et-usage-stats

- Microsoft answers:
https://docs.microsoft.com/en-us/sql...ql-server-2017

I've seen a possible solution for DB2 here, although I'm not sure I can trigger it on a view:
https://www.ibm.com/support/knowledg.../r0055061.html
------------------------------------------------------------------------------


I'm trying to use this view in an excel document later on, but putting my view inside a stored procedure (to insert each usage in a table) severely limitates usage possibilities in the future (not possible to select specific rows)

Anyone ever tried this before?
Any help would be appreciated.

- Emile

[RDKells]

Hi,

It's been a while since I've done this so forgive the brief overview, but functionality does exist called SQL Performance Monitors.

System i navigator -> your system -> databases -> your database -> SQL performance monitors -> at the bottom of the screen in "database tasks" click "Create a new SQL performance monitor"

Give it a name/schema

On the next screen there is a section called; "Statements that access these objects"

List the object(s) there

This can also be done via the green screen using STRDBMON;

https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/rzajq/dms.htm

The output will have the username and the SQL they ran, among other things.


I do think these monitors have an adverse impact on performance though and also can starts to chew up disk space pretty quickly so you shouldn't leave them running for long periods.


I'd take this information and do some further research, to ensure there are no other adverse affects; hope it helps.

Cheers,
Ryan

[eloiselle]

Thank you very much for your answer, it's exactly what I've been looking for.