SQL is amazing was ( Avoid Level checks )

PS

Regarding this:-


|SQL is a great alternative with a lot of possibilities. Personally, I prefer
|the old style, because the use of data repositories for common redefinitions of fields is not as
|complicated as by SQL definitions.
|In this case is a DDS definition much more clearer and easier. It's not all golden at SQL.

I demanded my team that all and any SQL CREATE must be implemented as embedded SQL in a SQLRPGLE.
One program per file that was created via SQL of course there could be a CREATE and many CREATE INDEX per table as well as DROP for recreation..
It was tricky finding files that were created interactively, but we had a SOX audit procedure that mostly worked ok.
This was for documentation purposes and for recreate, and the SOX auditors were happy.
So now I had documentation as good as DDS. Btw did you ever verify the DDS source matched the actual file? Our auditors did spot checks.
There are many ways to skin that cat.

SQL is amazing, in the end, I could to things with a single SQL statement that otherwise needed a multi line RPG program with all the overhead of debugging etc etc.
For reports we ended up dropping all reports into EXCEL spreadsheets that executed SQL in Excel VBA scripts via ODBC connection to the AS400.
Fwiw the SQL  'WITH' is absolute magic.
Because the Excel reports failed SOX audibility we still produced paper reports that went to read only PDF archive, never printed, for the SOX auditors.


Frank

On 29/06/2022 7:31 am, Frank Kolmann wrote:

Thanks Brian

1. LVLCHK(*yes) must be used.
2. I have restored huge files. Unless it is a RSTLIB I put in a process to delete LFs, restore , recreate LF, rebuild still takes time but at least the Restore is done.
3. You need a deeper understanding of how a level check works. In your LGL you need to define all the fields the LGL needs.
The method I propose does not work if the LGL has no fields defined. Then when you recreate LGL (for those with defined fields) the Level Check passes ok.
4. The technique also makes keyed PF unnecessary.
5. SQL without the LGL/Indexes to support can be slower than a wet wick.

Frank

On 28/06/2022 8:53 pm, Brian Parkins wrote:

"Your proposed technique requires to compile the files with lvlchk *no"

Hmmmm ... unless I'm misunderstanding, I don't think so. That's the key point of Frank's approach.

Brian.

On 28/06/2022 07:34, LOGIC IT: Karl FRITZ wrote:

Frank,

I do not agree with you.

I use LF for reading and PF for updating only, even if I have to read the PF
a second time.
So I get a clear file handling at all.

Your proposed technique requires to compile the files with lvlchk *no.
Thought, I should mention it.

And each new LF requires maintaining of access paths. Have a lot of fun
restoring a huge file with
50 LF's.

SQL is a great alternative with a lot of possibilities. Personally, I prefer
the old style, because the use
of data repositories for common redefinitions of fields is not as
complicated as by SQL definitions.
In this case is a DDS definition much more clearer and easier. It's not all
golden at SQL.

But don't misunderstand me, I use both - native I/O and SQL.

Just my 2cts.
kf

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] Im Auftrag von
Frank Kolmann
Gesendet: Dienstag, 28. Juni 2022 06:32
An: rpg400-l@xxxxxxxxxxxxxxxxxx
Betreff: Re: Avoid Level checks was ( Generic RPG File Handler for File I/O
using OA)

I learnt too late, but this is how to avoid level checks.

In RPG programs never ever use PF, only ever use LF. (Perhaps change the
RPG compiler to enforce this?)

It is even possible to retro fit this concept to old code with a bit of
file renaming and recompilation of the PF and related LF.

Then never ever change LF DDS, only create new LF as needed.

To change PF you only ever add fields to the end of the field list, even
if it means data is stored in two fields in the case of field size
extension.

Recompile the PF and related LF and your programs will work fine. The
only programs you need to change is those using the new fields.

Fwiw
Frank


On 27/06/2022 3:19 am, Jon Paris wrote:

"So far I haven't found an example where I can see how to use them for SQL

CRUD operations.  It looks like it works great for RPG file operation
codes."

And you won't find any because that is not what OA was designed to do.  As

I said earlier it was designed purely to allow conventional RPG op-codes to
access devices not directly supported by RPG.

Jon P.

I developed a program to do all this using JSON request/response avoiding
dependency on a file structure.    This way If my RFID changes on a file

my

RPG programs will not give level checks:)