Real-time data acquisition: Connecting your exercise bike to Informix or DB2

Using Java and Linux to capture real-time data

Capture data from an analog environment in real time and store it in an IBM ® Informix® Dynamic Server or DB2® database. Use WebSphere® Application Server Community Edition to create graphs of captured data and present them in Java™ 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.

Aron Y. Lurie (aron.lurie@gmail.com), 10th grader/webmaster, Newton South High School/Hebrew College

Aron Y. LurieAron 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 (lurie@us.ibm.com), Information Technology Specialist, IBM

Photo: Marty LurieMarty 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.


developerWorks Contributing author
        level

27 March 2008

Also available in Russian

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® Application Server Community Edition to create graphs of captured data and present them in Java™ 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.


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.

Figure 1. System architecture
Page when first loaded

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 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.


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.

Figure 2. Finding the connections for the right mouse button
Finding the connections for the right mouse button

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.

Figure 3. Wires attached to both right and left mouse buttons
Page when first loaded

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 4. Mouse re-assembled with wiring harness
Mouse re-assembled with wiring harness

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 5. Magnetic switches, magnet, and wiring harness
Magnetic switches, magnet, and wiring harness

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.

Figure 6. Magnetic sensor and magnet installed on the exercise bike
Magnetic sensor and magnet installed on the exercise bike

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.

Figure 7. The Zeemote device
The Zeemote device

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.

Figure 8. Zeemote prototype connected to the magnetic sensor
Zeemote prototype connected to the magnetic sensor

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.

Figure 9. Java target window for receiving mouse clicks
Page when first loaded

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

Listing 1. Java client for data capture, Informix/DB2
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("·");
       
        //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:

Listing 2. Informix Table schema for storing the sensor real time feed
create table revs (
	revkey serial,
	timestamp datetime year to fraction,
	rpm    int,
	milliseconds int
);
Listing 3. DB2 Table schema for storing the sensor real time feed
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.

Figure 10. Monitoring the Java client garbage collection with Jconsole
Monitoring the Java client garbage collection with Jconsole

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 Application Server 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.

Figure 11. Java Server Page for display of rpm data in Eclipse
Java Server Page (jsp) for display of rpm data in Eclipse

The page is being displayed in the Eclipse development environment. Eclipse is recommended for developing for WebSphere Application Server 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:

Web 1.0 refresh
<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):

Listing 4. Web 2.0 refresh
<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.


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.

Figure 12. Using maximum cycle time to determine maximum rpm
Page when first loaded

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.

Listing 5. Client maximum throughput calculation
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 Application Server 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:

Listing 6. Jmeter test results
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.

Figure 13. Jmeter performance graph, full page refresh
Page when first loaded

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.

Download

DescriptionNameSize
Code that goes with the articlecodesample.zip10KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere, Java technology, Web development
ArticleID=297891
ArticleTitle=Real-time data acquisition: Connecting your exercise bike to Informix or DB2
publish-date=03272008