We have an issue with an application using unique tables (VWQueue_###) in statements and there are thousands. These statements appear to be called on the table a few times a day but the statement where clause is the same for each table. So, wanted to normalize the statements to be
Select from VWQueue
Delete from VWQueue
Insert into VWQueue
And then basically drop all of the Select statements that refer to VWQueue into the Select bucket.
#SELECT FROM F_SW.VWQUEUE1introscope.agent.sqlagent.normalizer.regex.VWQEUEUE_SELECT.pattern=(SELECT)(.*)(FROM F_SW.VWQUEUE1)(.*)introscope.agent.sqlagent.normalizer.regex.VWQEUEUE_SELECT.replaceAll=falseintroscope.agent.sqlagent.normalizer.regex.VWQEUEUE_SELECT.replaceFormat=SELECT FROM F_SW.VWQUEUE1introscope.agent.sqlagent.normalizer.regex.VWQEUEUE_SELECT.caseSensitive=false
So this is the fourth or fifth...maybe more attempt. The documentation, while the examples are nice really don't go into what the different parts are and how the formats work.
One thing that I found is if you take the pattern and paste it into the Introscope workstation - Metric Browser - search tab (with use regular expression checked), it does not work. But if you add a (.*) at the beginning the metric browser will return the statements.
Sort of hoping that the replace format will accept a basic string instead of the $1 $2 ...) $3 style since I only care that the selects go into one bucket, inserts in a different, etc.
I'll update this when I actually get it working for the next lost soul....
So, got the normalization configuration deployed and it was read dynamically, so didn't need to restart the JVMs. The configuration worked rather well. We had to let it set for a while till there was application activity that would issue the statements, but after about two days, we saw three of the six replacement patterns.
The static replacement format did work and they showed upon the investigator. The other three statements do not appear to have been issued but hopefully in the next day or so, there will be some test ran in the stress environment that we have this deployed so it will pick up the count, max and delete statements.
Hope this helps,
Thank you for your blogs and Community posts on your experiences!!! I find it very inspiring that you do this regularly and hope that others follow your example.