CODEWITHSUNDEEP
sqldb2ibm-clouddb2-luwaudit-logging
Mike Pala
Mike Pala

Reputation: 806

List History of SQL Statements in DB2 Warehouse on Cloud

not sure if this is possible but is there is a way, preferably an SQL query that I could run as an admin, to find out what SQL statements have been executed, by what users, and how long they took to run, over a period of time.

Upvotes: 0

Views: 2492

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12359

You may create or use existing event monitor for activities.
The information on completed SQL statements with a lot of performance indicators is collected in its tables.
You have an ability to collect such an information for whole database, particular WLM objects (workloads, service classes), and even for particular sessions.

Upvotes: 2

mustaccio
mustaccio

Reputation: 19001

What you're asking for is typically called "audit". You can enable the Db2 audit facility in cloud instances, e.g.

create audit policy exec_policy categories execute status both error type normal;
audit database using policy exec_policy;

You cannot limit statement execution audit to queries only; DML and DDL statements will also be captured.

You will then regularly move audit records from the log into audit tables:

call audit.update()

and select the data:

select * from audit.execute

You'll find more details in the manual.

Note that audit has certain performance implications, as every audited event is recorded synchronously.

Obviously, you cannot go back in time and capture events that has already completed. Only events occurring after enabling audit will be recorded.

Upvotes: 1

Related Questions