Import DB2 table data into a spreadsheet
An illustrated example
Having worked in DB2 for Linux, UNIX, and Windows support for a few years, I have noticed that there is seldom an easy way to view data contained in tables in a readable format or one which can be used to generate graphs or charts easily without the need for complex tools. Many customers also ask me, "What is the quickest and easiest way to view or parse DB2 data so that I can generate graphs and charts to make sense of the data?" Common end-user applications such as Microsoft Excel and Lotus 1-2-3 can be used to import this data. This article will show you how to use ODBC / OLE-DB and .wsf files to import DB2 data.
Open Database Connectivity (ODBC) - An industry API which allows applications to transparently connect to data sources and access the data. An IBM DB2 ODBC driver is provided with DB2 installations ranging from the Runtime Client to Enterprise Server Edition (ESE).
Object Linking and Embedding for Databases (OLE DB) - A Microsoft API which allows applications to transparently connect to and access a data source. An IBM OLE DB driver is provided with DB2 installations ranging from the Runtime Client to ESE.
Worksheet File Format (WSF) - From the DB2 Information Center: Numeric data in WSF format files is stored using Intel machine format. This format allows Lotus WSF files to be transferred and used in different Lotus operating environments. As a result of this consistency in internal formats, exported WSF files from DB2 products can be used by Lotus 1-2-3.
Importing into an Excel spreadsheet using ODBC
To import into an Excel spreadsheet using ODBC, follow these steps:
- Open up an Excel spreadsheet and select New Database Query.
Figure 1. New database
- If this is the first time data is imported into the Excel using this method, then Microsoft Query needs to be installed, as this may not be installed by default.
Figure 2. Microsoft Query
Figure 3. Microsoft Query
- Now we need to define the data source that we will be extracting the data from which is to be imported into the spreadsheet. So lets choose New Data Source if has not been defined.
Figure 4. Define the data source
- We will be using the IBM DB2 ODBC driver provided as part of the DB2 installation. In this example DBEXCEL is the name of the database. Click on the 3rd field ‘Connect’ in the dialog box below and select the database that we will be connecting to.
Figure 5. Select database
- This dialog box will appear when we click on Connect above. Select the database alias and provide the user credentials needed to connect to the database.
Figure 6. Logon dialog
- Select which columns we would like to import. In our example, let's choose all the columns in the table.
Figure 7. Select columns
- Apply additional filtering if required. In our example, let's just leave this as it is.
Figure 8. Filter data
- No sorting of the data is required at this stage, however sorting data by columns can be useful.
Figure 9. Sorting data
- We want to return this data to Microsoft Excel.
Figure 10. Finish dialog
- At which location in the spreadsheet do we want to start importing the data? Let's leave it as the default of $A$1, the top left hand corner.
Figure 11. Where to import the data
- In order to import the data we need to establish a connection to the external data source.
Figure 12. Connect dialog
- We can now see the data imported into the spreadsheet ready to be analysed.
Figure 13. Data in spreadsheet
Importing into an Excel spreadsheet using OLE-DB
To import into an Excel spreadsheet using OLE-DB, follow these steps:
- Open an Excel spreadsheet and select Import External Data from the Data menu.
Figure 14. Data menu
- Choose Connect to New Data source.
Figure 15. Select data source
- Select Other / Advanced.
Figure 16. Data connection wizard
- Select IBM OLE DB Provider for DB2, then click on Next.
Figure 17. Data provider
- Test the connection with the user credentials provided to ensure that you can connect to the database.
Figure 18. Test connection
- Now you can connect to the database.
Figure 19. Connect dialog
- Select the table that you are going to import.
Figure 20. Select table
- Save the session if you like, otherwise click on Finish. Select where to insert the data and you should now have the data imported into the Excel spreadsheet.
Figure 21. Data in spreadsheet
Importing into a Lotus 1-2-3 spreadsheet using ODBC
To import into a Lotus 1-2-3 spreadsheet using ODBC, follow these steps:
- Open up an Lotus 1-2-3 spreadsheet and select Query Table.
Figure 22. Query table
- Select external table.
Figure 23. Query table assistant
- The database that we will be using is IBM DB2 (*), then click on Open.
Figure 24. Select DB2
- Create an ODBC data source if there isn’t one already defined.
Figure 25. Create an ODBC data source
- To connect to this data source, supply the user ID and password credentials required for connection.
Figure 26. Connect to database dialog
- Now you can connect to the database. In this example, the userid is athakkar and the database is DB2.
Figure 27. Open the database dialog
- Select the schema in which the table resides.
Figure 28. Select schema
- Select the table that we will be importing data from.
Figure 29. Select table
- Select the columns.
Figure 30. Select columns
- We can now see the imported data in the Lotus 1-2-3 spreadsheet.
Figure 31. Data in spreadsheet
Importing into a Lotus 1-2-3 spreadsheet using .wsf files
To import into a Lotus 1-2-3 spreadsheet using .wsf files, follow these steps:
- First, export the data from the database using the export command. For example:
db2 export to syscat.tablespaces.wsf of wsf select * from syscat.tablespaces
- Open up the Lotus 1-2-3 application. Select Open and choose All Files for File Type.
- Using my above example I will select syscat.tablespaces.wsf and click on Open.
Figure 32. Open file
- You should now have the data imported into the spreadsheet.
Figure 32. Data in spreadsheet
This article explains how to import data into Microsoft Excel and Lotus 1-2-3 in a quick and easy manner. After importing table data a few times you will not even have to refer to this article as the process is quite simple. This should allow you to quickly formulate graphs and charts. If you have defined table functions to gather various snapshots or have defined event monitors that are being written to a table, then importing this data allows you to generate a graphical representation of your monitoring data based on columns quite easily, especially for those familiar with Excel or 1-2-3.
Special thanks to Connie Lam from DD2 UDB Advanced Support for providing her input and reviewing this article.
- Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to expand your DB2 skills.
- Visit developerWorks DBA central to link to a world of resources for the DBA, including articles, tutorials, documentation, support links, community links, and more.
- Download a free trial version of DB2 Enterprise Edition.
- Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
- Learn about DB2 Express-C, the no-charge version of DB2 Express Edition for the community.