by Paul C. Zikopoulos
The SQL Modeler
The SQL Modeler in Toad for DB2 gives users a fast way to create the framework for SELECT, INSERT, UPDATE, and DELETE statements against tables, views, and aliases (sometimes referred to as synonyms). You can use this tool to also create data manipulation language statements (it will launch the SQL Editor) and joins. An example of the SQL Modeler is shown below. Note that you can drag-and-drop objects from the Object Palette to the SQL Modeler workspace:
The SQL Modeler can be used for automatic SELECT and JOIN syntax generation. You can see in the following figure that, by simply dragging a connection from the EMPLOYEE table to the DEPARTMENT table, you are generating the join syntax for this SQL statement at the same time:
When dragging and dropping objects from the Object Palette to the SQL Modeler, you can select multiple items by holding down the Ctrl key while clicking on the objects you want to work with in the palette. You can exclude system objects (using a filter) for easier navigation of the schema.
The SQL Modeler also lets you view the explain data of the DB2 optimizer's execution plan in the same way as the SQL Editor, and with a single click you can launch the SQL Editor with the statement you modeled for edit.
The Database Browser
The Database Browser acts as your typical object explorer in that it provides catalog information for specific objects in your DB2 database. The left pane of this feature contains a list of the objects for the selected tab, while the right pane displays catalog information for the object. An example of the Database Browser is shown below:
Note that when you select an object in your database, details of it are automatically shown in the right pane. In the preceding figure, you can see the column definitions for the table (since the default selected tab is the Columns tab) when you select the STAFF table. You can view lots of properties of the selected object. Specifically, you can tab to information on the following topics from the Database Browser:
- Used by
The figure to the right of this list shows all the actions you can perform from the Database Browser.
The following figure shows some of the tabs that are available. DBAs simply love the creation scripts in the Script tab that are automatically generated and maintained for you:
Of course, you can display the properties for, and work with, any of the following objects from the Database Explorer:
The Export and Import Utilities
Toad for DB2 comes with an export utility that is fronted by an intuitive wizard, which provides a single common interface to select file formats (including database-neutral formats such as .CSV files). The Toad for DB2 Export wizard also supports exporting a chosen format to a clipboard, which lets you leverage the OLE copy and paste features of your operating system. The Export wizard is shown below:
Of course, there is also a corresponding Import utility that you can use to populate your DB2 tables:
Jumping to Part 2...
In this first part, I have provided you with an introduction to some of the more notable features in the up-and-coming Toad for DB2 product. In Part 2 I will introduce you to even more capabilities. You can also check out a flash demo of Toad for DB2 at http://www.toadsoft.com/toaddb2/.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than ten years of experience with DB2 UDB and has written over sixty magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë his new daughter. You can reach him at: firstname.lastname@example.org.
IBM, DB2, and DB2 Universal Database are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2005. All rights reserved.
The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.