Microsoft Access and Mainframe DB2 Tables

[Robert Nusz]

I would like to use Microsoft Access 2003 as a front-end
to Mainframe Z/OS system and DB/2 tables. Is there anyone
out here in this news group that has been there and done
that? If so, what kind of success stories can be
related? Where can I get example code of what needs to be
done to define the table space access, does this happen
via the wizard's? Any input would be greatly
appreciated. We are running DB/2 7.1 on the mainframe,
and DB2/UDB DB2 Client 7.1 and 8.1 on the servers. Are
desktop units are running Ms/Access 2003 Professional,
Windows 2000 Professional op/system at service pack 3.
What else needs to be done to gain access from desktop
Ms/Access applications to DB2 mainframe table spaces?

Thanks in advance..

Robert

 

[Mike Labosh]

I would like to use Microsoft Access 2003 as a front-end

to Mainframe Z/OS system and DB/2 tables. Is there anyone
out here in this news group that has been there and done
that? If so, what kind of success stories can be
related? Where can I get example code of what needs to be
done to define the table space access, does this happen
via the wizard's?

As far as I can tell, you should be just fine as long as the mainframe is
configured to speak ODBC. Then you can use Access to link the external ODBC
tables or use pass-through queries to get the data.

Table Linking (someone correct me if I'm wrong):

Your queries operate on the linked tables, and Access sends ODBC calls to
the server or mainframe. I think what Access does is ask the mainframe for
the whole table and then processes your query locally. If this is still the
case, your application will have much higher network traffic and make the
client perform more processing than needed, and will run slower.

Pass-through queries:

Access sends the query to the server or mainframe and it is processed
remotely, and only the results come back.


A couple other thoughts: If the access clients don't need to modify the
data, you could have the mainframe export delimited text to periodically
import into Access. It might be worth looking into "Microsoft Host
Integration Server" which is a server product that supposedly provides a
transparent data access layer to mainframes, but I've never played around
with it because I don't have a mainframe.

--
Peace & happy computing,

Mike Labosh, MCSD

Feed the children!
Save the whales!
Free the mallocs!

 

[david epsom dot com dot au]

the server or mainframe. I think what Access does is ask the mainframe

the whole table and then processes your query locally. If this is still

That is a little bit simplified.

Access/Jet/ODBC tries to understand the mainframe tables, and
will try to only request relevant records. For example, if
you want "select * from tbl where idx = 1", that is what the
request will be.

Things get more complex when you use Access features like
user defined functions in the query criteria, or even outer
joins: the ODBC SQL specification only allows for ONE outer
join in a query, so Jet will decompose a query and do multiple
outer joins locally.

Pass through queries are not translated into ODBC SQL, so
you may use any syntax that is acceptable to the ODBC driver.
Naturally, some ODBC drivers do not expect or require ODBC
SQL :~).

(david)

 

[david epsom dot com dot au]

You may only be able to use Access compatible data types.
Data type conversion for other types may be unreliable.

You should start with ODBC drivers for your mainframe
data systems. You may wind up using OLEDB drivers instead,
so you should be aware of those, but ODBC is a good
place to start.

You should create and test an odbc FILE DSN first.
Then you should link to that using the linked table
manager.

Then you should return here for further help or advice.

(david)