RE: Help improve performance of RPG program - Long Post

>> Or you could go the other way and have your main RPG program write and then
>> run SQL source.  In this setup your date conversion routine would be a SQL
>> user defined function executed by the generated SQL source.  You could get
>> away from having to process the SQL a record at a time in your main program.
>> One INSERT statement for the file header record (or maybe the main RPG
>> program writes this record directly).  Another INSERT statement for the
>> field/column names record (or maybe the main RPG program could continue to
>> write this record directly).  Then a third INSERT using SELECT for the
>> remainder of the file:

>> INSERT INTO FTPOUTPUT ( FTPFIELD ) SELECT RTRIM( TEXT) || ',' ||
>> MYDATE(DATE) || ',' || CHAR(NUMBER) [etc] FROM INFILE

This is a long post.

How about we push this to the next level?

Step One:

We create four User Defined Functions for SQL.

1. OPENIFSFORNEW

Passes a single varchar string containing path and file to open. File would be 
opened to create new file and truncate any existing file. 

        OPENIFSFORNEW('/home/mydirectory/transmitfile.txt')

2. OPENIFSFORAPPEND

Identical to OPENIFSFORNEW except file is opened for writing with append.

3. WRITEIFS

Sends a varchar string and writes that string to the open IFS file.

        WRITEIFS(SQL statements to build string as detailed below)

4. CLOSEIFS

Close the open IFS file.

        CLOSEIFS()

User defined functions must return a value so all the functions would return a 
one byte blank. I will talk below about consuming the results. 

The trick to making all this work and work efficiently is implementing the UDF 
with a service program. I just recently completed a UDF called AS400DATE using 
this technique. I am supposed to be writing an article for ISERIES News about 
the function but, as usual, putting off. 

AS400DATE takes a AS/400 type date, i.e. a Decimal field, Integer field or 
character field and returns a Date Data type to the caller. AS400DATE is 
implemented as one service program with 8 procedures. One procedure for each 
type with and without optional parameter of format of field. For example, 
AS400DATE(Decimal 8,0 Field) would just call the procedure for Decimal 8,0 with 
no second parameter. Default is *CCYMD, i.e. 20050525. 

What I know of testing this program is implementing the UDF as a service 
program is extremely fast and you have persistence. In fact, I have not been 
able to find any difference between running with the function and not using the 
function. 

Those with further interest, I can send you the write-up.

So we end up with a single service program with four procedures. I can mock 
this up creating a service program that does nothing with the strings. 

Step two:

Create a single table called DummyFile with a single field called Dummyfield 1 
byte character and then add one record with blank value. 

Step three:

The program generates SQL statements. 

Update DummyFile
  Set DummyField = (Select Distinct 
OPENIFSFORNEW('/home/yourdirectory/transmit.txt')
                                From DummyFile)
// Write header

Update DummyFile
  Set DummyField = (Select Distinct WRITEIFS(Build string as below)
                                From FileToSend)
// Write record two

Update DummyFile
  Set DummyField = (Select Distinct WRITEIFS(Build string as below)
                                From FileToSend)
// Write the rest.

Update DummyFile
  Set DummyField = (Select Distinct WRITEIFS(Build string as below)
                                From FileToSend)

Update DummyFile
  Set DummyField = (Select Distinct CLOSEIFS()
                                From DummyFile)

So all the driver program has to do is to dynamically build the SQL Statements, 
prepare and run them.

Unknown here is how the distinct would work. If I were to write 1.5 million 
records, would it write 1.5 records into a temp file and them write out one or 
would it be smart enough to just keep consuming them as it goes.  

What do people think?