Re: Help improve performance of RPG program - Long Post

 >> Alan,

>> I didn't make it clear that your dummy update statement works great for the
>> 1.5 million record detail.  I just don't see any reason to create functions
>> you'll only call once or twice when you could use a procedure instead.  Why
>> code:

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

>> for an OPENIFSFORNEW function when you can just write:

>> call OPENIFSFORNEW('/home/yourdirectory/transmit.txt')

>> for an OPENIFSFORNEW procedure?

Ok, I get it. The reason that comes to mind is that I would have to call three 
programs vs. having to write one service program with four procedures. The 
programs in turn would have to call the procedures in the service program so 
instead of one program, I would need to maintain four programs and as far as I 
know, a stored procedure cannot be a service program. It must be a regular 
program.

In SQL Server, I can just do SELECT CLOSEIFS(') without having to specify a 
table name but as far as I know you cannot do that in AS/400 SQL. I tried to 
think of a way to do it without using the UPDATE or an INSERT. That blank has 
to go somewhere. If I don't consume it, the system is going to throw it to the 
screen and it is just a string constant. The only thing changing would be the 
file name string so you just create an internal function and pass the string 
and have it return the formatted string and pass to a internal function to 
PREPARE and execute. The code in the service program to implement is probably 
going to a 20 or 30 lines of code assuming I call my IFS service program. If 
someone has an idea of how to consume the blank without using an UPDATE or 
INSERT, I would love to hear about it.  

As far as creating functions, you are really not creating anything. You are 
just registering with the data base the name of the service program and 
procedure to call. Either way I have to write the same code to open the IFS 
file in the appropriate manner. The code in the service program would be 
identical in either case, I would just have three programs that would call the 
service program.

Also, I was originally thinking of implementing the Open as one function, 
OPENIFS. The first parameter would be the same, the second would be optional 
*NEW or *APPEND with *NEW being the default. I would still code two procedures. 
One for one parameter and one for two parameters. I went toward having two 
functions. Maybe I should do one. Your opinion?