On 01-Dec-2015 17:09 -0600, Vernon Hamberg wrote:
<<SNIP>>
My opinion, for what it's worth - ODBC is not going to have
something like column headings - those, as you know, are strongly
related to AS/400 and System/38, etc., reporting and maybe screen
design - things not even remotely considered when using ODBC, since
column headings are a presentation piece that doesn't really have
much to do with meta-data. AND it is specific to IBM i and its
ancestors.
Effectively true. Other databases operate mostly with just the
column name and REMARKS. But the DB2 variants *all have* a LABEL for a
COLUMN, and there has been confusion how that LABEL should be manifest
in the past; seems I have been somewhat confused, most recently,
probably based on my experiences from the past.
My confusion is related to the fact that a /LABEL/ is recognized for
describing result-sets, *in addition to* the /LABEL/ [aka COLHDG] for a
DataBase File (DBF). In review, I recognize now that I was conflating
the SQL_DESC_LABEL results from SQLColAttribute() API with the LABEL; in
part because I recall that LABEL was what the SQL CLI returned for those
DESCRIBE results, and what I recalled the CA ODBC driver would return
for an SQLColumns() API request for the REMARKS data when there is no
LONG_COMMENT defined. I recall the effect for the SQLColumns() API was
something like what COALESCE(REMARKS,LABELTEXT,"LABEL") would return
from SYSCOLUMNS. And I recall something like that was done
specifically, to avoid the pervasive issue of column-list requests being
_barren_ of descriptive text. Notably, such an effect is\would-be quite
valuable [for the DB2 for i] because the effect of COMMENT ON is not
something the DDS can effect, and thus few existing DDS-defined
files\file.columns ever would have had any REMARKS *unless* the SQL
statement COMMENT ON was performed sometime after the CRTPF|CRTLF; i.e.
effectively no DDS file on the system would ever present anything but
the [system\short] column name, making the column-list requests somewhat
unusable, given most files also had no ALIAS defined. And because most
people going from DDS to SQL try to mimic their DDS files, they likely
[e.g. by Generate SQL] have the LABEL ON (TEXT) being performed, but
never added COMMENT ON statements, so often the SQL files would be
devoid of REMARKS as well.
So although what I recall may not be exact, I would be surprised to
find that something was not provided to enhance the effect; and if
nothing is provide now, I suspect the newer effect would be a reversal,
because I am confident something was done in the past to overcome DDS
files being presented descriptive text that was nothing more than the
10-byte name.
I recommend using SYSCOLUMNS, because it gives one everything
SQLCOLUMNS does and more.
I am not so sure, at least not exactly. At least the extra join
activity in SQLCOLUMNS must be doing something not being manifest in
SYSCOLUMNS.? And adding all the CASE expressions to get the modified
presentation of what SYSCOLUMNS offers, would either need to be
specified on each query of SYSCOLUMNS or encapsulated in a VIEW anyhow
[to get what SQLCOLUMS most clearly offers differently]. So one point I
was making was, why not just update SQLCOLUMNS to give what is desired,
if SQLCOLUMNS already has everything else desired; i.e. if the OP is
querying that file purposely.?
Now there may be some thought as to what is to be used when talking
from other systems. An ODBC driver will likely go to SQLCOLUMNS
catalog tables - you have more experience in the database world so
far as that goes, I believe, from your work at IBM.
A generic driver AIUI, would query the SQLCOLUMNS VIEW. And FWiW,
clearly beyond the ODBC spec, that VIEW includes COLUMN_TEXT; being the
26th column, thus after the 18th column as the final ODBC spec'd column,
that is customized to the DB2 for i. Again, one point I made was
SQLCOLUMNS could be modified such that COLUMN_TEXT could be /enhanced/
to coalesce data from LABEL when there is no LABELTEXT, or simply by
adding a COLUMN_HEADING column; i.e. if COLUMN_TEXT is there but not
part of the spec, then why could someone not customize [or ask IBM] to
also add COLUMN_HEADING?
As to documentation - the SQL Reference is clear about the COMMENT
ON and LABEL ON statements - maybe the documentation on SQLCOLUMNS
and SYSCOLUMNS doesn't match that - is that the case?
I do not recall seeing anything /wrong/, just that the COLUMN_TEXT of
SQLCOLUMNS did not mention the origin as the LABEL (TEXT) statement; and
that the short\system column-names are undocumented, so for example
COLUMN_TEXT as equivalent\ALIAS FOR LABELTEXT means the latter is not
search-capable in the docs. Despite the missing mention of LABEL as
origin, given the COLUMN_TEXT of SQLCOLUMNS is derived from SYSCOLUMNS,
redirecting to the SYSCOLUMNS docs for the COLUMN_TEXT of that VIEW does
show the snippet of info suggesting the origin is the LABEL (TEXT)
statement.
I've said this to the OP in these lists, so I feel it's OK to say
here again - it seems the OP is coming from a completely non-IBM i
perspective. That's why I keep pressing him to look at IBM i
documentation, not DB2 LUW, especially, which can get us really off
the wrong track.
But IBM i docs are not generally helpful directly, when using a
client that is not using an IBM i provided driver; not to imply they are
any more helpful when using that ;-) I would think the client app docs
would need to be consulted first, and then the IBM i docs in
conjunction, and\or in conjunction with ODBC specs, for anything to
which the client docs defers to any server or ODBC implementation details.?
Of course all but the initial responses that clarified the two
variants [i.e. (TEXT IS) and (IS)] of LABEL ON for columns, likely are
of little value to the OP; likely any of our followups are not being
read anyhow, because they do not directly answer anything not already
answered by those earlier replies.
Having said that, in terms of openness, there is validity in his
approach and expectations, so far as ODBC-ish things are concerned,
and there is a challenge there for IBM.
Given many are using ODBC to connect without the concerns of the OP,
the challenge seems not so much to be for IBM.? AIUI the concerns are
the lack of descriptive text associated with the files on the system the
OP is using; and since the initial replies, likely the OP has been able
to ensure that the "admins [have] the ability to update the Remarks and
Texts to better describe the tables and fields" just as was apparently
the expressed goal.
midrange.com
