This JSON-based API reports on already applied statistics tuning events using data stored in TDStats, the Dictionary, and DBQL tables. Callers can limit the report to a specified time period and/or specified user objects.
Syntax
REPLACE PROCEDURE TDSTATS.AuditTrail ( IN jln JSON(2000) CHARACTER SET UNICODE) DYNAMIC RESULT SETS 1
Syntax Elements
AuditTrail is implemented as a SQL procedure with input parameters in the form of JSON key-value pairs and output in the form of Dynamic SQL result.
| JSON Key | IN/OUT | Data Type | Description |
|---|---|---|---|
| DatabaseName | IN | VARCHAR(128) | Limit audit trail to events that apply to a specified database. |
| TableName | IN | VARCHAR(128) | Further limit audit trail to a specified table of DatabaseName. |
| ObjectListName | IN | VARCHAR(128) | Limit audit trail to a named list of objects. See CreateObjectList. |
| EventType | IN | VARCHAR(50) | Limit audit trail to a particular type of event:
|
| StartTime | IN | TIMESTAMP(6) | Start time of the desired audit period. |
| EndTime | IN | TIMESTAMP(6) | End time of the desired audit period. |
| Dynamic SQL Result Column | Data Type | Description | |
|---|---|---|---|
| EventType | OUT | VARCHAR(50) | Type of recorded event. See related input variable EventType for possible values. |
| EventTimeStamp | OUT | TIMESTAMP(6) | Timestamp of when the event occurred. |
| DatabaseName | OUT | VARCHAR(128) | Database on which the event occurred. |
| TableName | OUT | VARCHAR(128) | Table on which the event occurred. |
| FieldNames | OUT | VARCHAR(10000) | Comma separated list of Field names on which the event was applied. |
| StatsOrIndexID | OUT | INTEGER | Dictionary identifier of relevant statistic or index. |
| SCOID | OUT | BIGINT | TDStats identifier of relevant statistic. |
| DDLText | OUT | VARCHAR(10000) | SQL DDL text that was run during the event. |
| StatsSubmitted | OUT | INTEGER | Number of individual stats submitted within DDL text. |
| StatsCollected | OUT | INTEGER | Number of individual stats collected (not skipped). |
| UtilityName | OUT | VARCHAR(128) | Name of bulk load utility. Relevant only for events of type BL. |
| InsertCount | OUT | BIGINT | Number of rows inserted during bulk load. |
| UpdateCount | OUT | BIGINT | Number of rows updated during bulk load. |
| DeleteCount | OUT | BIGINT | Number of rows deleted during bulk load. |
| ErrorText | OUT | VARCHAR(512) | Error, if any, from executing DDL text. |
| Description | OUT | VARCHAR(256) | Supporting evidence for the action applied during the event. |
Example: Using AuditTrail
CALL TDSTATS.AuditTrail('{"DatabaseName":"AuditTrail_DB"}’);
Result:
*** ResultSet# 1 : 12 rows returned by "TDSTATS.AUDITTRAIL".
EventTimeStamp 2022-01-18 15:13:37
DatabaseName AuditTrail_DB
TableName tab_with_missing_recs
FieldNames x1
StatsOrIndexId 2
SCOID 300,007
DDLText COLLECT STATISTICS COLUMN("X1") ON "AuditTrail_DB"."tab_with_missing_recs";
StatsSubmitted 1
StatsCollected 1
UtilityName ?
InsertCount ?
UpdateCount ?
DeleteCount ?
ErrorText ?
Description. Initial collection or refresh of single stat
....