Best Practices of Data Modeling with InfoSphere Data Architect

How do you become a more effective modeler? It is not enough to be proficient with theory and principles, you also need to know your tools. In a web technical briefing I was sharing my experiences with InfoSphere Data Architect, focusing on tool specific best practices. We had over a hundred participants in the live meeting, and here is what IBM newsletter says about it:

"And a second huge thank you to Dr. Vladimir Bacvanski for his fantastic job on the Best Practices in Data Modeling using InfoSphere Data Architect tech briefing."

 And here is from the IBM Optim twitter:

"@IBM_Optim: Awesome tech briefing on best practices in data modeling with #infosphere data architect. Thanks to Vladimir Bacvanski!"

Wow! It was a pleasure to share; we also had some great questions from the audience.

If you have missed the talk, here are the slides:

Best Practices of Data Modeling with InfoSphere Data Architect

Tech Briefing Preview: Data Modeling Best Practices with InfoSphere Data Architect

We are approaching the tech briefing on data modeling best practices on May 13! We will do a number of demos showing best practices that enable you to get most of the InfoSphere Data Architect. IBM developerWorks did a short podcast interview on the subject in preparation for the event:

Here is the link for the podcast.

The best practices for InfoSphere Data Architect are all covered in detail in the course Mastering Data Modeling with IBM InfoSphere Data Architect.

Data Modeling: Interview at the IBM Silicon Valley Lab

Here is a short interview on data modeling recorded during my visit at the IBM Silicon Valley Lab. We talk about data modeling and how it fits in with current software development processes and approaches  such as agile development. I also comment on InfoSphere Data Architect, which provides data modeling integration with other software development and design tools in the IBM Rational, InfoSphere, and Optim product families.

pureQuery Training: A Success Story from the Trenches

Recently I trained another group of developers and DBAs on pureQuery, IBM’s new data access platform for Java.  Here I want to share my experiences from teaching the course Developing Database Applications with Optim Development Studio and pureQuery and reflect on reception of pureQuery among experienced developers whose daily job is developing mission critical applications. In their environment, data amounts to petabytes, and performance and throughput are essential. The database is DB2 and the computer is IBM System z. Sometimes, a big machine is what you really need.  An army of scooters may not always be replacement for a forty-ton truck if you need to haul a lot. 

JDBC and ORM Experiences

Everyone in the student group had their share of Java enterprise applications under the belt and they had ups and downs with Java persistence technologies.  They have experienced the tediousness of writing JDBC code.  They have seen the initial productivity gain provided by object relational mapping (ORM) frameworks, just to be disillusioned by poor performance, difficulties in debugging and SQL tuning, and poor traceability. While they provide productive way of navigating persistent object associations, to dismay of DBAs, ORM solutions typically leave sophisticated data access and optimization features of modern databases untouched. Buried under to many layers of abstraction, the database languishes, its capabilities underutilized. At the end, performance and control suffer.

Enter pureQuery: The new Data Access Platform

pureQuery data access platform is different from ORM solutions.  It does not try to hide the database. Instead, pureQuery gives developers full access to the SQL, end achieves high productivity not only through developer friendly API design, but also through tooling provided by the Optim Development Studio, an Eclipse based IDE for development of data centric applications.

It was interesting to observe the students as they were learning about the different capabilities of pureQuery and its tooling. We prepared a set of labs and many hands on exercises where students could learn about pureQuery through a set of realistic scenarios. While there are many interesting and productive features of pureQuery, here are some that participants in my training classes liked the most.

The Code and the API

The real winner is the pureQuery API which enables application code that is significantly shorter and simpler than JDBC. A big advantage is that the pureQuery will map the Java bean properties to database columns, while the developers can write arbitrarily complex SQL. Annotated interfaces give another, more disciplined option for defining the queries for the data interfaces. Just check out this code fragment: this is all you need to do to get all employees of a company:

  

The Editor and the Tooling

Tedious database development tasks are greatly simplified.  The way how it is done resonates well with participants.  While the tools provide arbitration and guidance, what is generated is simple, easy to understand, and easy to modify. The editors provide auto-completion on SQL statements in Java strings.

A particularly interesting feature for many participants is that you can start with an arbitrary SQL query, and the tool will create everything needed to call that query, and transport the values in and out of the query or stored procedure. In many organizations where data is a critical asset, this fits the normal workflow where the data developers create the SQL.  In addition, you can even create a web service out of a query – a welcome feature for data centric services in SOA.

SQL Outline

This is a view in the IDE that provides insight into relationship between Java and SQL.  While developers liked this feature, it is the DBAs who are thrilled by it – you can trace the SQL to Java code, and vice versa.  Tracing from Java to the database:

And here is the tracing from database to Java:

This feature works when SQL is visible in the application, as a string or in the annotation. But it also works when optimizing JPA or Hibernate.  Even though the code is generated on the fly, at runtime, pureQuery runtime can capture the SQL sent to the database, and trace it to the line of code that emitted it in the user’s program. 


This traceability makes the work of developers and DBAs much easier.  It eliminates guesswork.  The developers and DBAs can now sit together and troubleshoot or optimize the code or tune the queries. 

When you run the application using pureQuery, one option is to collect performance metrics.  This immediately enables the developers to spot inefficient queries. Here are the metrics from the application run:

Static SQL

If DB2 DBAs have been missing one thing from Java persistence applications, this is it.  With static SQL, you send your SQL to the DB2 during development.  It gets prepared at a high optimization level, and runs faster than the SQL that is sent to the DB at development time.  Security is also enhanced, as the security is set up for a package – a group of SQL statements, and not just at the table level. Here is the representation of DB2 packages created from our Java application:

 

 

 

 

 

Conclusion

These are just some highlights - the features that immediately caught attention of my students since they directly translate to savings in time and effort.

Who is the pureQuery for?  One thing during training that was always well received is that pureQuery does not hide the database, yet it enables high productivity.  Is pureQuery an ideal solution for everyone?  Certainly not.  We know that there are no silver bullets.  If you do not care about your database, if you have a handful of users or performance is not an issue, then ORM frameworks and JPA may work out just fine. On the other hand, if your database is an essential part of your business, and you have strict and demanding performance requirements, you need to look into pureQuery.  And if you are running on System z, the performance gain translates directly into money savings due to lower utilization of the CPU. You can find some telling benchmarks in a blog post by Simon Harris on performance results.


While developers and DBAs obviously liked the technical advantages, it was the managers who smiled when they heard about the savings.

If you want to learn more about how to productively apply pureQuery in your team, check out the Developing Database Applications with Optim Development Studio and pureQuery  course description. For many clients, we customize the course to include the client’s coding standards, frameworks, and other specifics.

Happy persistence!

2010, Scala, and the Quiet Joy of Programming

It is the morning of the first day of 2010. The smell of freshly baked croissants and coffee spreads through the house. A view through the window: hummingbirds are flying in the garden. Scala nightly build just finished downloading. With a quick git init the repository is created. The cursor blinks, expecting. 2010: it looks like a good year!

Installing Windows XP on a Tablet PC with an SSD Drive

Is it possible to escape the lure of upgrading a laptop with a shiny new 128 GB SSD drive? Of course not! I am a big fan of virtualization and I am running a development stack consisting of tools like Rational Software Architect, InfoSphere Data Architect, WebSphere Application Server and DB2 9.7 Enterprise Edition in a VMWare virtual machine. My hope was to improve performance of my laptop by using an SSD drive.

To reduce the amount of gunk that accumulates over time, I decided to avoid restoring the content of the drive from my backup and instead do a fresh install. I replaced the hard drive on my Toshiba Portege M400 tablet with a 128 GB Patriot Torqx M28 SSD drive and started installing Windows XP from the MSDN distribution. However, the installation was not without problems and some research was necessary: the precise set of steps to get things working is not explicitly spelled out elsewhere. Same or similar steps should be applicable to other laptops and tablets. If you need to do this, I hope you will save yourself some time and frustration by following my advice!

How about Windows 7?

Good question! It turns out that my tablet is officially just a bit too old to run Windows 7. I was hesitant to go to Vista for its known problems and performance degradation in comparison with XP. So I decided to run XP just little bit longer, until tablets get quad core CPUs and 16 GB RAM ;-) When I teach and consult on software architecture and design I draw a lot (so I need a tablet), but I also run heavier stuff like WebSphere Application Server (so I need power) when I consult and teach things like SOA and Web Services.

Installing the SSD: Hardware is easy!

This was the easy part: unscrew the disk compartment, take out the old drive and put in the new one. The SSD drive behaves like a SATA drive it replaces. It fits nicely.

The Missing SATA Driver

Pop in the XP CD, start the setup and there is the first problem: XP does not recognize SATA ! The way around it is to provide a SATA driver on a floppy during the setup. Not USB, not CD or DVD - it must be floppy. Emulating floppy with USB drive did not work. I did not see a floppy drive for many years and my laptop did not come with one. What now?

The solution is to "slipstream" the driver using the nLite – the Windows installation customizer. (Check out their guides and forums for detailed guidance.) You also need to download the SATA driver from your computer manufacturer site. In my case, this was the Toshiba RAID driver. During the preparation of the nLite install, you provide the location of the unzipped driver, which gets integrated ("slipstreamed") into the XP. Next you should create an ISO image and burn it on a CD.

BIOS and the Lonely Drive RAID

As this was the only drive on the machine, the BIOS of my laptop was showing that the drive is in a JBOD setting. If you leave BIOS in this setting, the XP with the slipstreamed driver would still not recognize the drive! You need to go to BIOS and change the drive configuration to RAID – it does not matter that this is the only drive on the machine. Somehow, this setting makes some sense, as the driver is for RAID.

The MSDN Path Complication

If you are using MSDN distribution for installing your XP, you have a CD that contains XP Home, Professional, and Tablet versions on the same CD, but in different directories. The XP installation script has references to the location of files, which are now wrong for the nLite installation. In the directory where nLite copied the XP files, you need to go to the installation configuration file in i386\txtsetup.sif and modify the setup path SetupSourcePath to "\".

A Case of a Missing File

If you try to install now, XP will throw an error message about the missing EULA file and abort installation. The funny thing is that the file is in its proper location. This problem can be fixed by defining the installation type in nLite as "silent". Obvious ;-) You will need to provide the software key in an nLite configuration form.

The SSD Formatting Problem

At this point you may think you are in good shape: XP recognized the drive and you are proceeding to the point where you need to format your hopefully blazingly fast SSD drive. But you are here for a new surprise: XP reports that there is not enough memory to format the drive! To cut the long troubleshooting story short: XP setup cannot format the SSD drive. You need to format your SSD drive and create a partition using a separate working computer. That worked fine and I put the formatted SSD drive back into the laptop. Now I restarted the XP setup and when we reached the troubled point it was important not to create a new partition, but to use the drive as is. The XP finally starts installing and copying files to the SSD. Yay!

But where is the Tablet Stuff?

OK, now we have everything working – but there are no tablet features in sight! In fact, the MSDN tablet components are on the second CD, and we were supposed to be asked to insert it at some point, but that did not happen. Unfortunately, there is no easy way to retroactively add the tablet features. Here is a trick to get the tablet functionality:

1. Boot XP
   
2. Insert the Windows XP MSDN installation CD
   
3. Run setup program from the CD
   
4. Choose the "update" option
   
5. Setup is basically reinstalling XP, but is using the existing SATA disk driver on the machine
   
6. At one point it will ask you for the second CD. Insert it.
   
7. XP update completes and you have a working XP tablet.
   

At last: Success and Blazing Speed!

So was this hassle with SSD and XP reinstallation worth it? With all my programs installed, the boot time of my machine went down from over 4 minutes to 25 seconds - that is until CPU got steady on 0%. Starting a 22 GB VMware image now takes 25 seconds - 2.5 times faster. Starting Rational Software Architect in that VMware image improved 4.5 times. Wow! Now we can get faster to doing something more interesting than installing old operating systems!

Have fun and good luck with your upgrades!

Revolutionizing the Data Abstraction Layer with IBM Optim pureQuery

In this IOD 2009 talk I spoke together with Dan Galvin on using pureQuery: a new data access platform with some unique features. pureQuery is a practical technology that strikes the balance between high productivity of developers and providing a full control of the database access by not hiding the SQL. These features make it particularly useful for critical enterprise applications where high performance and large amounts of data dictate special attention to the data access layer and where we have witnessed many object-relational mapping technologies underperform. As a leading DB2 z/OS expert, Dan Galvin shines light on some important performance issues of data access technologies for large, mission critical systems using DB2 on System z. We particularly highlight the impact of static SQL and heterogeneous batch, which are missing in other data access solutions.

Revolutionizing the Data Abstraction Layer with IBM Optim pureQuery and DB2

View more presentations from Dr. Vladimir Bacvanski.