Program efficiency/questions

[ga]

I have a program written in RPG/III (not ILE although I can easily
convert it to be).

It calls an SQLRPGLE program. The SQLRPGLE calls one other C/L
program which simply tells me if a file exists or not.

I am guessing if the SQLRPGLE didn't "end" every time it was called
the mainline program would run faster...and probably the C/L program
which checked for the file existence?? But the other thought I had is
that these should probably be somehow all tied together to make them
more efficient.

If a file doesn't exist in an SQLRPGLE program in a select, will it
return an SQLSTATE code that I can test so I wouldn't have to do the
C/L program call?

Can anyone point me to the right direction on how to do this; i.e.
what manual should I start with??

Thanks,
ga
ga
nos...@nospam.fmctc.com

[Jonathan Ball]

On 5/21/2012 12:03 PM, ga wrote:
> I have a program written in RPG/III (not ILE although I can easily
> convert it to be).
>
> It calls an SQLRPGLE program. The SQLRPGLE calls one other C/L
> program which simply tells me if a file exists or not.
>
> I am guessing if the SQLRPGLE didn't "end" every time it was called
> the mainline program would run faster...and probably the C/L program
> which checked for the file existence?? But the other thought I had is
> that these should probably be somehow all tied together to make them
> more efficient.
>
> If a file doesn't exist in an SQLRPGLE program in a select, will it
> return an SQLSTATE code that I can test so I wouldn't have to do the
> C/L program call?

It would, but intuitively - that is, I don't really know, just guessing
- I think the overhead of trying to SELECT from a non-existent table,
generating some CPF and SQL messages, would be high. If you're doing
this a bunch of times, it could be costly (message logging is
"expensive"); but if you're only doing it once or twice in a job, it
might not be so bad.

An alternative would be to execute the CHKOBJ directly from your RPG
program. In either the RPG III (or RPV IV) or the RPGLE program, you
could do a call to QCMDEXC and monitor for errors. If it doesn't throw
an error, the file exists; if you get an error, it doesn't exist.

Still another alternative is to execute the CHKOBJ command by a call to
procedure 'system' in service program QC2LE. This would only be
available to you in RPGLE (or SQLRPGLE). Here's how I would do it:

d CmdToRun pr 10i 0 extproc('system')
d Cmd * value options(*string)

d CmdRunResult 10i 0

d Command s 100a

/free

Command = 'chkobj ' + %trimr(libnamvar) + '/' + %trimr(filenamevar) +
' *file';

CmdRunResult = CmdToRun(Command);

If CmdRunResult = 0; // CHKOBJ shows file exists

[do some stuff]

Else; // CHKOBJ indicated file doesn't exist

[do some other stuff]

EndIf;


You need to bind service program QC2LE into your program to use this.

[Karl Hanson]

Last time I checked, one problem with using the system() routine is that
the result is binary - ie, it either worked or failed. But in the failed
case, it is not easy to distinguish if it was for the reason of interest
("file not found") or something unexpected (eg "library not found" or
"cannot assign library"). QCMDEXC may enable more granularity in
distinguishing errors - I don't recall offhand.

Another alternative would be to create a CL module that runs CHKOBJ
(CRTCLMOD command), convert the RPG program to ILE, and bind the RPG
module (CRTRPGMOD command) to the CL module (CRTPGM command). Then a
bound call could be done from from RPG to CL, which should be faster and
also eliminate most command analyzer overhead because no parsing of the
CHKOBJ command is needed, like with either system() or QCMDEXC.

I believe the linkage to call a CL module from RPG is fairly simple.
Here is something similar for a call to a CHKMBR CL module:
D CHKMBR PR EXTPROC('CHKMBR')
D chkLibNam 10A CONST
D chkFilNam 10A CONST
D chkMbrNam 10A CONST
D chkAddOpt 1A CONST

A call to the CL module (in free format) would look something like this:
CHKMBR(SFILL : SFILN : SFILM : AddMbr) ;

Also in ILE, the CL module to run CHKOBJ could alternatively be placed
in a service program (*SRVPGM), to enable calls from multiple programs.
The ILE Concepts documentation may be useful, to learn more:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frzahg%2Frzahgileconceptprint.htm

The error returned by SQL should normally be SQLCODE -204 with SQLSTATE
42704.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fddp%2Frbal1sqlc.htm

--
Karl Hanson

[ga]

Karl Hanson <k...@youess.ibm.com> wrote:

Karl,

Thank you for your suggestions and I will investigate as I really
would like to improve the program performance. If binding will do that
then that's what I need to explore. Thanks much for sharing the
information!

ga

ga
nos...@nospam.fmctc.com

[CRPence]

On 21 May 2012 12:03, ga wrote:
> I have a program written in RPG/III (not ILE although I can easily
> convert it to be).
>
> It calls an SQLRPGLE program. The SQLRPGLE calls one other C/L
> program which simply tells me if a file exists or not.
>
> I am guessing if the SQLRPGLE didn't "end" every time it was called
> the mainline program would run faster...and probably the C/L program
> which checked for the file existence??

Does that imply the SQLRPGLE is getting called repeatedly by the OPM
program?

> But the other thought I had is that these should probably be somehow
> all tied together to make them more efficient.

Convert the first program to ILE and make the called SQLRPGLE run in
the same named [by explicit name, or *CALLER] activation group. If no
other OPM needs to call that SQLRPGLE as a program, there may be value
to changing that program to a procedure in a service program. IIRC,
avoid ACTGRP(*CALLER) if any OPM will call the program. Depending on
what the program does, there may be value in registering the program as
an SQL procedure, and making the first program SQLRPGLE to use SQL CALL
instead; or just using a CALLP if not worthwhile to have available to
other SQL work.

> If a file doesn't exist in an SQLRPGLE program in a select, will it
> return an SQLSTATE code that I can test so I wouldn't have to do the
> C/L program call?

Yes. As such, I would eliminate the separate call to a CL to check
for existence, and allow the SQL to notify with the SQLCODE -204 for
"not found". That way an OVRDBF can be used to redirect the program to
another file without some unintelligent CHKOBJ-like processing
preventing [breaking] the ability to use overrides; avoids having to add
to the CHKOBJ-like processing, some extra intelligence to process
overrides, if whatever is used does not offer that capability [e.g. like
QDBRTVFD does].

> Can anyone point me to the right direction on how to do this; i.e.
> what manual should I start with??

Karl's reply.

Regards, Chuck