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.
Any SQL will be limited, effectively, to the "externally described"
database files; some program described files may be accessed reasonably
well using SQL [e.g. substr, hex, etc.], and some externally described
files may not be supported for SQL activity. Whether those files are
created by SQL, DDS, or IDDU is immaterial. Of those, only the IDDU can
create database files that have no external description stored with the
database *FILE object. When a file with no external description is
"linked" to an external description, linked to a File Definition stored
in an IDDU dictionary, the SQL will be unable to "redirect" to the
dictionary to access a Record Format Definition. For such files, the
SQL will only see the "un-described" data; the SQL and other
documentation often incorrectly implies just one field\column.
Query/400, the QU1 product [IBM i Query], supports redirection to the
IDDU linked definition; DFU and the QQQQRY API [via qdbqpgmd] do as
well, though I am not aware of what if any other feature shares that
capability.
Sure would be nice if there was an OVRDBF invocation that could
enable that redirection feature in combination with enforcing a
read-only restriction on the open [for native or SQL] and perhaps
necessarily "hiding" any keyed access path information; enabling some
very easy means to access and copy\migrate the data from those types of
files versus effectively having only the Query/400 available to do that.
For example, the ability to CPYF RCDFMT(one_fmt_of_MFPF) FMTOPT(*MAP)
would be much simpler than composing the logic for "record ID code"
selection on the CPYF command; defining a *QRYDFN for each format also
would be easier than that [because the selected format applies the Rcd
Id code selection as part of the query], but that is an interactive
utility rather than a simple command invocation, and the RUNQRY command
does not allow the specification of a Record Format name for a
QRY(*NONE) invocation :-(
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].
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.
Regards, Chuck
midrange.com
