JDBD14 and J2EECompatibility

A few weeks ago we upgraded to the latest Oracle JDBC driver (ojdbc14). This caused some side effects which we have just understood. In particular:

  1. The property oracle.jdbc.J2EE13Compliant=true needs to be set for the driver to be fully compliant with J2EE (otherwise it works in oracle compatibility mode – i.e. broken). This is now set on all our central tomcat servers. If you are running tomcat locally you will need to set it yourself (see below).
  2. With this property set queries involving Timestamps return java.sql.Timestamp (which extends java.util.Date). This makes it easy to use Timestamps in JSP pages and with AIDA plots. Note, this could break existing code if it expects to get oracle.sql.Timestamp
  3. Subtracting timestamps in the oracle SQL query results in an oracle.sql.DURATION, which is hard to work with. Getting the timestamps into the JSP page and subtracting them there is easier.
Example.jsp
<sql:query var="data">
     select enddate,startdate,submitdate,cpusecondsused
       from processinstance PI
       where PI.process = ?
       <sql:param value="${row.PROCESS}"/>
</sql:query> 


<aida:tuple var="tuple" query="${data}" />    
<aida:tupleProjection var="wallPlot" tuple="${tuple}" xprojection="(ENDDATE-STARTDATE)/60"/>
<aida:tupleProjection var="waitPlot" tuple="${tuple}" xprojection="(STARTDATE-SUBMITDATE)/60"/>

Full code

Setting the J2EE13Compliant property

Command Line

setenv JAVA_OPTS "-server -Doracle.jdbc.J2EE13Compliant=true"

Netbeans

Windows Tomcat

Sparsifying time dependent data with Oracle SQL queries.

Often we have tables containing many (time related) data points, but we would like to plot only every nth point, or time average over n points. Both of these can be achieved using oracle's rownum built-in.

It is possible to get oracle to average arbitrary sets of consecutive points using this following query. We used this in the Pipeline II usage plots, but it may be useful in other trending related queries.

Example2.jsp
<sql:query var="data">
    select min(entered) entered,avg(ready) ready,avg(submitted) submitted ,avg(running) running from 
    ( 
         select sum(ready) ready, sum(running) running, sum(submitted) submitted,entered
         from processingstatistics 
         where entered>=? and entered<=?
         <sql:dateParam value="${startRange}"/>
         <sql:dateParam value="${endRange}"/>
         group by entered order by entered

     ) group by  floor(rownum/?)
     <sql:param value="${groupby}"/>
</sql:query>

Full code

Usage Example

  • No labels