JDBC performance tips

If you are into java and database development, you will find this article to be a gold mine: http://www.javaperformancetuning.com/tips/jdbc.shtml

It contains links and summary to tens of other performance articles related with java database application development.

Hi5 em “portugês”

Eu até aceiteva, mas tenho cá um feeling de que eles estão apenas interessados no meu donativo e não na minha amizade ;-).


PS: será que as comissões de festas se estão a render às novas tecnologias e vão passar a aceitar donativos via Paypal? 😉

How to get Billionaire in one day

Every other week Google Finance has a glitch in their real-time quote, resulting in some random company heading up the top gainers or losers. Usually, the glitch is just a few hundred or thousand percent in the company’s (de)valuation, but today it hit all records: 10,647,115,776.00%!!! One stock went up 10,647,115,776.00% in a single trading day, to a stock value of 8,619,905,024.00

I just which I had bought one single stock of this company yesterday 😉


It would be cool if NYSE servers had this type of glitch. The stock market would be much more fun and not so boring and depressing as it has been over the past months.


As a follow up on my last post comparing Static SQL with Dynamic SQL, I will now post an example of how to run the same code using static and dynamic SQL.

One of my visitors left a comment saying that the scope of static and dynamic SQL in Oracle is different than the one I mentioned. I am not familiar at all with Oracle, but was able to find some information on their documentation where they compare JDBC and SQLJ. Since their concept of static vs dynamic SQL is different from the concept in DB2, so my examples may not make sense for Oracle users. I also found out that although Oracle has had plans to desupport SQLJ in its data server, that support has been reinstated in their 10g release.

The two code samples I will show next are shipped with DB2 (get your free copy of DB2 Express-C) and can be found in the file %DB2FOLDER%/samples/java/sqlj/TbRead.java. I’ll just use one of the several examples in that file, that executes a sub-select statement in the employee table.

Sample code in SQLJ:

#sql cur7 = {SELECT job, edlevel, SUM(comm)
	FROM employeeWHERE job IN('DESIGNER', 'FIELDREP')GROUP BY ROLLUP(job, edlevel)};
while (true){
	#sql {FETCH :cur7 INTO :job, :edlevel, :commSum};
	if (cur7.endFetch()){
	System.out.print("Job: " + job + " Ed Level: " + edlevel + " Tot Comm: " +commSum);

Sample code in JDBC:

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT job, edlevel, SUM(comm) "
	+"  FROM employee "
	+"  GROUP BY ROLLUP(job, edlevel)");
	while (rs.next())
	if (rs.getString(1) != null)
		job = rs.getString(1);
		edlevel = rs.getString(1);
		commSum = rs.getString(1);
		System.out.print("Job: " + job + " Ed Level: " + edlevel + " Tot Comm: " +commSum);

Although both styles present different syntax, from a developer’s perspective, the only main difference is than when using JDBC one needs to explicitly fetch the row values into Java variables one by one. A common comment from Java developers is that SQLJ is not really Java (one needs to use annotations instead of java method calls), so they prefer to stick with JDBC.

Like I explained in my previous post, the biggest difference between these two styles (static SQL using SQLJ and dynamic SQL using JDBC) is that the SQL statements in the SQLJ files need to be compiled and bound to the database ahead of runtime. The following diagram illustrates this process:


After the deployment process, SQLJ execution is simpler than JDBC. While JDBC statements need to be prepared at execution time, SQLJ statements are already compiled and ready to use. The two following diagrams illustrate these differences:


As you can see, static SQL execution process is much simpler, but it requires a complex deployment process. This is an aspect of database development where there is a clash between DBAs and Developers. While ones – the DBAs – prefer the much more refined security and execution control provided by SQLJ and static SQL, others – the Developers – prefer the easier development process of dynamic SQL in the form of JDBC.

Soon, I will talk here about a new Java Data Access platform that supports the usage of both static and dynamic SQL at runtime (through a JVM property), allowing DBAs and Developers to use dynamic SQL on development and test environments and going with static SQL on the production environment. This way, the development community will get the best of both worlds: ease of deployment during development and testing phase and greater performance and control on the production environment.

If you are looking for a data management and application development tool, you should take a look at the new IBM Data Studio. It is an eclipse-based development environment, free to download and to use and with support to all major RDBMS. Download IBM Data Studio.

Static SQL vs Dynamic SQL

I have been wanting to write a few technical articles here on the blog, so I’ll start with something that is related with what I have been looking into at work: Static and Dynamic SQL.

From the conversations I have had with both DBAs and developers, it is clear that DBAs prefer static SQL, while developers prefer Dynamic SQL.

The difference between static and dynamic SQL is that static SQL needs to be compiled and bound to the database before application runtime, while dynamic SQL is compiled during runtime. Next, I’ll show a list of pros and cons regarding each one. 

Static SQL


  • compile at bind time. Since the statement is compiled only once and before we run our workload, we have all the database resources in order to generate the most optimal query execution plan. In DB2, there are 9 levels of optimization, being 5 the default one. When we bing our application package, we can pick the highest optimization level – 9 – and get the most optimal execution plan. Using a higher optimization level requires more resources for the compile phase, but since our workload is not yet running, we can afford this high resources requirement.
  • security. Security is probably the most common reason why people use static SQL instead of dynamic SQL. Static SQL allows the DBA to set authorization at the package level. For example, consider an application package app1, that provides SQL functionality to select employee’s name and address from the table employees. The DBA can five user JOHN execution privileges on package app1, even if user JOHN does not have SELECT authority on table employees. Static SQL provides a much finer layer of security.


  • need to bind before runtime. Although binding before runtime usually allows for more optimized access plans, doing this in a test or development environment can be cumbersome.
  • lack of tooling support. most of current IDEs provide coding assistance with support for APIs like JDBC. The lack of support from development tools discourages the use of static SQL.

Dynamic SQL


  • IDEs and APIs: using eclipse to develop Java code that interacts with the database using JDBC or JPA is much simpler than developing a SQLJ application.
  • statement caching. Dynamic statement caching avoids the need to compile the same statement multiple times, increasing the performance to values close to static SQL. However, bear in mind that a cache miss will be extremely expensive.
  • better statistics. Because the statement is compiled at runtime, it uses the latest statistics available, contributing to a better execution plan.


  • compile at runtime. There are a few reasons why compile at runtime can be a bad thing:

    • every time a statement is executed, it needs to be compiled, increasing the total statement execution time
    • the compile time will account for the total execution time, so using higher optimization levels may slow down the overall performance instead of improving it.
    • because the statement is only compiled at runtime, errors in the SQL statement won’t be detected until runtime.

As you can see, there are several reasons why you would choose one over the other. There is no perfect solution! But if you ask me, I would suggest the following: use Static SQL if security is your main concern and use Dynamic SQL if ease of development is your main concern.

ipod touch

Written from iPod touch. Touch screen keyboard sucks! I love my PDA with keyboard 😉 5 minutes to write this post .

Second part, written from laptop: I only had about 10 minutes to play with the Ipod Touch before the employee kicked be out. He wanted to go home and I was the last person in the store :-) The keyboard is really hard to use. My thumbs are too big, and it didn’t work when I tried my PDA pen nor a regular pen. I guess it expects a larger surface of contact, so I was only able to type with my fingers. My PDA (HP HW6915) has a regular keyboard, but even using the pen and touch keyboard is much faster than using the Ipod Touch one.

Apart from this bad experience, I didn’t have much time to try the device’s features. Only got to try the browser and I liked it. It displays the pages nicer than IE on Windows Mobile. It is also very slim, more than I expected. Still, not in my wishlist. I did like the iPod Nano and may get one sometime.

Harvard and MIT

Originally uploaded by Vitor Rodrigues.

Last month I completed the visit of the 4 major US universities that were on my To Visit list. After Sanford and Berkeley during my internship in California, visited Boston this October for a conference and had the opportunity to visit the Harvard and MIT campuses.

While Harvard is a beautiful place to visit with all the fancy coffee shops and restaurants around Harvard Square and a very nice bookstore, that makes you feel you are still in the XVIII century.

As for MIT, not much to see, besides lots of geeks (yes, they are easily spotable) and lots lots of bicycles in the parking lots. Just a few funny buildings like the ones in the picture.

As for Boston city, although at first it looks just like a small size NYC, after a few walks around the city you can feel its own vibe and style. For food, you must try the Italian restaurantss in the North End. For guided tours, the list of attractions listed on the trolleys is just a joke, i.e., “Access to MIT campus and Harvard Square” means: we let you out close to a subway station where you can catch the subway to there :-)

Persisting XML with OpenJPA

I’ve been looking at JPA – Java Persistence Architecture and decided to play a bit with OpenJPA using DB2 as the back-end. My goal: to persist and query XML data in DB2, making use of DB2’s pureXML capabilities to query the XML data using SQL/XML‘s XMLQUERY() function.

However, while OpenJPA has an extensive documentation, the examples are not always complete and there isn’t a lot of information on the web regarding OpenJPA error determination and solving. So, here are some recommendations for some of the problems I have encountered. The class xml.Address is the one to be persisted as XML in the database using JPA and it is stored as the field shipAddress of the Order objects.

 [java] Exception in thread "main" <openjpa-1.0.0-r420667:568756 fatal user error>
Type "class xml.Address" does not have persistence metadata. 

Suggestion: Remove the reference to xml.Address from persistence.xml

[java] Exception in thread "main" <openjpa-1.0.0-r420667:568756 nonfatal user error>
Encountered unmanaged object "xml.Address@9b2a51" in
persistent field "xml.Order.shipAddress" of managed object "xml.Order@12b3349"
during flush.  However, this field does not allow cascade persist. 
You cannot flush unmanaged objects.
  [java] FailedObject: xml.Address@9b2a51

Suggestion: Make sure you have no @Entity or @Embeddable annotations in xml.Address. The main annotation is @XmlRootElement.

 [java] Exception in thread "main" <openjpa-1.0.0-r420667:568756 fatal general error>
"xml" doesnt contain ObjectFactory.class or jaxb.index

Suggestion: add a file jaxb.index into your xml package containing all the classes to be persisted as XML: a file containing Address in our case.

Photoshop filters on GIMP

For the GIMP users that, like me, keep complaining that Photoshop has so much more filters to do some fancy effects, the agony is over :-)

I just found this today, but it dates back to 2004. There is a GIMP plugin that handles Photoshop plugins. It is called PSPI and it works like a charm on the filters I have tested so far.

Here are the instructions on how to get it working on Windows:

  1. download the binary package from here.
  2. copy the plugin file to “C:\Program Files\GIMP-2.0\lib\gimp\2.0\plug-ins”
  3. start GIMP
  4. click on Menu Xtns -> Photoshop Plug-in Settings
  5. add the folders where you have your photoshop plugins (.exe or .8bf files)
  6. restart GIMP
  7. all the compatible plugins in the folders you specified in step 5 will show up under the Filters menu (*)

This was a new for me, and I’m quite happy as there was a couple of photoshop filters that I like to play with sometimes (just for fun, but still).

(*) For some odd reason, all my plugins appear under the sub-menu “Flaming Pear”, the creators of the first photoshop plugin I loaded into GIMP.

DB2 on Rails update

I’m back to fiddling around with my Ruby on Rails experiments(1)(2). I was able to create a very useful 2-way mapping between Ruby objects and xml data stored in DB2 pureXML. Basically, trying to replicate some of ActiveRecord’s functionality but for XML data. I still find it odd, though, that both ROXML and xml-mapping haven’t had much activity as of late. I’m wondering if there is any new OXM library around that I don’t know of.

Also on the same topic:

  • the main DB2 on Rails website is up and running again, with a revamped design and now using wordpress instead of typo.
  • a new version of the ibm_db driver was also released, containing several bugfixes. Update it through gems (gem update ibm_db) or from here: http://rubyforge.org/projects/rubyibm/