RE: Help improve performance of RPG program - Long Post

Alan,

I think you're going overboard.  Granted UDFs are very useful, but in this case 
I'd say they are the wrong solution.

UDFs are supposed to be "quick" functions.  Having a long running UDF can be a 
problem.

If you wanted to do this strictly in SQL without using RPG, I'd put it into one 
or more Stored Procedures.



Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Campin
> Sent: Wednesday, May 25, 2005 2:29 PM
> To: RPG programming on the AS400 / iSeries
> Subject: 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?   
> 
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (RPG400-L) 
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
> 
>