05
Sat, Oct
2 New Articles

An Open-Source DB2 SQL Graphical Tool That You Can Use for All Your Databases

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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."

 

021611Snyderfigure01

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.

 

021611Snyderfigure02

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).

 

021611Snyderfigure03

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.

 

021611Snyderfigure04

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

 

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

 

021611Snyderfigure05

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.

 

021611Snyderfigure06

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.

 

021611Snyderfigure07

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.

 

021611Snyderfigure08

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

 

021611Snyderfigure09

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.

 

021611Snyderfigure10

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,

Thomas Snyder

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 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 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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More 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.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY 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.

  • SB HelpSystems ROBOT GenericForms 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.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA 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:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA 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.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra 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: