RE: Looking for file structure details

I want to start this by saying that I am 100% on board with SQL but not all
of my clients are. Some are old school and have always used DDS and can't
see a need to change. When IBM finally eliminates DDS, they will probably
never upgrade again or will buy a DDS to SQL conversion tool so they can
still "use DDS" to create their files. While I appreciate all of the
recommendations to use SQL instead of DDS for this project, as a consultant,
sometimes I have a rope to pull clients into the 21st century and sometimes
I have a string. This time I only have a thread.

Disclaimer out of the way...

Since someone asked, the project is that I have tables with 500M+ rows that
I have to be processed multiple times a day (come every 20 minutes), each
time adding up to 7M rows. Because of external requirements (non-iSeries
program inserting the data requires > 10 char file names, etc.), these were
created via SQL. The records need to be processed in a particular sequence
so don't process records out of order and there is a flag (and a date) so we
know if the record has been processed. Purging is done daily based on a key
structure that is different from the one used to process. Currently every
time the job runs, it reads through all 500M+ records in the specified
sequence looking for the records that have not been processed. I suggested
that we create the SQL version of a logical where I would specify the key we
need and a select clause to only pick up unprocessed records. That is when
I found that you can't create a view with an order by and you can't create
an index with a where clause. I gave in from an earlier debate and we are
now going to archive the data into a separate file so the original file only
contains unprocessed records and the archive file contains the key structure
we need for purging. Since the external requirements for SQL do not exist
for this file, I have to revert back to DDS because that is the client's
requirement.

As a last thought, I have been told (and can't find anything on the
internet) that I can't use SQL to create an object with a key structure and
a select/omit clause. When I suggested that I might be able to create a
view and then an index over the view, the old school reply was that it can
be done in one object via DDS (LF) and it takes two for SQL so SQL can't do
everything that DDS can do (before you comment on how much more it can do,
please reread the opening line of this email). I would love to be able to
provide the LF equivalent in one object. If anyone knows of a way, please
let me know.