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