Cloud Cruiser became HPE Consumption Analytics on Nov. 1st. You'll still see the old name in places while we update this site.

 

 

Consumption Analytics Documentation

Home > Cloud Cruiser 4 > Collecting, transforming, and publishing > JDBC

JDBC

The SmartJdbcCollector is capable of extracting data from a database using the JDBC protocol. A sample job file is located at <install_dir>/job_samples/jdbc_collect.xml.

As with all collectors, a data source is required to establish a connection for collecting data. For information about creating a data source and about drivers required for a JDBC data source, see Data sources.

Mapping data to HPE Consumption Analytics Portal

Output mappings are required, and can be column numbers starting at 1, column names as per the Select clause, or input names if input mappings are specified. Input mappings are optional for JDBC collection and can be used to assign names to column numbers that can be referenced from output mappings.

Feed configuration

A JDBC Feed is any data that can be retrieved via SQL using JDBC. The configuration defines a request and maps the results to CC Record content. Both queries and stored procedures are supported. This implementation uses cursors and might not be compatible with all database types.

The request can be parameterized with each parameter value specified separately by position and type. Column names from the result set can be referenced as input field names when defining OutputField objects. InputField objects are supported, but not generally used here because a column can be referenced, and even renamed, by just using OutputField .

Time and date

It is recommended to convert date and times stored in the database to strings as part of the request. This eliminates any deviations around DST changes that might be introduced by the JDBC driver. To do this, use the JdbcFeedConfig’s adjustTimestamps property.

Most JDBC drivers return date and time values such that results printed in the local time zone match the original values in the database. Date and time values are first translated to the number of milliseconds since "the epoch" by the JDBC driver. This offset in milliseconds is supposed to be 'UTC', or GMT-00:00, and will be wrong if the dates and times in the database were based on a time zone other than the current default.

Time zone aware databases, such as H2, provide accurate results that can be used directly by data collection. In these cases, timestamp conversion should be disabled here.

This is not the same as converting dates and times from another time zone. If the database content is GMT-05:00, then "10:00" in the database is supposed to mean 10:00 GMT-05:00 or 15:00 GMT. When a client in GMT-08:00 queries the data, most databases drivers will create a value that is 18:00 GMT. This will then print out as "10:00" when the milliseconds are applied to a calendar for the local zone. Time zone conversion is the adjustment from GMT-05:00 to UTC for collection. This conversion is to flip the GMT-08:00 value back to a GMT-05:00 value before collection then converts from GMT-05:00 to UTC.

Spring Batch XML example

<batch:step id="collect" next="assemble">
  <batch:tasklet>
    <bean class="com.cloudcruiser.batch.CCRecordCollectTasklet">
      <property name="active" value="true"/>
      <property name="feedName" value="ccdb"/>
      <property name="collector">
        <bean id="sampleJdbcReader" class="com.cloudcruiser.batch.collect.SmartJdbcCollector">
          <property name="feedConfig" ref="feedConfig"/>
          <property name="fetchSize" value="‐1"/>
          <property name="maxRows" value="‐1"/>
          <property name="queryTimeout" value="‐1"/>
          <property name="ignoreWarnings" value="true"/>
          <property name="verifyCursorPosition" value="false"/>
          <property name="dataSourceConfig">
            <bean class="com.cloudcruiser.batch.collect.DataSourceConfig">
              <property name="url" value="jdbc:jtds:sqlserver://127.0.0.1::1433/ccdb" />
              <property name="username" value="cloudcollect" />
              <property name="password" value="1234" />
            </bean>
          </property>
        </bean>
      </property>
    </bean>
  </batch:tasklet>
</batch:step>
Last modified

Tags

This page has no custom tags.

Classifications

This page has no classifications.
© Copyright 2018 Hewlett Packard Enterprise Development LP