OBJECT_STATISTICS table function

Edit online

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

Authorization:

For a *FILE object:
- The caller must have *EXECUTE authority to the library,
  - If the caller has *OBJOPR authority to an object, full details are returned.
  - If the caller has one of the following, partial information is returned along with an SQL warning of '01548'
    - Some authorization to the object, or
    - Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.
    Otherwise, no object information is returned.
For an ILE *SRVPGM or *PGM object:
- The caller must have *EXECUTE authority to the library,
  - If the caller has *OBJOPR and *READ authority to an object, full details are returned.
  - If the caller has one of the following, partial information is returned along with an SQL warning of '01548'
    - Some authorization to the object, or
    - Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.
    Otherwise, no object information is returned.
For a *JOBQ or *OUTQ object:
- The caller must have *EXECUTE authority to the library,
  - If the caller has some authority to an object, full details are returned.
  - If the caller has one of the following, partial information is returned along with an SQL warning of '01548'
    - *SPLCTL special authority, or
    - *JOBCTL special authority and the queue has OPRCTL(*YES), or
    - Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.
    Otherwise, no object information is returned.
For an *AUTL object:
- If the caller has some authority to an object, full details are returned.
- If the caller has one of the following, partial information is returned along with an SQL warning of '01548'
  - The owner of the object, or
  - *AUTLMGT authority for the object, or
  - Authorization to the QIBM_DB_SECADM function usage identifier.
  - Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.
  Otherwise, no object information is returned.
For a *USRPRF object:
- The caller must have some authority to the user profile
  Otherwise, no user profile object information is returned.
For any other object type:
- The caller must have *EXECUTE authority to the library,
  - If the caller has some authority to an object, full details are returned.
  - If the caller has the following, partial information is returned along with an SQL warning of '01548'
    - Authorization to the QIBM_LIST_ALL_OBJS_SQL function usage identifier.
    Otherwise, no object information is returned.

The caller must have *ALLOBJ or *AUDIT special authority to return values for the OBJECT_AUDIT column.

The schema is QSYS2.

object-schema

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 object-schema.

*ALL: All libraries.
*ALLAVL: All libraries in all available ASPs.
*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 object-schema 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, OBJTYPE, OBJLIB, OBJLONGSCHEMA, IASP_NUMBER, and IASP_NAME. 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 object-schema.

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.

A generic name can be specified to find multiple system objects. If the last character in the name is an asterisk, the name is a generic name. For example, to return object names that start with 'EMP', specify an object-name value of 'EMP*'. When a generic name is specified, long SQL names are not included in the search.

If this parameter is specified, only objects with this name in object-schema corresponding to the object types in object-type-list are returned.

If this parameter is not specified, all objects in object-schema corresponding to the object types in object-type-list are returned.

If object-schema is *ALL, *ALLSIMPLE, *ALLAVL, *ALLUSR, or *ALLUSRAVL and object-type is *LIB or LIB, the object-name parameter is ignored.

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 object-schema corresponding to the object types in object-type-list are returned. Values are returned for the following columns: OBJNAME, OBJTYPE, OBJLIB, OBJLONGSCHEMA, IASP_NUMBER, and IASP_NAME. 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. Contains the null value if no owner is available.
OBJDEFINER	VARCHAR(10)	The user profile that created the object. Contains the null value if the object definer is not known.
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 not been used.
LAST_USED_OBJECT	VARCHAR(4)	Indicates whether the LAST_USED_TIMESTAMP value is meaningful for this object. NO The object does not maintain the LAST_USED_TIMESTAMP. YES The object maintains the LAST_USED_TIMESTAMP.
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 not been reset.
IASP_NUMBER	SMALLINT	The auxiliary storage pool (ASP) where storage is allocated for the object.
IASP_NAME	VARCHAR(10)	The device description name of the independent auxiliary storage pool (IASP). The special value of *SYSBAS indicates SYSBASE, which includes the system ASP (ASP 1) and the basic user ASPs (ASPs 2-32).
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 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.
OBJLIB	VARCHAR(10)	System name of the schema.
CHANGE_TIMESTAMP	TIMESTAMP	The timestamp of the last time the object was changed.
USER_CHANGED	VARCHAR(3)	Indicates whether the object was modified by a user. Values are: NO The object was not modified by a user. YES The object was modified by a user. Contains the null value for certain objects that are installed as part of the operating system.
SOURCE_FILE	VARCHAR(10)	The name of the source file that was used to create the object. Contains the null value if a source file was not used.
SOURCE_LIBRARY	VARCHAR(10)	The name of the source file library that was used to create the object. Contains the null value if a source file was not used.
SOURCE_MEMBER	VARCHAR(10)	The name of the source file member that was used to create the object. Contains the null value if a source file was not used.
SOURCE_TIMESTAMP	TIMESTAMP	The last source update timestamp of the member in the source file at the time the object was created. Contains the null value if a source file was not used or if the source timestamp is not available.
CREATED_SYSTEM	VARCHAR(8)	The name of the system on which the object was created.
CREATED_SYSTEM_VERSION	VARCHAR(9)	The version of the operating system when the object was created. The field has a VxRxMx format where: Vx The character V is followed by a version number. Rx The character R is followed by a release level. Mx The character M is followed by a modification level.
LICENSED_PROGRAM	VARCHAR(7)	The name of the licensed program if the object is part of a licensed program. Contains the null value if the object is not a part of a licensed program.
LICENSED_PROGRAM_VERSION	VARCHAR(9)	The version number, release level, and modification level of the licensed program if the object is part of a licensed program. The field has a VxRxMx format where: Vx The character V is followed by a version number. Rx The character R is followed by a release level. Mx The character M is followed by a modification level. Contains the null value if the object is not a part of a licensed program.
COMPILER	VARCHAR(7)	The licensed program identifier of the compiler. Contains the null value if the object was not created with a compiler.
COMPILER_VERSION	VARCHAR(9)	The licensed program version number, release level, and modification level of the compiler. The field has a VxRxMx format where: Vx The character V is followed by a version number. Rx The character R is followed by a release level. Mx The character M is followed by a modification level. Contains the null value if the object was not created with a compiler.
OBJECT_CONTROL_LEVEL	CHAR(8)	The object control level for the object. Contains the null value if there is no object control level.
BUILD_ID	BINARY(16)	The build identifier value for the object. This can be set using the QLICOBJD (Change Object Description) API. Contains the null value if a build ID has not been set for the object
PTF_NUMBER	CHAR(7)	The Program Temporary Fix that resulted in the creation of this object. Contains the null value for a user created object.
APAR_ID	CHAR(6)	The authorized program analysis report (APAR) with this identification number associated with the last change. Will contain the value CHGDFT for a command that is changed using CHGCMDDFT. The Change Object Description (QLICOBJD) API can change this field to any value. Contains the null value is no value is available.
USER_DEFINED_ATTRIBUTE	VARCHAR(10)	Further defines an object type. This field is set by the user by using the QLICOBJD API. Contains the null value if the attribute has not been set.
ALLOW_CHANGE_BY_PROGRAM	VARCHAR(3)	Identifies whether or not any changes other than the text or the days used count and reset date can be made to the object's description by the Change Object Description (QLICOBJD) API. NO The QLICOBJD API cannot be used to change fields in the object's description other than the text or the days used count and reset date. YES The QLICOBJD API can be used to change fields in the object's description.
CHANGED_BY_PROGRAM	VARCHAR(3)	Identifies whether the object has been modified by the Change Object Description (QLICOBJD) API. NO The object has not been modified by the QLICOBJD API. YES The object has been modified by the QLICOBJD API.
COMPRESSED	VARCHAR(4)	Indicates whether the object is compressed or decompressed. Values are: NO Permanently decompressed and compressible. YES Compressed. TEMP Temporarily decompressed. FREE Saved with storage freed; compression status cannot be determined. Contains the null value if the object is permanently decompressed and not compressible.
PRIMARY_GROUP	VARCHAR(10)	The name of the user profile that is the primary group for the object. Contains the null value if there is no primary group for the object.
STORAGE_FREED	VARCHAR(3)	The storage status of the object data. NO The storage for the object data has not been freed. YES The storage for the object data has been freed. See the SAVOBJ or SAVLIB command, STG parameter, for more details.
ASSOCIATED_SPACE_SIZE	INTEGER	The size, in bytes, of the primary associated space of the object. Contains the null value if the object has no primary associated space.
OPTIMUM_SPACE_ALIGNMENT	VARCHAR(3)	Identifies whether the primary associated space for the object has been optimally aligned. Optimum alignment may allow for better performance of applications that manipulate the object. NO The space associated with the object has not been optimally aligned. YES The space associated with the object has been optimally aligned. Contains the null value if the object has no associated space.
OVERFLOW_STORAGE	VARCHAR(3)	Indicates if the object has overflowed the auxiliary storage pool it resides in. NO The object has not overflowed the auxiliary storage pool. YES The object has overflowed the auxiliary storage pool.
OBJECT_DOMAIN	VARCHAR(7)	The domain that contains the object. Values are: SYSTEM The object is in the system domain. USER The object is in the user domain.
OBJECT_AUDIT	VARCHAR(10)	The type of auditing for an object. Values are: NONE No auditing occurs for this object when it is read or changed regardless of the user who is accessing the object. USRPRF Audit this object only if the current user is being audited. The current user is tested to determine if auditing should be done for this object. The user profile can specify if only change access is audited or if both read and change accesses are audited for this object. CHANGE Audit all change access to this object by all users on the system. ALL Audit all access to this object by all users on the system. All access is defined as a read or change operation. Contains the null value if you do not have either all object (ALLOBJ) or audit (AUDIT) special authority.
OBJECT_SIGNED	VARCHAR(3)	Indicates whether the object has a digital signature. NO The object does not have a digital signature. YES The object has a digital signature.
SYSTEM_TRUSTED_SOURCE	VARCHAR(3)	Indicates whether the object is signed by a source that is trusted by the system. NO None of the object signatures came from a source that is trusted by the system. YES The object is signed by a source that is trusted by the system. If the object has multiple signatures, at least one of the signatures came from a source that is trusted by the system.
MULTIPLE_SIGNATURES	VARCHAR(3)	Indicates whether the object has more than one digital signature. NO The object has only one digital signature or does not have a digital signature. YES The object has more than one digital signature.
SAVE_TIMESTAMP	TIMESTAMP	The timestamp the object was last saved. Contains the null value if the object has not been saved.
RESTORE_TIMESTAMP	TIMESTAMP	The timestamp the object was last restored. Contains the null value if the object has not been restored.
SAVE_WHILE_ACTIVE_TIMESTAMP	TIMESTAMP	The timestamp at which the object was saved while active. Contains the null value if the object has not been saved while active.
SAVE_COMMAND	VARCHAR(10)	The command used to save the object. Contains the null value if the object has not been saved.
SAVE_DEVICE	VARCHAR(5)	The type of the device to which the object was last saved. Valid values are: OPT The object was saved to optical. SAVF The object was saved to a save file. *TAP The object was saved to tape. Contains the null value if the object has not been saved.
SAVE_FILE_NAME	VARCHAR(10)	The save file used to save the object. Contains the null value if the object was not last saved to a save file.
SAVE_FILE_LIBRARY	VARCHAR(10)	The save file library used to save the object. Contains the null value if the object was not last saved to a save file.
SAVE_VOLUME	VARCHAR(71)	The tape or optical volumes used to save the object. A maximum of ten volumes is returned. The string contains one blank between volume identifiers. If more than ten volumes were used, an ellipsis (three periods) is returned to the right of the identifier of the tenth volume. Contains the null value if the object was not last saved to tape or optical.
SAVE_LABEL	VARCHAR(17)	The file label used when the object was saved to tape or optical. Contains the null value if the object was not last saved to tape or optical.
SAVE_SEQUENCE_NUMBER	DECIMAL(10,0)	The sequence number used to when the object was saved to tape. Contains the null value if the object was not last saved to tape.
LAST_SAVE_SIZE	DECIMAL(15,0)	The size of the object in bytes at the time of the last save. This value defines the amount of storage that is required if the object is restored. Contains the null value if the object has not been saved.
JOURNALED	VARCHAR(3)	Identifies the current journaling status of the object. Valid values are: NO The object is not currently journaled. YES The object is currently journaled.
JOURNAL_NAME	VARCHAR(10)	The name of journal that receives the journaled changes or the name of the last journal if the object is not currently journaled. Contains the null value if the object has not been journaled.
JOURNAL_LIBRARY	VARCHAR(10)	The name of journal library that receives the journaled changes or the name of the last journal library if the object is not currently journaled. Contains the null value if the object has not been journaled.
JOURNAL_IMAGES	VARCHAR(6)	Specifies the kinds of images that are generated for changes to the object. Valid values are: AFTER Only after images are generated for changes to the object. BOTH Both before and after images are generated for changes to the object. Contains the null value if the object has not been journaled.
OMIT_JOURNAL_ENTRY	VARCHAR(7)	Specifies the journal entries that are omitted. Valid values are: NONE No journal entries are omitted. OPNCLO Open and close entries are omitted. Open and close operations on the specified file members do not create open and close journal entries. Contains the null value if the object has not been journaled.
REMOTE_JOURNAL_FILTER	VARCHAR(3)	The remote journal filter value for the object. Valid values are: NO The journal entries deposited for the object will not be eligible for remote journal filtering. YES The journal entries deposited for the object will be eligible for remote journal filtering. Contains the null value if the object has not been journaled.
JOURNAL_START_TIMESTAMP	TIMESTAMP	The timestamp journaling was last started. Contains the null value if the object has not been journaled.
APPLY_STARTING_RECEIVER	VARCHAR(10)	Specifies the name of the oldest journal receiver needed to successfully use the Apply Journaled Changes (APYJRNCHG) or Remove Journaled Changes (RMVJRNCHG) command. Contains the null value if the object has not been journaled or it has not been saved and restored since journaling was started.
APPLY_STARTING_RECEIVER_LIBRARY	VARCHAR(10)	Specifies the library name of the oldest journal receiver needed to successfully use the Apply Journaled Changes (APYJRNCHG) or Remove Journaled Changes (RMVJRNCHG) command. Contains the null value if the object has not been journaled or it has not been saved and restored since journaling was started.
AUTHORITY_COLLECTION_VALUE	VARCHAR(10)	Specifies the authority collection value used for the object when authority collection for objects is active on the partition. Valid values are: NONE No authority information will be collected for this object when authority collection for objects is active on the partition. A value of NONE will be returned if the object type is not supported by authority collection. *OBJINF Authority information will be collected for this object when authority collection for objects is active on the partition. The authority checking information is collected for each unique instance of the object level information associated with the authority check.

Example

Find all journals in library MJATST.

SELECT * 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.

SELECT * 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
```

Find any CL commands that have had their parameter defaults changed.

SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS', '*CMD'))
    WHERE APAR_ID = 'CHGDFT';