Re: _cipher API vs Qc3Encrypt/Qc3DecryptData vs SQL

Chuck,

A VIEW w/ a UDF and a trigger have different dynamics. A major one is that a VIEW requires explicit use by the requester. A trigger fires regardless of the data access method. This is a very important distinction.

As to your question "What does it mean to have a DSPPFM which returns data that is not really what is in the dataspace?", to paraphrase playground lingo "well, IBM started it!" How are timestamps stored in the DB? DSPPFM etc. handles that conversion just fine. Plus, if my job's date format is changed I will get a different format appearing on the screen. So it may not even display in the same way that it was originally written to the data base! This is a desirable behavior - because we want our data in usable form - even though the DB is "lying" to our applications.

The arguments you are making can similarly be made with write triggers, and even UDFs. Data can easily be scrambled using either method. Since all methods being discussed run in the same job stack, they are all just as "debuggable" as the main application program.


-mark

At 3/27/08 11:02 PM, you wrote:

It is fine to desire a way to manipulate the output of a database
file, and enhancements are a great way to provide that. However to
argue that it is too difficult to use UDF as the correct tool for the
job is little different than the many programmers implying that APIs are
too difficult to implement; that something /simple/ needs to be provided
instead. Please keep in mind that a UDTF [User Defined Table Function]
is a form of a UDF. Also in V6R1 there is derived key support & column
list on INDEX, so much of anything that was missing for legacy
applications is now moot; i.e. a VIEW was incapable of replacing a
legacy file, only when ordering was implicit. But even that is possible
prior to V6R1, with OPNQRYF to replace an ODP as a query of the VIEW.

>> The Read Trigger is used for auditing what was passed to the program
>> that issued the read request. If the Read Trigger could change the
>> data, then what was passed as data to the trigger for audit does not
>> match what was sent to the program; function defeated.
>
> I do not agree however with restricting read triggers to only
> an audit capability.
>
> Database may have viewed read triggers as initially providing an
> audit trail, but that should not preclude enhancements to allow
> improved functionality (updating the buffer in this case). And I
> certainly don't agree with precluding enhancements due to previous
> serviceability considerations.

An enhancement to enable a variation on the Read Trigger concept to
enable writing whatever is desired into the buffer may seem worthwhile
as an alternative implementation to UDF, but IMO only as an entirely
different kind of trigger; *if* even that were its implementation.

I think in general there is probably already good ways to accomplish,
without some form of a trigger program manipulating what is effectively
a program described buffer. What does it mean to have a DSPPFM which
returns data that is not really what is in the dataspace? A CPYF or
CREATE TABLE AS SELECT where the data in the target file is not what it
was in the file from which the data is being copied? A Read Trigger
applies to every row in each case. Sure a CASE statement, CAST, any
expression, or a UDF in the SELECT can change the data in the latter
example, but that was *requested* in the SQL itself; i.e. the /program/
requesting the data got what was asked versus receiving subversively
modified data by a change-capable Read Trigger -- where /subversive/
refers to without the knowledge of the HLL program[mer], where the
compile may even have been against the file long-prior to such a
rewriter of input to the program having been added to the file.

The program which requests my Era date UDF by Era(C) knows it is
getting back an adjusted Era date according to parameter C, instead of
the Gregorian date. If instead the Era date function became part of a
read trigger which could change the data, the program that does not
request Era date, while code to\expecting the Gregorian date is going to
effect complications and many /usage/ problems. And for the example of
that buffer rewrite for CPYF or CREATE TABLE AS SELECT... same dilemma.

>> Also IMO IBM would almost have to refuse to ever investigate any
>> problem where any Read Trigger was involved [if ever changing the
>> buffer were allowed], except under a paid consultancy. Non-SQL
>> trigger programs already were costly, to the point where many
>> inquiries as suspected defect were effectively forced through
>> ConsultLine before being /proven/ not to be a usage error; in my
>> experience over 95% were usage problems.
>>
>> At least UDF enforces well-defined structure\typing whereas a
>> read trigger could rewrite the output from the following request
>> to return "A BUNCH OF GARBAGE" instead of "SIMPLE", either on
>> purpose or by an error in coding:
>> SELECT VARCHAR('SIMPLE') FROM SYSIBM.SYSDUMMY1
>
> If there was a high amount of CPS activity (with modified buffers
> related to update/insert triggers) that were identified as user
> defects then, to me at least, that indicates that the provided
> documentation was insufficient. One does not accidentally fall
> into using and modifying the buffer provided to a trigger program.
> The user had to have done quite a bit of research into how a trigger
> should work. I don't know how many user problems were related to
> incorrect coding of update/insert trigger buffers, but the fact that
> the original example programs provided by IBM were coded incorrectly
> (not using the provided buffer offsets) undoubtedly had an influence
> on past CPS activity.

The problems for user-coded triggers were not even so simple, yet not
even that complex most of the time. But it is all moot. The point is
the inherent problem with a program receiving data that was not
requested! That will end with exactly the case of /accidentally using/
scenarios, reported as the dreaded "Incorrect Output" which implies
"Data Integrity" which implies "highest priority" aka Sev-1 and
"Critical Situation" which means more man-hours and most costs.

To have a program receive any modified data can break the program in
an uncountable number of subtle and extreme ways. If the Read Trigger
were change-capable and changed a[n effective] key value, then the
program which depends on the key could be broken; i.e. *functionally*
the program may return incorrect output according to the rules of the
program. If the Read Trigger changes the CCSID of the data and the
program does CCSID conversion it may be manifest as substitution
characters, serious mapping errors, and worse. When a UDF does wrong,
rather than the HLL program /tripping/ on the garbage-in, the function
is _there_ and called out as a more obviously the probable origin.

> I also have a concern with restricting new function due to the
> expense required to identify an underlying problem (using
> ConsultLine in your example). This suggests to me that insufficient
> serviceability tools were provided to Level 2 in order to quickly
> determine root cause. As with insufficient (or incorrect)
> documentation, insufficient service tools should not be a reason for
> precluding further enhancements in any area of the system.
>
> Providing sufficient documentation and serviceability should be built
> into the sizing for allowing read triggers to modify the data buffer.
> They should not be used to preclude allowing read triggers to modify
> the data buffer.

Again I suggest... It depends. If the costs to make the function
sufficiently serviceable to prohibit the function from being made
available, or the costs to service without that added serviceability
make the feature too costly to support in the normal cost structure,
there surely is cause to pick a different model for support [of that
feature]. But the real point is that offering the requested function is
far from appropriate for its ramifications. IMO it would be like [but
not as bad; just radical example] offering a bit randomizer that could
be run against spool data, and having included as part of OS support
cost structure for analyzing which spools were affected, potentially
months after the use of the randomizer by which time any logging as part
of the serviceability were long since gone. Do not give them the gun to
shoot themselves, and hope they recognize the folly in wanting to have
the gun, when they are probably just going to accidentally shoot
themselves. To be clear, it is because it is the _data_ that is
involved directly, possibly being modified as an effective lie to the
recipient of the requester of that data. IMO it is just a bad idea.

When the requesting program wants and asks for the modified data,
only then provide massaged data.

Regards, Chuck