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'.
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.
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.
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:
Contains the null value if the object is not an SQL object. |
Example
- Find all journals in library MJATST.
orSELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','JRN') ) AS X
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','*JRN') ) AS X
- Find all journals and journal receivers in library MJATST.
orSELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('MJATST ','JRN JRNRCV') ) AS X
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