Re: Displaying the contents of QSYS2.SYSTABLES.TABLE_TEXT

On 17-Dec-2015 10:14 -0600, Justin Dearing wrote:

TL;DR: SYSTABLES.TABLE_TEXT is supposed to contain table labels. I
know this table has a label. I can't get STRSQL or sqlWorkbench to
display anything in that field.

I have a query like so:

SELECT
TABLE_SCHEMA AS OBJ_SCHEMA, TABLE_NAME AS OBJ_NAME
,CASE TABLE_TYPE
WHEN 'P' THEN 'TABLE'
WHEN 'T' THEN 'TABLE'
WHEN 'L' THEN 'VIEW'
WHEN 'V' THEN 'VIEW'
END AS OBJ_TYPE
,TABLE_TEXT AS OBJ_TEXT
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA='FOO'

Rather than saying "I know this table has a label", by what interface is that _known_?; i.e. was the LABEL ON TABLE just issued, or is the value /known/ per being seen in one of or each of: DSPFD, WRKOBJ, DSPOBJD, WRKF, DSPLIB, DMPOBJ, QDBRTVFD, et al.?

<<SNIP>>
TABLE_TEXT seems to always become an empty string, but if I run
GENERATE_SQL() on these tables, a LABEL statement gets generated.

OK, that seemingly could suffice as the reason the value is /known/. But "a LABEL statement" does not equate with "_the_ LABEL statement for the TABLE"; i.e. LABEL ON acts on many more than TABLE objects. IOW, what /specifically/ was the generated statement?

The docs say the following about TABLE_TEXT:

TABLE_TEXT LABEL VARGRAPHIC(50) CCSID 1200A character string provided
with the LABEL statement.
I tried the following, and still get no results:
CAST(TABLE_TEXT AS VARCHAR(25)), VARCHAR(TABLE_TEXT).
I've used sqlWorkbench, and STRSQL and the field is always blank. I
would figure the green screen would be more forgiving in this case.

What am I doing wrong?

Possibly nothing. The data, in fact, may not _be_ in the catalogs. The Database *FILE object has a /historical/ label [aka TEXT], and one that is stored in the *LIB object; i.e. the label [aka TEXT] that is available in the Object Information Record (OIR). While generally the current and historical values will match, there could be data in the *FILE but not in the OIR, or vice-versa; such a mismatch could manifest itself in the manner described, and would be confusing. The Dump Object (DMPOBJ) output would show both the OIR and the stored-value in the *FILE. There is also the possibility that, and possibly just for the one file, the manner in which the LABEL gets written to the catalogs is flawed; i.e. there could be a defect in storing the LABEL stored in the object into the catalogs, and a CRTDUPOBJ of that file into another [probably similarly named] library might exhibit the same issue... serving as an effective testcase demonstrating a defect.

Verify in the physical data [in file QADBXREF in QSYS] what is in the row with the unique key identified by the library name and file name; look at that row with CPYF OUTPUT(*PRINT) or DSPPFM, and use the output from DSPFFD to ensure reviewing the proper offset.

If the information is missing in the physical data for the catalog, then usually that can be corrected by issuing a new LABEL ON, to force that data to be recorded in the catalog; figuring out the origin for the originally missing data would be another matter entirely.