Re: Object Last Use Date MDY compare issue

On 12-Feb-2015 15:19 -0600, Glenn Gundermann wrote:

I just tried using Object_Statistics and found the OBJLONGNAME showed
the system name and not the SQL name for one of my tables.
The table was originally created with no SQL name and then re-created
with an SQL name.

If a TABLE has a long name [visible both in Display File Description (DSPFD) output in USEnglish as the Alternative File Name, and via SYSTABLES as the value of either of the column names TABLE_NAME or NAME] when the invocation of the OBJECT_STATISTICS() gets made, then that name should appear for the OBJLONGNAME column. The value for ObjLongName from that UDTF should be effectively the same output [excepting perhaps delimiters] as what either the DSPFD or a query of the SQL Catalog VIEW SYSTABLES would have presented as the long name. Any other result is, with high probability, an apparent defect.

The underlying implementation of the UDTF is presumably, effectively just an invocation of the List Objects (QUSLOBJ) API; a trace might reveal more specifics of the implementation. AFaIK that API does not obtain the SQL long-name. The retrieval of the long name could be performed as a second phase of a two-phase process. If so, that would allow for a window of time over which differences may be visible for the object, between the two phases; i.e. the lock on the object could be held separately for each of two retrievals, versus one lock held across both :-(

Lacking a time-line for the described [re]create and activity, one might presume that the invocation of the UDFT was performed well after the creation of the TABLE with the new\expected name.?

Do I have to refresh the statistics somehow?

Yet given that question, one might presume the invocation was made at least once prior to the file being re-created with the long name, such that the result was valid for a previous invocation but a later invocation after the re-create still shows the old information.?

My SWAG is that, except what caching was effected by the user request, the UDTF will re-retrieve all of the information on each invocation, so a new invocation of the UDTF should present\relfect the information that was available from the object at the time of the invocation. Thus probably, unless the coded SQL is accidentally using [fetching (first+next) from] a previous cursor that was not closed, the implication of incorrect output is describing what is a defect. The results from new invocation in both a different job and the same job but after the activation group is reclaimed [presumably the object_statistics runs with *CALLER] could be tested for the possibility that a cursor may not be getting closed but the code assumes a new cursor is getting opened.