So I just wrapped up a very long support case (20095536) where my scheduled PRD's were missing data sources. The Performance Report Data (PRD) portlet had 4 sources in each graph and it was a known bug in base 9.20 that the scheduled reports of that PRD produced either Empty PDF reports. After the 2nd Hot fix it fixed it but I was still getting only 3 data sources.
The 2nd hot fix fixed this issue for other clients but for me it was still and issue. In my scheduled reports I was always missing a machine. When I investigated I noticed it was always my primary hub box.
On the support webEx when we looked at the S_QOS_DATA table, there were multiple entries for my primary hub and a few of those entries had NULL in the CI_METRIC_ID column. This was the issue.
The PRD portlet was able to graph the data points from my prihub box just fine but the report scheduler applet didn't' know how to handle NULL entries and it just didn't plot it in the PDF it produced.
Once we removed the bad NULL entries the scheduled reports then worked just fine.
My question is how do we clean up our UIM db with all these bad NULL entries in the S_QOS_DATA table?
The query to checked against the LAB DB produced only 42 rows:
--Check all our CI_METRIC_ID that are = to NULL
select table_id,ci_metric_id,created,origin,robot,source,probe,qos,target,r_table
from s_qos_data
where ci_metric_id is NULL
order by origin, r_table, robot, probe, source
But in my PROD DB I have 247k+ rows returned.
What is the proper procedure to clean this all up? During the WebEx we checked each and every corresponding RN_QOS_DATA_#### table to see if any data was present and then we deleted the NULL entry in the S_QOS_DATA table.
--Check the Table_ID's that all have a CI_METRIC_ID = NULL
select * from RN_QOS_DATA_0010 where table_id = 1813524 order by sampletime desc
I can't do that with 247k entries.
The delete SQL command to clean up the lab was just:
delete from s_qos_data where ci_metric_id is NULL where table_id = <primary_hub_table_ID's that were NULL>
But what can I do to clean this up in PROD on a much larger scale?
delete from s_qos_data where ci_metric_id is NULL
------------------------------
Daniel Blanco
Enterprise Tools Team Architect
DBlanco@alphaserveit.com------------------------------