DB2/400 + ODBC + Microsoft Access

rexgregbr

Active Member
Hi people,

I've inherited the mainteinance of an external (to JDE) application and I'm running into some troubles because of my lack of DB2/400 knowledge. I was going to post over the World tech forum, but after searching for help on both foruns, I've figured that my needs might be more suited to this forum, even if they get into World. Hope you can help me.

Basically, the app was developed under MS Access and its main purpose is to carry data from JDE tables to an external system. The data is transformed into the process, to fit the needs of the other system, so this is not a reporting app.

The app is based on ODBC linked tables. I have ODBC linked tables for the customer's JDE database (source database might be any of the available DBs for OW or DB2/400 for World) and also for the customer's installation of the 3rd part software (these are always Oracle tables).

The app also has a bunch of queries for each of the 'transformations'. Each query might be a simple select over a JDE table or might be a more complex join over multiple JDE tables, The queries are Add Record type, ie, they perform a select over the JDE tables and add the records retrieved to the Oracle destination tables.

I do have a good background on ODBC, Access (I've used it since version 1), databases (Oracle and SQL server) and JDE (6 years experience on the trenches of development). However, this is the first time the app is installed against a World site. To complete the scenario, I'm offsite, ie, the customer's site is in another state and all I can do is tweak the app here, send it to the customer and pray. There's a guy there that knows DB2/400 and is managing the app there.

I figured that as long as I had ODBC working against DB2/400, I'd be safe and sound. Well, basically, the app is working (I only need to read data from DB2/400, not write back), I'm using the client access ODBC driver and I can read data.

But, boy, oh boy, the performance is lousy. And I mean it. I was completely lost about the difference of performance for an install based on Oracle or SQL Server (fast) and an install on DB2/400.

Then the guy mentions something about linking logical files instead of physical files and I'm "Wait a minute, what does it mean?". After some online searching, I understood that DB2/400 has a physical file (no indexes) and a file might have several logical files (basically, indexes, but not SQL indexes). And I was scared to death after reading about Performance Analyzer and so on and so forth.

Problem is that the app is based on ODBC link table and there's not much I can do (except for trying to tweak the ODBC driver, if possible, on ODBC Administrator). I can't control the way that Access queries perform against the database (because I'm relying on ODBC to abstract all that).

Therefore, I'm aware of a few possibilities, but I'd like to get advice from people that have been there and done that.

1) I'm not sure if it's possible to link to logical files, but if so, it might help performance and I might be off the hook;

2) Another possibility raised by the customer is to create temporary load tables with only the records needed and point my linked tables to them instead of the JDE tables. This works for me (if the set of records is small enough), but will require the customer to create database routines that load these temporary tables;

3) I think that I might get better results if I create MS Access Pass-Through queries to select the data from DB2 using DB2/400's own SQL syntax (I understand that there's the right way to filter data from DB2 without killing performance - something about using ? as parameters and binding my criteria on the ?s later, but I'm not sure if this is feasible).

4) I would not like to get into coding all the queries (ie, not using queries at all, but rather connecting to the DB2/400 by code and performing all the logic of one query by code). But I'll go there, if needed.

5) If I have to code it, I'm not sure if ODBC is the right choice for this, since I think that the client (MS Access) gets to 'transfer' all the recordset from the database to the desktop in order to perform the standard query. I'm not sure if I'd get better performance with ADO or any other tool/technology.

What I can't do is coding on the DB2/400 side (I'd have to learn RPG?).

So, after this lenghty crey for help, can you guys give some hints, docs, suggestions, share some experience, send me best wishes, comment on my options, give some other options, etc?

Thank you in advance (and I mean that)!!!

Best regards.

BTW, my signature gives the usual setup I work on, but for this project, customer is on World and I don't have any idea of cum, As/400 version, etc. As soon as I find out, I'll let you know.
 

PVD

Active Member
Hi Guys,
I am very new in jde and want to know the detail steps about the odbc connection of jde with other third party database like ms access or oracle....
 

rexgregbr

Active Member
Hi PVD,

There's plenty of help on other threads around this forum and it will help you if you take a look on them.

The basics are: you need an ODBC DatasourceName (DSN) to your database. This is accomplished in Control Panel, Administrative Tools, ODBC Administrator. There, you create a System DatasourceName pointing out to your database connection (you'll need an user and password for your database - you can ask a DBA or CNC person to create it to you, with the rights to read data).

After creating the DSN, it's a matter of using it within your tool of choice. For instance, in Microsoft Access you link to data from an ODBC source and it creates a 'linked table' that you can use to execute queries.

There's also ODA, but I wouldn't venture over there. But you'll find plenty of threads regarding ODA here.

Hope it helps.

Best regards.
 
It is possible to link MS Access to DB2/400 logical file using ODBC. I have done it before. It is no different from linking to the underlying physical file.

A logical file in DB2/400 looks just like a physical file when used. Logical files can be used to remove unwanted columns and/or unwanted rows in addition to indexing on key fields.

Is your problem performance related to too much data being transferred? If that is the case, linking to a logical file can reduce the amount of data being transferred.

Logical files can be created in DB2/400 by using either SQL/400 syntax or by way of creating a DDS (Data Description Specifications) file and then compiling with command CRTLF (Create Logical File). Knowledge of RPG is not necessary, but DDS syntax is based on the old RPG output specs. However, it sounds like you would be more comfortable using SQL/400. STRSQL will start up a session in a terminal session. Client Access SQL or iSeries Navigator can also be used for SQL on an AS/400 if you prefer GUI.
 
Top