Re: Anyone using the *AFTER *READ Triggers in V5R1/V5R2?

 Oh wow...  Thank you.  This put some stuff in place for me.


---------------------------------------------------------
Booth Martin   http://www.MartinVT.com
Booth@MartinVT.com
---------------------------------------------------------

-------Original Message-------

From: midrange-l@midrange.com
Date: Wednesday, November 27, 2002 01:21:17 AM
To: midrange-l@midrange.com
Subject: Re: Anyone using the *AFTER *READ Triggers in V5R1/V5R2?

Booth:

As an initial disclaimer, I'll point out that the original question
specified V5R1/V5R2. UDFs (User-Defined Functions) may be much easier for
many to create on version 5 due to how IBM has changed what gets installed
with the SQL development kit and related items. Also, I am _not_ a UDF
development guru nor teacher, so anything I write will be over-simplified
and possibly wrong in detail at points; but others can correct and amplify
as needed.

On an AS/400, think of "tables" as physical files and "views" as logical
files. Therefore, an SQL 'CREATE TABLE' statement is a way of doing a CRTPF
and getting an externally-described PF without using DDS. Similarly, an SQL
CREATE VIEW' statement does a CRTLF without needing DDS. The DDS is more or
less replaced by using different clauses in the SQL statements.

If you create a "normal" LF with DDS, it's possible to create fields that
don't really exist in the physical database. You might CONCAT two physical
fields to create a virtual field that doesn't really exist. Or you might SST
a field to extract only a portion. Maybe you'd join an order detail file to
an item master file in order to pull an item description in when the order
details are read -- though the item description isn't actually kept in the
order detail records, the database has been told how to supply the item
description anyway.

Similar results can be had through SQL views and definitely functions, but
the results are far less limited. Essentially, by creating a function and
referencing the function in a view, you've created a more or less hidden
database program that supplies the value for one of the fields in a logical
file, just as CONCAT supplies a value. If you say that "CONCAT" is a builtin
function for DDS, then a UDF is a function you create yourself. An RPG
program that reads that view doesn't know that it's not really reading a
physical file and it doesn't care.

Beyond that, it turns into teaching SQL and UDFs; so that's enough. Go to
the the Info Center and just search on "udf sql" and you'll find lots of
stuff. Type some CREATE VIEW or CREATE FUNCTION statements into a source
member and execute them with RUNSQLSTM, or use OpsNav. If you have
interactive SQL, that's a better place to play with CREATE VIEW because of
the prompting help. (Hint: Use DROP VIEW to clean up after.)

Lots of fun stuff even if you can't find a use. Ever tried to create an LF
over another LF? No problem when you use a CREATE VIEW instead.

Just don't try to return a text value. (Joe, I'm kidding!)

Tom Liotta

midrange-l-request@midrange.com wrote:

> 13. Re: Anyone using the *AFTER *READ Triggers in V5R1/V5R2? (Booth
Martin)
>

> Tom, can you explain this a bit? I am a RPG guy and not too sure what a

>view" is, and have no idea what a UDF is. It seems important though.

>thanks.


--
--
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone 253-872-7788 x313
Fax 253-872-7904
http://www.powertechgroup.com