On 16-Mar-2012 11:33 , Steven Spencer wrote:
However, generic SQL lineage systems like Squirrel may or may not
have an easy way to handle this, each case must be examined
individually.
For any purely client-based [generic] SQL feature, there is likely
little to examine; i.e. almost certainly there will be _no support_ for
flat files. That is because most are likely wholly dependent upon the
SQL database to which they connect. For example, most likely being
dependent on the SQL catalogs to access the metadata. Unfortunately for
those who want to access /flat files/ ...
The SQL catalogs only provide details about "relational" database
files, thus "flat files" are not directly /visible/ to a generic SQL
client. That the metadata for non-relational files are not exposed via
the catalogs upon which a client SQL feature is likely dependent, would
render such a client unable to access the data, for not even knowing of
the existence of those flat files. Thus, per the "Subject:", most
generic utilities using SQL will simply *not* "see the field layout" for
the files; similarly likely for a 4GL that is purely SQL based.
A comparison of the data in the SQL catalog VIEW SYSTABLES with the
data as results from a DSPFD for the files in QS36F, to see if any or
perhaps just how few database *FILE names appear in the SQL catalog,
could be illuminating. For example, perform the following CL and SQL
statement in order, to generate a report showing which database files in
QS36F are "Not Available" to the SQL via the catalog:
DSPFD QS36F/*ALL *BASATR *OUTFILE FILEATR(*PF *LF)
OUTFILE(QTEMP/FDBASATR)
with /* query specific to prior DSPFD data from QS36F */
catdta (fn) as (select sys_tname from qsys2/systables
where sys_dname = 'QS36F')
SELECT ATFILE file, ATFTYP tp, ATFLS rel,
, ifnull(fn, 'Not Available') cat_file
FROM QTEMP/FDBASATR left outer join catdta
on ATFILE=fn and ATDTAT='D'
ORDER BY 2 desc, 1 asc
Only a client utility for which some very specific DB2 for i
server-based code exists, whether installed separately or created as SQL
[external] stored procedures created over the SQL connection, will be
able to access data from the flat files. And anything other than
read-only would be very problematic, requiring almost anything but the
SQL to enable, because of the limitations for access to the data in the
flat files via the SQL. That is because the SQL works effectively only
with /typed/ data fields\columns when dealing with database files, where
the base\primitive metadata comes from the external description stored
with a database *FILE created using either DDS or SQL DDL. The faux
metadata provided for database /flat files/ [e.g. via DSPFFD] is merely
canonical, rather than representative of the underlying data, to enable
exposing access path keys and record formats [e.g. via DSPFD].
Regards, Chuck
midrange.com
