DX Infrastructure Manager

Expand all | Collapse all

How do you clean up ci_metric_id = NULL entries in DB?

Jump to Best Answer
  • 1.  How do you clean up ci_metric_id = NULL entries in DB?

    Posted 01-09-2020 02:06 PM
    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
    ------------------------------


  • 2.  RE: How do you clean up ci_metric_id = NULL entries in DB?
    Best Answer

    Posted 01-10-2020 02:33 AM
    Hi Daniel,
    Mailed you a test version of the tool: nimsoft_qos_delete where I added the option -e.  This option gives you the possibility to select qos records where the ci_metric_id is null.
    Regards


  • 3.  RE: How do you clean up ci_metric_id = NULL entries in DB?

    Posted 01-12-2020 10:26 AM
    Hi Luc

    Can you please send me this test version . Iam currently running 9.0.2 and upgrading to 9.20 will be difficult task right now

    thanks

    Santhosh


  • 4.  RE: How do you clean up ci_metric_id = NULL entries in DB?

    Posted 01-13-2020 08:14 AM
    Mailed you directly a test version 1.9.2 of nimsoft_qos_delete


  • 5.  RE: How do you clean up ci_metric_id = NULL entries in DB?

    Posted 28 days ago
      |   view attached
    In attachment a "test" version for nimsoft_qos_delete version 1.9.3

    Attachment(s)

    zip
    nimsoft_qos_delete_1.9.3.zip   34.82MB 1 version