DB2 9 and Microsoft Access 2007 Part 1: Getting the Data…

Without a doubt, Microsoft Access 2007 (Access 2007) is one of the most widely used reporting and data persistence tools. Quite often, I see clients export massive amounts of data from their enterprise systems into Access 2007. They tell me they do this because they have so many applications, or information workers skills in the development of applications, built with an Access front-end.

The main problems associated with this approach are as follows:

1.  There are too many copies of the data. Maintaining multiple copies leads to redundant data costs.

2.  There is too much latency with the data. It takes too long to get the latest data to the people who need it. In addition, everyone sees different data with inconsistent points in time with respect to when the data was captured.

3.  The solution is too complex. This much data movement requires its own life cycle: maintenance, batch jobs, and more.

4.  The data is not secure. Because of its portability, data typically resides on minimally secured laptops.

5.  The solution is too expensive. Numerous components lead to increased costs. These costs are often hidden in distributed organizations, but they can be massive — core operating system licensing costs, additional data server license costs, network costs, patching costs, security costs for a whole new layer of data access, and more.

What’s more, there often comes a time when the IT staff must try desperately to rein in data sprawl and to solve the problems just mentioned. For example, governance is a great motivator for solving the sprawl of Access databases. Faced with such a daunting task, some IT departments define a mission to move Access databases to an enterprise data server, likely Microsoft SQL Server.

I’m not going to perform a competitive analysis of these data servers in this series; however, I will note that I’ve seen dedicated DB2 shops feel that they didn’t have a choice but to select SQL Server for such a mission because they would incur a productivity hit to their project development cycles. I’m writing this series to give you the confidence to let a data decision be a data decision and a tooling decision be a tooling decision.

Now consider leaving all that data in your DB2 (including a DB2 for z/OS) data server and just accessing it. After all, if your enterprise data warehouse (EDW) is built on DB2, it can scale pretty much limitlessly and is designed to handle all kinds of workloads. Or perhaps take those Access 2007 tables and move them to a DB2 data server, but keep all the application logic, reports, forms, and so on, in Access so as not to lose any productivity for your analyst and application development teams.

If you did this, you could achieve the following benefits:

1.  A dramatic reduction in the number of copies of the data, which can help support the goal of presenting a single view of the business through a single version of the truth.

2.  Reduced latency time with fewer input and access paths and near-real-time load and access tools providing up-to-date business intelligence.

3.  A simplified architecture making the environment easier to manage for faster solution development and quicker return-on-investment (ROI)!

4.  A more secure solution since data is locked in one place with enterprise-wide policies.

5.  A less expensive solution. Fewer components lead to reduced costs in both infrastructure and human resources; all this results in lower total cost of ownership (TCO).

The following figure summarizes these approaches:

For example, you can use the Configuration Assistant to add a new data server connection using the Add Database using Wizard option and optimize the DB2 database connection for Access as well as register it as an ODBC data source on your server. Alternatively, you can have DB2 create a DSN file for you to redistribute the connection information or have the connection only appear for a specific user defined on your server:

If the target DB2 data server that you want to connect to doesn’t show up in the Data Source Name list, simply click New to add it. The Create New Data Source wizard opens. Select the appropriate radio button that corresponds to the way you want this target data server to be accessible on your system. For our example, select System Data Source, click Next, select the IBM DB2 ODBC Driver (it is automatically installed when you install DB2 on Windows or a client), click Next, and then Finish. This process is shown below:

You can see in the previous figure the two IBM DB2 ODBC Drivers in the Create New Data Source window. This is because DB2 9 supports multiple-installation copies of the product. For example, in this case, I have one version of DB2 9.5 on my machine (denoted by DB295Main) and another separate installation running at the DB2 9 level. This feature is terrific, especially for migration and maintenance.

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, 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 Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles