OBJECT_STATISTICS table function

The OBJECT_STATISTICS table function returns information about objects in a library.

Authorization:
  • If the user has *EXECUTE authority to the library, and both *OBJOPR and *READ authority to an object, full details are returned.
  • Otherwise, partial information is returned along with an SQL warning of '01548'.
Read syntax diagramSkip visual syntax diagramOBJECT_STATISTICS(library-name ,object-type-list ,OBJECT_NAME => object-name )
The schema is QSYS2.
library-name
A character or graphic string expression that identifies the name of a library. If the library's name is a delimited name, the delimited form of the name must be specified. It can be either a long or short library name.
The following special values are allowed for library-name.
*ALL
All libraries.
*ALLUSR
All user libraries in *SYSBAS and the current thread's ASP group.
*ALLUSRAVL
All user libraries in all available ASPs.
*CURLIB
The job's current library.
*LIBL
The library list.
*USRLIBL
The job's current library and the user portion of the library list.
The following special value is allowed for library-name when object-type-list is '*LIB' or 'LIB'.
*ALLSIMPLE
The fastest approach to retrieving all user and system library names in *SYSBAS and the current thread's ASP group. Values are returned for the following columns: OBJNAME, OBJLONGNAME, and OBJTYPE. All other columns return NULL.
object-type-list
A character or graphic string expression containing one or more system object types separated by either a blank or a comma. The object types can include or exclude the leading * character. The special value of '*ALL' or 'ALL' can be used to return all objects in the library library-name.
object-name
A character or graphic string expression that identifies the name of an object or a library. If the object's name is a delimited name, the delimited form of the name must be specified. It can be either a long or short object name. The name must be the valid system name for the object unless the object is a file or a library; for files and libraries the SQL name can be specified.
If this parameter is specified, only objects with this name in library-name corresponding to the object types in object-type-list are returned.
If this parameter is not specified, all objects in library-name corresponding to the object types in object-type-list are returned.
The following special value is allowed for object-name.
*ALLSIMPLE
The fastest approach to retrieving the system names for objects in a library. All objects in library-name corresponding to the object types in object-type-list are returned. Values are returned for the following columns: OBJNAME, OBJTYPE, and OBJLONGSCHEMA. All other columns return NULL.
The result of the function is a table containing a row for each object with the format shown in the following table. All the columns are null capable.
Table 1. OBJECT_STATISTICS table function
Column Name Data Type Description
OBJNAME VARCHAR(10) System name of the object.
OBJTYPE VARCHAR(8) System type of the object.
OBJOWNER VARCHAR(10) The user profile that owns the object.
OBJDEFINER VARCHAR(10) The user profile that created the object.
OBJCREATED TIMESTAMP Timestamp of when the object was created.
OBJSIZE DECIMAL(15,0) Size of the object, in bytes.
OBJTEXT VARCHAR(50) The description of the object.

Contains the null value if the object has no text.
OBJLONGNAME VARCHAR(128) The SQL name for the object.

For an external procedure or an external function, the name will be returned when a single procedure or function exists for that *PGM or *SRVPGM object.

Contains the null value if an SQL name could not be returned.
LAST_USED_TIMESTAMP TIMESTAMP The date the object was used last. The time portion of the timestamp will always be 0.

Contains the null value if the object has never been used.
DAYS_USED_COUNT INTEGER The number of days an object has been used on the system.
LAST_RESET_TIMESTAMP TIMESTAMP The date when the days used count was last reset to zero. The time portion of the timestamp will always be 0.

Contains the null value if the days used count has never been reset.
IASP_NUMBER SMALLINT The auxiliary storage pool (ASP) where storage is allocated for the object.
OBJATTRIBUTE VARCHAR(10) The attribute for this object's type, if any.

Contains an empty string if no attribute.
OBJLONGSCHEMA VARCHAR(128) The SQL schema name for this object.
TEXT VARGRAPHIC(50) CCSID 1200 The description of the object, in CCSID 1200, for *LIB objects.

Contains the null value if OBJTYPE is not *LIB.
SQL_OBJECT_TYPE VARCHAR(9) The SQL type of the object. Values are:
  • ALIAS
  • FUNCTION
  • INDEX
  • PACKAGE
  • PROCEDURE
  • ROUTINE
  • SEQUENCE
  • TABLE
  • TRIGGER
  • TYPE
  • VARIABLE
  • VIEW
  • XSR

Contains the null value if the object is not an SQL object.

Example

  • Find all journals in library MJATST.
     SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','JRN') ) AS X
    or 
    SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','*JRN') ) AS X
  • Find all journals and journal receivers in library MJATST.
    SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','JRN JRNRCV') ) AS X
    or
    SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','*JRN *JRNRCV') ) AS X
  • Find all programs and service programs in library MYLIB. Use *ALLSIMPLE to return the list quickly, omitting the detail information.
    SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','PGM SRVPGM','*ALLSIMPLE')) X