Re: Looking for a user exit or something similar to monitor SQL commands being used.

Le 08/09/2023 à 19:26, a4g atl a écrit :

I need to track who is using SQL to do stuff in our database.
I have worked with the user exit, QIBM_DB_OPEN but that only gives me the
file, library and user accessing the file.

I want to be able to receive the SQL command so that I can validate

1. who is executing the command
2. are they doing a SELECT, INSERT, UPDATE, DELETE....
3. where the call is being made from.

Do you find what you need in a Plan Cache snapshot?
You can use the DUMP_CACHE_PROCEDURE to create a table with SQL statements.

https://www.ibm.com/docs/en/i/7.5?topic=services-dump-plan-cache-procedure

Be aware that the Plan Cache is cleared at IPL time, so you might need to run this procedure just before a power down. It might also be cleaned up during normal operations, so monitoring this event might also be useful

https://www.ibm.com/docs/en/i/7.5?topic=services-start-plan-cache-event-monitor-procedure

With so many tools available today on a PC or UNIX system and SQL being
learnt by so many, it is easy for anyone to do what they want.with the
database.

We also have many users accessing the system using ODBC and Excel.

Does anyone know of another user exit or similar process that I can use to
monitor the SQL usage? The process needs to be able to return a result that
will block the user from executing the command.

TIA, Darryl Freinkel.