Install SQuirreL SQL client to run interactive SQL on your IBM i.

As you start using embedded SQL more frequently within your RPG code, you'll soon find that your SQL statements are becoming larger and larger and the interactive SQL tool on the green-screen starts becoming quite cumbersome. In this article, I will introduce Squirrel SQL, which is an open-source SQL client that gives you not only a better interface to your DB2 database, but also access to numerous other databases.

I have to admit that I've been a green-screen lover for a long time and I initially rejected the use of graphical development tools in favor of staying with the tools that I'm used to. When I attempted to use graphical tools in the past for RPG, either they were too buggy, therefore deterring me from using them after an initial test run, or the mental segregation of development environments seemed to be more work than it was worth.

Last year, I had the opportunity to work outside of the IBM development realm and was exposed to all of the tools that are available there, and I was really impressed by how much of a productivity gain I was able to achieve using these tools. Upon returning to the IBM i, I have entered a new environment that emphasizes the use of the IBM Rational Developer for Power Systems Software as my primary RPG development tool versus PDM, but I still found a huge hole in my RPG tool set; I needed a robust interactive SQL tool that was not on the green-screen.

System i Navigator

I've tried using the SQL tool in the System i Navigator, and it does have some nice features, but it still wasn't giving me what I was looking for. I was primarily looking for a graphical SQL client that would have features like highlighted syntax and multiple SQL worksheet windows with a robust user interface, as I was accustomed to with the Microsoft and Sybase tools that I've used before. Not saying that iSeries Navigator doesn't have some noteworthy features, but these that I've mentioned were not to my satisfaction. And besides those specific options, I was also looking for a tool that I could use with Microsoft SQL server and MySQL in addition to DB2.

Enter the SQuirreL

SQuirreL SQL is open-source software that is contributed to by numerous developers and led by Colin Bell. It uses JDBC to connect to various databases and includes an editor that provides useful development features such as syntax highlighting.

You will need to download two components:

The SQuirreL Client

http://www.squirrelsql.org/

As of SQuirreL version 3.0, you must have Java version 1.6.x. For this article, we will be using the latest version available as of the publishing of this article, which is version 3.2.1.

Go to either the "Download and Instruction" page or the "Downloads" page on http://www.squirrelsql.org/ to get the squirrel-sql-3.2.1-install.jar file (Your version may differ).

JTOpen: The open-source version of the IBM Toolbox for Java

http://jt400.sourceforge.net/

JTOpen contains many resources for use with the IBM i. The one we will be taking advantage of is the JDBC driver that is included.

Go to the download page at http://jt400.sourceforge.net/ and download the latest zip file. As of this article, the latest version is jtopen_7_2_jdbc40_jdk6.zip. As the name implies, you are required to be at Java version 1.6.x as well.

Because the file is zipped, you will need to extract the files. I have created a jarFiles folder off the root directory of my C: drive on my Windows 7 computer and will be extracting my files into this folder. The file that I am concerned with is the jt400.jar file, which will end up being located in the following location: C:\jarFiles\jtopen_7_2_jdbc40_jdk6\jt400.jar. Your file location may differ depending upon which directory you are using and which version of JTOpen you are using.

Java Versions

Not Using Java 1.6.x?

If you are using an older version of Java, you can download an earlier version of either SQuirreL SQL or JTOpen by going into the "Files" page for either project; they are both available on Source Forge.

64-Bit Windows Java Errors During SQuirreL SQL Installation

If you are using the 64-bit version of Java on Windows, you will receive an error during the installation because the 64-bit Windows version of Java does not support the creation of shortcuts as of the writing of this article. The error I received was "could not create shortcut instance."

Figure 1: You'll get an installation error when using 64-bit Java. (Click images to enlarge.)

I believe you could still use the SQuirrelSQL client after this error occurr, but I wanted a clean install. So, to avoid this error, you simply download a 32-bit version of Windows for the install.

Checking Your Java Version

You should check your Java version before beginning the SQuirreL SQL installation. To do this, go to a command line (in Windows, click the Start button and run the cmd command). On the command line, type the following:

java –version

As you can see, I have a 64-bit version of Java installed on my Windows 7 Computer.

Figure 2: Here's what 64-bit Java looks like when displaying the version.

Using Another Version of Java

For those of you who are Windows-savvy, these instructions may seem verbose, but from my experience I'm sure some readers will appreciate the extra information.

Java allows you to run multiple versions of Java on your computer at the same time. To install another version, go to the Oracle Web site by clicking here. Download the 32-bit version of the JDK (Java Development Kit).

Figure 3: The 32-bit Version of Java does not have x64 after it. 

Installing SQuirreL SQL

In the step above, we downloaded the squirrel-sql-3.2.1-install.jar file. To install the software, go to a command prompt and navigate to the directory where you downloaded the jar file and execute one of the following commands:

If not using 64-bit Java:

java -jar squirrel-sql-3.2.1-install.jar

If you have determined that you are using a 64-bit version of Java, you'll need to override the default to use an alternate 32-bit version by specifying the patch to the Java executable:

c:\"Program Files (x86)"\Java\jdk1.6.0_21\bin\java.exe -jar squirrel-sql-3.2.1-install.jar

Note: Your path may differ, depending upon what operating system you are running on, what version of Java you installed, and where you installed it.

You can run the jar from any location because you will be specifying the installation path during the installation as seen in the figure below.

Figure 4: Installation SQuirreL credits and installation path are displayed.

Select "Optional Plugin - DB2" when you are prompted.

Figure 5: Select the Optional Plugin – DB2.

After you click on Next, the software will begin installing.

Note: If you have the 64-bit Java problem I discussed above, the program will crash before reaching the shortcut screen.

At the shortcut screen, you can just take the defaults and hit Next. I suppose there is a timing issue with the installation process; otherwise, you would be able to uncheck the Create Shortcuts checkbox and never encounter the above-mentioned 64-bit Java problem.

Figure 6: Set up the shortcuts.

After you make shortcut selections and click Next, your installation will be complete.

Running the Application

Depending upon the shortcut options you've selected, either you can start the application using the shortcut or you can call the squirrel-sql.bat file that is located in your installation directory.

When you start the application, you will get the splash screen and then you will see the application screen. In the application screen, click on the Drivers tab on the left. It should initially look like this.

Figure 7: Set up the JTOpen Driver with the pencil icon in the tool bar.

Setting Up the JTOpen Driver

To set up the driver, select the JTOpen(AS/400) driver and then click on the pencil icon in the tool bar directly above the driver list.

On the Change Driver Window, click on the Extra Class Path tab.  Then click on the Add button and navigate into the folder where you downloaded your JTOpen jar file. For my example, the jar is located at C:\jarFiles\jtopen_7_2_jdbc40_jdk6\jt400.jar.

Figure 8: Add the Extra Class Path and view successful driver install with check mark.

Once you add the extra class path and click OK, the JTOpen(AS/400) driver should now have a check mark next to it.

Setting Up an Alias

Once the driver is set up, you need to configure the application for your IBM i. Click on the Aliases tab on the left of the application screen. Then click on the blue plus sign (+) in the tool bar above the empty list box to add a new alias.

The initial values for the alias will show you what values to enter:

jdbc:as400://<host_name>/<default-schema>;<properties>

If your IBM i was named mcpress and you wanted to use a library named testlib, then your alias URL could look like this:

jdbc:as400://mcpress/testlib

Figure 9: Creatr an alias URL with user name and password.

Note that you could enter the user name and password, but there is a warning that the passwords are saved in clear text. So you may want to leave these blank and enter them when you connect.

Running a SQL Statement on your IBM i

Now that everything is installed and configured, let's take it for a spin. To connect to your IBM I, you will be using the alias that you've created. Double-click on your alias to be prompted for your user name and password (unless you've saved them with your alias).

Now pick a physical file from the library that you set up in your alias and run a SELECT * FROM over your file. You'll see the records listed in a table below your editing window.

To run a query:

1.  Type the SQL into the text area.
2. Select the query by dragging your mouse over it. 
3. Click on the running man icon.

Figure 10: Run select * from employee query in SQuirreL SQL Client.

Congratulations! You have successfully installed and tested your SQuirreL SQL Client. Now you're ready to start creating SQL works of art to embed into your RPG.

References

SQuirreL SQL

http://www.squirrelsql.org/

JTOpen: The open-source version of the IBM Toolbox for Java

http://jt400.sourceforge.net/

Toolbox for Java and JTOpen on the IBM Web site

 http://www-03.ibm.com/systems/i/software/toolbox/

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7,