Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 5, 2007

DB2 9.5 and the IBM Data Studio - Part 2 - Page 3

By Paul Zikopoulos

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.


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 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.

DB2 Archives