Toad for DB2 has a very powerful SQL Editor feature with for both DB2 z/OS and LUW. Now there are many ways to open up a SQL editor. You could go to the Tools menu and click on this Editor, or you could actually click on this big large icon on the toolbar. And that will launch the SQL Editor, an empty SQL Editor. And you could begin entering your SQL statements.
So for example, we have code completion on by default. So it's going to assist you in writing your SQL. So as I start typing it's going to give me a list of tables that I could use in my select clause. So I'm going to select the players table and hit Enter. And here you can see that it has populated the entire column selection from that table. And it's very easy to generate that SQL and run it.
And if we start over again, I'm going to try it again. I'm going to select Statement and I'm going to select "Football Players." And then from here, if I don't want to select all of the columns, which I did last time by default, I could select the columns that I wish to choose. So it brings up a list of columns for the table. And then I could hit Enter and it only inserts those columns that I've selected.
And it's very easy to create a where clause as well. If say where, Toad is going to give me a list of columns again. So I'll be able to say things like, position, and I'll say, equal. And what Toad will do is it's actually going to bring back by default the top 20 unique values. So I'll choose QB for quarterback. And here now I have a query when I run it that's going to bring back my list of quarterbacks in my football players table.
And you could continue to add things such as, you know if I wanted to add more I could say, and boom. It gives me-- perhaps all I want are those quarterbacks with the weight of less than 200 pounds. Now if I run this it's going to bring back a little bit smaller subset list. So very easy to create large, complex SQL statements.
We have many options within the SQL Editor including the ability to format. If you have real large, ugly statements, you could use this Format button and it will format those to your liking. There are several canned formats that we could use. You could actually roll your own underneath Options as well. But there are several different ways that Toad could be used to format SQL, to make it more user friendly and readable for you as well.
One thing that we have a lot of places all over Toad is the ability to get more information for objects. So while I'm in the SQL Editor I could actually click on a table name and hit F4. And you'll notice that the Object Details panel gets displayed with the additional information about that table. So you could see the columns. You could see the dependencies. You could see the script. So all this information is available just by hitting F4 when your cursors on an object.
There are many additional options. You could send this to an automation script. You could send the SQL to a multi query database query facility. A lot of other different things you could do inside the editor. I'm going to close down the Object Details.
Another thing to know within the editor is we've got a concept called SQL Recall. And what Toed does, as you execute statements within the SQL Editor it actually keeps track of those. The last 100 by default, it will keep track of. So if you wanted to bring back and execute a statement that was previously executed, all you need to do is bring up the SQL Recall window, double click on the SQL, and that SQL will show up for you. So it's a very handy feature for using with Toad for DB2.
If you wanted to keep the SQL around, we have a concept called Named SQL. So you could actually keep SQL around forever so it doesn't scroll off the top 100, or the last 100 list, by just move it over to the Named SQL.
Another thing that we have is we've got the ability to use snippets. I'm going to get rid of this SQL Recall control and I'm going to bring up the snippets control. So let's go to View, Code Snippets. And what code snippets are, they are shortcuts or a quick and easy way for you to lay the correct syntax for things like functions, scalar functions, aggregate functions, cursor statements, miscellaneous scalar functions. So for example, if you knew that you wanted to include an average statement in your SQL, you could just double click, and the syntax for including the aggregate function average will appear in your SQL script.
There are other things such as casting. For example, if you wanted to cast something to a decimal, you just double click on that, and it will give you the correct syntax. Along with generating the correct syntax inside your script, we also have links to the IBM documentation for specific help on that function that you choose. So I clicked on this link right here, and it brought up the IBM documentation about that specific scalar function. So a lot of details there can be just easily gotten and obtained just by a real click of the mouse.
And these snippets can be customized. You could create your own. Each one has a shortcut. So for example instead of bringing up snippets and having to double click or select these things, you just type in the shortcut. So the shortcut here is AVG. So if I just type in AVG, Control-Spacebar,
12:56