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. I’ll cover all the enhancements
and capabilities of the SQL assistance that’s part of this IDE in a future
article. I typically don’t use this option from the Database Explorer view
because you can’t save this SQL statement into a project; it’s 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.
Save Offline
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, I’ll give
you an example. While traveling on a plane, I often build SQL statements for future
demonstrations. Without Internet access on a plane, I can’t 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
isn’t what I want to do. What happens if the DB2 for Windows data server for
which I’m building the SQL statement isn’t 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 don’t have a connection to
the database. That’s cool!
When working in offline mode, you can:
-
View the objects in a database (for example, schemas, tables,
views, user-defined functions, stored procedures, and so on)
-
View database object properties
-
Create and view an overview diagram as discussed in the previous
section
-
Create objects in a data development project
Of course, you can’t do everything when you are working in
offline mode. Some of the actions that you can’t perform with an offline
database connection object are:
-
Filtering or refreshing the Database Explorer view (which
requires access to the data server); however, you can apply sub-filters to
filter out tables within a schema that was part of the database connection
object filter.
-
Sampling the contents of a database (since you have to pull the
contents of the data from a table)
-
Dropping data objects from the Database Explorer
-
Extracting, loading, or editing data
-
Running routines
-
Deploying routines to the same server (the routines need to be built,
of course)
-
Running SQL statements from the SQL builder or the SQL editor
(since you expect a result set); however, IBM Data Studio will still parse and
assist you in the creation of the SQL statement
-
Viewing Visual Explain information from an SQL statement
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 can’t save the contents of a database
if you’re 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 won’t 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, you’ll 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
can’t 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, I’ve
expanded the SAMPLE NO FILTER database connection object. You can see that
I can’t sample the contents of the data since I’m 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. We’ve barely
scratched the surface of IBM Data Studio, but as you can see, there’s a lot of
capability here. In my next article, I’m going to delve into all the things you
can do with overview diagrams, which I lightly touched on in this article.
»
See All Articles by Columnist Paul C. Zikopoulos
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: paulz_ibm@msn.com.
Trademarks
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.
Disclaimer
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.
» 
Comment and Contribute
