IBM Support

Duplicate entries in database causing missing data in RST report.

Troubleshooting


Problem

API query returns nothing when there are duplicate entries in history table even though the data is available at GST and RST reports.

Symptom

When turn on the trace (Full DSO trace) using WAS console in the portal, we can find the below ORA- error messages in the trace.log

[4/1/14 15:00:18:063 MDT] 00000025 E PVRcDataSource reQuery() [WebContainer : 6] Parent Query = DVUnitRSTAllRows prepareStatment() failed: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

Cause

Sometimes duplicate entries in the database causes internal database queries to error out. This causes missing data in the RST report. This could happen with the metrics from SAM pack which changes the current subelement and property inventory, and all of those changes get automatically reflected in the appropriate history table. The SAM pack also updates property values, and those get updated in the history table automatically. When inserts and updates to the table happen within the same second, we see duplicates in the database tables for the same resource and metric, one for insert and one for the update. The history table in question is the prop_desc_hist table. These duplicates need to be removed via a script so that SQL statements can run properly to get data.

Resolving The Problem

Run the attached SQL script file as database user pv_admin against the PV database. The script will simply output the rows that will be updated. It will not update them actually. After the output is validated by support team, the script can be invoked to actually update the rows so that timestamps are no longer same. The update can take 10 min or so depending on the size of data. Check the log in /tmp/resolve_updatedupes_PRDH.<tstamp>.log to see the outcome of the script.

[{"Product":{"code":"SSBNJ7","label":"IBM Netcool Performance Manager"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"IBM Tivoli Netcool Performance Manager (TNPM Wireline) Platform","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"1.3.0;1.3.1;1.3.2;1.3","Edition":"All Editions","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
21 June 2018

UID

swg21670976