Database troubleshooting

This topic provides information on how to analyze and troubleshoot problems that you might experience when using TrueSight Operations Management Report Engine with Oracle or SQL Server database.

Data collection stops

When using the Microsoft SQL Server database, if you assign the sysadmin role to a Report Engine database user, you might encounter data collection failures.

To resolve this issue, remove the sysadmin role from the Report Engine database user and restart the Report Engine service.

Java NullPointer Exception with DB Authentication Failure

When using the Microsoft SQL Server database, if the database login password is not set according to the password complexity policy, Report Engine might throw the Java NullPointer exception with DB authentication failure. 

To avoid this issue, perform the following on SQL server: 

  1. Navigate to Start > Control Panel > Administrative Tools > Local Security Policy.
  2. In the Local Security Policy console, expand Account Policies and select Password Policy.
  3. Ensure that the Password must meet complexity requirements option is Enabled.

Database troubleshooting queries

  • To check data collection, run the following query which will display date, time, number of data points collected for that hour, number of distinct devices, and number of distinct attributes from the previous day:

    select reuniv.rpt_to_char(reuniv.rpt_trunc(TIMERECORDED,'HH24'),'yyyy-mm-dd hh24:mi:ss'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from stats_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-1,'DD') group by reuniv.rpt_trunc(TIMERECORDED,'HH24') order by reuniv.rpt_trunc(TIMERECORDED,'HH24');

  • If you don't receive the daily_data and hourly_data in the database, check whether SQL Server Agent service or Oracle Scheduled job is running according to the database you are using.
  • The following query displays data from Hourly data table. If the number of devices and attributes are less than the above query, then it shows that the hourly summarization job is not working.

    select reuniv.rpt_to_char(TIMERECORDED,'dd-mm-yy'),count(*), count(distinct MOTYPEID), count(distinct ATTRIBUTEID) from daily_data where timerecorded> reuniv.rpt_trunc(reuniv.rpt_sysdate()-10,'DD') group by TIMERECORDED order by TIMERECORDED;

  • The following query lists all the attributes from which the Report Engine is collecting data. If you do not see attributes for which you want to collect data, then add them to TrueSight Infrastructure Management Server under external report attribute set.

    select reuniv.rpt_concat(reuniv.rpt_concat(ATTRIBUTESETNAME,','), ATTRIBUTENAME) from externalreportattributeset order by ATTRIBUTESETNAME, ATTRIBUTENAME;

  • To be able to get data in the reports in Oracle connection, perform the following steps:
    1. Login to the Report Engine Database using the reuniv DB user credentials.
    2. Execute following command where redbuser is the Report EngineDatabase user
      exec create_synonym_reuniv(‘redbuser’);
  • To get the errors related to Oracle jobs, run the get_re_log_table_message.sql script available at %BMC_PROACTIVENET_REPORTENGINE_HOME%\ReportEngine\schema\developer\oracle.
  • To get the error details related to the SQL Server job, run the following query and export the output in SQL Server Studio:

    select err_time ,err_msg from re_log with (nolock) order by id

Database jobs

The following table displays the various jobs that are scheduled in the Report Engine Database. You can use following schedules in case of any issues in job scheduling.

Database editionType of job

Job name and description

In SQL Server, the job names are similar except it has the
database and login name in the job title and names has _ instead of .

Scheduled time

Standard Edition and Enterprise Edition

Every hour

update_dst_offset;

Checks for the daylight saving time (DST) and makes changes when DST starts or ends.

14:00

Standard Edition and Enterprise Edition

Daily once

pr_analyze_config_tables;

Updates the table and view statistics in the database metadata.

0:00

Standard Edition and Enterprise Edition

Daily once

RE_DAILY_JOB;

Rebuilds the indexes to increase the efficiency of queries.

21:30

Standard Edition and Enterprise Edition

Every hour

RE_HOURLY_JOB;

Refreshes the VW_EXTENDED_COMPONENT_SLOT_D view.

14:00

Standard Edition and Enterprise Edition

Daily once

pk_purgedata.pr_purge_data;

Purges data according to the values in the data_retention table.

00:15 in Oracle.
22:00 in SQL Server

Enterprise Edition

Daily once

pk_updateSummaryTables_EE.FIND_NEW_DATA_IN_TABLE
('HOURLY_DATA');

This job is applicable only on Oracle.

Summarizes hourly data into daily data.

5:00

Standard Edition

Daily once

pk_updateSummaryTables.PR_HOURLYTODAILY;

Summarizes hourly data into daily data.

06:00 in Oracle.
00:15 in SQL Server

Enterprise Edition

Daily once

pr_scheduled_Add_partitions;

Adds day-wise future partitions for partitioned tables.

0:15

Standard Edition and Enterprise Edition

Every hour

pk_updateSummaryTables.PR_STATSTOHOURLY;

Summarizes stats data into hourly data.

14:00

Standard Edition and Enterprise Edition

Daily once

pk_create_thresholds.pr_call_CREATE_ALL_THRESHOLDS;

Refreshes threshold tables.

5:00

Standard Edition and Enterprise Edition

Every hour

MV_REFRESH_VW_CONFIGDATA;

This job is applicable only on Oracle.

Refreshes the VW_CONFIGDATA materialized view.

14:00

Standard Edition and Enterprise Edition

Daily once

pk_updateSummaryTables.MV_CREATEANDREFRESH;

This job is applicable only on Oracle.

Refreshes weekly, monthly, quarterly, and yearly summary data views.

0:00



To check the status of scheduled Oracle jobs

Use the following query to:

  • Check the status of the Oracle scheduled jobs.
  • Ensure that no job is broken.
  • Ensure that the next scheduled run shows the correct value where some jobs are scheduled for an hourly run and some jobs are scheduled for a daily run.

select to_char(next_date,'dd-mm-yyyy hh24:mi:ss')||', '||broken||', '|| what from user_jobs order by next_Date;

To check the status of SQL jobs 

Perform the following steps to check the status of SQL scheduled jobs:

  1. Access the Microsoft SQL Server Management Studio.
  2. Log in to the Report Engine database with valid credentials.
  3. In Object Explorer, expand SQL Server Agent.
  4. In SQL Server Agent, expand the Jobs folder. All jobs for the Report Engine database are displayed.
  5. Right click on the Jobs folder and select View History. The Log file viewer is displayed.
  6. In the Log file viewer, ensure that all Report Engine jobs are displayed as green. Ensure that the following message is displayed.
    The job succeeded.
Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Ashfaq Chataiwala

    pr_analyze_config_tables;

    Updates query optimization statistics in a table or an indexed view


    More appropriate would be...

    Updates tables and index statistics in database metadata


    Apr 10, 2018 07:53