IBM DB2 IDE

Aqua Data Studio

Hello, and welcome to Aqua Data Studio. IBM DB2 overview. In this session, we’ll be covering how easy it is to connect to IBM DB2 databases using Aqua Data Studio. Aqua Data Studio, a universal IDE that helps to connect databases and helps It professionals in their day to day tasks. It also allows you to develop and manage your databases, perform common tasks such as export import, querying databases, and visually analyzing those data. Aqua Data Studio supports more than 30 plus database platforms and works on a variety of major operating systems such as Microsoft Windows, macOS, and Linux. Finally, Aqua Data Studio supports not only relational, but no SQL and cloud based data sources that makes accessing your data quickly and easily. The agenda for today will be to register a server, perform simple SQL queries against the database, export the query results to an Excel spreadsheet, create a visual analytics diagram, and finally reverse engineer it to a data model.

DB2 Relational Database

So let’s get started. Here I have Aqua Data Studio, and here I have gone ahead and established a connection to an existing DB2 database. What I would like to do is to be able to show you how easy it is to register an IBM DB2 database. That’s going to be the first step. I’m just going to go ahead and right click on this particular server and go to Server Properties. This brings up my IBM DB2 LUW. You pick the one on the left, which is below the LUW 7.X, and choose this option. You can give it a name, choose a type. Here I’ve chosen Production, and you can choose a tap color so that it will uniquely identify the database platform that you’re working with. I would use the tab title format as default. I can then use my DB2 credentials here with my username and password.

Connect to DB2 Database

Here I can give my hostname and finally I can give my port number and a database I’m trying to connect to. Now I’ll go ahead and choose a test connection. Then I’ll go ahead and choose save. Now we can go ahead and choose Connect. Here you can see under my schema, you can see a list of objects that will be populated. Here I’ll go under my schema here and expand it. Let’s take a look at how the navigation tree is here. Here you can see a list of tables, views, some stored procedures and triggers that you have. Now let’s explore the navigation tree and specifically on the storage and management security aspects of the DB2 database. Here, if you were to expand the buffer pools, you’ll see that you have a default IBM buffer pool and you have your default tablespaces that when you install DB2, it comes with it.

If you were to go ahead and want to know some details about these buffer pools, just go ahead and right click and choose Alter Buffer Pool or choose Buffer Pool Properties. The same goes for the table spaces. You can go ahead and alter the table space or you can just go and right click and just choose Table Space Properties. Next, let’s go under the management. Here if you expand this, you can look at some of the connection info, like who’s connected to which server. Next, let’s go and choose the security folder. Here you can see I have my groups, users and roles. Currently if you want to look at what the user role is on this, you can go to the User properties and this will give you all the permissions on what they have, on what roles they are able to do. Now that we’ve covered this, let’s go ahead and see how easy it is to go ahead and execute queries.

Query Analyzer

Within Aqua Data Studio. You can do one of two things. You can either go to the Query main query menu and then from there you can just look at some of the options that are available. Or you can go ahead and right click on a particular object and choose the Query Analyzer. Now this brings you the ability to go ahead and write your own queries. Or if you prefer a shortcut, you can always go ahead and right click on the object that you’re looking for and use the select Top 1000 as an option. Now here you can go ahead and hit the Execute button and here you can see it will go ahead and give you the information. That was in a text format, you can have it in a grid format, et cetera. Now let’s see how we can go ahead and build a query using the Query builder.

Query Builder

Here if I were to right click on any of the objects, I can go to Query Builder and this brings up the Query Builder. All I have to do is as you would do with any of our database platforms, you can just drag and drop the objects here. Here you can choose whether you want any specific tables or et cetera. Here you can go ahead and add these. Now, if I wanted to add a join between this employee here and the department here, I can go ahead and do the join between the work department and the department number here. I can also go ahead and execute some advanced queries. You’ll see Aqua Data Studio is building those queries right here. Now let’s take a look at how we can go ahead and add some advanced results. Let me go and right click and choose select from the product table.

Now I can actually go and choose under the New Visual Analytics workbook, under the Result set, or I can go to the Visual Analytics and choose New. Now here you can actually go and choose to connect to data. Here, since we’ve worked with this last query, I’m just going to go ahead and load that data set and it gives me my dimensions and measures. I can then drag and drop these according to my wish. I have a list of info that I need here. It gives me all of that there. If I want to look at the promo start date and I want to have it in a different color, I can look at all of these here in different options. Now, what’s really cool about this is that as I hover over, I can actually go ahead and view the data on this particular section.

Export Data to Excel

It’s pretty awesome. Next, let’s take a look at how we can export this result set to an Excel spreadsheet. Now here I can actually go ahead and say view as Excel spreadsheet and within a couple of seconds I have the result set right here. Now lastly, let’s take a look at how easy it is to generate an Er diagram from this tool. All you gotta do is right click on the schema and then go tools and choose Er Diagrammer Generator. Or you can go to the Er modeler main menu and say Generate. Here, since I have my IBM DB2 LUW, go ahead and connect to this particular schema. Here I have some choices that I can choose. I can choose this. If I have some views, I can also go ahead and choose them as well. So we’ll just give it a moment.

Within a couple of seconds I have a great looking Er model that I can share with my colleagues who are interested in looking at how the database has been designed. I can also go ahead and display only certain items if I need to. Here it’s on the primary key and foreign key. And then here I’ll just choose this. I also have the option to generate a sql script right off this and choose an HTML report, save it as image, compare it with the model compared with the database, all that good stuff. That was how easy it was to work with IBM DB2 in connection with Aqua Data Studio. I hope you folks have found this useful. For more information you may download a free trial at aquafold.com and feel free to contact [email protected]. Thank you.