RE: Help improve performance of RPG program - Long Post

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

> 
> 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.

I can see your point here.  However, even with your procedures in a service 
program, you'd still have to have one program/stored procedure to call them.

I'm not convinced that it makes sense to handle it this way.  Basically, I 
don't see any benefits to calling the procedures as UDFs instead of bound 
procedure calls.

However, how do you envision WRITEIFS() being used?

It would seem to me that there are two ways:

1) WRITEIFS(:StringToOutput)

or 

2) Select WriteIFS(field1 concat,',', concat cast(field2 as char), concat ',' 
myDateFunc(field3)) into :errorFlag
   from INPUTFILE

option one still requires data to be brought up from the DB into the HHL 
procedure to process.

Option two on the other hand, may have some performance benefits as the DB 
record doesn't get sent to the HLL, just a string.

The burning question, is it faster to manipulate the fields in a HLL or in SQL 
using CAST?


> 
> 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.  

Scalar UDFs can be used just about anywhere you can use an expression.

So for instance, you can do:

set dummyVar = CloseIFS();

You don't need the dummy file/fields at all.  Just an dummy SQL procedure 
variable.

I'd have the UDF return a SUCEESS/FAIL flag that I could check before 
continuing.


> 
> 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?
> -- 
> 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.
> 
>