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
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
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:
- Type the SQL into the text area.
- Select the query by dragging your mouse over it.
- 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
JTOpen: The open-source version of the IBM Toolbox for Java
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,
Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.
Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.
Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.
Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.
MC Press books written by Thomas Snyder available now on the MC Press Bookstore.
 |
||
 |
|
Advanced, Integrated RPG See how to take advantage of the latest technologies from within existing RPG applications. List Price $79.95 Now On Sale
|
|
||
 |
|
Extract, Transform, and Load with SQL Server Integration Services Learn how to implement Microsoft’s SQL Server Integration Services for business applications. List Price $79.95
Now On Sale
|
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online