Receive File From Host...but in the wrong sequence

[littlepd]

I have a couple of users that have just over the past couple of weeks begun to complain that their download files are not sequenced correctly. Here's the scenario:

- Positive Pay file to send to the bank is built by check run program on i.
- Users use i Access for Windows "Receive File From Host..." option on the Actions menu to retrieve the file down to their desktops, then FTP them to the bank.
- Files have a format defined by the bank, the sequence of records being important: Voids first, Checks next, Total last.
- The records in the table on the i follow this format, i.e.; the total row is last.
- For the past two weeks on Monday, the files are being downloaded from the i with the Total row first. The rest are in the correct sequence.
- There is no "order by" clause specified in the data options of the transfer.

Last week, I had the user change the "Sort sequence" on the "Misc" tab under the "Format options" button from "System i Default" to "Hexadecimal". That seemed to work...for the rest of last week. This morning it failed again. I had the user try every different option available for "Sort sequence" (except User Specified Table) with no changes to the order of the lines in the downloaded file.

What could be causing the file transfer to change the sort sequence of the records in the table?

Any ideas?
-Michael

[littlepd]

Re: Receive File From Host...but in the wrong sequence

OK. I think I've found the solution.

The file is keyed, and the download is retrieving the file in the keyed format. When I view the file on the I, I'm looking at it in RRN sequence. Since RRN is how I want to download it, I can add the ORDER BY RRN(filename) clause to the data options on the download and that should do the trick.

My only question now is: why did it all of a sudden stop working? It's not like the key on the file changed, or that the data suddenly hit a situation where the keyed sequenced no longer matches the RRN. If it doesn't work today, it should not have been working for the last several years!

I can't imagine that a change was made to the client access download that would cause this. I will ask the user if any updates have been applied recently just to rule this out.

[Scott M]

Re: Receive File From Host...but in the wrong sequence

Is the file setup to reuse deleted records?

[MichaelCatalani]

Re: Receive File From Host...but in the wrong sequence

My only question now is: why did it all of a sudden stop working? It's not like the key on the file changed, or that the data suddenly hit a situation where the keyed sequenced no longer matches the RRN. If it doesn't work today, it should not have been working for the last several years!

The answer could be any of a number of things. A temporary index may have been cleared by an IPL The file size may have changed which caused the query engine to select a different approach to retrieving the data. Normally, situations like this have to deal with FirstIO and AllIO bias settings.

You can read about that here: http://www.ibmsystemsmag.com/ibmi/ad...th-SQE/?page=5

This sounds like the culprit. AllIO typically chooses to read the file top down, whereas FirstIo chooses a key in which best fits the order by clause. Since you didnt have an order by clause, a change in the bias would cause the query engine to choose a different access method, one in which the data retrieval was in a different sequence, which would be allowed since no ordering was specified.