View bind parameters in long running SQL Statements in logs

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

By default, the Automation Engine will show long running database calls, but not provide the parameters of the run:

20210509/061201.006 - U00003524 UCUDB: ===> Time critical DB call!       OPC: 'SLCO' time: '10:008.077.000'
20210509/061201.006 - U00003525 UCUDB: ===> 'SELECT * FROM RH WHERE RH_Type    = ? AND RH_AH_Idnr = ?

Is it possible to show those within the logs, so DBA's may review the execution plans for the SQL?

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Resolution

Within the UC_SYSTEM_SETTINGS, SERVER_OPTIONS digit 11 determines this behavior:

https://docs.automic.com/documentation/webhelp/english/AA/12.3/DOCU/12.3/Automic%20Automation%20Guides/help.htm#AWA/Variables/UC_SYSTEM_SETTINGS/UC_SYSTEM_SERVER_OPTIONS.htm

11. Extended output of time-critical database accesses in the log file.

    The Automation Engine logs the bind parameters of all time-critical database accesses whose select statements take more seconds than specified in this server option. Any number between 1 and 9 can be specified.

    Regardless of this setting, bind parameters will not be logged in insert statements.

    Tip: We recommend using 3 when you use this option. This means that the bind parameter of all selects that take more than 3 seconds will be logged. Therefore, the Automation Engine logs the BIND VARIABLES for long running SQL statements within the LOG file without the need for a trace.

    Character: Number

Please note, this will increase logging so could affect total size/io if there are a large number of time critical calls.