Due to some process timing issues with the remote box, we changed the procedure from a pull to a push, so now when the processes complete on the remote server (server B) it then PUSHES the records to the local server (server A). When we made this change the process has slowed down considerable, it takes sevaral hours to process one tenth of the file (about 1.2 million records) before it would eventually timeout (about 4 hours).
My question is there any communication differences between pulling and pushing data using the SQL tasks? or do you have any ideas as why the transfer speed would change so dramatically?
Usually, this will have more to do with your iSeries than the communication. There are several tweaks and such that can be made when dealing with situations like this.
A few (but not all) could be running the SQL Statement through an SQL Analyzer (iSeries Navigator) and seeing what it recommends.
Some things that stand out is whether or not it's going through the CQE or SQE. The analyzer will probably recommend an index (or more) to help with the retrieval of the data... There are also issues where the table you are inserting this data into might have multiple indexes that need updating as you insert the data.
Not knowing the full scope of the project, this could be a situation where you want to just get the data from System A to System B, then run a local job on System B to insert the data. You will probably find that to run much faster.