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.
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.