Re: 5722-QU1 Question - tools for query-style function ?

On 23-Jan-2012 18:14 , Steven Spencer wrote:

On 22-Jan-2012 18:17 , CRPence wrote:

On 18-Jan-2012 02:56 , Steven Spencer wrote:

In each case I would be curious if there are any "gotchas" in
trying to access QS36F data, after you make up some IDDU or DDS
or special source representation. If the product wants the "true"
DB2 database, then I could compile and copy files over to DB2 for
testing purposes.

(snip a bunch)
To access the S/36 database file data stored in "program
described" files [in QS36F or any library; i.e. not just S36EE
"Files" libraries] from the SQL, the data would need to be exposed
either via "externally described" physical files or by SQL [most
likely only or mostly just] "external" routines such as User
Defined (Table) Functions [UDTF].

Am I correct in thinking that once you do the handwork of the User
Defined Table Functions (which I have never done, but sounds like
some type of normal file spec thing, as long as stuff like packed
data considerations do not intervene) that then you are running in
a functionally equivalent mode as if you were using DB2 files with
source descriptions compiled ?

Yes. The CREATE FUNCTION describes the effective Record Format for whatever data will be returned, as row-data, when the routine is invoked. This is defined by SQL for the use by SQL. A[n external] program which implements the function gets the data from wherever, and then forms the row-data as declared by the column list of the RETURNS TABLE clause of the CREATE FUNCTION.

The UDTF is something like a logical view of the data, for providing a Record Format to some data. Instead, providing a described view of a copy of some physical data, as a TABLE().

Unless something has changed, a VIEW still can not directly enable a definition of a column list over the program described file data, because the SQL does not allow CREATE VIEW over a program described file. However just about any function other than IDDU linking [or the utilities that may rely on IDDU linking; QRY and DFU] should not care if the program described file were to be replaced by an externally described file; the data copied to the new file using direct-map [CPYF FMTOPT(*NOCHK)]. Care would be required, to maintain any key fields for that transition. VIEW(s) then could be created over that other file to get the data; using scalar UDFs or various [often complex] expressions that would convert the data from raw\internal to a specific type.

An effective script as a very simplistic example:

<code>

crtpf qtemp/raw rcdlen(8) /* instead of BLDFILE */

insert into qtemp/raw values(x'00155FF0F0F1F5F5')

create view qtemp/rawvw (p50, c5) as
(select dec(left(hex(left(raw,3)), 5), 5, 0)
, right(raw,5)
from qtemp/raw)
-- RAW in QTEMP not valid for operation.

create table qtemp/rawddl (raw char(8) for bit data not null)

cpyf qtemp/raw qtemp/rawddl frommbr(*first) tombr(*first)
mbropt(*replace) fmtopt(*nochk)

create view qtemp/rawvw (p50, c5) as
(select dec(left(hex(left(raw,3)), 5), 5, 0)
, right(raw,5)
from qtemp/rawddl)

select * from qtemp/rawvw

-- report from above select:
....+....1....+..
P50 C5
155 00155
******** End of data ********

</code>

No matter what is done to migrate data to enable a query interface
that uses the SQL, be sure to correct any decimal data problems
which are generally pervasive for applications that originated or
mimicked the behavior of the S/36 programming. Moving to SQL DDL
will "force" programs to operate correctly [failing if they do
not], but that may not be desirable if the applications have not
already been reviewed and recompiled to prevent writing bad decimal
data.

This is similar, I think, to the issues when going from QS36F to
DB2. A lot of flakey forgiven QS36F data can come back to bite you,
and you might have to clean up stuff, maybe blank fields, or unused
fields poorly represented.

The same, AFaIK, to what I allude.

Similarly, when I ported my programs from SSP to S36EE, I ran into
DDM (Disk Data Management) tightening up the doubly-defined RPG
programs by giving error messages if they did not close one
file-record, while it was being accessed by a second
file-same-record. A funny little gotcha that was poorly documented,
but of which IBM was quite awares and let me know when I called.

Not sure about that issue; Distributed Data Management? But, I suppose, some nuances always arise and must be dealt with for most transitions.

So if I have to tighten up some data, I don't mind. It is like
preparation for DB2, and par for the course. I know the programs
and data well, so it is sort of routine easy-work.

If the data and programs have already been updated to resolve and prevent the decimal data errors, then using DDL versus DDS is preferable. When defining the data to be processed by the SQL in scalar UDF or table UDF, that the data is proper will be required just as with DDL. If a UDF does not have to "cleanse" decimal data, the code can be much more succinct.

So .. do I understand sort of properly ? An SQL-oriented utility
program will have a user-defined capability that it can point to
that is similar to F & I, IDDU and other such files specs ?

If I understood the question...

User Defined [table and scalar] Functions, created with and defined to the SQL, the [utilities based on the] SQL can then access data that is defined or resides outside of the SQL, or is just less structured than the SQL requires. However there is no simple capability to direct-map from an untyped number of bytes to a type like RPG and IDDU can, so IMO the "similarity" is somewhat strained; i.e. there is no ability to, like with the data in the example script above, to simply declare the file [dynamically] as being "(P50 DEC(5, 0) NOT NULL , C5 CHAR(5) NOT NULL)" like both the IDDU and RPG allow.

Regards, Chuck