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
midrange.com
