Discover the DBVisualizer
November 17, 2004 Kevin Vandever
We on the iSeries are a proud bunch, aren’t we? We defend our beloved machine to the bitter end. We talk about its dependability, security, and excellent software development environment. But even with all that the iSeries has to offer, we still find ourselves left with GUI envy. That is to say that, although we love our iSeries machines, we are often ashamed to admit that we work primarily in a green-screen world.
IT’S NOT YOUR FAULT
Such envy is understandable. After all, the user community is getting more accustomed to GUI-based applications everyday, in the form of e-mail and the Microsoft Office Suite of products. Now they want GUI-based business applications, too. And if that’s not enough, developers on other platforms ridicule us iSeries developers about our archaic development tools, and when they are not ridiculing us they speak in condescending tones about our “legacy” applications.
What those uneducated iSeries wannabes don’t realize is that it hasn’t all been 5250 emulation without so much as a point and click or a drag and drop. We have many tools at our disposal, like WebSphere Development Studio client, WebFacing, and Operations Navigator, that allow us to perform a variety of operations, administration, and programming tasks in GUI-based environments, as well as to provide GUI-based applications to the user community.
Now that the interoperability between Java and other iSeries programming languages has become so seamless, because of tools like IBM’s Toolbox for Java and the Java Native Interface, and enhancements to the GUI-based opportunities on the iSeries are almost endless. It is with that in mind that I’d like to introduce to you a Java-based database tool you can use to view and modify DB2 data on your iSeries.
WHAT ABOUT OPERATIONS NAVIGATOR?
Those of you who are familiar with OpsNav may realize that you already have a GUI-based tool that allows you to view and modify DB2 data on your iSeries. (For more information on OpsNav, check out “DB2 Library Contents in Operations Navigator” and “Run SQL Scripts Using Operations Navigator”; or get a more detailed look at SQL in “Using the SQL Features in Operations Navigator”.) Well, you are correct, and it is an awesome tool, but there are a few reasons why I want to talk about an additional database tool. For one, not everyone on the iSeries has access to OpsNav. Some shops don’t use it. And those who do use it may have limited access to it, or may not install all of the components. And although it’s an excellent tool, OpsNav works only with DB2 on the iSeries. What if you have other databases in your shop that you want to access, like SQL Server, MySQL or SAP DB, on platforms such as Microsoft Windows, Linux, or Solaris? You can’t get to them using OpsNav, so you’ll need an additional tool that probably costs additional dollars. Also, I want to give you a taste of the Java open source community and show you an example of the kinds of things that you can get–for free!
JAVA-BASED DB TOOL FOR NUTTIN’
IBM’s developerWorks is a Web site devoted to developers and covers everything from training and technical newsletters to product trials, downloads, and sample IT projects. Whether its database tools or information or the latest on wireless technology, chances are that starting at this Web site is a good choice. In fact, it was after a meeting where database tools options were discussed that I decided to check out developerWorks to see if there was anything out there that would help us out, but would not break the already fragile IT budget in the process. That’s where I found DBVisualizer.
DBVisualizer is a cross-platform database tool used for all major relational databases. It is built on Java technology and therefore uses Java Database Connectivity (JDBC) drivers as its connectivity mechanism. Minq Software developed DBVisualizer, and it was to their Web site that I was pointed when I clicked the DBVisualizer link in developerWorks. The tool allows you to browse the database structure, view detailed characteristics of database objects, edit table data graphically, execute arbitrary SQL statements or SQL scripts, reverse engineer primary/foreign key mappings graphically, and even chart your database with its advanced charting options. All this from a GUI-based environment. Yippee!
You can download a free version of the software and use it as long as you like. Or you can evaluate the licensed version for 21 days before paying the appropriate license fees for the product. The fees are listed on the Web site, but the most you’ll pay is $99 per license if you order one to three licenses. The cost goes down after that. You also pay for upgrades to new versions, but they are less than the original fees.
To use DBVisualizer, you will need the Java 1.4 runtime environment (JRE) loaded on whatever platform you choose to run the software. Many platforms are supported, including Windows, Linux, and all common versions of Unix. But don’t fret if you don’t already have Java 1.4 installed, because when you download the executable install file for DBVisualizer, you have a choice of including the Java virtual machine (JVM) or just the tool itself.
Once you’ve downloaded the install executable, simply double-click it to install the product. You will be asked some simple questions about where you want the product installed, but you will also be asked to provide the directory where your JRE is located. Again, no need to fret, as DBVisualizer will find the appropriate directory, even if you didn’t include the JRE with your download, and allow you to select it from a list box. Cool, eh?
WHEN CAN I FRET?
So far it seems too easy, right? Well, it was for me, too. I was able to download and run the tool in only a few minutes. But then it came time to connect to a database. And, of course, I chose that good-for-nothing, green-screen-based legacy machine that is the iSeries. The first thing you have to do when you start DBVisualizer for the first time is to set up the JDBC drivers you plan to use. The tool doesn’t come with these drivers, so you have to find them yourself. No problem, as DBVisualizer knows where in the JRE to find these drivers, and it points you right to it. Click here to see what the driver manager screen looks like.
From the panel on the left, I selected the DB2 driver name. Once you have the name, you have to provide an appropriate URL format and JDBC driver class. The format is not as hard as it looks, because once you select a driver name, DBVisualizer provides a format template for you. You just have to add the real IP information. On the bottom right portion of the driver manager screen, you’ll see two tabs: User Specified and System Classpath. I didn’t do anything to System Classpath, but I did have to play with the User Specified tab a little in order to find the appropriate DB2 JDBC driver. As you can see in the driver manager screen, there is a little folder icon on the far right side of the panel. You can click this button to find the directory of your JDBC driver. DBVisualizer, again, was smart enough to know where I wanted to look, so it gave me a good head start. Once I clicked the appropriate JAR file in my JRE, I was presented with a list of classes. The JDBC driver is marked with a green icon. I selected that driver from the list box in the driver class entry area above, and I was set. Or so I thought.
This is when I started to fret. Minq Software claims that you can connect to any database that JDBC can connect to. Well, I’ve used JDBC to connect to the iSeries many times, but I wasn’t able to do so with what I had just done. When I went to connect, I received an error stating that the driver did not mach the database I was trying to connect to. I was stumped and about put the tool aside as a great tool as long as you don’t connect to the iSeries, until I realized that whenever I connect to the iSeries using JDBC, I do so using IBM Toolbox for Java. Thankfully, I had the toolbox installed on my computer, so I went back down to the little yellow folder icon on the middle right of the panel and clicked to find the toolbox JDBC driver. As you can see, I found it in C:JTOpenlibjt400.jar. Now, I admit that because I have a lot of experience with the toolbox, I knew to look there and knew what JAR file to use. Without this experience I’m not sure what I would have done, but I probably would have started with Minq Software’s Web site and tested its support structure. So after some initial fretting it wasn’t long before I was able to attempt another connection to the iSeries. For more information on the toolbox, go to the Four Hundred Guru newsletter index page and read one of the many articles written on the subject.
Now I was ready to try again. Click here to see the main connection page. You can see on the left that I named my connection, and on the right you can see the details about the connection. I replaced my IP address with the generic “Your IP Address,” in order to protect the innocent, but in reality this is where you’d see the actual URL to your database. Click Connect, and away you go. In this case, using the toolbox JDBC driver worked, and I was now connected to my iSeries.
|
THE FUN YOU CAN HAVE
Once you have successfully connected to your database, you can create a connection to another database on a different platform, or you can work with the database you just created. Double-click the connection name on the left, and you will receive a list of iSeries libraries in the left pane. From there, you can view the database contents, run an SQL script, and examine the details of the table along with its relationships. If you right-click the table, you can run a “Select All” script, which will give you a screen where you can change the SQL statement to fit your needs or execute that statement by clicking the green arrow at the top of the screen. If you want to get back to the database objects panel, click the appropriate tab. You can always come back to the SQL panel by clicking the SQL Commander tab. There is also a Monitor tab that allows you to monitor what’s going on with the database, but I have not messed with that much, so I invite you to do so.
I have used other database tools that cost a lot more money, and they are very good, but for what I invested in DBVisualizer, I found it to be just what I needed. I didn’t cover all the cool things it can do, nor did I really cover the difference between the free version and $99 licensed version; however, everything I showed you in this article was performed using the free version. My advice is to play with the tool a little, evaluate the licensed version, which you can do for 21 days, and if you like it, pony up the bucks for the licensed version of the tool. These guys don’t know me so, I get nothing either way. But it’s easily accessible Java-based tools like this that make our jobs easier and, for us iSeries folks anyway, able to hold our heads up high with other developers and our user community.
Click here to contact Kevin Vandever by e-mail.
