Part one of this series discussed using Access 2007 as a graphical front end to a back-end DB2 data server. Part two examines how to leverage DB2 as an integration layer, and demonstrates some of the business rules that Access 2007 maintains when presenting DB2 data to information workers.
In the first
part of this series, I showed you how to use Access 2007 as a graphical
front end to a back-end DB2 data server. Specifically, I showed you how to
implement linked tables and create an abstraction layer over those linked
tables such that information workers can work with business artifacts directly
without worrying about different naming conventions; all the while, the data
resides in a DB2 data server and there is virtually no hit to productivity. In
this article, I want to delve deeper into what you can do with the data that
resides in those linked tables, add more tables to our Access 2007 front end to
show how to leverage DB2 as an integration layer, and demonstrate some of the
business rules that Access 2007 maintains when presenting DB2 data to
information workers.
Before you start
This series assumes that youve linked the DatabaseJournalAccess2DB2
Access 2007 database created in Part 1 to the ORG, EMPLOYEE, STAFF, DEPT, and
INVENTORY tables in the DB2 SAMPLE database and created an abstraction layer
over those tables such that the All Tables view looks like this:
If you need help getting to this point, check out DB2 9 and
Microsoft Access 2007 Part 1:
Getting the Data....
Working with DB2 data in Access 2007
At this point, youve got some linked tables set up for a
number of tables that reside in the SAMPLE DB2 database. As previously
mentioned, you can easily see any of the data these tables hold by
double-clicking the table in the All Tables view. In the following
figure, I opened the Staff business object, which links to the STAFF table in
the SAMPLE database:
As you can see in the previous figure, Access 2007 returns
the data for a given table in a native Microsoft data grid. This provides you
with a usable way in which to work with your data.
Note: The term data
grid refers to the Access 2007 object that houses a data set. A data
set is the set of data that is returned by a query, stored procedure, linked
table, and so on. A data grid is used to display a data set to information
workers in Access 2007. In this article, I refer to these terms. When I refer
to a data grid, Im referring to any of the operational controls that Access
2007 allows you to perform; for example:
.
When I refer to the data set, I mean data; for example, in the previous figure,
is
part of the data set.
Consider the result set from the query SELECT * FROM STAFF when run from the
DB2 command line processor (DB2 CLP) against the SAMPLE database:
You can easily add a record to the STAFF table, through the
Staff linked table, using Access 2007. To add a record to a data set, scroll
down to the bottom of an Access 2007 data grid, locate the empty cell, and use
it to add a new row. An asterisk (*) denotes this input row. For this
example, add a new row as follows:
Notice that when you begin to edit a row, an edit icon (
)
appears in the left margin of the data grid of the row you are editing. This
icon denotes that you are changing or adding data to a data set (in this case, the
STAFF table).
You can alter existing data in the data sets rows as well.
For example, for employee Gafney, change his job to Mgr by
overwriting Clerk. You can put focus on this row by clicking any field
in the row and Access 2007 will highlight the row to show you that the focus
has changed.
When you have finished making changes to a row, simply click
elsewhere in the data grid and shift focus away from the row you just changed
(or added).
Note: If you press Enter while editing a
field, Access 2007 changes focus to the next field in the row.
The color of the margin in the data grid changes according to
the action you are performing. By default, the whole row is selected. (An
orange line surrounded the entire row when you selected it in the previous
figure.) When you change a specific column, the orange focus surrounds the
field being changed and the margin marker (
)
turns orange (
)
to show you have changed focus on a specific
field in a specific row. As you change the data, the margin marker will
show the change icon again (
).
To commit any changes you make, click the
,
as shown below:
If you ran the same query in the DB2 CLP, you would see
these changes. (Im showing you the results in the DB2 CLP so you can see that
changes are occurring; of course, you could simply reopen the data grid to see
them as well):
If youre a database administrator (DBA) and youre reading
this article, you may be having a bit of a fit right now imagining all sort of
devious data corruption incidents that youre going to be on the hook for. You
can relax: Access 2007 keeps the authorization profiles that are assigned to
users in DB2. In other words, if you dont have write access to a table (or if a
table is removed) then you wont be able to write to it.
In the following figure, you can see that I gave Chloe
access to the STAFF table, but only for read operations:
Now if user Chloe uses Access 2007 to see data in the STAFF
table, she will be able to view the data, but wont be able to make any of the changes
I detailed above. For example, if Chloe tried to make the following change to
the ORG table:
Access 2007 would return the following error messages:
You can also see that Chloe doesnt have access to the ORG
table either (which she sees as Organization in Access 2007):
If she tries to access this table through Access 2007, she will
receive the following error message:
This authorization enforcement is dynamic: a DBAs changes
to the authorization semantics for a given set of database objects are
effective immediately.
Besides altering and inserting data, you can delete data
from the data grid as well if you have the right privileges:
Now if you look at the STAFF table you would see that this
row no longer exists:
But you can see that the change made to employee 350
is still here, which shouldnt be surprising.
Access 2007 will honor advanced table features such as generated
columns, sequences, and so on. For example, create a table called COMPLAINTS
using the following DDL (substitute your own schema for PAULZ where
appropriate):
CREATE TABLE PAULZ.COMPLAINTS(COMPLAINTNUMBER SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1
MINVALUE +1 MAXVALUE +32767 NO CYCLE CACHE 20 NO ORDER),
LASTNAME VARCHAR(50) NOT NULL,
COMPLAINT CLOB(1048576) NOT LOGGED NOT COMPACT);
Now add COMPLAINTS as a linked table to your Access 2007 database
and add some values to the table. Using the Access 2007 data grid, you can add
new rows, leaving the COMPLAINTNUMBER column empty in the data set since it
will be automatically generated by DB2. When you save the changes and reopen
the data grid, you will see that the generated columns were added to the base
table:
What happens if you tried to specify an explicit value for
the COMPLAINTNUMBER column; well, just as if you tried to specify a value for
this column using an INSERT
statement and the DB2 CLP, you would receive an error in Access 2007 too:
You can do many interesting things with linked tables and
DB2 as your back-end data server. For example, although its beyond the scope
of this article to show you how to set the following examples up, I created a
view in DB2 that actually covers an Access 2003 table (that version number
wasnt a mistake). You can see in the following figure that I exposed this as a
linked table in Access 2007. When I select this object, Access 2007 goes to DB2
and takes the data from what it thinks is a DB2 view but is really a covering
OLE DB function, and then fetches the data from Access 2003. Pretty neat, huh?

While, at first glance, this may seem like an extreme example,
and Im not suggesting you needlessly hop through layers of an IT
infrastructure to get data, but imagine seeing your DB2 data server as an
integration platform as opposed to just some place to store data. In this
example, you can imagine the benefits: your business users would continue to
simply work with their business artifacts, IT would be satisfied with their
increased controls over the data, and, as more and more disparate systems are
brought together, their ability to centrally manage these systems for control,
governance, and more.
In the following figure, you can see that Ive added some
more linked tables to our Access 2007 integration front end from Access 2003
and Excel 2003 all managed from DB2 and exposed through Access 2007:
As Ive previously stated, it makes no difference which DB2 data
server you are connecting to. In the following figure, I added a table that
resides on DB2 for z/OS, but note that the data Im trying to add violates a
NOT NULL condition on a column in this table:
Of course, you can read from DB2 for z/OS data servers and
perform all the actions Ive detailed so far in this article:
Heres an example of a linked table thats connected to a multidimensional
clustering (MDC) table that resides in DB2 for AIX and contains 10,000 records
dimensionally organized around two dimensions: DIVISION and DEPARTMENT:
Access 2007 provides a number of native data grid features to work with your data sets.
For example, Access 2007 has built-in
order operators:
It even supports composite filtering whereby data values are
first ordered by the qualifying column followed by a subsidiary-ordering
column:
You can see in the previous figure that Access 2007
considers a NULL value to be larger than a defined value so it subsequently
orders those rows without values for the YEARS column before others, and then
applies an order to the SALARY column.
If a sort operation has been placed on a column, Access 2007
will alert you to this using a sorting icon (
)
beside the columns to which a sort condition has been applied. You can work
with the sorting technique applied to any column by clicking this icon and
selecting the appropriate action:
You can choose to hide columns too. Simply select the column
header (
),
right-click the column, and select the Hide Columns option:
Hidden columns arent removed from your table but rather
simply hidden. To get them back, just right-click anywhere in the data grid and
select Unhide Columns:
Another feature I like when working with large data sets is
the ability to quickly search the data set for specific values using the search
bar located at the bottom of an Access 2007 data grid. In the following figure,
you can see that I entered an employees name in the search field, and
Access 2007 quickly located the first occurrence of that entry in the data
grid:
You can use the row navigator to move focus from row to row
using the controls located at the bottom of the data grid (
),
or just manually enter a row number in the Record field (
)
and Access 2007 will jump to that record in your data set.
Copy and paste operations make it easy to move data around:
Ill show you in a subsequent article in this series how
useful this feature can be when administering a DB2 data server.
Access 2007 has a nifty feature called quick filters, whereby you can
select a tuple from the data grid, right-click, and select a filtering option. For
example, in the following figure you can see that I applied a quick filter such
that only those employees who belong to department 20 are shown in the data
set:
If a data set is filtered, Access 2007 uses a filtering icon
(
)
in the column to alert you to the fact that a filter has been placed on the
columns data distribution. You can alter the filter, turn it off, change its
semantics, and more by clicking the filter and selecting the appropriate
option:
Wrapping it Up...
In this article, I showed you how to work with data sets in
Access 2007 that contain DB2 data. As you saw, you get all the productivity
that you are used to with Access 2007 and all the data management benefits from
DB2 that your IT department wants. I also demonstrated in this article some of the
various data manipulation language (DML) operations you can perform on your DB2
data from Access 2007, how authorization controls (among other things) that you
apply to your tables in DB2 are dynamically extended to the linked tables
created in Access 2007, and more.
In the next part of this series, Im going to show you some
of the things you can do now that you have your DB2 data in Access 2007, such
as create DB2-based reports, forms, and more.
»
See All Articles by Columnist Paul C. Zikopoulos
Trademarks
IBM, AIX, DB2, and z/OS are
trademarks or registered trademarks of International Business Machines
Corporation in the United States, other countries, or both.
Microsoft is a trademark of
Microsoft Corporation in the United States, other countries, or both.
Linux is a registered trademark
of Linus Torvalds in the United States, other countries, or both.
Other company, product, or
service names may be trademarks or service marks of others.
Copyright International
Business Machines Corporation, 2008.
Disclaimer
The opinions, solutions, and
advice in this article are from the authors experiences and are not intended
to represent official communication from IBM or an endorsement of any products
listed within. 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
authors knowledge at the time of writing.