RE: Help improve performance of RPG program - Long Post

 >> First off, why the Select Distinct?

To dispose of the blanks returned by the UDF. Every UDF must return something. 
I looked at the manual and you cannot return nothing so I will tell it to 
return a one byte char field for each call . Cannot return a NULL so it would 
know that there is only one possibility. Be nice if IBM added that.  

If I have it correctly, the operating system(SQL Runtime) is going to read a 
record from data base, Build the string in the WRITEIFS, call the UDF and then 
back to get another record same as any other SELECT so if you did not put the 
DISTINCT in I would get 1.5 million blanks returned if processing a file with 
1.5 million records just as if I was using my AS400DATE UDF but I would get 
back 1.5 million date data types.

>> Note the use of the MAX function.

You know Charles, you have got it. If I use MAX instead of DISTINCT, I don't 
have any of the problems I was thinking about. Each time it runs the UDF, it 
returns a blank, compares it to the previous value and does nothing because 
they are the same. That's it. Thank you very much. 

>> I'd do the headers like so

>> Set dummyVar = WriteIFS( stringToWrite )
  
You are forgetting that you must have a SELECT statement. There is only one 
WRITEIFS issued for the whole 1.5 million records so it has to know where the 
get the data from just like any other SELECT. The set only works for, I think, 
for UDF that just do one thing like Opening the IFS. I haven't tested it yet so 
I don't know if it even works. 
I can use a SET for OPENIFS because the manual shows an example of a function 
SET :VALUEX = SUBSTR(:VALUEY,1,1) so Set :DummyField = OPENIFS(:FileName) will 
work. 

You know, it just occurs to me that it doesn't really make any sense to use SET 
on some and EXECUTE IMMEDIATE on others. Might as well just have one function 
that receives the string and does an EXECUTE IMMEDIATE. 

Again I would like to emphasize that that I am not going to be reading the 
records into the HLL. The HLL is there only to build and execute the SQL 
String. All the work occurs in the SQL Statement itself. This seems to be a 
point of a lot of confusion.  The execution in the HLL is:

OPEN
WRITE Header1
WRITE Header2
WRITE 1.5 million records
CLOSE

No loops. Just these 5 statements plus what is needed to build the string to 
execute.

I see the high level code as just being:

BuildFieldTable(FileNametoCopy);
ExecuteSQLString(BuildOpenString('/Home/YourDirectory/YourFile.txt'));
ExecuteSQLString(BuildFirstHeader());
ExecuteSQLString(BuildSecondHeader());
ExecuteSQLString(BuildCopyString());
ExecuteSQLString(BuildCloseString());
ReleaseFieldTable();

That should be it. 

You know Rich, it just occurred to me. You don't even need the field table 
build. Simply have a table:

TableName
TableLibrary
BuildHeaderOneSQLString
BuildHeaderTwoSQLString
CopyRecordsSQLString

By rules of normalization, I would normalize to two tables:
                          Unique
    TableName
    TableLibrary
    TransmitFileName
K TableName
K TableLibrary

                        Unique
    TableName
    TableLibrary
    TypeOfString
    SQL String
K TableName
K TableLibrary
K TypeOfString


Now, if you realize you need to add another type of record to the FTP file, 
just add another SQL string type and program works the same.

You could just build the statements in STRSQL or OPS NAV and paste them in. If 
you need to add a field, just edit the SQL string. The only statements you 
would need is for the WRITEIFS. You could still build the string dynamically 
but is it worth it?

"Make everything as simple as possible, but not simpler." Albert Einstein
 
So at this point, we have reduced the code down to a few dozen lines of code 

Ok, so know the high level code is just:

Chain (FileNameToCopy:LibraryName) FileNameHeader;
ExecuteSQLStatement(BuildOpenString(TransmitFileName));
Setll (FileNameToCopy:LibraryName) FileNameSQLStatements;
DoU 0 = 1;
    Read FileNameSQLStatements;
    If %EOF or FileNameChanged;
        Exit;
    EndIf;
    ExecuteSQLString(SQLString);
EndDo;
ExecuteSQLString(BuildCloseString());

>> stm = 'values Select MAX(WriteIFS(' + wDataFormatString + ') from ' + 
>> wInputFileName + ') into ?'

>> /exec SQL prepare s from :stm

This is exactly what I am doing with the EXECUTE IMMEDIATE. I just don't have 
to bother with parameter markers and you cannot use parameter markers with 
EXECUTE IMMEDIATE. Manual says to use EXECUTE IMMEDIATE if the string is 
different each time and it would be. The wData FormatString iand wInputFileName 
is different each time it is executed.