Re: SQL limited access to QS36F - how strong, how weak is the access ?

Hi,

 Steven Spencer wrote:

DDS source is easy to create, for flat QS36F data.  If the

situation > where the utilities read only the operating system
compiled DDS ...  that is more involved

....If they are iSeries utilities (Lansa, NGS, etc) they

addressed this > years ago, and they really do not care if it is
QS36F or DB2..... > If they are ported stuff, SQL-based, then you
have to check  utility-by-utility.  There can be problems. They may
read the OS  that only defines the file as one big field, (based on
BLDFILE or  File-name,Disp-new, and the key field as a divider.
They would have  had to develop an alternative, and may never have
bothered. ... If the HTML reporting only reads a DDS source member,
sitting in QDDSSRC, then I would simply put one there.

Chuck Pence

   The SQL can _access_ data from /flat files/ directly, but the
capability is very limited; difficult and unpleasant at
best.  There was [and I believe still is] no support to CREATE VIEW
over a flat file. The SQL will recognize only the /program
described/ definition; i.e. the BLDFILE version as presented by
DSPFFD, and will not redirect to the
File\Format\Field definitions of a linked IDDU definition nor to any
other separate dictionary\dictionary-like to dynamically describe
the data.  The data would be accessible to the SQL when the access
is defined by and to the SQL, as described data [columns], such as
with a User Defined Table Function.  But that UDTF definition alone would only
directly enable SELECT FOR READ ONLY access, so the file\data are
still barely accessible via the SQL.

=================================================

Steven Spencer

My main interest in special access at this time is the group of query
programs and 4GLs, native utilities, and SQL-lineage utilities.  Here
is how I see the four groupings (yes, I am omitting some SQL-oriented
development environments, feel free to add to and tweak or even
disrupt my groups).

a) 4GLs  - Lansa, Magic, WinDev, maybe EGL

b) iSeries utilities - Surveyor, NGS, MRC and more

c) DBVisualizer, Aquafold, AQT, Squirrel, JasperForge and BIRT/Actuate and more

d) iSeries SQL

What you say, I am going to guess, applies, in general, to

(c) and (d) , although I would especially like verification on (d)
and some of (c).  Remember that some of (c) are inquiry utilities
anyway, so the "barely accessible" is ok.

(a) from what I understand, generally gives a method in their
environment, although it may in some cases be a user defined table
that is not limited, in that environment. And if that is the nature
of the workaround, packed fields have to be "no problemo"

(b) is not really SQL and generally they have handled this question,
due to the iSeries lineag.

=================================================

Chuck Pence

   So while Nathan had suggested that "Before you can use tooling
to  generate mobile applications, your first step must be to
externally  define your physical files, and learn how to create SQL
views" is a stretch [hyperbole?], to enable the SQL access
generally and most easily effectively requires the alluded
conversion.  Surely that was the
intended point of that comment.

Steven
This is a little hard to follow. I think you are saying that first we
must have SQL access.

Chuch Pence

   FWiW, CQE-based utilities that would presumably perform SQL-like
query functions [Query/400 being one example] can access the IDDU
definition of a program-described file linked to an IDDU
dictionary. The integrated DB2 for i[/5 etc.]

Steven
Understood. And this is the pattern followed by NGS, which bills
itself as a superior "Query"

Chuck Pence

SQL was not enabled to do so, even  when implemented via CQE.  So
while some "iSeries utilities" could process SQL SELECT statements
to perform query activity against a "QS36F file", the application
may be implemented via the QQQQRY API using the CQE rather than
using the actual DB2 for i SQL.

Steven
As long as they, or anybody, gets it done, would there be any major
problem in this alternative ?

Thanks for the backdrop.  I realize that most people don't care too
much about this, since they went to DB2 long ago, and many of the
people using QS36F are intimidated a bit by the many options and
confusions.  I'm just trying to work it through.

Steven Spencer
Queens, NY

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.