speeding up data access to an AS400 database...

[WellsCarrie]

If this is not the right place to ask this please let me know....

I have 4 web applications whose primary datasource is located on an AS400 machine. In production I am using
Provider=MSDASQL.1 to connect to the data and first load an ADODB record set that is then transfered to a .net datatable object. I am doing it this way because only 80% of the final data is in the AS400 the rest is on excel spread sheets and our oracle db.

My problem is that the loading of data from the AS400 can take up to 10 minutes if the user is trying to pull large quanities of data (30 days worth of records verus a single day's worth for instance).

My development machine has the odbc connection set through Rumba and the production machine has it set through Client Access ODBC Driver (32 bit). Both are equally slow.


Any one have any suggetions on speeding things up? I am open to trying anything.

Thanks!

 

[cdurbin]

zip and unzip the data

We zip and unzip the data before transmission. We then send it to the i5(iseries, AS/400) where it hits a file with a trigger on it. We verify the data in a temp file and send back an acknowledgement file to the user showing we got it all. Once the file is verified, we process it through the iseries. It is pretty complicated, but works fairly well. You might want to call for details:

Chris Durbin, Western Kentucky Coke
270-563-4735 ex113

 

[WellsCarrie]

Do you zip and send data back from the i5 using the zip?

I'm only reading data these users don't have add/change access.
Thanks!

 

[cdurbin]

You should probably give us a call

You might want to call us and talk about the finer details. I would have to spend all day trying to explain in print.

 

[WellsCarrie]

THANK YOU!

The AS400 guy is going to meet with me this afternoon. He said he was unaware that anyone was trying to query the AS400 the way I was. (He has a diffrent boss than me. Isn't cross team collaboration just great.)
anyway, he refused to promise me anything but at least he's willing to take a look and try to help.

I 'm glad I broke down and posted, I thought the problem was all in my code.

Again, THANKS!

 

[WellsCarrie]

Last update for now. AS400 people are great to work with.

Our guy here at Maybelline took a look not just at what logical files I was using (apprently I was using one at least ) but took a look at my SQL. There were several places where I was joining files in the way I'd do it for SQL server tables that I needed to change as well as looking at my code logic to determine which logical file I should use. I never knew there were so many options.

Not only all of that above, but he and his boss decided that the best route was for them to write me a "join file" that will return me all the data in one row versus the 6 hits to the AS400 I was using to get "straggle" data. They said that on top of the "join file" they could also give me some logicals to speed reading that file up as well.

The short of this lesson is that if you don't know the answer "ASK"!

THANK YOU Chirs for pointing me in the right direction.