OBJECT_STATISTICS table function

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

Authorization:
  • For an object that is not a user profile:
    • If the caller has *EXECUTE authority to the library,
      • If the caller has *OBJOPR and *READ authority to an object, full details are returned.
      • Otherwise, partial information is returned along with an SQL warning of '01548'.
      Otherwise, the object information is not returned.
  • For a user profile object:
    • The caller must have at least one of the following:
      • Some authority to the user profile, or
      • Authorization to the QIBM_DB_SECADM function usage identifier.
      Otherwise, the user profile object information is not returned.
Read syntax diagramSkip visual syntax diagramOBJECT_STATISTICS(OBJECT_SCHEMA => object-schema ,OBJTYPELIST => object-type-list ,OBJECT_NAME => object-name )
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.
Start of change*ALLAVLEnd of change
Start of changeAll libraries in all available ASPs.End of change
*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'.
Start of change*ALLSIMPLEEnd of change
Start of changeThe 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, and OBJLONGSCHEMA. All other columns return NULL.End of change
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.
Start of changeA 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. End of change
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.
Start of changeThe 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, and OBJLONGSCHEMA. All other columns return NULL.
End of change
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.

Start of changeLAST_USED_OBJECTEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
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.
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.

Start of changeOBJLIBEnd of change Start of changeVARCHAR(10)End of change Start of changeSystem name of the schema.End of change
Start of changeCHANGE_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp of the last time the object was changed.End of change
Start of changeUSER_CHANGEDEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.

End of change
Start of changeSOURCE_FILEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the source file that was used to create the object.

Contains the null value if a source file was not used.

End of change
Start of changeSOURCE_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the source file library that was used to create the object.

Contains the null value if a source file was not used.

End of change
Start of changeSOURCE_MEMBEREnd of change Start of changeVARCHAR(10)End of change Start of changeThe name of the source file member that was used to create the object.

Contains the null value if a source file was not used.

End of change
Start of changeSOURCE_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe 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 Start of changeor if the source timestamp is not availableEnd of change.

End of change
Start of changeCREATED_SYSTEMEnd of change Start of changeVARCHAR(8)End of change Start of changeThe name of the system on which the object was created.

Contains the null value if the system is not known.

End of change
Start of changeCREATED_SYSTEM_VERSIONEnd of change Start of changeVARCHAR(9)End of change Start of changeThe 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.

Contains the null value if the version is not known.

End of change
Start of changeLICENSED_PROGRAMEnd of change Start of changeVARCHAR(7)End of change Start of changeThe 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.

End of change
Start of changeLICENSED_PROGRAM_VERSIONEnd of change Start of changeVARCHAR(9)End of change Start of changeThe 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.

End of change
Start of changeCOMPILEREnd of change Start of changeVARCHAR(7)End of change Start of changeThe licensed program identifier of the compiler.

Contains the null value if the object was not created with a compiler.

End of change
Start of changeCOMPILER_VERSIONEnd of change Start of changeVARCHAR(9)End of change Start of changeThe 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.

End of change
Start of changeOBJECT_CONTROL_LEVELEnd of change Start of changeCHAR(8)End of change Start of changeThe object control level for the object.

Contains the null value if there is no object control level.

End of change
Start of changePTF_NUMBEREnd of change Start of changeCHAR(7)End of change Start of changeThe Program Temporary Fix that resulted in the creation of this object.

Contains the null value for a user created object.

End of change
Start of changeAPAR_IDEnd of change Start of changeCHAR(6)End of change Start of changeThe 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.

End of change
Start of changeUSER_DEFINED_ATTRIBUTEEnd of change Start of changeVARCHAR(10)End of change Start of changeFurther 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.

End of change
Start of changeALLOW_CHANGE_BY_PROGRAMEnd of change Start of changeVARCHAR(3)End of change Start of changeIdentifies 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.
End of change
Start of changeCHANGED_BY_PROGRAMEnd of change Start of changeVARCHAR(3)End of change Start of changeIdentifies 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.
End of change
Start of changeCOMPRESSEDEnd of change Start of changeVARCHAR(4)End of change Start of changeIndicates 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.

End of change
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.
Start of changeASSOCIATED_SPACE_SIZEEnd of change Start of changeINTEGEREnd of change Start of changeThe size, in bytes, of the primary associated space of the object.

Contains the null value if the object has no primary associated space.

End of change
Start of changeOPTIMUM_SPACE_ALIGNMENTEnd of change Start of changeVARCHAR(3)End of change Start of change 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.

End of change
Start of changeOVERFLOW_STORAGEEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeOBJECT_DOMAINEnd of change Start of changeVARCHAR(7)End of change Start of changeThe domain that contains the object. Values are:
*SYSTEM
The object is in the system domain.
*USER
The object is in the user domain.
End of change
Start of changeOBJECT_AUDITEnd of change Start of changeVARCHAR(10)End of change Start of changeThe type of auditing for an object. Values are:
*ALL
Audit all access to this object by all users on the system. All access is defined as a read or change operation.
*CHANGE
Audit all change access to this object by all users on the system.
*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.

Contains the null value if you do not have either all object (*ALLOBJ) or audit (*AUDIT) special authority.

End of change
Start of changeOBJECT_SIGNEDEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates whether the object has a digital signature.
NO
The object does not have a digital signature.
YES
The object has a digital signature.
End of change
Start of change SYSTEM_TRUSTED_SOURCEEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeMULTIPLE_SIGNATURESEnd of change Start of changeVARCHAR(3)End of change Start of changeIndicates 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.
End of change
Start of changeSAVE_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp the object was last saved.

Contains the null value if the object has not been saved.

End of change
Start of changeRESTORE_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp the object was last restored.

Contains the null value if the object has not been restored.

End of change
Start of changeSAVE_WHILE_ACTIVE_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp at which the object was saved while active.

Contains the null value if the object has not been saved while active.

End of change
Start of changeSAVE_COMMANDEnd of change Start of changeVARCHAR(10)End of change Start of changeThe command used to save the object.

Contains the null value if the object has not been saved.

End of change
Start of changeSAVE_DEVICEEnd of change Start of changeVARCHAR(5)End of change Start of changeThe 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.

End of change
Start of changeSAVE_FILE_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe save file used to save the object.

Contains the null value if the object was not last saved to a save file.

End of change
Start of changeSAVE_FILE_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe save file library used to save the object.

Contains the null value if the object was not last saved to a save file.

End of change
Start of changeSAVE_VOLUMEEnd of change Start of changeVARCHAR(71)End of change Start of changeThe 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.

End of change
Start of changeSAVE_LABELEnd of change Start of changeVARCHAR(17)End of change Start of changeThe 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.

End of change
Start of changeSAVE_SEQUENCE_NUMBEREnd of change Start of changeDECIMAL(10,0)End of change Start of changeThe sequence number used to when the object was saved to tape.

Contains the null value if the object was not last saved to tape.

End of change
Start of changeLAST_SAVE_SIZEEnd of change Start of changeDECIMAL(15,0)End of change Start of changeThe 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.

End of change
Start of changeJOURNALEDEnd of change Start of changeVARCHAR(3)End of change Start of changeIdentifies the current journaling status of the object. Valid values are:
NO
The object is not currently journaled.
YES
The object is currently journaled.
End of change
Start of changeJOURNAL_NAMEEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeJOURNAL_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeThe 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.

End of change
Start of changeJOURNAL_IMAGESEnd of change Start of changeVARCHAR(6)End of change Start of changeSpecifies 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.

End of change
Start of changeOMIT_JOURNAL_ENTRYEnd of change Start of changeVARCHAR(7)End of change Start of changeSpecifies 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.

End of change
Start of changeREMOTE_JOURNAL_FILTEREnd of change Start of changeVARCHAR(3)End of change Start of changeThe 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.

End of change
Start of changeJOURNAL_START_TIMESTAMPEnd of change Start of changeTIMESTAMPEnd of change Start of changeThe timestamp journaling was last started.

Contains the null value if the object has not been journaled.

End of change
Start of changeAPPLY_STARTING_RECEIVEREnd of change Start of changeVARCHAR(10)End of change Start of changeSpecifies 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.

End of change
Start of changeAPPLY_STARTING_RECEIVER_LIBRARYEnd of change Start of changeVARCHAR(10)End of change Start of changeSpecifies 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.

End of change

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 
  • Start of changeFind 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
    
    End of change
  • Start of changeFind any CL commands that have had their parameter defaults changed.
    SELECT * FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS', '*CMD'))
        WHERE APAR_ID = 'CHGDFT';
    
    End of change