× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.