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

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

Surely many readers of TipsMake.com will feel quite confused with DB2 8.5 or DB2 9.2. This series will introduce some of the DB2 9 application methods with Microsoft Excel 2007. Hopefully they will be useful for those who are interested in DB2, the future management system of the database.

Part 1: Insert DB2 9 data into Microsoft Excel 2007

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

Data tab is a new function in Microsoft Excel 2007. With Excel 2007, you can perform a lot of data functions besides common operations like formatting, editing. Most notably, you can import data from various sources into Excel. To enter data, use the ' Get External Data ' function (in the left pane of the Data tab). That's exactly what you need when you want to import DB2 9 data into an Excel 2007 file.

To import the data content of the STAFF table into the Excel 2007 worksheet, follow these steps:

1. In the ' Data ' tab, select ' From Other Sources '> ' From Data Connection Wizard ' in the ' Get External Data ' box:

As you can see in the image above, Excel 2007 supports importing data from various sources (sql server files, sql server analysis services, xml, Microsoft query) into the worksheet. But in the context of this article, we will import the data of DB2 9, which is not defined in Excel 2007, so you need to select ' From Data Connection Wizard '.

2. The ' Data Connection Wizard ' window opens, select ' Other / Advanced ' and click ' Next '.

3. Open the ' Data Link Properties ' window, select ' IBM OLE DB Provider for DB2 ' in ' OLE DB provider (s) ' and click ' Next '.

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

The illustration in step 2 shows the method for you to choose the type of ' ODBC DSN '. When the data source is entered as a DB2 database, you can choose the type of database configuration that is the user (user), system (system) or a DSN file using the Windows utility available - 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 data instead of just putting data into an Excel 2007 file.

4. Select the database you want to connect from the ' Specify the DB2 database ' box. Enter the correct login information for your database in the ' Log on Information ' box and click ' Test Connection '.

If the database you want to connect to is not in the type defined on the machine running Microsoft Excel 2007, you can search for existing Data Servers and their databases (as well as DB2 Connect entries if you want to Create additional DB2 connections to machine I or DB2 for Z machines using the ' DB2 Discovery ' component built into Excel. You can do another type by selecting the ' Direct server connection ' button and selecting a database from the ' Select the database on the server ' list (click the ' Refresh ' button to update the server list and list). Latest database before selection).

Somewhat out of the scope of this article a bit, but it is better to note that you can use the ' Advance ' function to set advanced properties for this connection. For example, set the timeout for connecting to 'time-out', or create a hard-coded password.

5. Click ' OK ' when connecting successfully.

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 as shown below will be displayed:

If this database is being used (maybe because it is in a silent state or a backup process is being performed), Excel 2007 will display an error message:

6. The ' Data Connection Wizard ' opens. Select the table with the data you want to import into Excel 2007 and click ' Next '. For example, you can select the content of the STAFF table located in the SAMPLE database.

Note : To create the SAMPLE database in DB2 9, you need to enter the db2sampl statement from the operating system's Command Prompt screen.

7. You may optionally leave the information for this connection or not. But it will be better if you record it to be reused later. After that, click the ' Finish ' button.

If you do not want to record this connection in the future, simply click the ' Finish ' button in the previous step. In the image above you can see that I have recorded the current database connection (which is the only connection to the STAFF table) for future use. You can optionally add a description for this connection object (in the ' Description ' field) and search for the metadata (in the ' Search Keywords ' field) to make the following query times easier. Finally, note that you can set good controls for the authentication section by clicking ' Authentication Settings '.

8. The ' Import Data ' window opens. Use this window to describe where the data will be placed in Excel 2007 worksheet. You can select the default values ​​and press OK .

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

The Excel 2007 worksheet will display form data:

Of course, at this point you can adjust the display data to suit you through formatting functions strongly support in Excel 2007 and apply Table Style style for tables from the Design tab. Eg:

You can use the ' Table Style Options ' function in the Design tab like:

When the data is downloaded to Excel 2007, you can use the ' External Table Data ' function in the Data tab to manage the database connection. For example, select Unlink will separate data from the table and turn them into independent:

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

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

In the chart above you can see the salary and commission percentages for 5 employees in room 20. Of course, as long as you don't disconnect the log file, you can refresh the data, the chart will be Update immediately. For example, run the following DML command to change the percentage of Pernal commission achieved (Pernal is an employee in the chart):

UPDATE TABLE STAFF SET COMM = 55565.56 WHERE ID = 20

Now refresh the data:

And of course, the base data is also updated:

Let us rethink how to use the most common form of data with Excel: segmenting data into an unofficial Data Server. So, if your real data changes, when will your analysis for real data be? Let's wait for the answer in the next post.

Summary

In this article we have seen how easy and easy it is to import DB2 9 data into Excel 2007 files. Besides, we also know some useful components to change the way data is displayed. Marketing (using different functions of Table Tools) as well as how to create a chart in Excel and how to refresh real data.