Remedy Smart Reporting - Error occurred when connecting to the database: java.lang.Exception: Source AR System is marked as unavailable

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy IT Service Management Suite


    COMPONENT:

    ITSM In-App Reporting 9.1.00


    APPLIES TO:

    Remedy Smart Reporting



    PROBLEM:

     

       
    • Smart Reporting Data Source connectivity keeps failing continuously. Due to which users are not able to run any reports.
    •  
    • Also, the Broadcast reports failing as the data source connection is not available.
      
    Steps to Reproduce:

    - Cross Launch to Smart Reporting from Mid Tier. 
    - Run any report and it throws error; failed to establish database connection. 
    - Broadcast any report and it fails to deliver to the users email. 
      
    Below is the error from the smartreporting.log file:
    Error occurred when connecting to the database: java.lang.Exception: Source AR System is marked as unavailable 
    java.lang.Exception: Source AR System is marked as unavailable  
       
    • This error occurs more than few times in an hour.

     


    CAUSE:

    Volatile Datasource entries missing in the database


    SOLUTION:

     

       
    • Enable to Volatile Data Sources in Smart Reporting to fix the data source connectivity issues. 
      
    What is Volatile Data Sources?

    This is a configuration setting that tells Yellowfin to check all of the data source connections every 30 seconds and, if a connection is bad or closed, to reset that connection.

    When do I need it?

    As data sources frequently reside on servers physically separate from the primary Yellowfin installation, they can be subject to bad and unstable connections. This will typically be reflected in the yellowfin.log with errors similar to 

    WARN (JDBCConnection:call) - Error occured when rolling back uncommited changes on connection: java.sql.SQLException: I/O Error: Connection reset

    connection exception: connection does not exist

    Or within the source.log files with similar correlated errors:

    Network error IOException: Connection refused: connect

    Connection object is closed

    Note that there are many more errors that signify the need for this setting, if you are having problems that you think may be connection related, it will worth enabling Volatile Data Sources. 

    How Do I enable Volatile Data Sources?

    To enable volatile data sources, you will need to run three queries directly against your configuration database. As querying your configuration database has the potential to cause permanent damage to your Yellowfin instance, it is highly recommended that you consult with your DBA to do this, and back up your database prior to making any changes.


    1. This query simply turns on the functionality

       
      INSERT INTO Configuration (iporg, configtypecode, configcode, configdata)
    VALUES (1, ‘SYSTEM’, ‘VOLATILESOURCES’, ‘TRUE’);

    This query sets how many milliseconds the timeout will be before retrying

    INSERT INTO Configuration (iporg, configtypecode, configcode, configdata)
    VALUES (1, ‘SYSTEM’, ‘VOLATILETIMEOUT’, ‘3000’);

    This query sets the number of times Yellowfin will retry

    INSERT INTO Configuration (iporg, configtypecode, configcode, configdata)
    VALUES (1, ‘SYSTEM’, ‘VOLATILERETRIES’, ‘5’);
      
     
      2.  To enable JDBC verify, back up and then update your web.xml file   
          
        <C:\Program Files\BMC Software\ARSystem\SmartReporting\appserver\webapps\ROOT\WEB-INF> web.xml

    Add:
       
       <init-param>
      <param-name>JDBCVerify</param-name>
      <param-value>true</param-value>
    </init-param>
      
       
        In the section where you see JDBCDriverClass or JDBCUrl or JDBCUser.   
      
       By setting JDBC Verify , it will test the repository database connection frequently. When the connection is interrupted, it will automatically reconnect.
    This eliminates the need to manually reset the connection.

    This change requires smart reporting service restart.
      
      
       
      

     


    Article Number:

    000140578


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles