Video : DBArtisan for the DBA

00:07

DBArtisan

Hello. Let’s get started. Here we’re looking at DB Artisan’s welcome page, where we can quickly find things that we need to start with. Either a new project, new is equal. Look at our recent files on our recent data sources here, let’s access the data source Explorer. For this skill. Sprint. I’m connected to both SQL Server as well as Oracle, so let’s expand my data sources. Here’s my SQL Server. Here’s my Oracle. So, one of the things that DBA find themselves needing to do often is looking at the processes running on the database. To do that from DB Artisan, select the data source from the utilities menu, select the drop down and we’ll find database monitor. Again, we’ll go through the list of these other utilities later on in this skill, Sprint. In this case, I already have a monitoring session active with my Oracle data source.

01:01

Database Monitoring

On another screen I’m going to start up a process in SQL Plus so we can see that as we refresh the screen here, we’re going to start able to view some of the processes. Here is the exact query that I’m running in my session. Here we get the session ID as well as other information. Now at any time I want to hide the SQL and maybe get more real estate to look at all the processes, I can simply select the Toggle here to show SQL. Here I’m able to see all the processes, then more real estate if I want to. Again, toggle that back on, we can show that if we need to stop this, we can simply select and kill the session. Here we’re going to kill process 35. What you can see on my other session, I get the prompt, the aura 28, indicating that my session’s been killed.

01:54

Again, doing that right within DBArtisan. By taking a look at the query that’s being executed, I’m also able to look for Oracle, look at the long operations as well as locks, any blocking processes and other information. As you can see, I’m also able to as I go back to the processes, one thing that our users have requested is the ability to move this window, have it float if you will. Now in the later versions we have the Float option. Within DBartisan for the database monitor, select Float. Here I now have a floating window displaying my process monitor. Again I could drag that over to another menu if I’d like another monitor and work with it from there. If it comes time, bring it back, I can dock it as a tab document. So here again, able to work through. Now at any time, I’m also able to look and filter by anything.

02:53

If I did want to filter by a specific server, a specific machine, I’m able to do that in real time. If I’m looking here for users and I want to see if Sys is running anything. I can see that as well. Again, nice features found right within the database monitor. If I were to hop over to my Oracle connection and let’s open up a database monitor, I’m sorry, for SQL Server, open up a data source monitor here, then I can see my information here as well. Again, here is my active process, my process for DBArtisan, showing the exact query that DBArtisan is actually executing to bring back this grid. For SQL Server, we’re able to see very similar information just specific to the platform. Again, process locks any blocking processes all the way over to current executions. Again, we’re able to see that information right from the database monitor in DBArtisan.

03:50

Schema Extraction

Again, to find that, simply select the Utilities menu and find Database Monitor. So, another option that our customers like utilities. From the Utilities menu, we have some options available. We have schema extraction, migration and publication. Here let’s walk through a Schema Extraction example using SQL Server. Again, multiple ways within DBR Artisan to do things. In this case, we’re going to use the Extraction Wizard. I’ll come back later in the Skill Sprint and show you another method of extracting the objects in your database. So let’s get this started. A new one will select my SQL Server 2012 instance that I’m working with, I’m connected to. Here I’m going to select my Gim database, an example database that I use. Here we can opt for the Schemas. Again, in this case, I’m going to be looking here to find the specific schemas that I’m going to get. I’ll bring over the DVO schema.

04:44

Here are my object types. In this case we’re going to select tables. And here it’s going to drop down. I can select my dependencies that I’d like to be brought over. I’d like views as well, and then procedures. And lastly Indexes. So we have that list. If I expand the object filter on the right side, I can look at my tables. I’m going to select all look at my views. Again, we’ll select all any procedures I do, so we’ll select those and then finally indexes will bring all the indexes over. So let’s go to the next page. Here I’m able to start fine tuning the Extraction. Here I’m able to group dependencies if I’d like. By default, that’s on, I can order drops by selection, I can script the drop statements. Again, by default, that’s disabled, I can obviously script the create statements, object privileges, system privileges.

05:43

As we go through, we can start looking at other, including the storage. Again, we can include that if we’d like. The file encoding, we have the options for that as well. The different encoding options. Scroll down more, you can see the script owner. If we want to extract this out without the owner. You’ll notice that when I uncheck this, you’ll notice what happens in our preview pane. I unselect it, you’ll notice that the DBO dot went away, selected again, and we bring it back. If we want to change the owner, we can drop that down and then point it to a new owner. Again, we’ll retain the owner bringing his DVO. Here are some data so we can truncate the table if we’d like. We can do some data movement. If we’d like to do movement here, we can create insert statements if that’s applicable for this example.

06:29

Here I want to point out also that we’re able to save the Options template. If you do this often, if you’re doing multiple extractions, we can create an Options template. Anytime you use this command, we can drop this down and it’ll pull back all those options for you. So we’ll press next. Here is our overview of what we’re going to be bringing over in our Extraction. Here is the script that we’re going to generate. So let’s press finish. We’ll look at all, and here we can see my execution is finished. 22 objects were received. We’ll press Continue, and here displayed is the Extraction script for this environment. Really quick, I’m able to bring this out, extract all the sequel, and display that in an ISQL window, in a sequel file that we can use at any time. So we’ll close this down. The other method of using an Extraction and what we’ll use for that is we’ll come over here quick to an Oracle connection, and let’s just pull in the we’ll go to the Sys scheme and look at the tables.

07:35

Again, we’re going to be bringing back quite a few tables in our Sys schema. Again, you can see the multiple tables. We have. DBArtisan’s counted 1221 tables to be exact. Now, what we can also do in this example is, again, we’re going to multi select some tables. Again, I’m just using the Control button to drop this down and select the multiple. I will right click on the object and I will select Extract. Again, at this point, DV artisan is going to build the Extraction script for those selected objects. So again, it’s just working through this. It’s going through pulling that in. Now, you’re also able to specify the dependencies that come along with that. So, here is our list, our SQL script that’s been created for that ad Hoc Extraction. Again, we have the Extraction Wizard through the Utilities menu, or what we refer to as the Ad Hoc Extraction.

08:33

Oracle

That’s when we find via the Explorer, we’ll find the objects that we like extracted and select those right click and then choose Extract. To control that, we’ll go to File Options. We’ll look at our options. We’ll go to the DDL extract tab. Here we have our options for extraction, we have some control. There we’ll go to the Oracle tab. So here specific. The Oracle extraction. I can look here the object types that I want to include a drop statement for. I’m also able to here in the right hand pane select the dependencies to include. So here is the control. A lot of the customers that I’ve worked with will ask me I’m not seeing certain dependencies come back with my extraction. I’ll immediately point them to this options menu and more often than not they find the issue is here. With my tables, I can drop this down.

09:27

The only dependency I’m bringing back is our indexes. I could choose whichever object I’d like and when I choose an extraction, those objects will come along with it as part of that extraction process. Again, nice quick way of editing and configuring DB artisan to work with a specific environment that you’d like. Again, during the ad hoc extraction process, we have total control there. Let’s back up. Let’s go back over. Now let’s go back up to our SQL Server instance. Here we’ll go to the utilities and Schema migration. So again, similar look and feel. We’re going to have a wizard to walk us through the steps. Here we’re selecting that SQL Server instance again, that’s as our source. Now what about our target? In this case, we’re going to open up SQL Server. Again, I’m actually choosing the same server. I’m going to put this into a different database.

10:24

So again we’ll walk through. My gim is my source, my Gimqa is my target. Again, I’m working on the same physical server. You can understand easily how this translates to push this across to multiple. Next and here we’re able to list our schemas if we like. In this case, we’ll leave that blank. We’ll look at our object types. Again, let’s just bring over the tables and we’ll bring over the procedures. In this example, again, we’ll look to see which objects are coming over. In this case, I just want those selected to DVO. Again, I could have restricted that by going to the Schemas tab. I chose to do it manually this way. And here are my procedures. Just selecting the two procedures. Again highlighting how I can pick and choose what I like. Next, we’re going to look at our options. So here I want to drop statements.

11:21

I’m going to group the dependencies, retain the owner. In this case, I’m going to have no movement. I’m fine with no data being brought over. We’ll press next. Here is my summary. Here is my operation is saved this file. At this time I could press Finish execute this job. I could save it to run at another time within DBArtisan and I could bring this Sav session back and then we can run it at a later date if we’d like. Let’s go ahead and press Finish for this example, here we can see that the migration was complete. If we look at all the settings, we can see that my migration script brought everything over. Again, looking at the dependencies brought those over as well, and we’ll close that. Now, if I were to expand into the databases, look at my Gimq a database, expand the Schemas, the DBO Schema.

12:28

Look at the tables. Here we can see the tables were brought over empty rows, which is exactly what I told DB Artisan to do. Now let’s take a look at some of the other features found in the utilities menu. So again, we have Schema publication. Following that same look and feel of that wizard, we’re able to generate a nice report based on the Schema and or database. We have data load and unload. If I select for SQL Server, again, data unload, we can work. Here is a new operation, stepping through, we have our directory, this is our database. We can look at the Gim database here with some rows here’s, the separated files we can use the BCP utility. Again, stepping through what tables we can select, all here. We can update stats if we need to. Press next here. Do we wish to reuse the operation?

13:23

Again, as you’ll notice, we have this command, it’s going to store it right here. We’re ready to go. Then, for future times, press Next. Here is our summary. At this point we can press finish and we will unload the data. Again, we have a data unload operation, complete six tables, unloaded successfully. Again, I have those data files now ready to go. Conversely, I’m able to load the data. Now. This is working with SQL Server. Again, if I selected my Oracle data source, I have some more options to choose from. Here we not only have data load and unload the database monitor, but now we have integration with some of the Oracle utilities. Here in this case, I have support for the data pump, both export and import, as well as the traditional imp and EXP commands from Oracle. In this case, I could export using the data pump.

14:20

Walking through the wizard, we’re going to take you step by step through and then provide the job at the end that will run the utility, the data pump utility and export the data. So again, we’re stepping through all this. Yeah, we can just step through here, we’ll just pick a couple and you can see how we completely generate the query clause if we need to. Here’s pulling the directory where the data pump is. You have to instruct Artisan where to look for that utility. Majority of the time it’s in that Oracle home folder that you’ve installed all your Oracle client in. And then you’re able to enable logging. And then you have your job name. Again, we can walk through the wizard with Dvrson to create the data pump job, both export and import. Again, using the utilities within DB artist. Again, if I look at some of the additional utilities for Oracle, we can see some archive activity, backup control, file instance configuration.

15:16

SQL Server

If we choose for instance configuration, we can see the instant parameters for our SQL Server for our Oracle environment here. So, getting lots of options here within the utilities. I also want to hop in, go back to my SQL Server environment, really quick. The database. As I mentioned, we’ll go into the DBO as well and then let’s go to the tables. The other thing that were able to show, I showed the extraction just as easy. I could have selected all the tables and selected migrate from here as well. Again, we have our migration as well as our extraction both through the utilities menu and then from more of an ad hoc approach, right clicking on the selected objects. Now, as I’m working with objects, specifically tables, and really these apply to all of our objects, you’ll notice that when I multiselect, I still have a list of commands to run.

16:12

Again, as a DBA, I’m able to pick multiple. For this case, I could update the statistics here. I’m going to be presented with the dialogue again, I can look at some of the options that we have a full scan. If I like statistics type, we can go to the index columns of all existing stats. We can schedule this to run at a later date if needed. We can step through this, we can look at any dependencies and we will always show you the preview of what script we’re going to run. This is pretty straightforward. We’ll press execute and all of our stats then are updated for these objects. I mentioned, when I looked at the database monitor about seeing the exact SQL that Dbartisan. As a DBA, it’s sometimes nice to be able to understand what queries are being executed behind the scenes of DBArtisan and sometimes take that sequel as a starting point for maybe a larger file or a larger script that you’re going to run.

17:12

SQL Logging

To do that Dbartisan for the SQL logging utility So here, let’s go ahead and let’s get us to the point where I wanted to be to start this. Again, I’m connected to my Oracle environment. We’ll go to my schema, let’s just pull in the system, look at the tables. At this point, if I’m curious of what information, of how I’m bringing back this information, what query am I executing, we can look at the log file and this log file, this first option is a toggle simply on or off first. We can go into recording mode, which is a more in depth recording mode. Open we can open a log file. Some of the options pertaining to the log file here we can truncate and then Truncate and Log. So this is what I suggest. Starting off with, I want to make sure that my log file is truncated.

18:04

So it’s starting off clean. I’ll select truncating log. It doesn’t exist, which is fine, because I’m going to create a new one. Right now, if I go to log, you’ll see that logging is turned on. It is indeed enabled. At this point, I’m simply going to select refresh. Davidson ran the script needed to refresh this list. So here I’m going to disable login. And this easy example. I’ll open the log file, and here you have the exact sequel that DBArtisan ran behind the scenes. I’m going to format this, and then we can see the specifics of the query statement that DBArtisan is executing to bring back those results. So, again, even at this point, I could execute to bring back the list in more of a grid format. If I needed to get that information out of DBArtisan in a manner other than potentially a screenshot of the Explorer window, you can simply log the query, execute it.

19:02

Script Library

Now you have total control. If I were to need to get this out and put it into an Excel file, I can select that as well and save it as an Excel file. So, again, some of the options within DB Artisan pertaining to the logging. Lastly, and this is a new feature within the nine seven five release of DB Artisan that I wanted to talk about is the ability to create a script library. So here it’s under view. It’s a script library tab. Here we’re opening up a script library. Now, as a DBA, you’re able to take all of your scripts and categorize them, create groups, and then have them linked. Here my example is I have my own scripts, and DB Artisan will ship with all these scripts as well. If I’m interested in, for instance, if I navigate to Oracle, you can see if you’re interested in what queries needed to bring back all the tables, right?

20:00

If you ever need to let me open up just an IQL window. If you ever need to bring something in, you can drag and drop it wherever inside of another Is Equal window. Wherever you need to put that statement, you can drop that in. The same applies for anything new. Here I have my own script library. If I right click on this entry, I can look at the properties. What you see is AI transcription shortcut description, the actual file name that this shortcut is linking to. We’re going to link to a specific file name, any hot keys, any file type. We can drop that down. Whatever we’d like to bring, we’re going to default to Is Equal and there’s no case. At this point, double click it will open it up. Here is the script that I’ve stored in my library. Now, as DBA, you have the ability right from within DB Artisan to create that script library to house all of your scripts.

20:54

So, again, no need to go back scrolling through navigating through your file system and define it as part of a folder structure in your script library, and you have one place to go and find all the scripts that you need. I hope this was helpful in showing some of the features within DBArtisan that make DBArtisan the standard for DBAs.