Re: SQL data access through service program per file -- RPG400-L

If you are talking program or service program to service program you don't
need to return through things like SQL. Just break down into multiple
functions. One to load, another to retrieve one record, etc. In other
words, use ILE.

To talk to GUI or something you need stream interface.

On Sep 16, 2016 12:21 PM, "Jon Paris" <jon.paris@xxxxxxxxxxxxxx> wrote:

"Just to clarify, the service programs would also allow the retrieval of
result sets (multiple rows); so I guess it would not be purely CRUD
functions”

Just be sure that you either return the result set in a parm or (better)
use the new RTNPARM keyword on the return value definition. Without that
returning values of any size is a major performance hit.

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Sep 16, 2016, at 12:57 PM, Joni Vanderheijden <

joni_vanderheijden@xxxxxxxxxxx> wrote:

Thanks everyone, for sharing your knowledge. It's interesting to read

the different views on this issue, and the different ways it has been
implemented.

Just to clarify, the service programs would also allow the retrieval of

result sets (multiple rows); so I guess it would not be purely CRUD
functions.

Because I messed up in my original question, I'd like to clarify the

argument I proposed that the big performance gains are to be found in
joined SQL statements. When I'm talking about replacing program joins with
SQL statements working on multiple files (or views on normalized files),
I'm talking about the procedure outlined in the following article:

http://iprodeveloper.com/database/boost-program-

performance-using-open-access-rpg-rpg-oa

@Dave Clark: Could you recommend some reference material on the MVC

framework you described. I don't think I fully grasp that way of working
yet.

Kind regards,

Joni

________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxx> on behalf of Nathan

Andelin <nandelin@xxxxxxxxx>

Sent: Friday, September 16, 2016 3:34:34 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL data access through service program per file

A colleague of mine proposed to create a service program for each new
table, implementing the CRUD functionalities in SQL. We would be using

the

functions from the service program to access the files instead of native
RPG I/O.

For CRUD operations, have you considered wrapping RPG I/O in service
program procedures?

The arguments are that IBM is focusing its improvements on SQL, and
leaving native I/O behind.

I'd like pass along a quote from Jon Paris:

"I'm not a fan of "SQL is the answer... Now, what what the question?"

SQL was designed for set-based functionality while RPG I/O was designed

for

record-level access. Both are valid use cases.

Also this would separate data access from business logic and in time
create a database layer.

Good architectural point.

An other solid argument is that we would limit the need for creating

indexes, and could construct the appropriate access paths when

performance

is evaluated.

CRUD operations without indexes? No, I hope the fallacy of that is

obvious.

However, I have some doubts for this approach.

If you have doubts about SQL being the answer to CRUD operations, you're
not alone.

I fear that it would discourage developers to create programs starting

from

a joined SQL statements (program join), instead encourage sequential

read

operations from multiple files (program join).

When you need to join files, I'd recommend SQL Views and Join Logicals

over

program joined operations.

I feel this is where most of the performance gains can be found.

Additionally, I've always thought that native I/O is more performant in
retrieving a small amount of records from a file...

I've done some benchmark testing where RPG op codes performed 100% - 600%
more efficiently than SQL equivalents. Testing a foreign-key RI

constraint

using SETLL for example may perform 600% more efficiently than an SQL
SELECT.

I thought the service programs could lead to deterioration of

performance.

Actually, I would recommend wrapping CRUD operations in I/O service
programs for the following reasons:

1. Avoids the problem of "F" specs multiplying like rabbits and having to
recompile many modules when file layouts occur.
2. Avoids multiple open-data paths when multiple programs need access to
the same files in the same job.
3. Facilitates a standard interface for I/O error handling (appropriate
error messages).
4. Facilitates the principles of loose coupling and high cohesion between
DB I/O modules and application modules.
5. Facilitates code maintenance by appropriately scoping I/O service
programs.

In summary, the idea of encapsulating CRUD operations into service

programs

is good, but the idea of SQL being the answer to all I/O, is bad.

You asked about CRUD operations. Requirements for dealing with "result
sets" is a different matter, and something which SQL excels at.
--
This is the RPG programming on the IBM i (AS/400 and 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.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

--
This is the RPG programming on the IBM i (AS/400 and 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.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.

--
This is the RPG programming on the IBM i (AS/400 and 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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.