Re: SQL data access through service program per file

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.