Re: Displaying the contents of QSYS2.SYSTABLES.TABLE_TEXT

On 17-Dec-2015 13:48 -0600, Justin Dearing wrote:

On Thu, Dec 17, 2015 at 12:41 PM CRPence wrote:

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

<<SNIP>>

IOW, what /specifically/ was the generated statement?

LABEL ON TABLE SCHEMANAME.VIEWNAME
IS 'A long string of human readable english' ;

GENERATE_SQL generates 3 label statement, at least when I invoke it
the way I do. The one above, and two large ones that ad the label and
text values for the columns

What am I doing wrong?

Possibly nothing.

In this case I think you're right.

The data, in fact, may not _be_ in the catalogs.<snip/>

So to summarize, QSYS2.GENERATE_SQL() is looking in multiple places
for the label statement, And QSYS2.SYSTABLES.TABLE_TEXT does not.

Likely both features look the same place for the text; i.e. within the *FILE object. The latter however, *looked* [past-tense emphasize] sometime in the past when the file was created or changed or when the DBXREF catalog data was last refreshed, such that the data was stored in the row; the data is not retrieved from the object dynamically when the catalog is queried. The former would look at the *FILE on disk, thus dynamically retrieving the data from the object.

I tried to allude to the possibility that the TABLE_TEXT not being visible in the catalogs may have an origin in a defect; that the correction was to re-apply the label [I suggested using LABEL ON TABLE, but CHGPF TEXT() should effect the same, and what is the effect of CHGOBJD TEXT() I do not recall, but IIRC the /Librarian/ feature [that maintains the OIR of the generic object-types] invokes the Database [as object handler] when the OBJTYPE(*FILE) Object Type is of the Database File (DBF) specific variety of *FILE in order that the Database can do any /extra/ processing such as notify the DBXREF that the file TEXT attribute has changed. Thus the effect of each should be similarly reflected in the catalogs.

My mistake is once again assuming that DB2 for i is a perfect
abstraction of IBM i objects.

He-heh; Software. Rarely do I see the word /perfect/ used in such a context [i.e. software], and if so, my first thought would be hyperbole :-)

These files were in fact created with DDS.

The TEXT() specified on the Create Physical File (CRTPF) would have established the historical TEXT, and should have established an Object Information Record (OIR) TEXT() attribute to match. The catalog should have reflected the value near immediately [in the DBXTXT field of the QADBXREF file] in an asynchronous operation as applied from a dequeued entry taken from the QDBXREFQ in the QDBSRVXR job, If the value of the DBXTXT is reflective of neither the TEXT() stored in the *FILE itself or the TEXT() stored in the OIR [associated with the *LIB object in which the file is located], then that is clearly a defect.

The view I just created in DDL, did have its label show up in the
system catalog.
<<SNIP>>

Quite possible the old DDS file could have been tracked to the catalogs long ago, but when the label was applied, there was a defect that existed but since no longer exists; or that the label was obtained as an effect of a refresh of the *DBXREF data and there was a defect whereby the proper label was not reflected properly in the catalog but that defect no longer exists.

A duplicate of the file may or may not exhibit the same issue; if so, then probably a current defect is the origin, and if not, then the re-registration of the label for that DDS-created file should rectify the issue. Or the DDS-created file could be moved into an alternate library and then moved back; that may effect a total refresh of the catalog reflection of just that file, though there is a small possibility the effect is just an update to the key value [i.e. the reflection of the library name] in the catalogs, but if there is a refresh and the DBXTXT is updated, the effect of the operation will be conspicuous. And of course there are the *DBXREF refresh techniques; e.g. RCLSTG SELECT(*DBXREF) or RCLDBXREF to /reclaim/ [aka refresh] the data for the system\ASP or just for a library.