IT Tricks

DB2 9 and Microsoft Excel 2007 – Part 1: Get data

DB2 – The largest, most powerful and fastest database management system in the world today developed by IBM. Compared to Oracle, its main and strongest rival, DB2 is not as well known. But believe that in the near future, DB2 is the next choice for the next generation of database administrators besides SQL Server from Microsoft or Oracle of Oracle.

Surely many readers of Quantrimang.com will feel quite surprised with DB2 8.5 or DB2 9.2. This series introduces some of the methods of applying DB2 9 with Microsoft Excel 2007. It is hoped that they will be useful to those interested in DB2, the future governance system of the database.

Part 1: Bringing DB2 9 data into Microsoft Excel 2007

To import data into an Excel 2007 file from the DB2 Data Server, you need to start with a database connection. This function is supported in Excel 2007, on the Data tab (see figure below).

Data tab is a new feature in Microsoft Excel 2007. With Excel 2007, you can perform a lot of data functions in addition to common operations such as formatting and editing. Most prominent is that you can import data from different sources into Excel. To enter data, you use the function “Get External Data”(In the left pane of the Data tab). That’s the functionality you need when you want to import DB2 9 data into an Excel 2007 file.

To import STAFF table data contents into the worksheet of an Excel 2007 file, perform the following steps:

1. Trong tab β€œData“, choose “From Other Sources” > β€œFrom Data Connection Wizard“In the box”Get External Data”:

As you can see in the picture above, Excel 2007 supports importing data from different sources (sql server file, sql server analysis services, xml, Microsoft query) into worksheets. But in the framework of this article, we will be importing data for DB2 9, data type has not been defined in Excel 2007, so you need to select β€œFrom Data Connection Wizard”.

2. Window “Data Connection Wizard“Open, select”Other/Advanced“And click”Next”.

3. Window “Data Link Properties“Open, select”IBM OLE DB Provider for DB2“In”OLE DB provider(s)“And press”Next”.

When you install the DB2 9 client on your machine, the corresponding “OLE DB provider” will also be automatically installed. And Excel 2007 will use this component to retrieve data from the DB2 database.

The illustration in step 2 shows how you can choose the “ODBC DSN“. When the input source is the DB2 database, you can choose the type of database configuration to be the user, the system or a DSN file using the built-in Windows utility – the Data Source function ( ODBC). My SAMPLE sample database is configured as a system DSN file, so I can also use this connection to query the data instead of just importing it to the Excel 2007 file.

4. Select the database you want to connect from the box β€œSpecify the DB2 database“. Enter complete and accurate login information for your database in the box β€œLog on Information“And click”Test Connection”.

If the database you want to connect to is not a defined type on the machine running Microsoft Excel 2007, you can search for existing Data Servers and their database (as well as DB2 Connect entries if you wish. makes additional DB2 connection to machine I or DB2 for machine Z) using the “DB2 Discovery“Integration in Excel. You can perform another style by selecting the button “Direct server connection“And select a database from the list”Select the database on the server“(Please press”Refresh”To update server list and latest database list before selecting).

Slightly out of the scope of this article, but it is better to note that you can use the “Advance“To set advanced properties for this connection. For example, set time-out for connection to “time-out”, or create a hard-coded password.

5. Click “OK“When the connection is successful.

The DB2 OLE DB Provider is usually fully integrated in Excel 2007. For example, if Excel 2007 cannot connect to the SAMPLE database because the database instance is down, an error message like below will be displayed:

If the database is in use (it could be because it is in a silent state or a backup is in progress), Excel 2007 displays the error message:

6. β€œData Connection Wizard” open. Select the table containing the 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.

Attention: To create the SAMPLE database in DB2 9, you need to enter the command db2sampl from the operating system’s Command Prompt screen.

7. You can choose to leave information for this connection or not. But it’s better if you record it so you can reuse it next time. Then, click the “Finish”.

If you do not want to record this connection in the future, simply click on the button β€œFinish“In the previous step. In the picture above you can see that I have recorded the current database connection (which is the only connection to the STAFF table) for later use. You can optionally add a description for this connection object (in the field “Description“) And search for metadata (in the field”Search Keywords”) To make the following queries easier. Finally, note that you can set up good authentication controls for the authentication by clicking on β€œAuthentication Settings”.

8. Window “Import Data” open. Use this window to describe where the data will be placed in an Excel 2007 worksheet. You can select the default values ​​and press. OK.

As you can see in the figure above, the place to store the DB2 data can be a Table or a Pivot Table Report, or both. I will discuss them in the next article. (If you want a more detailed configuration of the properties of the connection string, click the button Properties).

On the Excel 2007 file worksheet, it will display data like:

Of course, at this point you can adjust the display of the data to suit you through the powerful formatting function in Excel 2007 and apply the Table Style to the tabular format from the tab. Design. Eg:

You can use the “Table Style Options“In the Design tab like:

Once the data is downloaded in Excel 2007, you can use the “External Table Data”In the Data tab to manage the database connection. For example, selecting Unlink will separate the data from the table and make them independent:

And you can quickly filter the returned data without writing any SQL:

One of the things I really like about Excel 2007 is the powerful charting feature. Using this method you can easily represent your DB2 9 data as graphs.

In the chart above you can see the salary and commission percentage for the 5 employees in room 20. Of course, as long as you don’t disconnect from the record set, you can refresh the data, the chart will be update immediately. For example, run the following DML command to change the percentage of commission earned by Pernal (Pernal is an employee in the chart):


Now, refresh the data:

And of course, the base data is also updated:

Let’s rethink the most common data usage method with Excel: splitting the data into an informal Data Server. So, if your real data changes, when will your analysis for the real data be? Let’s wait for the answer in the next article.


In this article we took a look at the simple and easy way to import DB2 9 data into an Excel 2007 file. Besides that we also learned about some of the components that are useful to change the way data is displayed. display (using various Table Tools functions) as well as how to create a chart in Excel and how to refresh the actual data.


Leave a Reply

Your email address will not be published. Required fields are marked *