Use the Beaver!

In this article I will show you how we can use beneficial plugins to enhance our RDi environment.

If you find the Run SQL Scripts limited in its functionality, not being able to prompt SQL statements, no auto-complete etc, then the Beaver may be for you!

DBeaver is free and open source universal database tool for developers and database administrators.

It supports all the usual databases, including our DB2.

The latest version, version 6.0.3 was released on the 21/04/19.

Installation

The easiest way to install DBeaver is through the Eclipse marketplace, which can be found under the Help menu.

Use the search facility to narrow your search.

Follow the install instructions to install DBeaver.

If you are having difficulty using the Eclipse marketplace, it can be downloaded and installed manually from here.

RDi Perspective

Once installed, use the DBeaver perspective. This can be seen in the figure below.

To create a new connection to our IBM i, click on the New Connection button (shown below), or use the CTRL + N shortcut.

Select the DB2 for iSeries/AS400 option and follow the wizard to create a new connection.

Once the connection is completed, we can expand on the new connection and see all our libraries/schemas, and drill down to see additional details.

Features

Let me show you some of the useful features in this plug-in.

Searching

Searching for any database object is easily performed using the search box. This is shown in the figure below.

Easy to use and very fast.

Quick View

To easily see the contents of a table, just right click and select Read Data in SQL Console

And, voila, all records are shown. Additionally, you can also filter the results by using the Ctrl + Space shortcut.

Auto Prompting

A very useful feature is the auto prompting. When we start typing a SQL statement, the beaver will automatically show us the columns and their attributes. Stops some of my typos!

Current Schema

Another one to stop my typo’s – My days in the Royal Navy, where they taught me to type, is far in the distance!

Look for the toolbar option to set the current schema, as shown obove. It even provides a drop-down list of all schemas on our box.
Another very useful feature.

Create Table Wizard

The Create Table Wizard assists those for whom the Create Table SQL statement doesn’t roll off the tongue.

1. Right-click the schema, or Table section > Create >Table
2. Enter the name of your new table
3. Right-click in the column section > Create > Column
4. A new window will allow you to input the column attributes
5. Click the Save button on the lower toolbar

Once the Save button is pressed, a summary will be displayed prior to creating the table.

These are just a small number of the features in this impressive package. I’m sure you can find more, let me know your favourite.

Conclusion

Hopefully, this article has given you an insight on how we can include additional packages into our RDi environment. Yea, I know there are other options to access our database, but I have found this one to be very useful.

If you have any questions, let me know.

I’m looking forward to the next round of the i-UG user group meetings this year in the UK, the International i Power 2019 conference on the 11th & 12th June 2019. Hopefully we can meet up at the main event of the year at our local football stadium in Milton Keynes. Full details can be found on the iUG site here.

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 39 years IBM midrange experience.