System i SQL WITH SELECT DISTINCT

I have the SQL below in place, and it has an unacceptable response time.

This is used with a DECLARE, PREPARE, OPEN and FETCH in an RPG Program where the selected fields are placed in host variables, populated in an array and then sorted [descending] for subfile display.

The 2 tables in use are not keyed at all (PFs), and they are joined below as shown in the WHERE clause.

Select DISTINCT B.Fld1, B.Fld2, B.Fld3, B.Fld4,
 A.Fld1, A.Fld2, A.Fld3, A.Fld4, A.Fld5, A.Fld6, A.Fld7, A.Fld8, A.Fld9
 From TableA A, TableB B
 Where A.Fld2 = B.Fld5
 And A.Fld1 = B.Fld6 || B.Fld7
 And ((A.Fld7 BETWEEN <from-date> and <to-date>)
 Or (A.Fld5 BETWEEN <from-date> and <to-date>))
 

I have rewritten this as a "true" left join, with no improvement.

I have also used 2 available LFs with A.Fld2 and A.Fld1 as keys, with minor improvement.

I feel a recursive SQL could do the trick, but I lack the experience to whip it out. I have the selects from each table created and functioning as desired by themselves. I just don't know how to put them together into one beautiful beast to get the result I desire.

This result set is roughly 10,000 rows for a week time period, and I need to see 2 weeks.

There are almost 6,000,000 records in TableA and about 160,000 in TableB.

Right now, the logic is

1. Run a simple SQL before the one above.


2. Cursor through the records and populate an array


3. Run the SQL above.


4. Cursor through the records and append to the same array


5. Sort the array and use it to populate the subfile.

In debugging, I verified that the SQL above is the meat of the problem.

Truth is that I have 3 files that I believe can be joined into 1 result table to build the subfile.
If I can get past the query above, then 'I think I can' handle joining the other file.

My guess is there is someone out there that can "Whip this Out"! I once worked with him!

This is not an RPG, system i question. I have laid down some SQL like this in RPG before. Problem was that someone else wrote the SQL. : (

---