Re: Logical (or SQL view) imposing a format on a flat PF?

On 16 Apr 2013 14:27, James H. H. Lampert wrote:

I've been informed that we may be needing to create either a DDS
LF or an SQL VIEW imposing a format on an existing flat file.
I'm not personally aware of what data types might be involved,
so I'm assuming a worst case scenario.

I seem to recall that there are ways to do it, as well as ways to
use IDDU to impose a format on an existing flat PF. But I'm having
trouble remembering what does and does not work in this regard.

Can somebody point me in the right direction?

The best resolution would come from converting the flat file to an externally described file. Programs that access the file as a flat file should remain unaffected by the change.

LNKDTADFN can link an IDDU definition to a file, however the file remains program-described. The SQL does not use the /redirect/ feature like Query/400 does, to obtain the record format definition from the IDDU dictionary. The QQQQRY API has the option to redirect, as does the QDBRTVFD API, but IIRC, OPNQRYF does not redirect.

While a SELECT can be performed on a /program described/ database file, [unless something had changed since v5] the SELECT in a CREATE VIEW can not reference that same file. The CREATE fails diagnosing the file as "not a database file" with a reason code that says the file is program-described.

The concept of both DDS LF and SQL LF are such that they cast from [expressions involving] fields to define a field of some data type. There are only a specific and very small set of casting capabilities. There is no /overlay/ capability for either feature. A field in an LF can not be defined as the %sst of the raw buffer and then be designated [without mapping from an underlying implicit character type] as having the Packed numeric data type. And even though Zoned can come close to effecting that in effect, the stored negative values can not be represented; i.e. if that substring of data is not all valid decimal digit characters 0xF0 to 0xF9, the mapping from the "character" representation of a number fails. Thus unless the best case scenario can be assumed, that the /flat file/ is effectively all character data, then there is really little hope for using either a DDS LF or an SQL VIEW with a SELECT naming that program described file on the FROM-clause. Do not be fooled by the oft offered _false_ implications that a DDS LF will assist; at least do not be fooled that they are very much functional beyond the best-case scenario of needing to represent only character columns.

IMO the best bet for read access, assuming converting the file or copying the data to an externally described file is not an option, is to create an external UDTF for the flat file to expose the data to the SQL, and then that result table is exposed to anyone requesting access to the data via the SQL. An RPG program for instance, can just define variables to overlay the raw data to get their values. An SQL UDTF could be used in the same way, but the expressions in the SELECT can be ugly. Yet each likely-to-be commonly defined expression [e.g. selecting BCD Packed data from a substring of raw data being cast to Decimal\Numeric with the help of the HEX scalar] can be encapsulated as a UDF; and an external UDF, such as with RPG, can just overlay the data to return the typed data. Then, the User Defined Table Function can be encapsulated in a VIEW to make the UDTF for accessible; e.g.
create view myViewOfFlatFile01 as
select F.* from table( UDTF_FF01() ) as F