DB2 9 and Microsoft Excel 2007 - Part 1: Getting the Data... - Page 2
July 3, 2007
6. The Data Connection wizard opens. Select the table whose data you want to import into Excel 2007, and click Next. For example, you can select the contents of the STAFF table located in the SAMPLE database:
Note: To create the DB2 9 SAMPLE database, enter db2sampl from your operating systems command prompt.
7. Optionally, save that data connection (I'll refer to this as a database connection since thats what it is), a great idea if you plan to reuse it, and click Finish:
If you dont want to save this database connection in the future, you can simply click Finish in the previous step. In the previous figure, you can see that Ive saved the current database connection (which is solely a connection to the STAFF table) for future reference. You can optionally add a description of this connection object (in the Description field) and search metadata (in the Search Keywords field) for easier subsequent retrieval. Finally, note that you have fine grained control over the authentication settings for this connection by clicking Authentication Settings.
8. The Import Data window opens. Use this window to specify where the data should be placed in your Excel 2007 worksheet. Select the defaults for now, and click OK:
You can see in the previous figure that you can choose to place the data that is retrieved from DB2 9 in a Pivot Table report or chart, or both. I will discuss these options in my next article. (If you want to further configure this connection strings properties, click Properties.)
Your Excel 2007 worksheet should now look similar to this:
Of course, at this point you can use the data as you see fit. For example, you can leverage Excel 2007s strong formatting capabilities and apply a Table Style from the Design ribbon, for example:
You can also use the Table Style Options frame in the Design ribbon, like this:
When the data is loaded in Excel 2007, you can use the External Table Data frame in the Data ribbon to manage the database connection. For example, selecting Unlink will detach the data from the table and make it independent:
And you can quickly filter the returned data without writing a single line of SQL:
One of the things I love about Excel is its strong charting capability. Using this method, its very easy to whip up some snazzy charts with your DB2 9 data:
In the previous chart, you can see the ratio of salary to commission expenses for the 5 employees in department 20. Of course, so long as you didnt disconnect the record set, you can refresh the data and the chart will be updated immediately. For example, run the following data manipulation language statement to drastically change the amount of commission Pernal makes (hes employee 2 in the chart):
UPDATE TABLE STAFF SET COMM=55565.56 WHERE ID=20
Now refresh the data:
And, of course, the base data is updated as well:
Now think back to the way most clients leverage Excel: by staging the data in a de facto data server. If your real-time data changed, when would be the next time your analysts would be analyzing real-time data? Thats right, after the next batch job!
Wrapping it Up...
In this article, I showed you how easy it is to import your DB2 9 data into an Excel 2007 worksheet. After getting the data into Excel 2007, I showed you some cool features that you can use to change the way the data is displayed (using different Table Tools ribbon frames) as well as how to create a chart and refresh the data in real time.
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 and has written more than one hundred articles and several books about it. Paul has co-authored the books: DB2 9 New Features, Information on Demand: Introduction DB2 9 New Features, 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: mailto:email@example.com.
IBM, DB2, System i, and System z are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft is a trademark 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, and 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.