developerWorks  >  Information Management | WebSphere | Java technology  | Web development  > Real-time data acquisition: Connecting your exercise bike to Informix or DB2 Using Java and Linux to capture real-time data

Document options Document options requiring JavaScript are not displayed Print this page PDF - Fits A4 and Letter 1077KB (25 pages) Get Adobe® Reader® E-mail this page Discuss Sample code Rate this page Help us improve this content

Level: Intermediate

Aron Y. Lurie (aron.lurie@gmail.com), 10th grader/webmaster, Newton South High School/Hebrew College
Marty Lurie (lurie@us.ibm.com), Information Technology Specialist, IBM?

27 Mar 2008

Capture data from an analog environment in real time and store it in an IBM ^® Informix^® Dynamic Server or DB2^® database. Use WebSphere^® Community Edition to create graphs of captured data and present them in Java^TM Server Pages. All the steps, including the installation and wiring of the magnetic sensors, the computer interface, the client OLTP code, and the JSP code for presentation of results is presented in an easy-to-follow format.

Introduction

The real world is analog. Computers are digital. This article shows you how to capture data from an analog environment in real time and store it in an Informix^® Dynamic Server or DB2^® database. See how to capture analog signals using a laptop and an Informix/DB2 OLTP client. Use WebSphere^® Community Edition to create graphs of captured data and present them in Java^TM Server Pages. Follow along as the authors describe the steps to install and wire magnetic sensors, the computer interface, the client OLTP code and the JSP code you use when presenting your results. easy-to-follow format.

Data capture: The analog versus the digital world

Nature stubbornly refuses to go digital. The typical member of the animal kingdom just doesn't appreciate the value of digital signal processing and is not likely to get a personal computer or cell phone. If the real world won't go digital, computers have to accommodate analog input. To make computers relate to the real world, we need sensors and analog-to-digital conversion. An exercise bicycle is a great example of how to interface a computer and an Informix or DB2 database to sensor data. Creating a row in a table for every revolution of a bicycle pedal also can motivate an interesting discussion about what information is worth capturing, what data is valuable and worth saving, and when data should be aggregated and archived.

What is real time?

The term "real time" has a range of interpretations. The key question to ask is: Is the system fast enough to keep up with the events it is trying to capture and process? A standard Linux^® distribution running Informix or DB2 and using Java as a programming language has significant variations in processing time. The CPU scheduler, file system sync, database checkpoint, Java garbage collection, and other factors conspire to create a worse-case response time. The good news is this worse-case response time, if significantly faster than the real time event cycle time, can still meet the requirements as described in the Wikipedia definition listed below.

Wikipedia defines real time in this context:
A system is said to be real-time if the total correctness of an operation depends not only upon its logical correctness, but also upon the time in which it is performed. The classical conception is that in a hard or immediate real-time system, the completion of an operation after its deadline is considered useless - ultimately, this may lead to a critical failure of the complete system. A soft real-time system on the other hand will tolerate such lateness, and may respond with decreased service quality (e.g., dropping frames while displaying a video).

If our system misses a bicycle pedal stroke it is not a catastrophe. However, if the anti-lock brake system in your car misses input data, the results are of much greater concern!

Our expectation is not that everyone reading this article connects their exercise equipment to a computer. (If you do, please consult with your doctor before trying to set any transaction records). The ideas presented here are intended to help you understand event capture and processing. These concepts can also be applied to general online transaction processing (OLTP). The system throughput discussion and the discussion about using the maximum processing time to predict transactional throughput for a single agent has wide applicability.

Back to top

Architecture

The architecture illustrated in Figure 1 shows the end-to-end system design. The exercise bicycle, representing any device generating real-time analog events, is illustrated on the left of the diagram. This device is connected to an analog-to-digital converter. In this example, the converter is a simple magnetic switch combined with a mouse. Once the analog world is transformed into a digital representation, a mouse click in this case, it is ready for processing by the application.

The Java application client in the illustration is a command line program. No Web interface is required. It simply receives a mouse click generated by the analog source, and inserts a corresponding row into Informix or DB2, shown in the dark blue in the diagram. As an OLTP application, Informix is IBM's recommended platform for this type of application. Porting this code to other platforms, such as DB2, is very straightforward and the code is provided in the sample files. A discussion of Informix and DB2 porting can be found in the Resources.

The reporting aspect of the system is shown on the far right of the illustration. In this article, WebSphere Application Server Community Edition (WASCE) is used for reporting. More information on this Java EE server is available in the Resources section. In the performance testing discussion, discover why creating the graph of the bicycle data as an image file is preferred to using a page-generated dynamic image. Also, see the advantages of using a Web2/Ajax component, JavaScript, to refresh only the graph and not the entire page.

Back to top

Analog to digital input

The example in this article uses a simple analog signal to convert to a digital pulse. When dealing with more complex analog input, you have to consider a variety of factors including the amplitude of the signal, the sample rate, the number of bits per sample, and more. See the wiki page discussing the analog-to-digital converter to get basic information about this topic.

The hardware selected provides a fantastic short cut to getting from the analog to the digital domain. Since we have a simple pulse input, a computer mouse or the Zeemote^® (a Bluetooth^® game controller) provides the connection to the computer, switch debounce, power, and the required software drivers. This makes the project significantly easier to implement! The switch debounce is not always perfect, and occasional spikes of very fast RPM can be seen in the output graph.

Using a wired mouse for input

Computer mice have been subject to much experimentation, with over half a million Google hits for "mouse hacking." The mouse for this project needs a magnetic switch in parallel with the mouse button. Figure 2 shows a voltmeter measuring the potential across the circuit board traces connected to the right mouse button. Yes, that really is the right hand button, the circuit board is inverted.

To access the switch function from outside the mouse, wires are soldered to the mouse circuit board. Since you never know what may prove useful for future projects, both buttons are wired, as Figure 3 shows.

The wires coming from the mouse are connected to a simple stereo socket. Surprisingly, the mouse went back together with no parts left over! Figure 4 shows the mouse assembly, stereo connector and stereo male-to-male jumper cord.

Figure 5 shows the magnetic switches wired to the other end of the jumper cord and the magnet used to trigger the switch. This magnet attaches to the exercise bicycle's pedal arm. For this project only, the left mouse switch was used. The extra effort of wiring both buttons was minimal and, at some point, the second sensor is sure to prove useful.

Figure 6 shows the switch and magnet installation. Notice the magnet has been upgraded from the small, white one in Figure 5 to an industrial-strength version. The top of the switch is visible next to the tube of the exercise machine. Transparent tape was used to secure the magnets and sensor since duct tape was deemed to be too robust for this project.

Using Zeemote — a Bluetooth wireless device

In keeping with the theme of real time analog data, a wireless analog sensor seemed like a perfect addition to the project. Again, the objective of the article is not to instrument your exercise bike, but to explore how to connect and capture data from analog inputs. The Zeemote^® represents a true analog joystick designed to be used with cellphones.

The Zeemote web site reads:
"Zeemote^®, Inc. makes intelligent wireless controllers for mobile devices. Our first product, the patented Zeemote^® JS1, is the first compact wireless thumbstick for mobile gaming. Small, fast, ergonomic, and wireless, the Zeemote^® controller creates a near console game playing experience with true analog control."

Figure 7 shows the Zeemote.

We were able to get an engineering version of the Zeemote wired to our specifications. In Figure 8, the Zeemote has replaced the mouse device and is transmitting pulses over the wireless Bluetooth^® standard to the Java client. It is up to the reader to design the analog to the digital interface for their environment.

Back to top

Java client for data capture

For each turn of the pedal, the mouse is generating one click on whatever window has the mouse focus. In order to generate data about the bike, you need to be able to record these clicks in the database. You can do this by creating a Java window that listens for clicks, and each time it receives a click, it handles it by inserting a row into the database via JDBC with a timestamp and some other information. Of course, in order for this to happen, the mouse must first be positioned over the window. Figure 9 shows the simple target window for detecting pedal revolutions.

Below is the Java class that manages the clicks. Both the Informix and DB2 versions are included in the code samples available for download.

import java.awt.*;
import java.awt.event.*;

import java.sql.*;

public class BikeListen extends Frame implements MouseListener, WindowListener {
    private PreparedStatement pst;
    //pedaling timer variables
    private long lastSpin, spinDiff,thisSpin=System.currentTimeMillis();
    //method timer variables
    private long timerStart, timerFinish, timerDiff, totalTimes, numTimes=0, maxTime;
    private final long MILLISECONDS_PM = 60000;
    private Connection conn;


    public BikeListen(){
        //define database url
        String jdbcurl = "jdbc:informix-sqli://192.168.3.51:54321:"+
	   "informixserver=f_ids;user=informix;password=YOUR#PASS#HERE;database=rpm";

         //load ifx driver
        try{
             Class.forName("com.informix.jdbc.IfxDriver");
         }
         catch (Exception e){
             System.out.println("FAILED: failed to load Informix JDBC driver.");
         }

         //connect to database
         try{
             conn = DriverManager.getConnection(jdbcurl);
         }
         catch (SQLException e){
             System.out.println("FAILED: failed to connect!");
         }

         //prepare the sql insert statment for better performance
         try{
             pst = conn.prepareStatement
	("insert into revs( timestamp, rpm, milliseconds )  values (current, ?, ? )");
         }
         catch (SQLException e){
             System.out.println("FAILED to prepare statement  : " + e);
         }

        setSize(500, 500);
        //add listeners for clicks + window closing
        addMouseListener(this);
        addWindowListener(this);

        setTitle("Point mouse inside window before you start riding");
        System.out.println("Point mouse inside window before you start riding");
        setVisible(true);
    }


    public void mousePressed(MouseEvent e) {
        //start timer
        timerStart = System.currentTimeMillis();

        double rpm = 0;
        //lastSpin should be what thisSpin was assigned last time
        lastSpin = thisSpin;
        //thisSpin should be the current time
        thisSpin = System.currentTimeMillis();
        //the difference is the elapsed time
        spinDiff = thisSpin - lastSpin;
        if (spinDiff > 0)
            rpm = MILLISECONDS_PM/spinDiff;
        //check to see if its a right click
        if (e.isMetaDown())
            //System.out.println("Right pressed, " + spinDiff);
            ;
        else{
            // now insert value into database
            try {

                pst.setInt (1, (int)rpm );
                pst.setInt (2, (int)spinDiff );
                pst.addBatch();
                pst.executeBatch();
            }
            catch (SQLException einsert){
                System.out.println("FAILED to Insert into tab: " + e.toString());
            }
        }

        if (numTimes % 10 == 0)
            System.out.println("");
        System.out.print("7");

        //end timer
        timerFinish = System.currentTimeMillis();
        timerDiff = timerFinish - timerStart;
        numTimes++;
        //add time to total
        totalTimes = totalTimes + timerDiff;
        //if its the new max print it
        if (timerDiff > maxTime){
            maxTime = timerDiff;
            System.out.println("\nNew max time: " + maxTime);
        }


    }
    public void mouseReleased(MouseEvent e){;}
    public void mouseEntered(MouseEvent e){;}
    public void mouseClicked(MouseEvent e){;}
    public void mouseExited(MouseEvent e){;}

    //actionlistener for button
    public void windowClosing(WindowEvent e) {
        System.exit(0);
    }
    public void windowActivated(WindowEvent e){;}
    public void windowDeactivated(WindowEvent e){;}
    public void windowOpened(WindowEvent e){;}
    public void windowClosed(WindowEvent e){;}
    public void windowDeiconified(WindowEvent e){;}
    public void windowIconified(WindowEvent e){;}

    public static void main(String args[]){
        BikeListen bl = new BikeListen();
    }

}

The class needs to do a few things in order to translate the click into a row in the database. First, it needs to take care of the click capturing. It does so by extending Frame — this is what provides the actual window that appears. By implementing MouseListener and overriding mouseClicked(MouseEvent e), this class also provides a method that can be called whenever the mouse is clicked on the Frame. In order to direct the Frame to use the mouseClicked(MouseEvent e) method that we have defined, "this" must be added to the Frame as a listener — as you see in the constructor: addMouseListener(this). WindowListener is also implemented to provide the user with some way to close and exit the program.

The class' constructor does a few things that prepare the client to insert information into the database. It defines the database's URL for JDBC and attempts to load the driver, connect to the database, and create a prepared SQL statement to improve performance later. Then, all mouseClicked() has to do is capture the current time using System.currentTimeMillis(), find the difference between that and the last time a click was received and the RPM from that, and insert the two into the table using the prepared statement. A timer can also be put on the duration of this process, in order to record and analyze the connection and insertion speeds. This is discussed further in a later section.

On the other end, the database is set up to receive this information into a relatively simple table called "revs" that follows the following schema:

create table revs (
	revkey serial,
	timestamp datetime year to fraction,
	rpm    int,
	milliseconds int
);

create table revs (
	revkey int generated always as identity,
	tstamp timestamp,
	rpm    int,
	milliseconds int
);

This table provides us with the ability to store a few useful pieces of information — when the click is captured, it captures how many RPMs the biker was pedaling (calculated by the Java client) and how many milliseconds there were in between this turn of the pedal and the previous one.

Java garbage collection (gc) is another important issue related to making a system work in real time. The jconsole tool provides a nice graphical view of the gc process, shown in Figure 9. Each time the memory utilization drops, creating the "sawtooth" plot, it represents a gc cycle. The slow, upward trend in the memory consumption line may indicate a memory leak! For more information about jconsole, see Resources.

Figure 10 shows a graph that monitors the Java client garbage collection using Jconsole.

Back to top

Reporting using JSP, Ajax and Web 2.0

Reporting on real time data requires regular refreshing of the output. This is very taxing for the database and report server. Creating an updated graph on a regular interval and providing this as a .jpg image significantly increases the throughput of the reporting server. Each new request for the results page displays the existing .jpg and doesn't do a database request to create a fresh image.

Read DB2/Informix and open source: Database defense against the dark political arts to better understand the issues surrounding background image generation vs creating a new image for each page request-response and particularly to Listings 9 and 10.

To create the reports, you need a graphics generator, a report server, and a refresh mechanism.

Generating graphs in Java: JFreeChart

An excellent package for generating graphics in Java is JFreeChart. According to their website "JFreeChart is a free 100% Java chart library that makes it easy for developers to display professional quality charts in their applications."

Generating the graph of the RPM data at regular intervals is accomplished via a Java application. The XYjdbc.java source code in the sample programs shows how to generate the .jpg file. JFreeChart can also be used in a Web page to dynamically generate a graph, but the overhead increases as the number of users increases.

Application Server: WebSphere^® Community Edition

We used WebSphere Community Edition for this project (see Resources).

For the purposes of this article, a simple Java Server Page with embedded JavaScript is used. Figure 11 shows this page.

The page is being displayed in the Eclipse development environment. Eclipse is recommended for developing for WebSphere Community Edition. For custom plug-ins that enhance the development experience, click here.

Refresh mechanisms: Web 1.0 vs Web 2.0 and Ajax

Graphs are not useful unless there is a way to display them — and the real-time system is pointless unless the visualization can be updated in real time. Since the graph lives in a browser, some part of the display must refresh in order to show the new graph. In a purely HTML based fashion, a fix is simple enough — simply write a line of code into the top of the page that tells the browser to refresh every few seconds. The code would look like this:

<META HTTP-EQUIV=Refresh CONTENT="1; URL=autorefresh.html">

Web 1.0 offers auto refresh, but when this code is used, it is implementing a get request for the entire page.

However, in today's world of Web 2.0, it would be considered an abominable waste of bandwidth to reload the entire page every time you just wanted to re-display the graph. Instead, a more elegant solution would consist of a Web page where a segment of JavaScript running unnoticed would be able to fetch the new graph every few seconds and have it show up on the page, without involving any other part of the page.

While many would associate this type of focused reloading with AJAX, for an image, no script needs to be called and no XML content is returned. Instead, the solution comes by using JavaScript's "document.images" which is able to change the source of the image. However, if you are just setting the source to what it was previously and are not changing the name of the image that you need to retrieve, the browser would rely on the cache and not try to download the new image. In order to force the browser's hand, you can use a neat trick where you append "?" and a unique number on the end of the image source (we use the date):

<html>
<head><title>javascript refresher</title>
</head>
<script language="JavaScript">

function refreshMe() {
   var random = Math.floor(Math.random()*10000);
   if (document.images) {
       var srcString = 'rpms.jpg' + random;
       //append pseudo-random number to end of image URL
       document.images.thegraph.src = srcString;
   }
   //wait between calling this method again, in milliseconds
   setTimeout('refreshMe()',1000);
}

setTimeout('refreshMe()',1000);
</script>


<body>
<h1>Cyclist Revolutions Per Minute, Real Time Display</h1>
Click the refresh button if the graph does not appear below.
<hr>
<img src = "rpms.jpg" name = "thegraph">
<hr>











            

Aside from the code to refresh the image, you also need some way to call the function every few seconds or so. At the end of the method, it calls setTimeout(), instructing it to call itself after 1 second of waiting. It continues in this cycle endlessly, providing a periodic update of the image and allowing you to visualize your real-time data in real-time.

Back to top

System throughput

Computers and bicycles often beg the same question: "How fast can this thing go?" The system is only as fast as its slowest component. To evaluate the client, the longest processing time is used to calculate the upper throughput limit. The reporting server is evaluated using the Jmeter test harness.

Client maximum throughput

To determine the maximum throughput for the Java client, the code is instrumented to report the longest pulse processing time, as Figure 12 demonstrates. This varies by the speed of the client computer, database server, the workload from the reporting server, and variations in network speed and traffic.

In this case, the longest processing time was 193 milliseconds. Our simple calculation shows a maximum processing rate of 310 inserts per minute. This is for a single client. Multiple clients can be processed in parallel due to the robustness of the Informix database.

193ms per insert
1/.193 = 5.18 inserts per second
60*1/.193 = 310.9 inserts per min

Jmeter to stress test

The Jmeter test harness was used to test the WebSphere Community Edition server. This is a flexible test client that can generate multiple threads and measure a range of responses. More information on Jmeter is available in the Resources section.

Since Jmeter is not a JavaScript client, a static html page is included in the WebSphere project for Jmeter to test against. Jmeter simulates a full-page refresh with an HTML GET request of the entire page, or http://localhost:8080/BikeRPM/BikeRPM.html. It simulates the Javascript selective image request with a get of only the .jpg url, in our case: http://localhost:8080/BikeRPM/rpms.jpg

To ensure there is a positive case for using JavaScript refresh, the BikeRPM.html page has a background image of 1.5megaBytes. You could argue that this was excessive, and we might have to agree with you. But the results from the test are stunning:

10 users, 100 page requests

Full page refresh: 		 3,824 requests/minute
Javascript image refresh:	23,210 requests/minute

The performance testing shows that partial refresh is faster than a full-page refresh. It also shows that limiting the size of the objects in a web page can have a dramatic performance impact.

Back to top

Conclusion and next steps

This article has given you the tools you need to capture and present analog activities in real time. Hopefully it has sparked your interest in a variety of other ways as well. There are lots of areas to explore based on the topics we have introduced. Here are just a few ideas :

• Architecture: Consider multi-client input, perhaps even an exercise bicycle race?
• Analog-to-digital input: Capture a range of analog voltages with more comprehensive AtoD technology.
• Java client for data capture: Improve the switch debounce in software.
• Reporting - JSP/Ajax/Web2.0: Provide query filtering capability, summary reports, and drill down to additional detail.
• System throughput: It is always possible to go faster...explore tighter code and better experimental athletes.

Back to top

Download

	Information about download methods

Resources

• Read Database Transition: Informix Dynamic Server to DB2 Universal Database to learn about Informix and DB2 porting.
  
  
• Visit the IBM WebSphere Application Server Community Edition page to learn more about the Java EE server.
  
  
• Read Using Jconsole to Monitor Applications to get started with jconsole.
  
  
• Visit the Apache Jakarta Project to learn about Jmeter.
  
  
• Other articles by the authors include:
  • Google Maps Mashups part 1 and part 2
  • Linux parallel database clusters,
  • federated database part 1,
  • and part 2,
  • tunneling secure traffic via ssh,
  • web click stream analysis on Linux,
  • Informix ^® administration,
  • web polling
  • and WebSphere^® Tuning and Administration
  
  

• Get WebSphere Community Edition to follow along with this project.
  
  
• Download a free trial version of Informix Dynamic Server.
  
  
• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.

• Participate in the discussion forum.

About the authors

		Aron Lurie was midway through 10th grade while writing the article. He has had a web development business for 5 years, and ever since 4th grade he has been teaching himself new languages. He is the Webmaster for his school newspaper, and has been Webmaster for his USY chapter and the Hebrew College. In his spare time, Aron is a member of the Newton South ski racing team. Unfortunately, he has not yet found a way to combine computers with skiing.

		Marty Lurie started his computer career generating chads while attempting to write Fortran on an IBM 1130. His day job is in Systems Engineering for IBM's Software Division, but if pressed he will admit he mostly plays with computers. His favorite program is the one he wrote to connect his Nordic Track to his laptop (the laptop lost two pounds, and lowered its cholesterol by 20 percent). Marty is an IBM-certified WebSphere Administrator, IBM-certified DB2 DBA, IBM-certified Business Intelligence Solutions Professional, Linux+ Certified Administrator, Informix-certified Professional, and IBM-certified SOA Solution Designer.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top