How to prevent Infrastructure agent MS SQL monitoring errors: Could not find stored procedure 'forAllDb'

Products

• CA Application Performance Management (APM / Wily / Introscope)

Issue/Introduction

[ERROR] [IntroscopeAgent.DBMonitor] 
com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'forAllDb'.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:232)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1672)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:903)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:796)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535)
 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:208)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:183)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:695)
 at com.ca.agent.extensions.dbmon.commands.DBMonExecuteQueryCommand.fireAndUpdateMetric(DBMonExecuteQueryCommand.java:158)
 at com.ca.agent.extensions.dbmon.commands.DBMonExecuteQueryCommand.execute(DBMonExecuteQueryCommand.java:123)
 at com.ca.agent.extensions.dbmon.DBMonChain.run(DBMonChain.java:66)
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 at java.lang.Thread.run(Thread.java:750)

In case database team doesn't want to deploy the store procedure 'forAllDb', how to prevent such errors? As this could be some security  risk,

Resolution

The default schemaExt.json in APMIA MySQL monitoring extension configuration includes some sql metric mappings relying on the "forAllDb" store procedure execution.  If the customer doesn't want to deploy the "forAllDb" store procedure, then they would also need to clean up those sql metric mappings that would reference the "forAllDb" store procedure.  To test, the customer could consider backing up the original schemaExt.json out of the config folder first and then modifying schemaExt.json to remove all sql metric mapping elements that would start with "query": "exec forAllDb @schema= . . .  

 

The default schemaExt.json has 2 of such sql metric mapping elements at the end of the element list for "sqlMetricMappings" attribute near the bottom of the schemaExt.json file.  Please ensure to remove the comma after the last element in the updated list for "sqlMetricMappings" attribute.