This discussion is archived
8 Replies Latest reply: May 22, 2012 11:39 PM by 937607 RSS

MetricDataService:getHistoricalData:SQLException:java.sql.SQLException

937607 Newbie
Currently Being Moderated
Hello,

I have created a plugin to display energy consumption and cost.
When the target instance is deployed/redeployed to the agent and monitored using EM console I can see the right data displayed.
If I logout of em console or I try to access the same target Instance from a different machine using the em console I get the following errror.

**2012-05-16 11:45:47 [MpCui] ERROR Unhandled Error [Unexpected Error]: An unexpected error has occurred while processing the page or dialog "MetaDataPageActivityDef[homePg]". The user interface item "mpDefault0...LineChart138" has reported the error: faultCode:DataServiceErr faultString:'An error occurred calling the metric data service.' faultDetail:'MetricDataService:getHistoricalData:SQLException:java.sql.SQLException: Invalid column name'*The UI for monitoring is Implemented using the declarative/Metadata method of MPCUI framework.*

Below I am listing my mpcui ,targettype and default collection files.

TargetType.xml
************************************************************************************************
<TargetMetadata META_VER="1.0" TYPE="emerson_trellis_ececType">
<Display>
<Label NLSID="ecec_displayname">Emerson Trellis ECEC Target Type</Label>
</Display>

<Metric NAME="Response" TYPE="TABLE">

     <Display>
          <Label NLSID="ecec_response_displayname">Response Metric</Label>
     </Display>

     <TableDescriptor>
          <ColumnDescriptor NAME="Performance" TYPE="STRING" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_response_performanceLabel">Performance</Label>
               </Display>
          </ColumnDescriptor>
          <ColumnDescriptor NAME="ExtraDetails" TYPE="STRING" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_response_extraDetailsLabel">Extra Details</Label>
               </Display>
          </ColumnDescriptor>
          <ColumnDescriptor NAME="Status" TYPE="NUMBER" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_response_statusLabel">Status</Label>
               </Display>
          </ColumnDescriptor>

     </TableDescriptor>
     <QueryDescriptor FETCHLET_ID="OSLineToken">
          <Property NAME="scriptsDir" SCOPE="SYSTEMGLOBAL">scriptsDir</Property>
          <Property NAME="classpath" SCOPE="GLOBAL">$CLASSPATH:%scriptsDir%/JavaFetchlet.jar</Property>
          <Property NAME="command" SCOPE="GLOBAL">java -cp %classpath% acme.test.TestPlugin status</Property>
          <Property NAME="startsWith" SCOPE="GLOBAL">etx_result=</Property>
          <Property NAME="delimiter" SCOPE="GLOBAL">|</Property>
     </QueryDescriptor>
</Metric>

<Metric NAME="EnergyConsumptionCost" TYPE="TABLE">
<Display>
<Label NLSID="ecec_energyCosumptionCost">Energy Consumption Energy Cost Metric</Label>
</Display>

<TableDescriptor>
     <ColumnDescriptor NAME="DeviceName" TYPE="STRING" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_deviceName">Device Name</Label>
               </Display>
     </ColumnDescriptor>
     <ColumnDescriptor NAME="EnergyConsumption" TYPE="STRING" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_energyConsumption">Energy Consumption</Label>
               </Display>
     </ColumnDescriptor>
     <ColumnDescriptor NAME="EnergyCost" TYPE="STRING" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_energyCost">Energy Cost</Label>
               </Display>
     </ColumnDescriptor>
     <ColumnDescriptor NAME="ExtraDetails" TYPE="STRING" IS_KEY="FALSE">
               <Display>
                    <Label NLSID="ecec_extraDetails">Extra Details</Label>
               </Display>
     </ColumnDescriptor>

</TableDescriptor>
     
     <QueryDescriptor FETCHLET_ID="OSLineToken">
          <Property NAME="scriptsDir" SCOPE="SYSTEMGLOBAL">scriptsDir</Property>
          <Property NAME="classpath" SCOPE="GLOBAL">$CLASSPATH:%scriptsDir%/JavaFetchlet.jar</Property>
          <Property NAME="command" SCOPE="GLOBAL">java -cp %classpath% acme.test.TestPlugin %deviceName% </Property>
          <Property NAME="startsWith" SCOPE="GLOBAL">etx_result=</Property>
          <Property NAME="delimiter" SCOPE="GLOBAL">|</Property>
     </QueryDescriptor>
</Metric>

<InstanceProperties>
<InstanceProperty NAME="deviceName" OPTIONAL="FALSE">
<Display>
<Label NLSID="DeviceName_iprop">Device Name</Label>
</Display>
</InstanceProperty>
</InstanceProperties>

</TargetMetadata>
*****************************************************************************************************************************************
MPCUI
<?xml version="1.0" encoding="UTF-8" ?>
<CustomUI target_type="emerson_trellis_ececType"
xmlns="http://www.oracle.com/EnterpriseGridControl/MpCui"
xmlns:mx="http://www.adobe.com/2006/mxml"
xmlns:mp="http://www.oracle.com/mpcui">
     <SqlStatements/>
<UIMetadata>
<Integration>
<mp:Integration targetType="emerson_trellis_ececType">
<mp:PageActivityDef id="homePg" label="Home" isDefaultPage="true" />
</mp:Integration>
</Integration>
<ActivityDefinition>
<mp:Page id="homePg" label="Home Page">
<mp:services>
<mp:MetricValuesDataService id="mv1" flattenData="true" targetName="{appModel.target.name}" targetType="{appModel.target.type}"
                                   metricName="EnergyConsumptionCost"                                    columns="{['DeviceName','EnergyConsumption','EnergyCost','ExtraDetails']}"
                                   timePeriod="REAL_TIME" />


<mp:AvailDataService id="ads"      targetName="{appModel.target.name}" targetType="{appModel.target.type}" />

</mp:services>
          <mx:VBox height="100%" width="100%">
               <mx:HBox height="33%" width="100%">
                    <mp:Region title="Status" width="100%" height="100%">
                         <mp:InfoDisplay id="statusInfo">
                              <mp:InfoItem label="Target Name :" value="{appModel.target.name}"/>
                              <mp:InfoItem label="Target Type :" value="{appModel.target.type}"/>
                              <mp:InfoItem label="Status" value="{ads.currentStatus}" image="{ads.currentStatusIcon}" />
                              <mp:InfoItem label="statusSince" value="{ads.statusSince}"/>
                         </mp:InfoDisplay>
                    </mp:Region>
               </mx:HBox>
          <mx:HBox height="33%" width="100%">
                    <mp:Region title="Energy Consumption" width="100%" height="100%">
                    <mp:LineChart id="energyConsumption" width="100%" height="100%" targetName="{appModel.target.name}" targetType="{appModel.target.type}" metricName="EnergyConsumptionCost" metricColumns="['EnergyConsumption']" timePeriod="REALTIME" interval="15" />
                    </mp:Region>
               </mx:HBox>
               <mx:HBox height="33%" width="100%">
                    <mp:Region title="Energy Cost" width="100%" height="100%">
<mp:LineChart id="energyCost" width="100%" height="100%" targetName="{appModel.target.name}" targetType="{appModel.target.type}" metricName="EnergyConsumptionCost" metricColumns="['EnergyCost']" timePeriod="REALTIME" interval="15" />
                    </mp:Region>
               </mx:HBox>
          </mx:VBox>

          </mp:Page>
     </ActivityDefinition>
     </UIMetadata>
</CustomUI>
**********************************************************************************************************************************************************
default Collection file

<TargetCollection TYPE="emerson_trellis_ececType">
<CollectionItem NAME="Response Metric">
<Schedule>
<IntervalSchedule INTERVAL="5" TIME_UNIT="Min"/>
</Schedule>
     <MetricColl NAME="Response">
          <Condition COLUMN_NAME="Status" CRITICAL="1" OPERATOR="LT"/>
     </MetricColl>
</CollectionItem>
<CollectionItem NAME="EnergyConsumptionCost">
<Schedule>
<IntervalSchedule INTERVAL="5" TIME_UNIT="Min"/>
</Schedule>
</CollectionItem>
</TargetCollection>
******************************************************************************************************************************************************

Deployments are done using OEM 12c.

Please suggest where I am wrong in my implementation.

Regards
Anand.
  • 1. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    jriel Newbie
    Currently Being Moderated
    I don't see anything clearly wrong in your target type or mpcui meta-data though the error indicates a request for historical data (MetricDataService.getHistoricalData) which does not agree with anything in the mpcui code (all your references are to real-time). Also, in the browser where you see this error, can you go to the Target menu and go to the Monitoring->All Metrics page. On this page please verify that you can view the metric you are referencing from your mpcui meta-data (e.g. the EnergyConsumptionCost metric, the EnergyConsumption and EnergyCost columns) and that you can see data history and also real-time values. You do this by selecting a column and then use the "View Data" dropdown to change the period from Real-Time to any of the historical ranges.

    When you go to the homepage where you see the error, please add "&loglevel=ALL,CONSOLE&traceEnabled=true" to the URL in the browser address bar and then reload the page. A small logging window should appear near the bottom of the page. Once the error has occurred please capture the messages logged to this window and either post them here or e-mail them to me (jonathan.riel@oracle.com).
  • 2. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    937607 Newbie
    Currently Being Moderated
    Hello Jonathan
    Thanks for your response !

    I am sending u the details of the log message generated and the SOAP Message along with the screen shots for metric collection at the following email id jonathan.riel@oracle.com.

    One more doubt that I want to ask

    At present the metric "EnergyConsumptionCost" doesnot have any Key column defined and I am gathering the data is real time,so when my fetchlet is called it returns me the sample data with different values for EnergyCost and EnergyConsumption,i.e fetchlet returns multiple rows for the same deviceName instance Property.

    The Agent will try to store this values in repository.

    If we donot define the Key column will that effect the working of home page when we logout of the em console.

    *******************************************************************************************

    To check the behaviour I added a column named "SrNo" of type Number and defined it as " IS_TRUE=TRUE" , generated the .opar file and imported.
    When I deploy the generated opar file to OMS server my target_type.xml file has my key column missing because of which the sql script generated doenot have any mention of my "SrNo" column. How can this be resolved.

    Regards
    Anand.
  • 3. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    jriel Newbie
    Currently Being Moderated
    There are a couple of issues that we’ll need to discuss with you. The first is to help you understand metric keys, how to use them and how to version your meta-data. One of us will get back to you on that or have someone else from the EM team respond on that issue.

    The second problem relates to your MPCUI meta-data and the error you are seeing. Based on the screenshots and logs you’ve sent (thanks this is very helpful), you’ve created the “EnergyConsumptionCost” metric but have not added anything to the default_collection for it, so there is no historical data saved to the repository. This is confirmed in the All Metrics page where you see no historical data.

    In your MPCUI code you have several LineCharts that reference the metric and use the timePeriod=”REALTIME”. You also have the MetricValuesDataService tag below., and it includes the timePeriod=”REAL_TIME”.

    <mp:MetricValuesDataService id="mv1" flattenData="true" targetName="{appModel.target.name}" targetType="{appModel.target.type}"
    metricName="EnergyConsumptionCost" columns="{['DeviceName','EnergyConsumption','EnergyCost','ExtraDetails']}"
    timePeriod="REAL_TIME" />

    There are two things here. The first problem with this tag is “REAL_TIME”. This is a typo and should be “REALTIME” if you needed to keep this. I don’t see any error in the meta-data handling in the TraceLog.txt that indicates this is a problem, so this is something we need to address on our end.

    The second issue is that you don’t reference this dataservice (mv1) anywhere in your mpcui meta-data. Because you don’t actually use this you can safely remove this tag from your meta-data. You only need to declare a dataservice (MetricValuesDataService, SqlDataService, etc.) if you are going to use it within a component elsewhere.
  • 4. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    937607 Newbie
    Currently Being Moderated
    Hi

    Made the chages specified but problem persist.I am uploading the new chages that I have made.

    TargetType.xml
    <TargetMetadata META_VER="1.3" TYPE="emerson_trellis_ececType">
    <Display>
    <Label NLSID="ecec_displayname">Emerson Trellis ECEC Target Type</Label>
    </Display>

    <Metric NAME="Response" TYPE="TABLE">

         <Display>
              <Label NLSID="ecec_response_displayname">Response Metric</Label>
         </Display>

         <TableDescriptor>
              <ColumnDescriptor NAME="Performance" TYPE="STRING" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_response_performanceLabel">Performance</Label>
                   </Display>
              </ColumnDescriptor>
              <ColumnDescriptor NAME="ExtraDetails" TYPE="STRING" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_response_extraDetailsLabel">Extra Details</Label>
                   </Display>
              </ColumnDescriptor>
              <ColumnDescriptor NAME="Status" TYPE="NUMBER" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_response_statusLabel">Status</Label>
                   </Display>
              </ColumnDescriptor>

         </TableDescriptor>
         <QueryDescriptor FETCHLET_ID="OSLineToken">
              <Property NAME="scriptsDir" SCOPE="SYSTEMGLOBAL">scriptsDir</Property>
              <Property NAME="classpath" SCOPE="GLOBAL">$CLASSPATH:%scriptsDir%/JavaFetchlet.jar</Property>
              <Property NAME="command" SCOPE="GLOBAL">java -cp %classpath% acme.test.TestPlugin status</Property>
              <Property NAME="startsWith" SCOPE="GLOBAL">etx_result=</Property>
              <Property NAME="delimiter" SCOPE="GLOBAL">|</Property>
         </QueryDescriptor>
    </Metric>

    <Metric NAME="EnergyConsumptionCost" TYPE="TABLE" USAGE_TYPE="VIEW_COLLECT" FORCE_CACHE="TRUE">
    <Display>
    <Label NLSID="ecec_energyCosumptionCost">Energy Consumption Energy Cost Metric</Label>
    </Display>

    <TableDescriptor>
         <ColumnDescriptor NAME="SerialNo" TYPE="NUMBER" IS_KEY="TRUE">
                   <Display>
                        <Label NLSID="ecec_SerialNo">Serial No</Label>
                   </Display>
         </ColumnDescriptor>
         <ColumnDescriptor NAME="DeviceName" TYPE="STRING" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_deviceName">Device Name</Label>
                   </Display>
         </ColumnDescriptor>
         <ColumnDescriptor NAME="EnergyConsumption" TYPE="STRING" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_energyConsumption">Energy Consumption</Label>
                   </Display>
         </ColumnDescriptor>
         <ColumnDescriptor NAME="EnergyCost" TYPE="STRING" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_energyCost">Energy Cost</Label>
                   </Display>
         </ColumnDescriptor>
         <ColumnDescriptor NAME="ExtraDetails" TYPE="STRING" IS_KEY="FALSE">
                   <Display>
                        <Label NLSID="ecec_extraDetails">Extra Details</Label>
                   </Display>
         </ColumnDescriptor>

    </TableDescriptor>
         
         <QueryDescriptor FETCHLET_ID="OSLineToken">
              <Property NAME="scriptsDir" SCOPE="SYSTEMGLOBAL">scriptsDir</Property>
              <Property NAME="classpath" SCOPE="GLOBAL">$CLASSPATH:%scriptsDir%/JavaFetchlet.jar</Property>
              <Property NAME="command" SCOPE="GLOBAL">java -cp %classpath% acme.test.TestPlugin %deviceName% </Property>
              <Property NAME="startsWith" SCOPE="GLOBAL">etx_result=</Property>
              <Property NAME="delimiter" SCOPE="GLOBAL">|</Property>
         </QueryDescriptor>
    </Metric>

    <InstanceProperties>
    <InstanceProperty NAME="deviceName" OPTIONAL="FALSE">
    <Display>
    <Label NLSID="DeviceName_iprop">Device Name</Label>
    </Display>
    </InstanceProperty>
    </InstanceProperties>

    </TargetMetadata>
    *****************************************************************************************************************************************************
    Collection.xml

    <TargetCollection TYPE="emerson_trellis_ececType">
    <CollectionItem NAME="Response Metric">
    <Schedule>
    <IntervalSchedule INTERVAL="2" TIME_UNIT="Min"/>
    </Schedule>
         <MetricColl NAME="Response">
              <Condition COLUMN_NAME="Status" CRITICAL="1" OPERATOR="LT"/>
         </MetricColl>
    </CollectionItem>

    <CollectionItem NAME="EnergyConsumptionCostGroup" UPLOAD_ON_FETCH="TRUE">
    <Schedule>
    <IntervalSchedule INTERVAL="3" TIME_UNIT="Min"/>
    </Schedule>
         <MetricColl NAME="EnergyConsumptionCost" />
    </CollectionItem>

    </TargetCollection>

    ****************************************************************************************************************************************************
    MPCUI.xml

    <?xml version="1.0" encoding="UTF-8" ?>
    <CustomUI target_type="emerson_trellis_ececType"
    xmlns="http://www.oracle.com/EnterpriseGridControl/MpCui"
    xmlns:mx="http://www.adobe.com/2006/mxml"
    xmlns:mp="http://www.oracle.com/mpcui"
    xmlns:cht="oracle.sysman.emx.components.charts.*">
    <UIMetadata>
    <Integration>
    <mp:Integration targetType="emerson_trellis_ececType">
    <mp:PageActivityDef id="homePg" label="Home" isDefaultPage="true" />
    </mp:Integration>
    </Integration>
    <ActivityDefinition>
    <mp:Page id="homePg" label="Home Page">
    <mp:services>

    <mp:AvailDataService id="ads"      targetName="{appModel.target.name}" targetType="{appModel.target.type}" />

    </mp:services>
              <mx:VBox height="100%" width="100%">
                   <mx:HBox height="33%" width="100%">
                        <mp:Region title="Status" width="100%" height="100%">
                             <mp:InfoDisplay id="statusInfo">
                                  <mp:InfoItem label="Target Name :" value="{appModel.target.name}"/>
                                  <mp:InfoItem label="Target Type :" value="{appModel.target.type}"/>
                                  <mp:InfoItem label="Status" value="{ads.currentStatus}" image="{ads.currentStatusIcon}" />
                                  <mp:InfoItem label="statusSince" value="{ads.statusSince}"/>
                             </mp:InfoDisplay>
                        </mp:Region>
                   </mx:HBox>
              <mx:HBox height="33%" width="100%">
                        <mp:Region title="Energy Consumption" width="100%" height="100%">
                        <cht:LineChart id="energyConsumption" width="100%" height="100%" targetName="{appModel.target.name}" targetType="{appModel.target.type}" metricName="EnergyConsumptionCost" metricColumns="['EnergyConsumption']" polling="true" interval="15"/>
                        </mp:Region>
                   </mx:HBox>
                   <mx:HBox height="33%" width="100%">
                        <mp:Region title="Energy Cost" width="100%" height="100%">
    <cht:LineChart id="energyCost" width="100%" height="100%" targetName="{appModel.target.name}" targetType="{appModel.target.type}" metricName="EnergyConsumptionCost" metricColumns="['EnergyCost']" polling="true" interval="15"/>
                        </mp:Region>
                   </mx:HBox>
              </mx:VBox>

              </mp:Page>
         </ActivityDefinition>
         </UIMetadata>
    </CustomUI>

    ******************************************************************************
  • 5. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    jriel Newbie
    Currently Being Moderated
    A couple of things. Are you now able to see both real-time and historical data in the All Metrics page? Can you please send me updated logs with traceEnabled turned on and the request/response messages for the failed request?

    Finally, collecting a metric historically every 2 or 3 minutes is not typical. Given the possible volume of data we don't recommend collecting metrics more frequently than every 5 minutes.
  • 6. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    937607 Newbie
    Currently Being Moderated
    Hello

    Sending the needed trace files to your mail Id
  • 7. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    jriel Newbie
    Currently Being Moderated
    The problem is that the columns to be plotted in the LineChart are "STRING" type and need to be "NUMBER" type. The error is not clear, but is indicating that the rollup tables don't include the metric columns being requested (because STRING columns aren't aggregated). We will file a bug to provide a better error message, but the datatype of the columns needs to be changed if they are going to be shown in a LineChart.
  • 8. Re: MetricDataService:getHistoricalData:SQLException:java.sql.SQLException
    937607 Newbie
    Currently Being Moderated
    Hello Jonathan,

    Thanks for your suggestions and now the data on the UI is shown correctly.

    I have few queries

    1> Can we use the "CompareTarget" feature provided on the history page declaratively on our custom MPCUI page.
    2> Can you put some more light on the Key column defined in the Metric defination?When do we define them.
    MyUnderstanding is " Key column must be defined when a single call to the fetchlet returns more than one row"

    3> If I have to show the data for the user given date range on my line chart what approach I need to take.Declarative /Programmatic


    Regards
    Anand.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points