Video : SQL Queries for Multiple RDBMS

00:08

DBArtisan

Hello everyone and welcome to this DBArtisan connecting building queries with multiple databases webinar. I’m your host Anita Mahadev, and of housekeeping rules for today. If you have any questions, please feel free to enter those questions in the Q and A in the go through Webinar Control Panel. If you’re having any other questions, we’re more than happy to take it offline. Or if you were to request a demo, happy to do so as well. Today what we’re doing is talking about how we can leverage DB Artisan, which is our flagship database administration tool for a multitude of activities. I just would like to get a show of hands if people can see my screen, please. Awesome, thank you so much. What we’ll do today is we’ll be exploring the DBArtisan product line, and one of the key benefits that we’ll be taking a look at and focusing on is our ability to build queries faster and easier from multiple database platforms.

01:27

User Interface

Without further ado, let us go ahead and explore the user interface, shall we? On the left-hand side is our Database Explorer. The Database Explorer, it comprises of all of these folders where you have three different database platforms that we’re connected to. Here I have Microsoft SQL Server 2019, and then here I go and take a look at Postgres SQL MySQL which has version eight, and here I have Postgres SQL which is version 14. With the host of these options we can connect and start building queries with ease. Now what I would like to do is to go ahead and showcase SQL Server and MySQL today and we’ll show you on the UI on how to get started. On the right hand side is where you have the Information Explorer that talks about the various server components and also gives you information about the particular server.

02:32

Utilities

Here you’re looking at SQL Server and with the MySQL here you’re looking at all the metadata associated with that MySQL. Once you are able to get into each of these, each one has got specific utilities that are specific to those database platforms. At the top you have your toolbar and your menu bar. If I were to click on SQL Server, for example, and if I needed to first and foremost register a data source, I can actually go to the data source menu and click on Register.

Multiple Databases

Now this is where we will show you how we support all the different database platforms. We have the ability to leverage all of these here. For example, if I’m interested in Oracle, I could click on that. If I do sidebase, I have all that information here. Now for databases that are not currently supported, what you could do is use the providers ODBC or JDBC driver and that helps you to leverage at least the basic querying capability so that you’re not at a loss.

03:54

Data Filtering

So that’s where DBArtisan shines. Now let’s explore what we have here in terms of the explorer options. So here we have our filters. Here what I could do is I could technically filter out only my objects, which means to what user I’ve connected to and what privileges I have. That is a great way of doing it. I can also ignore system-level tables, which means that I do not have to bring in anything from the system-related table, so it’s not going to bring up any of those. Now if I were to uncheck that, I can actually go in here and take a look at these things. Now I get my system tables here. So that’s awesome. Let me just go and focus on showing my objects and ignore the system objects. Here in DBArtisan, we make it very easy for you to get started by writing queries.

05:03

Here if I expand the Wide World Importers database, I can see if I can go ahead and start browsing each of these. Here it shows you the table owner. If you’re interested in a schema, you can actually go and just type it in here and it gives you those and the same thing for the tables as well. Here if I give employee I should get any of that, or if I just type in any of these, I will get part of the word or any of that information. Of course, we also have sorting, so we can sort it based on the maximum number of rows here. We have about those many rows in the database. If I am interested in particular file groups here, you can see I have all of those file groups being stored, all that good stuff. Now let’s talk about how we can query the information.

06:06

SQL Queries

Let me go under here and let me pick the sales schema here. I will go ahead and let’s say I go ahead and right-click here and I say select start from and there you have it. It quickly just gave me all of the information that I needed. Now you could also one of the key benefits of DBArtisan is that in the results pane, you can actually go ahead and highlight specific rows. Let’s say that you’re doing a testing project for a certain department and then they’re asking for some test SQL. You can actually go ahead and do this here. If I click on okay, and there you go. Now I can actually go ahead and look at this test SQL and I can get this back to my development team and go from there. I can actually go and just say format SQL here and there you go, it looks very pretty.

07:04

Now I can also from here on I can actually add this to my script library now script library will enable you to have shortcuts that you can generate the SQL statements here. For example if I were to just enable it and enable a hotkey. If I just use control, then I do control one and then I open up a brand new Is equal window and I just type in CTRL one and there you go, my code Snippet is ready. This makes also really handy when you’re working with multiple areas, you can actually have them in projects. For example, I can actually go and do files say new project and from here I can actually go ahead and say I’ll just call this as the advertiser, I’ll call the wide World Importers DB project. You have options where you can initialize from the database or from any of these existing files.

08:10

I’ll click on yes I’ll choose my database platform here and from here I can actually go ahead and pick the database. I’ll pick the schema since I’m interested in only the sales schema here. Here you’re given a list of objects that you would like to go ahead and bring back. I’ll just pick tables and here I’ll just do select all, choose next. Here’s where I can actually go ahead and choose Retain owner and the file encoding type. We support all of these and you can also include drop statements, include system privileges, object-level privileges, all of those information here. Now once I’ve done this, I can actually go ahead and save this. I can save it as a dot options template. I can do that or I can just hit finish and go from there. I’ll go ahead and hit finish and we should be good on there.

09:17

Reporting

I can also generate a report here. If I pick this and it will give me an extraction report at this time. With that extraction report I can actually go and look at all of the objects that I’m bringing in and what is populating information here. Now this is very good for you as DBAs for documentation purposes. That gives you the benefit of leveraging this here. Now moving back, let’s hit continue. And now here’s our project that’s ready. Here you can actually go ahead and connect it to any kind of Git repository, et cetera. How you would do that is you would basically just connect this WWI DB to your local branch and do a Git push and it would go automatically to your Git repository.

SQL Queries

Now, if you wanted to go ahead and start building a query real fast, what you can do is you can initialize a brand new ISQL window and you can right click here and you can start pasting SQL syntax that you would like.

10:36

Here if I’m interested in looking at any of these, I can actually go in here and paste any of the SQL syntax that I need I also have the ability to do any of these here as well. If I choose, let’s say, the sales here, if I do any of these, and if I get any of the properties, I can go ahead and do that. I also can get these like this and start building my queries like this. I can just say Pay statement and boom, there you go. Now I can go ahead and execute the statement and this gives me the result set. It’s going ahead and getting me all of these rows. And once it’s done with it’ll stop. Now what I can do is I can actually go back to DB Artisan here. In the other aspects of this, if you look at from a grand scheme of things, we also have our utilities menu.

11:42

So, for example, here, if you look at this, we are looking at data, unload the database monitor. That’s one of the key areas of DB Artisan where you can see that what are the processes that are going on. You have all of those options there. You can also go ahead and kill a process if you wish. By highlighting it and doing that, then what you can do is you can also set the refresh rate here. It’s doing every 90 seconds. I can just put it every 30 seconds if I wish. I just need a quick show of hands to make sure that everybody’s, all of this is resonating and that you’re able to follow. Okay, awesome. Next what I’ll do is I’ll actually give you a sneak peek into how you can actually go ahead and start using the Query Builder. If I were to go to my tools and then here I can actually go into Query Builder, this is where I can actually go ahead and showcase all of these.

12:57

Let’s say I’m looking at multiple areas. If I go and just right-click and say Add. Here again, you have the ability to choose the schema that you’re interested in. So I’ll choose the sales schema. I’ll pick let’s say I’ll pick order line orders. I’ll pick customer archive and customer sales. Yes. I’ll right click on Add and DB Artisan will automatically go ahead and pick all of those. All I can do is I can pick my primary key ID like this. Now I can actually go ahead and start getting all of this information here. Now that I have this query below, I can actually click on this and it brings me to the direct editing of this. Now I can just go ahead and execute the statement. And there you go. Again, we do support the ability for you to just do a copy and then paste it in the Query Builder.

14:08

Also any changes you make here will be affected in the query below. As I’m adding newer columns, you can see that it’s building this. So we have that here as well. Next, in DB Artisan, as we’re building this, one of the key aspects is to try and figure out what we have in the query builder is we have this auto layout option. It auto layouts for us very nicely. For example, if I were to bring this down, it neatly auto layouts this for us. We also have the ability to auto join as well. If it’s not already selected, it will go out and auto join it. You can also go ahead and check the syntax and it’ll give you if there’s a warning for this. A DBA Artisan helps out in a lot of ways. Whether if you’re a new DBA starting out, you can also go ahead and get started with this tool with ease.

15:19

Next, let’s take a look at how you can go ahead and show the data types as well. So that’s another aspect. When you’re building a query, you need to look at the data types that you’re querying against to make sure that they match. Here I’ll just say hide unused columns. That makes this more presentable. There you go. Now only the query and the columns that I’m interested in are being displayed so that I know that these are the queries and these are the columns are matching. That gives you another error proof method of working within the query builder. Here in the statement properties, this is where you would actually go ahead and look at including row count limits. You can generate SQL 92 if it’s been supported by the database. The maximum row count, you can actually just specify give me the top ten or the top 100.

16:18

Yeah. You can also go ahead and auto-format this and you can also showcase column data types in your query diagram, all that good stuff. You can also go ahead and put in the table color if you wish. If you wanted this and if I wanted the title font to be Verdana and columns font also I want it to be Verdana and it gives you this kind of color. You have all of those options there and I’ll go ahead and change this back. There you go. You have now the font and the colors, everything being set for you.

SQL Server

Next in DB Artisan, we will take a look at some of the areas that are specific to this particular database platform. For example, if I click on the SQL Server one, go to utilities, I have the ability to look at my database manager. This is where it tells me what exactly this database does.

17:35

Looks at the data files, looks at the log files, the space and the DDL. At any given point in time, we provide you with all of these commands at Quick Access. For example, if you wanted to do a DB C on the database, you can just check which operation you would like to do. It goes and gives you that as well. You also have the ability to preview the SQL and you can schedule this accordingly. This brings up the job scheduler of your Windows task scheduler. You can go ahead and run this weekly, daily at your convenience. You have all of those as well. You can do it every week or even some of the work days of the week going to advanced. You can repeat this task every 10 minutes. All that good stuff. There’s a lot of integration with the operating system and the tool itself.

18:44

Workbench

Next, let’s talk about how you can be very effective with utilizing DB artisan with our code workbench option. Here if you look at the auto-replace so let’s say I’m working with some of these commands and I wanted to go ahead and have a quick expression and it should autocomplete based on these activations. Let’s say I go ahead and add a new one. I’ll just call it create. The activation for this is going to be tab. And I’ll do it with create table. Here I’ll just put a carat symbol and I’ll call it as table name as a template and I’ll click okay, so now if I go and open up a new I sequel window type in create hit tab, I get this. So really handy. Today we discussed a couple of options where we can help you to build queries efficiently and easily using multiple databases.

20:03

MySQL

Now I showed you with SQL Server. Now let me show you on MySQL so here the same thing holds true and if you look at the Tools menu you will get the query builder here as well. Here all I got to do is just pick that and then again do this, do an auto join, I can do that and then just pick all of these here. Last but not least, I’ll pick the last table as well, auto layout. Go here and I bring these here. Go to Table and just say hide unused columns. Yeah, there you go. Now I can actually have this here. Plus I can go back to the query here, just do a copy. Here I can actually go ahead and run this. Either way it will look at the syntax and see if there’s a joint type near this. It just depends on that, but at least you know that it’s giving you the correct error message from the parser itself.

21:38

Performance Analysis

Now here, as you can see in DB artisan, each of these are different based on the database platform, right as I described, the next thing I wanted to showcase is the analyst pack now, the analyst pack is only available for SQL Server, Oracle, DB Two, and Sybase here. If you take a look at the performance analyst, this gives you an overview on how you can go ahead and view certain activities within the database. Let’s say you’re interested in the memory I O space. Get a list of all the databases it gives you. Like, look, there’s a bottleneck here and it’s giving you an ad hoc. This is an ad hoc performance analysis tool that will actually go ahead and install a couple of stored procedures within the system database. You should be able to go ahead and view this here as well. You can choose to install this on a user database, preferably a user database.

22:47

From here, you can take a look at all of the SQL, any alarms that there are. Here you can see if I can right click and go to the applicable page. It gives me all the information I need to do. Diagnosing the databases real quick really helps with DB Artisan. With that, I would like to go ahead and take some questions here. I’m going to go ahead and stop sharing here and let’s take a look at here. Okay, so let me answer these questions. Yes, we will be making this recording available, and would MariaDB be considered as MySQL? Yes, it would be considered as MySQL. If I want to look at the delta between the two, that is where you have the ability to use our DB Change Manager tool. For any kind of change management capability, you would have to use our DB Change Manager tool.

23:57

Okay, so what we’ll do is we’ll make this recording available to you, and then if you have any questions where you can go ahead, I’ll start sharing again. Where you would like to go ahead and get information about DB artisan, I’ll go ahead and open up a web browser window. You will have to go to Idera.com and under Idera.com, if you go to the product section, you can go into the DBPR Studio section and you can start for free today. We have free trials that you can take advantage of. We also provide you with all the features and all the availability as well, and we will provide pricing. We’re super excited for you folks to be trying out DB Artisan today. Let me take a look at one more question here. So, Aqua Data Studio is a different tool set. It provides different database migration.

25:08

There’s no direct path or migration to DB Artisan from Aquadata Studio. It has certain capabilities in terms of visual analytics. So that’s something different. It’s more catered towards the business user or business analyst. DB Artisan is primarily catered towards the database administrator. You can manage and administer databases, but this is much more advanced in its capabilities. Those are the areas and if you would like to request a demo, please send an email to [email protected] and I’ve just put in the chat for everybody’s benefit. Please send an email to [email protected] and then we look forward to speaking with you soon. Okay, I think we still have a couple of more questions. So yes. If you’re focused more on analytics yes. Then Aquadata Studio is for you. Do you work with AWS and Azure? It depends on are you asking about the platform as a service or the infrastructure as a service, please.

26:37

In terms of the infrastructure as a service, we do connect with AWS and Azure as a service. So, yes, as long as you’ve opened up the RDS and the Firewall rules, you should be able to connect to those two. Yes. So, for example, let me quickly share my screen again. Here let me go to my data sources. If I go to New if I go to Data source and choose register data source? Yes. We do have Azure here, so we just do not have AWS RDS, but it’s similar to that. So did I answer your question? Please? Yes. Okay, awesome. Well, thank you, everyone. It’s been a real pleasure, and we’re super excited for you to be trying out DB Artisan, and we can’t wait to see what it will do for you and your organization and wishing everybody a great rest of the day. Thank you.