Troubleshooting
Problem
When using the records affected column in Guardium reports or alerts, you notice that the value is not as you expect. The number is a positive value but is not the exact number of records that the SQL statement affected.
Symptom
Records Affected value is positive but incorrect. For negative or zero values see "Log Records Affected" in "Table 1. Settings that Apply to All Inspection Engines" here:
Inspection Engine Configuration
Inspection Engine Configuration
Cause
There can be many causes for incorrect reporting of records affected. In general the functionality is dependant on the performance of the Guardium sniffer process, which analyzes, parses and logs data into the collector. The sniffer is designed to prioritise client to server traffic e.g. connection information and SQL executed on the database. In some cases this priority means that the records affected, which is server to client information, will not be accurate.
Known causes of incorrect reporting of records affected are:
Applications e.g. SQL Developer, TOAD, can break up the request-response flow to the database to display information in the user interface. For example they may make a subset of the response results available initially and the rest available after 'scrolling' in the application. This results in the sequence of response packets from the server to the client being interrupted.
Since each application is unique, Guardium may not be able to interpret the request-response flow as expected. Due to priority of client to server performance, the sniffer can not calculate the records affected accurately in this interrupted data flow. Therefore it can report the records affected with unexpected values.
Known causes of incorrect reporting of records affected are:
- Packet drops due to performance issues
- Result sets that are too large for the sniffer to handle
- Missed login packets
- Unsupported older database protocols
- Unexpected application behaviour or interruptions in the data stream
Applications e.g. SQL Developer, TOAD, can break up the request-response flow to the database to display information in the user interface. For example they may make a subset of the response results available initially and the rest available after 'scrolling' in the application. This results in the sequence of response packets from the server to the client being interrupted.
Since each application is unique, Guardium may not be able to interpret the request-response flow as expected. Due to priority of client to server performance, the sniffer can not calculate the records affected accurately in this interrupted data flow. Therefore it can report the records affected with unexpected values.
Diagnosing The Problem
1. In your monitored database run a command that will affect a known number of records. e.g
2. Check the Records Affected value for that statement in the Guardium report.
- select * from table limit 1000;
2. Check the Records Affected value for that statement in the Guardium report.
If the value is positive and incorrect it could be caused by one of the points above. Note that you might get a different result if you use a different method of access to the database.
Resolving The Problem
1. Ensure that you do not have any performance problems with the sniffer. Check the GUI->Guardium Monitor->Buffer Usage Monitor. Basic indicators of a potential performance problem are:
For more information on sniffer performance check section 7.1.2 in the deployment guide. If you have any doubts about the performance Guardium support can help. Attach the output of "support must_gather sniffer_issues" if you contact support.
- ALP column is non 0
- TID column is changing more than a few times per day
- Analyzer Queue Length is often above 0
- Logger Queue Length is often above 0
For more information on sniffer performance check section 7.1.2 in the deployment guide. If you have any doubts about the performance Guardium support can help. Attach the output of "support must_gather sniffer_issues" if you contact support.
2. If the problem has not changed after ruling out sniffer performance, then unexpected application behaviour or protocol is the likely cause. Guardium support will treat these types of problems on a case by case basis. Note that it may not be possible to resolve this problem if resolution will cause a high impact on sniffer performance.
When contacting support please send:
- Output of "support must_gather sniffer_issues"
- Slon capture run while you execute the SQL that reproduces the problem
- Note of the exact SQL captured in the slon, the number of records affected on the database and in the report.
Related Information
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSMPHH","label":"IBM Security Guardium"},"ARM Category":[{"code":"a8m0z000000Gp0SAAS","label":"SNIFFER"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Was this topic helpful?
Document Information
Modified date:
01 May 2020
UID
swg21692106