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:
TABLE_SCHEMA AS OBJ_SCHEMA, TABLE_NAME AS OBJ_NAME
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
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.?
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.