DX Application Performance Management

I am trying to use Regualar expression to normalize SQL (Oracle) database within jboss application on both Windows and AIX servers. The interesting thing is on Windows server when used the below regular expression, works but not on AIX servers. Please help. Find the expression below:

introscope.agent.sqlagent.normalizer.regex.keys=key1,key2,key3,key4

introscope.agent.sqlagent.normalizer.regex.key1.pattern=SELECT.*FROM.*

introscope.agent.sqlagent.normalizer.regex.key1.replaceAll=true

introscope.agent.sqlagent.normalizer.regex.key1.replaceFormat=SELECT FROM

introscope.agent.sqlagent.normalizer.regex.key1.caseSensitive=false

introscope.agent.sqlagent.normalizer.regex.key2.pattern=CREATE.*TABLE.*

introscope.agent.sqlagent.normalizer.regex.key2.replaceAll=true

introscope.agent.sqlagent.normalizer.regex.key2.replaceFormat=CREATE TABLE

introscope.agent.sqlagent.normalizer.regex.key2.caseSensitive=false

introscope.agent.sqlagent.normalizer.regex.key3.pattern=DROP.*TABLE.*

introscope.agent.sqlagent.normalizer.regex.key3.replaceAll=true

introscope.agent.sqlagent.normalizer.regex.key3.replaceFormat=DROP TABLE

introscope.agent.sqlagent.normalizer.regex.key3.caseSensitive=false

introscope.agent.sqlagent.normalizer.regex.key4.pattern=INSERT.*INTO.*

introscope.agent.sqlagent.normalizer.regex.key4.replaceAll=true

introscope.agent.sqlagent.normalizer.regex.key4.replaceFormat=INSERT INTO

introscope.agent.sqlagent.normalizer.regex.key4.caseSensitive=false

Dear Community:

Can you provide an answer/next step for Daniel?

Thanks

Hal German

If the normalizer is giving problems, I'd recommend using the 'maxlength' property.

introsocope.agent.sqlagent.sql.maxlength=***

where *** > 1 (default is 990)

Start at 500 and go up or down from there as you see fit.

Hello Hiko,

                   Is this a hot config or managed application should be restarted? I don’t see that in the agent profile so I need to add that?

Thanks,

Daniel Addo

Hello,

          Since this is production, the managed server is scheduled to be restarted in mid September, however there was an article which stated to turn off sql statement in sqlagent.pbd file. I did that to no avail.

Follow these steps:

1.    Open the sqlagent.pbd file and locate the SQL statements. For example:

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;Ljava/sql/ResultSet; DbCommandTracer "Backends||SQL||Query|" 2.    Remove from the trace directives you want to turn off. For example: TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/ lang/String;)Ljava/sql/ResultSet; DbCommandTracer "Backends||SQL||Query"

3.    Save the sqlagent.pbd file.

The SQL statement metrics in the SQL agent have been turned off.

From <https://docops.ca.com/ca-apm/9-7/en/implementing-agents/java-agent/configure-java-monitoring/configure-sql-monitoring-for-java#ConfigureSQLMonitoringforJava-RegularExpressionSQLStatementNormalizerExamples

Please help if you know how to properly use it.

Thanks,

Daniel Addo

It is not a hot property and yes, it needs to be added by you.

Hello Daniel, 

If you find an issue using the sqlnormalizer in specific OS and you have evidences, then I suggest you open a support case and include agent debug logs and if possible  screenshots.

However, if you are looking for ways to prevent a metric explosion from SQLAgent, then you can use the below options, some already discussed:

a) Reduce the length of SQL statements. The default maximum length captured by the agent is 999. You can modify this by adding the following line to the IntroscopeAgent.profile file:
introscope.agent.sqlagent.sql.maxlength=

b) Set the following properties available since 9.1:
introscope.agent.sqlagent.sql.turnoffmetrics=true
introscope.agent.sqlagent.sql.artonly=true
introscope.agent.sqlagent.sql.turnofftrace=true

c) Use New SQLAgent normalization mechanism - this is the one you are currently using.

d) Turn Off SQL Statement Metrics but keep the backend or top-level JDBC metrics

Open the sqlagent.pbd file and locate the SQL statements. For example:

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/lang/String;)Ljava/sql/ResultSet; DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query|{sql}"

Remove {sql} from the trace directives you want to turn off. For example:

TraceOneMethodWithParametersIfFlagged: SQLAgentStatements executeQuery(Ljava/ lang/String;)Ljava/sql/ResultSet; DbCommandTracer "Backends|{database}|SQL|{commandtype}|Query"

see https://docops.ca.com/ca-apm/10-7/en/implementing-agents/java-agent/configure-java-monitoring/configure-sql-monitoring-for-java#ConfigureSQLMonitoringforJava-TurnOffSQLStatementMetrics 

I hope this helps,

Regards,
Sergio

I guess what I am struggling with is that the SQL normalizer is there to remove SQL statements without having to restart the manage application. So far that has failed as designed on AIX OS as it has worked for me on Windows OS. I have opened a ticket for that to be addressed. I am here to find out if anyone has encountered a similar demise and if so what was the resolution.

Thanks,

Daniel Addo

Can someone help Daniel with a good next step?