Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » DB2 » DB2 Distributed (UDB) Articles Archive » IBM® DB2® Universal Database™ and the Microsoft® Excel Application Developer… for Beginners
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 4373
 

IBM® DB2® Universal Database™ and the Microsoft® Excel Application Developer… for Beginners

by Paul C. Zikopoulos

In a previous article, I showed you how Microsoft Access can be seamlessly integrated into a DB2 Universal Database (DB2) environment. In this article, I’ll take you through some basic functions that you can perform with Microsoft Excel Professional Edition and DB2. As you read through this article, remember that the examples use DB2 for Windows®, but they could be any member of the DB2 family (although you require DB2 Connect™ for DB2 for iSeries™ and DB2 for zSeries® with a couple of minor exceptions) or even other relational and non-relational data sources (using DB2 Information Integrator).

Excel is likely the world’s most popular business intelligence (BI) tool. The META Group’s Business Performance Management (BMP) study of 450 key influencers in the North American marketplace found that Excel was the primary tool for reporting. In fact, their respondents (from different industry verticals) indicated that 66 percent of all financial management reporting and 64 percent of all budget planning was done in Excel.

Most large enterprises have a significant investment in Excel applications and quite often (just as in the Microsoft Access case I covered in my previous article) they don’t even know it. Why? Excel is a great tool to offload reporting and analysis. It’s on virtually every desktop, and once you get the data, you can keep it local and work with it. You don’t need a DBA. Quite simply, power users can invest a couple of weeks of hard work and build a decent application and non-relational data repositories to manage business data.

So far so good, right? Well, perhaps not…

Compliance and regulatory issues such as Sarbanes Oxley (SOX) and the Basel II accord are key examples of applications whose underlying data stores (in this case Excel) have to change. However, businesses can’t afford to do away with the logic invested in these applications, and nor should they have to!

At first glance, the standard consideration to underpin these applications is going to be Microsoft SQL Server. This may or may not fit into your strategic direction. Would it make a difference if I told you that the data store behind your Excel spreadsheet could easily be DB2 for Windows, or even DB2 for z/OS® (or more), and that your power users wouldn’t even know the difference.

In this article, I show you how to generate a report using Microsoft Excel in a DB2 environment. In a future article, I’ll detail how you can create the more powerful (and ubiquitous) Pivot Table in the same environment, and show you some other powerful features.

All-in-all, the features that I show you are really a continuation of the DB2 application developer mantra: “you choose the API and the programming model, and we’ll ensure you remain productive.” In other words, make database decisions based on data, and tool decisions based on available tooling.

When you take into account that you can host your Microsoft Excel application data on a DB2 platform, along with the fact that the list price of a DB2 solution can be as much as 75 percent cheaper in a highly available environment than SQL Server, it’s easy to see why DB2 is the perfect platform for you to host your Excel data. The key here is that the skill set and simplicity behind the building of the existing reporting or application infrastructure can remain intact or be enhanced or migrated: the choice is yours!

Getting to DB2 Data from Excel

The easiest way to get access to DB2 data from your Excel application is to use an OLE DB driver. To set this environment up, you simply have to install a DB2 client on your workstation and the rest you can do from Excel with mere clicks of a button.

DB2 Version 8.2 (formerly known as DB2 “Stinger”) includes a new DB2 Run-Time Client Lite, which has all of the data access APIs (ADO.NET, OLE DB, JDBC, SQLJ, etc.) in a compact 12 MB installation that comes with a royalty-free distribution license. This makes deployment of a DB2 client a lot easier than in any previous version (the DB2 Run-Time Client’s footprint is about 80 MB). You can download the DB2 Run-Time Client Lite at: http://www-306.ibm.com/software/data/db2/runtime.html. (If you’re following this article with a test machine that has a DB2 server installed, you do not need to install a DB2 client since all DB2 servers come with a built-in DB2 client.)

If you are planning to deploy a workstation image and want your power users to access your DB2 data sources with Excel, I recommend configuring a test workstation with access to all of your required data sources (as well as setting configuration parameters or registry settings) and using a response file installation with a profile. In a Windows environment, you can use the db2rspgn command (which invokes the DB2 Response File Generator utility) to “‘cookie-cut” your installations for speedy deployment.

Once you have correctly configured access to your DB2 data source, perform the following steps to retrieve data into your Excel spreadsheet:

Note: The following steps assume that you are using Microsoft Office Professional Edition.


1. Select Data  Import External Data  Import Data… from the action menu bar.

The Select Data Source window opens, as shown below:

2. Select Connect to New Data Source.odc and click New Source

The only difference between setting up a connection to SQL Server and setting up on to DB2 is that SQL Server users can save themselves a click or two by selecting New SQL Server Connection.odc.

The Microsoft Office Data Connection wizard opens:

3. Select Other/advanced and click Next.

The Data Link Properties window opens:

4. Scroll through the list of OLE DB providers and select IBM OLE DB Provider for DB2, and then click Next. The Data Link Properties window opens.

When you installed a DB2 client, one of the data access APIs you installed was for OLE DB. DB2 V8.2 includes a full-featured OLE DB provider (it can even be used for loosely coupled transactions) that is extremely fast (orders of magnitude faster and feature-rich than its counterpart in DB2 V7.2).

5. Select the Existing data source radio button and the name of the database that you want to connect to using the Data source drop-down box. Also, specify values for a User name and Password that you want to use for your database connection.

The following window shows a connection to the SAMPLE database using the user account paulz, and password — well, I’ll keep that my little secret.

You can specify other options in this window for your connection. For example, leaving the User name and Password fields blank will default to the user name and password that you used to log onto your Windows server. If you are following this article on your own server, this is likely fine. If you are connecting to a remote server, unless you’ve defined a user account with the same specifications and credentials on that machine, you should not leave this field blank.

You can also use the Direct server connection option to ‘discover’ a database server that is associated with your selected OLE DB driver on a specific system, as shown in the following window:

You can ignore the other tabs for now; if you need help, click Help.

6. Test the connection by clicking Test Connection.

If the connection is successful, you should see a window like this:

7. Click OK.

The Connect to DB2 Database window opens:

This is a standard OLE DB connection window. Click OK.

8. In the Data Connection wizard, select the table that contains the data you want to import into a DB2 database, and click Finish.

You can click on Next to specify other options, such as a description of the data, and so on.

9. Excel will give you options that allow you to select where you want to put your DB2 data: in the existing worksheet, in a specific range, or in a new worksheet. For our example, just click OK.

Note: Sometimes when connecting to OLE DB data sources, the Connect to DB2 Database window from Step 7 appears again. If it does, just click OK.

10. Your data is ready for use. I used the chart wizard to quickly create a breakdown of the employees by Department, shown below.

And a Whole Lot More

The examples in this article brought static data back into your Excel spreadsheet. If that data was volatile in nature, you may want to refresh the data in an ad-hoc manner to ensure your reports are up to date. You can do this by using the Refresh Data option under the Data menu:

Also note that once you’ve set up a data connection, it persists in the Data menu:

These connections are actually stored in your user profile, in the My Data Sources folder, which is one of the My Documents folder’s children:

You can do a lot more than what I showed you in this article. For example, as I mentioned earlier, you can use this data to create a Pivot Table or a Pivot Report:

You can even generate an XML file from your DB2 tables, validate it against an XML Schema or DTD in DB2, and work with an object model representation of the data in Excel:

This example emphasizes one of the key attributes of XML — agnostic interchange of data between applications. DB2 had no idea that this data was destined for an Excel spreadsheet — and that is the whole point.

You can also filter out objects or schemas that you aren’t interested in (or don’t have access to):

This is a very useful feature that you should become familiar with — especially if you’re running an ERP-like system such as PeopleSoft or SAP, where the database typically contains thousands of tables. Think about the time it would take for Excel to enumerate a non-filtered list of tables into the thousands!

You can also use the Microsoft Query Editor and all the features associated with that utility:

And there’s more! (I’ll cover some of these more advanced features in future articles.)

Wrapping it Up

It’s a cliché, but with DB2, you can truly “have your cake and eat it too.” If you’re a shop with a heavy investment in applications built on the Excel platform and you’re looking for an enterprise-worthy data store, you need not look any further than the DB2 family. The point is that you can actually choose a DB2 solution; you don’t have to use what you may have thought you did before reading this article.

The DB2 Information Management team wants developers and analysts to remain productive. That means they should use their native tooling and access plans. Whether you’re an information worker (Microsoft’s latest term for Microsoft Office power-users) or a .NET developer — everyone can feel at home with the DB2 family.

--

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 and has written numerous magazine articles and 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). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: paulz_ibm@msn.com.

Trademarks and Disclaimer
 
DB2, DB2 Connect, DB2 Universal Database, IBM, iSeries, zSeries, 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.
 
Other company, product, and service names may be trademarks or service marks of others.
 
The opinions, solutions, and advice in this article are from the author’s experience and not intended to represent official communication from IBM. 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.

Contributors : Paul C. Zikopoulos
Last modified 2005-04-12 06:21 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone