Issue executing Update Query via ODBC to a remote iSeries in Access 2010
Posted on 2014-08-25
Recently my organization migrated our local iSeries to a virtual instance in a different part of the country, it's been great thus far with no issues what so ever. Both locations are setup with a VPN connection between them and we've had good performance. Recently, I've been having an issue with an update query that I run in Access 2010. I have a table of about 30,000 rows with 5 columns where I need three of the columns to update corresponding columns in another table located on the remote iSeries. When this query was done locally with the iSeries done next to me, I had 0 issues. Today, it takes over 1.5 hours to complete the request. I know that I don't have a data definition issue and I know the connection is still good because I can view and update individual records when the table is linked back to the iSeries. I was wondering if anyone has encountered this or can offer any suggestions as to how I can see what is taking the query so long?
I've further completed a test where I used the File Transfer program from IBM to upload the physically created file from Access to a skeleton file sitting on the remote iSeries and that transfer takes a whole 10 seconds. Before I go further and develop this work around where I complete the update directly on the iSeries, I wanted to exhaust all issues I could check for on my server side.
Any help would be great appreciated.