z/OS Platform Evaluation and Test (zPET) runs customer-like workloads in a Parallel Sysplex environment to perform the final verification of new IBM Z hardware and software. 

Introduction

Should I read this blog?

If you don’t already know what IBM Z Digital Integration Hub (zDIH) is, or what capabilities it offers, we strongly recommend reading our previous blog, zPET Experiences with Z Digital Integration Hub (zDIH), as a prerequisite to reading this one. In our previous blog, we give a brief introduction to zDIH, provide links to official zDIH guides & documentation, and provide some insights that could be helpful to beginners on zDIH.

If you do know what zDIH is, but you don't have much firsthand experience loading or viewing data with it, we additionally recommend reading IBM Z Digital Integration Hub (zDIH) - Loading, Viewing, and Querying Data, which covers topics including setting up the zDIH Management Center (to view your clusters & data) and running a sample zDIH application (to load data to your cluster). 

Why should I read this blog?

This blog will focus on DBeaver, a popular open source SQL client, which offers an additional avenue to interact with your data residing in zDIH. We will walk you through the steps to install & configure DBeaver, and provide examples for how to query your data. Additionally, this post won't read like some of our previous posts where we take time to discuss concepts and so on. Our goal here is to provide a concise step-by-step guide for using  DBeaver in tandem with zDIH, while still providing an abundance of information and tips to get you through confusing processes.

For the rest of this blog we will assume you have a basic understanding of the product. This blog is not meant to be a replacement for the product documentation, but rather a cook book to fast track your learning of IBM zDIH. See the IBM Z Digital Integration Hub documentation at https://www.ibm.com/docs/en/zdih for complete installation and customization instructions.

Installing DBeaver & Configuring a zDIH Connection

1. Installing DBeaver
   1. Install wizards & zip files are available at: https://dbeaver.io/download/
   2. This should be a straightforward download & install - we recommend downloading the installer path and following the default steps within the wizard
2. The following steps will refer to the zDIH provided documentation for this process which is available at: https://www.ibm.com/docs/en/zdih?topic=interfaces-establishing-sql-interface-zdih 
3. Locate the zDIH JDBC driver
   1. This step is paramount. If you do not have the JDBC driver then Steps 4 and beyond will not work in the end. We assume that you've previously setup the Management Center (MC) on your workstation. If you have not, then please read IBM Z Digital Integration Hub (zDIH) - Loading, Viewing, and Querying Data before continuing on
   2. Within the MC installation directory on your workstation you should have the following file: <zdih_client_extracted_path>/zdih210/sql/hazelcast-jdbc-enterprise-x.x.x.jar
4. Open DBeaver and create a new DBeaver Database Driver
   1. Go to Database (top toolbar) and select Driver Manager
   2. 
   3. On the resulting pop-up select New
   4. 
   5. On the next pop-up enter the values for the corresponding properties but DO NOT click OK just yet
      1. Driver Name: any custom name for the driver (here we entered MyDIHDriver)
      2. Driver Type: Generic
      3. Class Name: com.hazelcast.jdbc.Driver
      4. 
   6. Before clicking OK, switch to the Libraries tab > click Add File > this should bring up a file explorer window where you'll need to select the <zdih_client_extracted_path>/zdih210/sql/hazelcast-jdbc-enterprise-x.x.x.jar file that we identified earlier
   7. 
   8. Once the jar has been added you can click on Find Class and choose com.hazecast.jdbc.Driver from the drop down menu. Once selected you can now click OK!
   9. 
5. Add a new connection to your zDIH cluster
   1. There are a few places where you can click to make a new connection, two New Database Connection buttons which should be visible in the top-left of your DBeaver window, or from the Database > New Connection option in the toolbar
   2. 
   3. On the following pop-up menu you can filter the different types of database connections by typig in the name of the Driver you entered earlier - remember that here we used MyDIHDriver. Select it and click Next
   4. 
   5. On the next pop-up menu enter the appropriate JDBC url for your cluster, but don't click Finish just yet
   6. 
   7. Now we must add the cluster name for the connection, but be warned this step can seem a bit finicky. To add the cluster properly:
      1. Change to the Driver properties tab > right-click in the middle of the menu > you should see an option Add new property pop up as shown below
      2. 
      3. Enter clusterName in the resulting pop-up and click OK - you are not adding what you want the cluster name to be, you are adding a property called clusterName 
      4. 
      5. Once you see clusterName appear in the Name column, click next to it in the Value columns and enter the name of your cluster. For some reason it's not readily apparent that you can click there to add the value
      6. 
   8. You may now click Finish, and you should see a new connection created in the Database Navigator panel on the left side. When expanded, your connection should look something like the picture below. If so, you're ready to start issuing queries!
   9. 

Querying Your Data

1. Create a new SQL script
   1. There are several places where you can create a new SQL script:
      1. the SQL button in the 2nd toolbar
      2. SQL Editor tab > New SQL Script
      3. right-click your connection > SQL Editor > New SQL Script
      4. 
   2. Whichever method you choose, once you’ve opened a blank script you can either begin writing the SQL query manually, or you can right click a table > select Generate SQL > choose the type of query you want to run
   3. 
   4. Here we’ll choose to do a simple select and see that DBeaver generates a full query for us. By clicking Copy the query will be copied to your clipboard and you can paste it in your SQL script
   5. 
   6. Now that you have your SQL script fully finished, all that’s left to do is run it! This can be done several ways, one of which is to right click the specific query to run (here we chose only the first) > Execute > Execute SQL query. This will automatically populate the results tab in the bottom of your DBeaver window
   7. 
   8. 
   9. You could also choose to right-click > Execute > Execute SQL script which will run each query in succession. When this is done, you'll see the result for each query appear in a separate results tab, along with some statistics
   10. 

Conclusion

If you have made it this far without any issues, then congratulations! You now know how to connect DBeaver to zDIH and effectively use it to query your data. We hope that this has been a useful guide and that you learned something new. Thanks for reading!

References

• DBeaver
• zDIH Official Documentation
  • Home Page
  • Establishing a SQL interface for IBM zDIH
• Previous zPET blogs on zDIH
  • zPET Experiences with Z Digital Integration Hub (zDIH)
  • IBM Z Digital Integration Hub (zDIH) MongoDB Connector Testing
  • IBM Z Digital Integration Hub (zDIH) - Loading, Viewing, and Querying Data

Authors

Trent Balta (Trent.Balta@ibm.com)

Kieron Hinds (kdhinds@us.ibm.com)