DB2 9.5 and the IBM Data Studio - Part 2 - Page 3
November 5, 2007
New SQL Statement
You use this option to generate a new SQL statement to run against the database you are connected to. IBM Data Studio gives you the option to use the SQL Builder (which has drag-and-drop query-building components) or the SQL Editor to build your SQL statements. Ill cover all the enhancements and capabilities of the SQL assistance thats part of this IDE in a future article. I typically dont use this option from the Database Explorer view because you cant save this SQL statement into a project; its more for ad hoc querying of the database and, therefore, not as useful as the case when you build an SQL statement as part of a database development project, where it can be persisted, subsequently turned into a Web service, and more.
In the Connecting to a database section, I briefly commented on the option to work offline. This, for me, is one of the coolest features of IBM Data Studio. To best illustrate the use of this nifty feature, Ill give you an example. While traveling on a plane, I often build SQL statements for future demonstrations. Without Internet access on a plane, I cant connect to my remote DB2 for z/OS data servers and use the SQL facilities in this IDE to build my SQL statements. Of course, since DB2 family members have 95% of the SQL API in common between them, I can move the SQL code to any project despite what platform the DB2 data server is running on (distributed or mainframe); but that isnt what I want to do. What happens if the DB2 for Windows data server for which Im building the SQL statement isnt local but is rather on a domain somewhere in my network? By creating an offline copy of the database, IBM Data Studio creates a schema cache that allows me to perform many operations (especially for building SQL statements) even when I dont have a connection to the database. Thats cool!
When working in offline mode, you can:
Of course, you cant do everything when you are working in offline mode. Some of the actions that you cant perform with an offline database connection object are:
To enable a database connection object for offline mode, perform the following steps:
1. Ensure that you have a live connection to the database object for the selected database connection object (because you cant save the contents of a database if youre not connected to it).
2. Right-click the connected database connection object, and select Save Offline:
IBM Data Studio will go through the entire schema that is included in the selected database connection object. (For example, if a schema is filtered out, it wont be saved for offline operations.)
For a database with a large schema, this could take a long time. For example, SAP with all its modules fully implemented has about 60,000 tables! If you want the IDE to work in the background to complete this task, select Run in Background. In the bottom-right corner, youll see the status of this operation if you run this task in the background:
In this step, you can see that I enabled the SAMPLE NO FILTER database connection object for offline operation.
3. Disconnect from the database by selecting Disconnect from the pop-up menu (so you can test working in an offline mode)
4. Right-click the database connection object that you enabled for offline work, and select Work Offline. IBM Data Studio loads in the cached schema to enable offline work:
Tip: Running this task in the background gives you the opportunity to work with another database connection (perhaps one that is local) while the schema is being loaded for this connection.
When the schema cache has been loaded, a toggle appears beside the database connection object, allowing you to expand its contents on offline mode:
In the previous figure, you can see I have three states of database connections. The PAULZ-only FILTER object has a live connection (since it has a green icon). The SAMPLE and TOOLSDB objects have no connection (they have gray icons, but more obvious, you cant expand them), and the SAMPLE NO FILTER object is disconnected but enabled for offline work (since it is expandable, but the icon is gray). Finally, you see that the connection status at the bottom of the Database Explorer view shows that you are working with a cached database connection object.
In the following figure, Ive expanded the SAMPLE NO FILTER database connection object. You can see that I cant sample the contents of the data since Im working in an offline mode:
However, I can work with the table since its definition is part of the schema cache, (you can see that I expanded the table to see its columns and their data types):
Wrapping it up...
In this article, I took you through the rest of the operations that you can perform on a database connection object. Weve barely scratched the surface of IBM Data Studio, but as you can see, theres a lot of capability here. In my next article, Im going to delve into all the things you can do with overview diagrams, which I lightly touched on in this article.
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 thirteen years of experience with DB2 and has written more than one hundred-fifty magazine articles and is currently working on book number twelve. Paul has authored the books Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, 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, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë his daughter. You can reach him at: firstname.lastname@example.org.
IBM, DB2, and z/OS 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.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2007. All rights reserved.
The opinions, solutions, and advice in this article are from the authors 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 authors knowledge at the time of writing.