IBM Support

Generating a Report of Objects Deleted as part of the DLTUSRPRF for a User ID

How To


Summary

This document steps trough the process of creating a report of all Objects that were deleted as part of the DLTUSRPRF operation for a User ID.

Objective

Generate a report listing all objects deleted for a User Profile.

Environment

Using the SQL Service QSYS2.DISPLAY_JOURNAL() to create a report that lists the objects Deleted for a user profile.

Authorization:

  • You must have *USE authority to the journal and to all requested journal receivers.
  • *OBJEXIST authority is required to the journal if object-name is omitted or if object-name specifies an object that no longer exists.
  • If object-name is *ALL, you must be authorized to every object associated with a journal entry.

 

Note: This method is only available on R720, R730, R740 and R750 of the Operating System.  

 

Before you start, verify that the QAUDJRN journal exists and that you are tracking Delete, Rename, or Move operations.
Use the DSPSECAUD command and make sure the following settings are configured on your system:
Security journal QAUDJRN exists . . . . . :  YES    
Current QAUDCTL system value  . . . . . . : *AUDLVL *OBJAUD
Current QAUDLVL system value  . . . . . . : *DELETE
 
If not there, use the CHGSECAUD command to add them to your system.  Keep in mind that the auditing values need to be active on your system before the user profile was deleted. 

Steps

Using the Run SQL Scripts tool, run the following SQL Statement:
 
SELECT JOURNAL_ENTRY_TYPE,
       ENTRY_TIMESTAMP,
       JOB_NAME,
       JOB_USER,
       JOB_NUMBER,
       "CURRENT_USER" AS CURRENT_USER,
       SUBSTR(CAST(object AS CHAR(30)), 1, 10) AS object_name,
       SUBSTR(CAST(object AS CHAR(30)), 11, 10) AS object_library,
       OBJECT_TYPE,
       PATH_NAME
    FROM TABLE (
            QSYS2.DISPLAY_JOURNAL(
                JOURNAL_LIBRARY => 'QSYS',
                JOURNAL_NAME => 'QAUDJRN',
                STARTING_RECEIVER_NAME => '*CURCHAIN', JOURNAL_ENTRY_TYPES => 'DO, LD', 
                STARTING_TIMESTAMP => '2024-08-01 00:00:01',
                ENDING_TIMESTAMP => '2024-08-01 13:34:00') -- Specify the time period for the DLTUSRPRF
        )
    WHERE JOB_NAME = 'QPADEV001R' -- Specify the Job Name for the user that issued the DTLUSRPRF
          AND Job_user = 'V6CASTIL' -- Specify the Job User for the user that issued the DTLUSRPRF
          AND Job_number = '422688' -- Specify the Job Number for the user that issued the DTLUSRPRF
    ORDER BY object_library,
             path_name
 
image-20240801090815-1
 
 
Note 1: Specify the Job Name. Job User and Job Number that issued the DLTUSRPRF command.  
Note 2: Modify the time and date to when the DLTUSRPRF was issued. 
Note 3: If Hexadecimal data is displayed on the report, you need to change the JDBC connection properties to translate the CCSID to 65535. On the Run SQL tool, select the Connection Menu > Connected > Edit JDBC Connection > Translation and click on Translate CCSID 65535. Save the connection and reconnect to run the SQL.

Additional Information

More information on the DISPLAY_JOURNAL table function can be found on the link:

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHyAAM","label":"Security"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"and future releases;7.2.0;7.3.0;7.4.0;7.5.0"}]

Document Information

Modified date:
16 January 2026

UID

ibm17162192

Page Feedback