Re: sysibm.sqlcolumns column_text Corrections

LOL - I guess I don't buy your analogy about C and RPG, but so it is.

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.

I recommend using SYSCOLUMNS, because it gives one everyting SQLCOLUMNS does and more.

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.

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'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.

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.

Regards
Vern

On 12/1/2015 4:25 PM, CRPence wrote:

On 01-Dec-2015 10:55 -0600, Vernon Hamberg wrote:

On 12/1/2015 8:43 AM, CRPence wrote:

On 30-Nov-2015 16:56 -0600, Rob wrote:

<<SNIP>>

[ COMMENT ON COLUMN schema.table.column IS "Field Comment" ] Runs
in System i Navigator without an error.
[ LABEL ON COLUMN schema.table.column IS "Field Label" ] also Runs
in System i Navigator without an error.

But neither change COLUMN_TEXT.
<<SNIP>>

The former statement should have changed the REMARKS. The latter
statement makes changes that are not visible\manifest in the data
available from the SQLCOLUMNS VIEW [unless the VIEW is customized,
or has been updated by IBM to reflect a newer ODBC [than 1.0]
specification.

Try instead:

LABEL ON COLUMN schema.table.column TEXT IS 'Field Text'

Or [better IMO], enabling one statement to modify multiple
columns, use the following syntax instead:

LABEL ON COLUMN schema.table
( column1 TEXT IS 'Field Text'
, [...]
, columnN TEXT IS 'Column Text'
)

<<SNIP>>
You may have seen my post where I recommend NOT using SQLCOLUMNS,
rather, to use SYSCOLUMNS.

Yes. Both you, and Buck. But that could be somewhat like saying "Use C" to an RPG programmer; I do not recall clarification by the OP as to why one is being used vs another, or if any followup ruled-out or confirmed that was an option for them.

I suggested another approach [though I would not recommend vociferously], to customize that VIEW. or to ask for a clarification or possible /correction/ regarding what the SQLCOLUMNS VIEW offers, because the ODBC specs were updated to include something more in SQLColumns() API than is apparently being included in the VIEW SQLCOLUMNS. That is, if there is a SQL_DESC_LABEL of ODBC that should correlate to COLUMN_HEADING but the SQLCOLUMNS VIEW provides only COLUMN_TEXT, then that VIEW could be enhanced to provide the first 50 characters of "LABEL" as COLUMN_TEXT when the latter is NULL, or even just provide the effective COLUMN_HEADING *while keeping* the remaining columns unchanged; i.e. the OP may have a reason to use SQLCOLUMNS, for what the SYSCOLUMNS does not provide, at least not manifest in the same manner.

Briefly,

LABEL ON COLUMN [column-name] IS 'Field Label'

update COLUMN_HEADING in SYSCOLUMNS - nothing in SQLCOLUMNS, which
knows nothing about column headings (the 3-part, each 20 long stuff).

Thus why an inquiry as to why SQLCOLUMNS does not provide what SQLColumns() provides in a newer specification, could potentially prove fruitful; barring or pending that, the VIEW_DEFINITION [aka TEXT] of the SQLCOLUMNS VIEW could be retrieved and then modified to include the equivalent of COLUMN_HEADING, from which a CREATE VIEW SQLCOLUMNS could be performed to replace the existing copy [and placed under system change management, to ensure the same action\customization is repeated whenever the object has changed since the last time that custom update was effected].

There is little reason I can see why SQLCOLUMNS could not be updated to return a separate COLUMN_HEADING [aka LABEL], thus reflecting the newer specification, or [the first 50 characters of] the LABEL if COLUMN_TEXT is NULL so as to satisfy the concept of the OP to get the information [possibly] with the least updates; i.e. just the LABEL ON COLUMN ... IS, vs both that and LABEL ON COLUMN ... TEXT IS:
varchar( TRIM(COALESCE(COLUMN_TEXT,"LABEL")), 50)

I did try a COMMENT ON TABLE, and that comment *is* stored in the
actual PF object - even a regular PF, not a TABLE.

Yes, stored in the *FILE space object.

And in the catalogs; DBXREM of QADBXREF, and manifest in some SQL catalog VIEW(s) such as SYSTABLES as REMARKS [aka LONG_COMMENT].

I also did a COMMENT ON COLUMN, and it is also stored in the PF in
the list of columns you see in a DMPOBJ output.

Stored in the separate *FMT space object; as a composite piece of the *FILE, rather than being stored in the *FILE space object.

And in the catalogs; DBIREM of QADBIFLD, and manifest in some SQL catalog VIEW(s) such as SYSCOLUMNS as REMARKS [aka LONG_COMMENT].

And in the Retrieve File Description (QDBRTVFD) API which maps [possibly somewhat directly] what was seen in the Dump Object (DMPOBJ) output, in Qdbfdict as the "Data dictionary file definition comment text", in Qddfdfct as the "Field definition comment text", and for what is irrelevant\unsupported for the SQL, in Qddfditx as the "Format definition long comment".

These "remarks" are not visible, however in DSPFD for the table or
in DSPFFD for the columns.

Correct. They were not deemed worthwhile to include there; too much text, although they should be available via the same interface and QPDSPFD and QPDSPFFD with 6=Print from the Work With Data Definitions (WRKDTADFN) of the Interactive Data Definition Utility (IDDU). The Long Comments [REMARKS] were available from the Data Dictionary (*DTADCT) files [physical file QIDCTP02, field Q02CTX; stored in multiples of 256 characters and possibly restricted to a /practical/ limit of ~1360 characters for viewing vs the actual limit of 2000 of the SQL REMARKS] when the COLLECTION was implemented WITH DATA DICTIONARY [or for definitions of an IDDU Data Dictionary], but since v3r1 the WITH DATA DICTIONARY is deprecated, and the preferred method to obtain the information is from the catalogs.

And as noted in past discussion, the F23=Long Comment of the Query/400 [IBM i Query] supports all three types [fil, rcdfmt, fld] of comments\remarks, and that [oddly] the SQL 5250 interfaces seem never to have bothered, despite the SQL being the only non-deprecated interface to the feature.