Re: Displaying the contents of QSYS2.SYSTABLES.TABLE_TEXT

Hi Justin

To clarify - a label is not a term we normally use for anything in the database on TBM i, and I think there's some confusion about that, since SQL has the LABEL ON statement to do different things.

The TABLE_TEXT field is the text description of a table - that one is a 50-character value that you would see in WRKOBJ FOO/*ALL *FILE

If it makes sense, I suggest forgetting the term "label" - I know it's in the long name, but note that the long name starts with TABLE_TEXT - so it is a TEXT label - or, in other words, a TEXT description. Of course, maybe I should get used to using "label" more - would have to see if that's more common in the general database community.

Note also that it is 50 long - and if your system has its QCCSID set to 37, you should not need to do the CAST, either.

Try adding, just for fun, another condition in the WHERE -

and table_text <> ''

I ran your SELECT over a library, and there was text in TABLE_TEXT - but not for all the objects.

I also ran it in the new Run Sql Scripts in Access Client Solutions, and the text descriptions were there.

I don't think green screen or run sql scripts or anything else will matter. It's all SQL.

Are the LABEL ON statements generated for the table, or for the columns IN the table? Just a random thought.

HTH
Vern

On 12/17/2015 10:14 AM, 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'


That's actually inside a CTE (window function) and the main query generates
some GENERATE_SQL() calls. I hope to post a polished version of this later.
TABLE_TEXT seems to always become an empty string, but if I run
GENERATE_SQL() on these tables, a LABEL statement gets generated.

The docs say the following about TABLE_TEXT:

TABLE_TEXTLABELVARGRAPHIC(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?

Justin