Ben Boise's Blog

Just Bottom Line it for me, will ya?

Ben Boise — Fri, 05 Feb 2010 08:00:00 GMT

I have to giggle whenever I read the title of this entry. No, it’s not because I think I’m the King of Comedy (though, I do look pretty funny). The title came from a quote spoken by a former boss I had in a previous life. We were in a meeting with one of those co-workers who notoriously covers all the details of a topic, but rarely gets to the point. After about an hour of soaking in this increasingly irrelevant series of details, my boss finally said “just bottom line it for me .” The phrase became a favorite joke around the cafeteria table.

As I expand my understanding of DB2 LUW, I occasionally get stuck in the details. As users and creators of technology, it’s very easy to get so caught up in the details of what we’re doing that we lose sight of the proverbial “big picture.” As I was poking around in Toad for DB2, I noticed a screen I had previously ignored:

What you see, in the above screenshot, is the Database screen from the Database Explorer (requires the Database Administration configuration of Toad for DB2 LUW). I have spent a lot of time in the Database Explorer. How is it that I’ve overlooked this screen so frequently? Well, it’s because I’ve spent most of the time exploring other objects like schemas, tables, buffer pools; in other words, too much time mired in the details. What I enjoy about this screen is that I can use it to help me get back to the “big picture.” Each tab presents high-level information about the database you’ve selected. In the above screenshot, you can see the connection information about your current connection.

**The Applications Tab

The Applications tab shows me summary information about the currently connected applications in my database. When I select an application, the bottom portion of the screen will show more detailed information, such as Locks and current transactions the application is processing. In fact, you can right-mouse click on an application and Force it!

**The Space Usage tab

Now I can graphically view space allocation as well as use my right-mouse click to perform operations on the various tablespaces.

**The Monitor tab

Now I can view lock activity, buffer pool hit rates, connections, and SQL activity over time.

You can probably guess what the Utilities, Grants, and Script tabs are showing you. Don’t forget, you can right-mouse click on the database name, using the left-hand side of the Database Explorer, to perform additional operations on the database:

I feel really silly for having glossed over this jewel for so long.

Proactive Index Usage Analysis with Quest SQL Optimizer for DB2 LUW

Ben Boise — Wed, 06 Jan 2010 08:00:00 GMT

Using indexes can improve the performance of applications using the database. This is well known to many, many people. However, the application of too many indexes can have the reverse effect. There’s a balance that must be struck and you may find yourself needing to locate indexes that aren’t being used. If you’re already using the Quest SQL Optimizer for DB2 LUW, you can take advantage of the Index Usage Analyzer to help with this process.

Before we jump into this feature, I feel I should provide you with a little bit of background information. The Index Usage Analyzer identifies unused indexes by analyzing access plans and it is a wizard-driven process. While working through the wizard, you must specify which SQL statements you want to use for analysis. These SQL statements can be entered while working through the wizard or they can be selected from other features of the SQL Optimizer; specifically, the SQL Repository or the SQL Scanner. Let’s touch briefly on these other features.

The SQL Scanner extracts SQL statements embedded in database objects, captured from the DB2 Event Monitor, or stored in application source code and binary files. Once the SQL is extracted, access plans are retrieved and then classified according to a Problematic, Complex or Simple category, all without executing these statements. This is useful for locating potentially poor performing statements that may need to be optimized using the SQL Optimizer. You can access this feature using the Tools | SQL Scanner menu.

The SQL Repository stores statements that are used in the analysis of database performance. These statements could very well be ones you’ve deemed to be critically important. The repository is really a file that is located somewhere accessible to the workstation on which the SQL Optimizer is installed. You can manage the contents of the repository using the Tools | SQL Repository menu.

To start the Index Usage Analysis, select the Tools | Index Usage Analyzer menu. Now, select the Analysis | New Analysis menu. You’ll see the following screen:

** Please note you may have seen this screen when you first accessed the Index Usage Analyzer menu. Because I have previously run through this process, I chose the New Analysis menu option.

Click Next and you’ll see something similar to the following:

** I think it’s a good idea to provide a proper Name and Description for the analysis. “Analyzer1” with no description isn’t terribly, well, descriptive. Save you from yourself (maybe it’s just me that’s absent-minded).

Click Next:

** Use SQL captured in the Repository or SQL Scanner

Ok, now we’re coming to where we need to supply the SQL statements we want to use for this investigation. I’ve highlighted the dropdown list that is used to add SQL from the SQL Repository or SQL Scanner features I mentioned previously. You can also walk through another wizard-driven process and manually enter the SQL you want to investigate by clicking on the icon highlighted below:

** Use the above button to step through a wizard-driven process to manually add SQL

Make sure to check the box next to the SQL you want to use in this investigation and then click the Finish button. This starts the analysis process and when it’s complete, you’ll see a screen similar to the following:

We can use the tree to focus our analysis by looking at the tables used by the SQL we’re investigating as well as the SQL statements themselves. Selecting a node changes the information displayed in the right-hand pane.

** An example of a selected SQL node with the Index Used tab selected

** An example of a table node selected and examining the Unused Index

If I’m satisfied with the results, I can use a right-mouse click feature to actually drop an Unused Index. Naturally, I should use caution before dropping an index and take the appropriate steps to ensure this is the proper decision. If I’m examining the Unused Index tab, as shown below, I can right-mouse click on an index and select the Drop Index option:

I can examine my SQL statements, for unused indexes, by entering them or by selecting statements I’ve already captured in the SQL Scanner or SQL Repository. This is just another example of how many of the features in the Quest SQL Optimizer for DB2 LUW work together. Happy hunting!

Toad for DB2 LUW and Spotlight on DB2 LUW play well together

Ben Boise — Tue, 15 Dec 2009 08:00:00 GMT

Did you know that you can call Spotlight on DB2 LUW from Toad for DB2 LUW and vice versa? Well, ok maybe you did. If that’s the case, please move along, there’s nothing to see here. Still with me? Then read on!

Toad for DB2 LUW and Spotlight on DB2 LUW exist as separate executables on your workstation. You might now be thinking “no kidding, Ben.” That’s fine. After all, I have been called the Master of the Obvious. However, we may find ourselves working in one solution and then realize we have the need to work in the other solution. Naturally, we can just launch the solution of interest from our programs menu. However, let’s consider saving ourselves a little bit of time and launching them within some proper context. Let’s say I’m working in Toad for DB2 and have the need to do additional diagnostics using Spotlight. Rather than launching Spotlight, selecting the proper database connection and then connecting, I can simply use the Tools -> Quest Tools -> Spotlight on DB2… menu within Toad:

Spotlight on DB2 LUW then loads and creates a new Spotlight connection using the connection’s information from Toad for DB2!

Let’s consider the opposite approach. I may be diagnosing an issue in Spotlight on DB2 LUW and realize that I need to perform an operation within Toad. In this example, I’m working in the Tablespace Analysis drilldown in Spotlight. If I right-mouse click on a tablespace, a window loads that allows me to operate on the object using Toad:

If I choose the Alter… option, Toad for DB2 loads, connects to the database I was diagnosing in Spotlight, and then loads the appropriate menu for Altering this tablespace:

I encourage you to use the solutions in a manner that is most comfortable to you. With the integration that exists between Spotlight on DB2 LUW and Toad for DB2 LUW, I can save myself time and use these solutions within a relevant context. Just a little something else to think about!

Investigating and Managing Differences with SQL Navigator

Ben Boise — Mon, 23 Nov 2009 08:00:00 GMT

Quest Software has two of the most powerful tools for PL/SQL development: Toad for Oracle and SQL Navigator for Oracle. Personally, I spend more time working with Toad for Oracle and I truly enjoy the solution. Before I proceed any further, I want to clarify a point. I am not advocating one solution over the other for PL/SQL development. That’s a conversation I refuse to have, because it borders on arguing which religion is better. I’m of the opinion that you should use the solution you’re most comfortable using and that helps you get your job done well.

Recently, I went to visit some customers who are using SQL Navigator for Oracle. Well, because I spend most of my time with Toad for Oracle, I found my SQL Navigator skills to be a bit rusty. In preparation for our meetings, I spent some time refamiliarizing myself with this solution. During this preparation, I discovered a feature that I wanted to share with you. Granted, I’m running the risk of exposing a feature you’re already aware of, but that’s the chance I take .

SQL Navigator for Oracle has a utility that allows you to compare both script differences and object differences. You can launch the comparison process in a few ways (imagine that). We’ll focus on using the menus within SQL Navigator for Oracle. All you have to do is select Tools | View Difference:

Selecting this menu loads the following dialog:

As you can see, we have the option of selecting Script or Object. The Script option allows us to compare a variety of file types. If you click the ellipses, you can activate the Files of Type dropdown to see the file formats that are supported for comparison purposes. I’m going to take us through an example of using the Object comparison.

The interface is very straight-forward. Simply select the Object radio button and fill out the proper objects you wish to compare from the various dropdowns. You’ll see something similar to the below screenshot:

**Please note that I am comparing the OW_CUSTOMERS table in my QUEST_DEV schema to the OW_CUSTOMERS table in my QUEST_PROD schema. My connection provides me access to both of these schemas; hence, the Session information is identical in both dropdowns.

You guess it. Now all we do is click the OK button and we have the following results:

You can see that we have a column size difference between these two schemas. This is indicated by the blue syntax in the above screenshot (line #3).

In my opinion, this is where it gets good. If I select the line that is different (line #3), you’ll notice a change in the toolbar:

The eraser icons give us the ability to Delete left text or to Delete right text. The arrow icons allow us to Copy to right or Copy to left. What does this mean to us? Using these icons we can now react to these differences.

Let’s say that we need to make our QUEST_PROD table look like our QUEST_DEV table. Our QUEST_PROD table’s NAME column is 64 bytes and we need it to be 70 bytes. Using the Copy to right arrow (the arrow pointing to the right), we can start to make this change:

Now, line #3 is identical on both the left-hand and right-hand sides of the screen. If you click on the floppy disk icon, on the toolbar, you’ll be prompted to save the changes as a script.

Icon to click:

Dialog to save changes:

Now that we’ve saved our changes, we can very easily load the script into the editor and execute it to apply our changes. All we have to do is click on the SQL icon in the toolbar:

This loads our script into SQL Navigator for Oracle’s editor:

We can now execute this script to apply our desired changes!

Investigating differences is very easy in SQL Navigator for Oracle. Perhaps equally important is the fact that we can react to these differences in a safe and logical manner. I find that I’m sometimes my worst enemy when it comes to managing changes. SQL Navigator for Oracle helps save me from myself.

Generate Schema Documentation in Toad for DB2

Ben Boise — Tue, 27 Oct 2009 07:00:00 GMT

In one of my previous lives, I worked at a startup company testing software. Our application used Oracle as the back-end and the nature of my role had me working closely with our Oracle DBA. As part of our processes, our DBA had to build documentation describing the schemas our application needed. The reports included things like table names, column names and their data types, indexes, etc. This used to take him hours until he discovered the HTML Schema Doc Generator feature in Toad for Oracle. With a few clicks, he was able to build a report containing all relevant information needed for our documentation. Well, Toad for DB2 offers the same capability.

To generate a Schema Report, in Toad for DB2, select one or more schemas in the Database Explorer. Next, simply right-mouse click and select the Send To | Schema Report menu. You should see something similar to the below screenshot:

There are two sides of this screen that allow you to customize your report. The left-hand side shows you the list of objects that will be included in the report. If you wish to remove an object from the report, you can select it and hit the Delete key to remove it. The right-hand side contains the settings for the report. You can use these settings to refine the information you wish to include in the report. Once you’ve refined the object list as well as the report settings, you can select Schema Report | Generate from the menu, or click the button on the toolbar highlighted in red below:

A new tab will display the report:

The toolbar in the Report Preview tab provides additional functionality, such as adding a watermark, changing the report background and saving the report and emailing it as other types of files like PDF and Excel. If I make changes and want to refresh the report, I’ll need to click the icon shown in red below:

Anyone having to produce this kind of documentation may find this feature of Toad for DB2 to be a huge time saver. Feel free to tell the boss it’ll still take 4 hours. What you do with your free time is your business ;-)

A Recent Customer Walk-through of Toad for Data Analysts

Ben Boise — Thu, 01 Oct 2009 07:00:00 GMT

Sometimes, I’m asked to help a customer work through a particular challenge they’re facing when using one of Quest’s solutions. Many times, the meetings are one-on-one sessions with the customer where we take turns sharing our desktops to better understand the problems being faced and how the solution might be able to address the problems. I actually enjoy the one-on-one sessions, because it gives me deeper insight into how our customers are using our solutions. Plus, it’s a great way to meet and connect with people, which is the part of my job I enjoy the most. Yes, at times, I’ve been called ‘weird’.

The most recent meeting was helping a customer using our Toad for Data Analysts solution working with an Oracle database. In this particular situation, the customer wanted to execute a query against multiple schemas and save the results to an Excel spreadsheet. The tables involved in the query exist in each of the roughly 250 schemas the customer needed to query. There are many ways we could have tackled this problem using Toad for Data Analysts. I was focused on how we could accomplish the goal in as few steps as possible. I want to share how we approached the problem.

Filter the list of objects for querying

The first step was to give the customer access to the various tables, across all schemas, he needed to query. The Object Explorer makes that easy for us. I suggested using the Dropdown view of the objects and entering the * wildcard in the schema list:

The red box highlights the wildcard I mention. As you can see, this gives me access to all tables across every schema I have access to use (based on my database credentials). However, we needed to take it a step further and limit the list of tables to the subset the customer wanted to query. So, we filtered the list of tables as well:

The red box shows what the filter might have looked like for the customer. In my example above, I’ve filtered the list of tables using the following characters: *OW_*, which shows only tables that start with OW_ regardless of the schema in which they exist.

Generate the SQL Syntax

Fortunately, Toad for Data Analysts makes it very easy for us to generate SQL syntax. In our case, we needed to generate a select statement. To do this, we just used our Ctrl key to highlight the tables we wanted to query, clicked our right-mouse button, and selected the following menus: Generate SQL -> To Editor -> Select Statement:

This loads the editor window and populates the Select syntax for every object we selected in the Object Explorer:

Now that we had our needed syntax, we saved the file with a .sql extension.

Generate the Report

The next step was to save the results to an Excel format. We decided to use the Export Wizard to help us with this process. This wizard is accessible from the Tools -> Export -> Export Wizard menu:

I won’t walk you through every step of the wizard as much of it is very obvious. When we walked through the wizard, we specified that we were using a Query File and selected the file we created from the query process mentioned previously:

We then specified the output format to be Excel and told the wizard where we wanted the generated file to be stored. The final screen of the wizard is worth mentioning, because it gives you a nice option:

At the end of the wizard, we can choose to Export the data and/or Save template. Templates simply store the selections you made through the wizard. The idea being, rather than repeating this process over and over, I can simply choose to execute the template from the Tools -> Export -> Execute Export Template menu. In fact, I can use the Toad for Data Analysts Automation feature to simply execute this template at a time of my choosing. For more on Templates, take a look at templates on Toad World.

Using these steps, we had achieved what the customer wanted to accomplish using Toad for Data Analysts. As I mentioned before, there are several other ways we could have approached this, but we seemed to discover a solution that worked well for the customer. Ultimately, creating happy customers is my job!

Sync points and Restarts in Toad for DB2

Ben Boise — Thu, 10 Sep 2009 07:00:00 GMT

Managing and working with large scripts can be challenging. Failures during the execution of such scripts can make a person downright cranky. Personally, I’ve never liked scrolling through hundreds of lines of code to isolate the problem in a script, hopefully correct the issue, and then attempt to restart the execution at the proper line. For those of you using Toad for DB2 4.5, you may not be aware of a new feature that addresses precisely this problem.

For scripts that are generated within Toad for DB2 4.5, you have the option to let Toad for DB2 generate what are known as Sync points. Sync points are special text that is recognized by Toad for DB2’s editor. They do two things: perform a commit and allow you to restart execution at a particular point within your script. In order for these sync points to appear, you must first enable them within Toad for DB2’s options. Simply navigate to Tools | Options | Database | DB2 | General and check the “Generate scripts with syncpoint statements” checkbox.

Once this option has been selected, you’ll see the special text “#SYNC”, followed by a number, appear in any script generated by Toad for DB2. Let’s take a look at an example:

In the above screenshot, I’ve highlighted several sync points generated by Toad for DB2. In this example, we have sync points numbered 10, 20, and 30. Remember, each sync point executes a commit, but it also allows us to restart our script execution at a particular point. In the above screenshot, these points are enumerated 10, 20, and 30.

Toad for DB2’s editor allows us to restart our script execution at a particular sync point. To do this, I simply select the restart button from the editor toolbar:

I can also use the Editor | Restart Script menu selections. In both cases, we’re presented with the following dialog:

I can use the dropdown and select the appropriate sync point:

Or I can simply type the appropriate sync point into the dropdown list. Once I’ve made my selection, the execution continues from the proper point in my script! Happy scripting!

Auto Completion and Workspaces in Toad Data Modeler

Ben Boise — Mon, 10 Aug 2009 07:00:00 GMT

By default, adding an entity to a Workspace will cause this entity to be added to every workspace within your model. Well, the entity isn’t really being duplicated; it’s just that a shortcut to that entity is being added to every workspace in the model. Recently, we’ve received some questions from customers asking how they can change this behavior. Fortunately, it’s really easy to do. Before I tell you how to change this behavior, let’s walk through this behavior.

Workspaces allow me to separate components of my model in order to organize them in a way that makes sense to me. For instance, I may need to create a workspace that contains the objects specific to customer information such as name, address, etc. I may create another workspace that contains objects specific to buying history. Really, this is very similar to creating sub-models in an effort to add sanity to our modeling efforts. In Toad Data Modeler, adding an object to one workspace in your model, automatically adds that object to your other workspaces within the model. Let’s start with an empty model that has three workspaces:

In the above model, we have three workspaces: All Items, Workspace1, and Workspace2. All workspaces are empty. If I add an object, in this case, an Entity, to any workspace, the object automatically populates in the other workspaces. Let’s add an Entity to Workspace1:

If I select the All Items workspace, I see the following:

And Workspace2:

You can plainly see this Entity appears in every workspace even though we only added it to Workspace1. If we find this behavior undesirable, we can turn it off. In fact, we can turn it off for specific workspaces. All we have to do is right-mouse click in an empty area of the workspace and choose Workspace Format:

And we see the following menu:

All we have to do is uncheck the Auto Complete check box to turn this behavior off:

Let’s do this for Workspace2 and then add another Entity to Workspace1:

Above, we see the two entities in Workspace1.

If we select the All Items workspace, we’ll see the Entity was added as expected:

But if we look at Workspace2, we’ll see this Entity hasn’t been added:

This is the behavior that we wanted. All we have to do is change this option for every workspace in our model where we don’t want objects automatically populating. As luck would have it, we can make this the default behavior for all Physical Models. Go to the Settings -> Options menu, select Physical Model from the left-hand tree and the Workspace tab on the right hand side:

You can see the Auto Complete option listed here:

Setting the option here will prevent objects from populating in other workspaces when we’re working with the model. As you can see, you have the option of setting it at the Workspace level or making it the default behavior for the entire model. The choice is yours. Hope this helps!

Two approaches to SQL Tuning using the Quest SQL Optimizer for Oracle

Ben Boise — Fri, 31 Jul 2009 07:00:00 GMT

I sometimes worry that people are confused about how to use the Quest SQL Optimizer for Oracle to help them tune their SQL statements. The solution is extremely powerful and provides you with the options to control numerous aspects of the optimization process. In fact, I suggest you read Richard To’s articles on Toad World to get an in depth understanding of the optimization process and the various settings within the SQL Optimizer for Oracle that can be used throughout. His blog can be found here on Toad World: Richard To's Blog.

I will not pretend to be the expert that Richard To is when it comes to SQL optimization. I’m going to make a big leap and attempt to simplify the process for folks that might be having some trouble understanding this topic and what the SQL Optimizer for Oracle can do to help. In particular, I’m going to focus on just optimizing SQL statements by rewriting them. I frequently work with customers that have been unsuccessful in finding better alternatives using the SQL Optimizer for Oracle. In most cases, it’s because they do not fully understanding the approaches that are available within the solution nor how to use them. I’m hoping this topic may be of some value to the folks that are finding themselves in this situation.

SQL statements follow a particular path whenever they execute. I can ask Oracle to show me that path by requesting an Explain Plan. Each step in the plan has a cost associated with it. One of the challenges with cost, however, is that higher costs don’t always correlate directly to poorly performing SQL. When we optimize SQL statements we need to be able to influence the path that is taken when the statement executes so that we can achieve our desired performance. To do that, I can rewrite my SQL code until I achieve two things: An execution plan that is different from the original statement and an execution plan that is desirable (not necessarily less costly). This is not a trivial process.

The crux of tuning SQL statements, in the SQL Optimizer for Oracle, is to automate much of the process of:

Creating alternative statements that are semantically equivalent to the original statement
Eliminating those alternatives that have identical execution plans to the original statement
Getting statistics for the execution of the alternative statements

The first step helps us ensure that we’re retrieving identical records while rewriting our original statement. The second step helps us make sure that the statements follow a different execution path that might be considered more desirable than the original statement’s path. The third step provides us with more information about the statements’ executions, in real statistical terms, to help us choose the ideal alternative. Remember, the best alternative plan could very well be more costly than the original plan. That’s why the statistics generated by step 3 are so important.

What’s great about the SQL Optimizer for Oracle is that I can be very hands-on in the 3 step process or I can be hands-off. If I’m a hands-on person, I might prefer to use the Tuning Lab. If I’m hands-off, I might choose the Batch Optimizer. In other words, if you’re very curious about the alternatives that are generated and want to get involved in the process, use the Tuning Lab. If you would rather just see what the best alternative statement is, if it exists, use the Batch Optimizer.

Tuning Lab

Notice that I have the opportunity to see the different alternatives that are suggested and their corresponding plan costs. I can also use the left-hand side buttons to compare the alternatives and see differences in their syntax and execution plans:

I can then pick and choose which alternatives to execute in order to see important execution statistics like Total Elapsed Time, Total CPU, Logical Reads, etc:

Now I can decide which alternative I consider to be the best alternative.

Batch Optimizer

The above screen allows you to just say “just show me if a better alternative exists”. If it does, let me see some more detail:

We can still observe differences in syntax and execution plans. This time, we’re focusing solely on the original and the best performing alternative.

Regardless of which approach you want to take, the process remains the same:

Creating alternative statements that are semantically equivalent to the original statement
Eliminating those alternatives that have identical execution plans to the original statement
Getting statistics for the execution of the alternative statements

You just get to choose how involved you want to be during each step of this process. I know this is an oversimplification. That’s the idea. Richard’s blog provides much more detail about the numerous other features and options you can use during the optimization process. It’s definitely worth reading. I hope this helps anyone who may find themselves a bit lost when it comes to optimizing their SQL statements and how the SQL Optimizer for Oracle can help.

Enjoy!

Extended ALTERs and Handling Data in Toad for DB2 LUW

Ben Boise — Mon, 20 Jul 2009 08:00:00 GMT

Last week, I was demonstrating some of our DBA-focused features of Toad for DB2 LUW (Linux, Unix, Windows). When we came to the topic of Extended Alters, the audience voiced some concerns about the efficiency of this feature. Specifically, they were concerned with how Toad for DB2’s generated script was handling the data stored in the table we were altering. The customer is dealing with very large data sets, so simple INSERT statements were not going to help. They wanted to know if Toad for DB2 could take advantage of some DB2 utilities that make this process more efficient. Let’s explore the scenario a little further.

Toad for Data Analysts for the Toad for Oracle User – Part IV

Ben Boise — Tue, 30 Jun 2009 07:00:00 GMT

As I promised earlier, the focus of this blog entry will be using the Editor in Toad for Data Analysts. The Editor is where you can enter your own SQL code to query the database. Since you’re already familiar with Toad for Oracle’s Editor, you’ll notice some similarities in Toad for Data Analysts. Hopefully, these similarities will allow you to begin using the Editor as quickly as possible.

How do I activate the Editor?

We’ll use the menus again for activating the Editor. Simply select Tools | Editor

You should be aware of a couple of toolbar menus that I have active. One is the Connections toolbar and the other is the Editor toolbar. The Connections toolbar allows me to specify the database connection I’d like to perform the SQL execution against. So, if I’ve connected to multiple data sources, I can choose which one I want to query. The Editor toolbar gives me access to icons to format code, execute SQL, etc. It also allows me to specify the current schema. This is the same current schema selection concept you’ve experienced in Toad for Oracle. To activate these toolbars, right-mouse click to the right of the Help menu to load the toolbar selection interface.

Make sure that Connections and Editor are selected. They’ll then be visible as toolbars in Toad for Data Analysts.

I can have multiple Editors active in Toad for Data Analysts. These appear as tabs and I simply continue to load them using the Tools | Editor menus.

Do I have those shortcut keys and “.”lookups like I did in Toad for Oracle?

Yes, you certainly do! For a full list of what’s available, load the Help system and type “shortcut” into the Index tab.

One thing that is worth mentioning is the object lookup feature. In Toad for Oracle, if I need the details of an object I am working with, in the Editor, I can highlight the object and press the F4 key. This loads the object’s details in a pop-up window. In Toad for Data Analysts, the process is the same, but the details load in a window on the bottom of the Editor screen as opposed to loading in a pop-up window.

Do I have Code Snippets?

Yes, you do! You can load them by selecting the Editor | Code Snippets menu. These menu selections make the Snippets menu active within Toad for Data Analysts. This menu is a fly-out that you can select and pin once the menu is active.

Because Toad for Data Analysts allows you to work with multiple data sources, we have snippets available for various data sources. You can add your own by right-mouse clicking, in the snippets window, and creating your own folders and snippets. In fact, I can load available snippets for the current connection, in the Editor window, by using the “Ctrl+Space Bar” combination on my keyboard. This should be familiar behavior given your Toad for Oracle experience.

Can I execute scripts and statements?

Yes! In fact, the keyboard shortcuts are the same in Toad for Data Analysts and Toad for Oracle! F5 executes scripts and F9 executes statements. You can also use the toolbar icons if you prefer.

When you execute your code, remember to check the bottom portion of the Editor menu for any results sets. This is similar behavior to the Query Builder.

Can I format my code?

Yes! Formatting code can be done using the double yellow arrow icon, in the Editor toolbar above, or using the Editor | Format Code menu. The formatting options are located in the Tools -> Options menus as shown below:

Hopefully, you’ve become very comfortable understanding and using some of the major features in Toad for Data Analysts. In many cases, Toad for Data Analysts is similar to Toad for Oracle, so you’ve probably had no trouble taking advantage of this great solution from Quest Software. I strongly encourage you to continue to explore the menus within Toad for Data Analysts and take advantage of the numerous features we haven’t discussed. We’ve truly just scratched the surface! Oh, and remember your best friends “F1” and “right-mouse click”. They’ll help you along the way!

Toad for Data Analysts for the Toad for Oracle User – Part III

Ben Boise — Mon, 22 Jun 2009 07:00:00 GMT

In Part III of this series of blogs, I’ll discuss the Query Builder in Toad for Data Analysts. In my previous entry, I said that I would talk about both the Editor and the Query Builder in Part III. Both features are so rich that I feel that separating the topics would be the best approach. So, we’ll focus on the Query Builder for this topic.

As you might have guessed, the Query Builder generates SQL syntax. It’s very similar to Toad for Oracle’s Query Builder, but there are some differences you’ll notice when using the TDA (Toad for Data Analysts) flavor. Let’s get started.

How do I activate the Query Builder?

The most direct way is to use the Tools menu and select Query Builder from the list. There are other methods available, but we’ll stick to using the menus. The Query Builder is a drag and drop type of interface. In order to drag and drop objects onto the Query Builder, you must use the Object Explorer window. Remember, this window is located in the bottom portion of the Navigation Manager window. Activate the Navigation Manager by selecting the View -> Navigation Manager menus.

You can drag single objects or multiple objects from the Object Explorer and drop them onto the Query Builder. Use the “Ctrl” key, on your keyboard, to select multiple objects from the Object Explorer. Please note, where referential integrity is defined in the database, the joins between tables will be generated automatically. You can still create your own joins by simply dragging a column from one table to the column of another table. What’s really neat about this approach is the Query Builder allows me to grab tables from different data sources to use in a query. Remember to select the appropriate data source, from the Navigation Manager window, to refresh the Object Explorer’s list of objects. After that, the process is the same. Simply grab the objects from the appropriate data sources and drop them onto the Query Builder. You will need to create your own joins when working with multiple data sources. Depending on the data sources you’re working with, you may see something similar to the below screen shot.

If I need to alter the joins that are displayed in the Query Builder, all I have to do is double-click on the join lines. The join lines are highlighted by the red box in the below screenshot.

This loads a window that allows you to make appropriate adjustments to the join syntax.

Once you have the objects you wish to use in your query and have adjusted any join statements, simply check the box to the left columns you wish to include in your query. You’ll notice the bottom portion of the Query Builder will start to change.

I use the bottom half of the Query Builder to refine my syntax. It works like a grid. The columns are the columns I’ve selected from the tables and the rows represent different approaches to refining the query. For example, I can create a WHERE condition, on a column, by simply choosing that row for the appropriate column. In this example, we’ll set a WHERE condition on the Balance column. All I have to do is click the Where Condition row in the Balance column.

If I click the ellipsis, I see the following:

For the WHERE condition, I can choose a very straight-forward approach and use the Form tab to build the condition. The Formula tab, while still a graphical approach, gives me access to much more functionality.

This is like switching to Expert mode in Toad for Oracle’s Query Builder! I’ll create my condition and our grid will update.

Hopefully, you’ve got the gist of refining your queries using this grid approach. Pay particular attention to working with DATE columns. Toad for Data Analysts gives me the ability to create Date Range hints to save myself the trouble of having to constantly update a query with a proper date range (the last 30 days, for example). Check it out.

To see the SQL syntax that’s being generated, you can select the Query tab on the bottom half of the Query Builder.

The Query Builder window now displays the syntax.

If I prefer to work in the Editor, I can right-mouse click (your best friend!) and send the syntax to the Editor by selecting the “Open in Editor” option. I’m not tied to a graphical approach if I like to get my hands dirty .

To execute your query, go to the Query Builder menu and select Execute SQL Statement. You may have noticed a shortcut key listed in this menu. Yes, it’s F9 and it’s the same shortcut key you’re used to in Toad for Oracle.

Our Query Builder window now displays our results.

Remember, right-click is your friend. If you right-click on your results data grid, you can see additional functionality, including the ability to Export your results to a variety of formats.

The next topic will cover functionality in the Toad for Data Analysts Editor. I mean it, this time. Have fun!

Toad for Data Analysts for the Toad for Oracle User – Part II

Ben Boise — Mon, 08 Jun 2009 07:00:00 GMT

Previously, we discussed how to establish connections to the database. If you’re interested in a refresher, the entry can be found here: http://www.toadworld.com/Community/Blogs/tabid/67/EntryID/382/Default.aspx Now that we’ve connected to the database, we’ll examine how to work with the database objects.

Where are my objects?

The list of objects, for the active database connection, can be found in the Object Explorer tab of the Navigation Manager window. To activate the Navigation Manager window, simply select View | Navigation Manager from the Toad for Data Analysts menus. This will load as a fly-out window that you can then mouse over and pin (this process should be familiar by now). The bottom half of this window contains the Object Explorer tab:

*Please note that in this screenshot, I also have the Project Manager window open so I have two tabs showing: one for the Object Explorer and one for the Project Manager. You activate the Project Manager window from the View menu.

The Object Explorer contains a list of objects for the active database connection. You’ll notice that I have selected the ORCL (QUEST_DEV) connection in the Navigation Manager, so I’m seeing the objects for that connection. If I switch to my Toad Sample Database Access connection, my Object Explorer updates with the list of objects for that connection.

Think of the Object Explorer as the Left Hand Side (LHS) of the Schema Browser in Toad for Oracle. You still have the right-mouse click functionality available on the objects, just as you do in Toad for Oracle. Simply right-mouse click on the object of interest to view these options.

You can also view the objects grouped in different ways:

These are similar groupings to what you have in Toad for Oracle.

Now that you’ve found the LHS of the Schema Browser’s equivalent in Toad for Data Analysts, what about the Right-Hand Side (RHS)? This is known as the Database Explorer in Toad for Data Analysts. You can activate the Database Explorer by selecting Tools | Database Explorer from the menu.

The combined behavior of the Object Explorer and Database Explorer is very similar to the Schema Browser in Toad for Oracle. When an object is selected from the Object Explorer, the Database Explorer will refresh to display the details of the selected object:

Each of the tabs in the Database Explorer presents different information about the object, just like the tabs on the RHS of Toad for Oracle’s Schema Browser.

Hopefully, you’re now comfortable with locating objects, performing operations on them and discovering their details in Toad for Data Analysts. Next time, I’ll be discussing the Query Builder and the Editor in Toad for Data Analysts.

Have fun!

Toad for Data Analysts for the Toad for Oracle User – Part I

Ben Boise — Thu, 28 May 2009 07:00:00 GMT

In this series of blog topics, I will attempt to translate certain features in Toad for Data Analysts for those of you who are more familiar with Toad for Oracle. As you may (or may not) know, Quest Software allows licensed owners of Toad for Oracle 9.7 to use Toad for Data Analysts free of charge! Toad for Data Analysts is Quest Software’s cross-database platform Query and Reporting tool. For Developers and DBAs who work predominately in Oracle, but have the need to query and/or report against other database platforms (SQL Server, DB2, and Sybase, just to name a few), then the pairing of Toad for Oracle and Toad for Data Analysts is for you.

I assume the audience is already familiar with Toad for Oracle. I do not intend to do an in depth feature by feature comparison of Toad for Oracle and Toad for Data Analysts. I’m merely attempting to get you comfortable enough with using Toad for Data Analysts so that you can quickly begin to explore and use this wonderful solution.

Two things to remember

Your two best friends in Toad for Oracle, right-mouse click and F1, are still your best friends in Toad for Data Analysts. F1 loads the help topic for the window that you have active and as you already know, the help system is wonderfully, well, helpful. Right-mouse click exposes additional functionality all throughout Toad for Data Analysts. So, remember you can still right-mouse click on an object to perform operations on the object and you can right-mouse click in a data grid to export to other formats. These are just a couple of the right-mouse click features so please remember to use this feature within Toad for Data Analysts.

How do I connect to the database?

Before you can do anything, you have to establish a connection to the database. Naturally, the first step is showing you how to establish these connections. You can create connections two ways in Toad for Data Analysts. You can select the File | New | Connection menu to begin the process or you can use the Connection Manager. To activate the Connection Manager, select the View | Connection Manager menu. This will activate the Connection Manager which exists as a fly-out menu on the left-hand side of the screen:

The red box on the View menu highlights where to activate the Connection Manager. The red box on the lower left side of the screenshot shows where this fly-out menu appears.

Once the fly-out is active, I can click the push-pin icon to keep the menu visible (just like you would the Object Palette or Code Snippets menus in Toad for Oracle).

I then click the plug icon with one star as highlighted by the red box below:

Please note that your Connection Manager may look different than the one shown above. Clearly, I’ve already created multiple connections.

This brings up the New Connection screen. If I click the Pick a group… drop-down box, I see the following:

Toad for Data Analysts allows me to work with multiple database platforms and I can group my connections according to the database platform I need to work with. This makes it easier to organize my connections. Select the database platform you need to connect to and provide the appropriate details for that connection. The details you need to supply will vary depending upon the platform you’re working with. You can choose to connect to the database using the details that you’ve provided or simply save the connection information for use at a later date.

If I choose to make a connection to the database, the icon for that connection will change. The plug icon will show a green circle with a white arrow inside to indicate that a particular connection is active:

The red boxes show that I can establish a connection to any database platform within Toad for Data Analysts. In the above screen shot, I’ve connected to an Access data source and an Oracle data source. No more switching between various applications to work with those different database systems.

Now that you’ve connected to the database, it’s time to get to work. Stay tuned for the next update where we’ll discuss working with objects.