On 1/22/11 7:03 AM, Joe Pluta wrote:
On 1/22/2011 7:25 AM, Vern Hamberg wrote:
You're on the same track as I. Yeah, multi-format logicals can't
be processed by SQL. Multi-member files can, if you either OVRDBF
to one of the members or if you create an alias - the latter is an
iSeries-only thing to get around how SQL knows nothing about
members.
The SQL ALIAS is not unique to DB2 for i. The ALIAS can refer to
something other than just [the data from] another TABLE; that, and the
corresponding extension to the CREATE ALIAS syntax however, may be
specific only to the IBM i.
Actually, I wish that IBM had treated members as the actual table -
they do that with path names in FTP, where you don't PUT a FILE,
you PUT the member. Alas!
Indirectly the SQL TABLE does just that, for the inability to have
more than one member [except conceptually and by implementation,
partitions as members\data within the scope of the TABLE].
But that wish ignores the heritage upon which the TABLE is founded,
of which the integrated nature of the database with the OS is so
important to the value. Having given in to such a wish for the design,
there would have been little incentive for the DB2 to have provided the
SQL as anything other than a separate world from the OS as with other
databases; i.e. only by CONNECT statement of SQL could gain access to
the data. With that design, there would be no ability of the FTP to
even know-of\see the SQL TABLE, and thus unable place data into the
[member of the] TABLE.
By an alternate wish to instead attempt some integration, perhaps by
having the SQL TABLE as a new file type or file attribute and having
special treatment entirely separate from the original database *FILE
type. That would have increased significantly the cost to provide the
support for the new object type or file attribute, with any reasonable
fit to the already existing operations of the OS; e.g. add various
separate but equal integrated features like GRT, DUP, SAV. Probably
would have been easiest to not support most of the native data
management like open, utilities like CPYF, nor even SAV\RST, etc. and to
have left just the CQE for *FILE and having started with a fully SQL
modeled means for data access. But again with such limits, the obvious
answer would have been to just keep the separation complete, and just
have an add-on database providing for SQL-only access instead of an
integrated database providing non-SQL access; i.e. why enable anything
like SAV\RST when there is no such thing\statement in the SQL?
FWiW the TCP/IP FTP is a means to transport binary or text *data*,
not objects. A "FILE" is not PUT on any other OS, just as the "MEMBER"
is not PUT on the IBM i. In either case, the "data" is PUT; not the
file, not the member. Since the member "contains" the data on the IBM i
for the /QSYS.LIB file system, the path used to define the source and
target for the PUT of the data is to the member; i.e. viewed from the
FTP, the *FILE serves mostly as a portion of the path to the member with
the data plus a *RCDFMT common to all members in the file which defines
record length and [perhaps] how the data must align.
So... the *FILE is an effective equivalent to the *DIR because each
is a container and maintains some shared attributes for all entities
within. The *FILE as directory is sure to contain only "files" with
length-defined "records", whereas the *DIR contains "files" with any
variety of binary streams without any regard to "records" or possibly a
text stream with any [or even no] means to define the "records".
Trivia tidbit of the day - when you create an alias to a member,
the i does not create a logical file; it creates a DDM file.
Very interesting!
That the ALIAS is *implemented* as a DDMF is of little interest,
since for the SQL from a user perspective, only the entry in the catalog
is of any consequence. Dependence on the existence of a DDM device file
to represent the SQL ALIAS would be a dependence on an implementation
detail; and of course, implementations are subject to change. FWiW, and
although there is no support for so doing, the *LOCAL DDMFile can be
changed via CHGDDMF to make that *IP defined DDM device file function
via native database access [e.g. CPYF, READ] to address the same local
member that the SQL would access when referred to as an ALIAS; that the
DDMF is not created originally with those attributes as the effect of
the SQL CREATE ALIAS seems to emphasize the conspicuousness of the
nature of the existence as a device *FILE, only as some means for
implementation versus as any requirement for actual function.
Regards, Chuck
midrange.com
