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'.
*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, Start of changeIASP_NUMBER, and IASP_NAMEEnd of change. 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.
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.
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, Start of changeIASP_NUMBER, and IASP_NAMEEnd of change. 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.

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

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

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.

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

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

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

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

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