Display Journal from SQL - IBM i

Display Journal

Journals play very important role on IBM i and are helpful to identify who has updated the data and/or to retrieve the data before the update or delete from the tables and so on. 

Data from the journals can be retrieved by using DSPJRN command. One other way of retrieving the data from journals is by using SQL table function DISPLAY_JOURNAL. Data returned from this function is similar to the data returned by DSPJRN command. 

We will see few examples to see how DISPLAY_JOURNAL works. 

In the above example, 

• We are passing two mandatory parameters Journal Name and Library.
• 'JOURNAL_NAME' - Name of the Journal.
• 'JOURNAL_LIBRARY' - Name of the Library Journal is present in.
• Below are the few columns we are retrieving from Journal. This function returns much more data and full list can be found in this link on IBM Knowledge center. 
• 'ENTRY_TIMESTAMP' - Time when the Journal entry was captured (E.g.: Time when the data was written, updated or deleted).
• 'JOURNAL_ENTRY_TYPE' - Returns the type of Journal entry.
• 'COUNT_OR_RRN' - Returns the RRN of the data in the Physical file.
• 'OBJECT' - Returns the Object details. Object Name and Library Name (and Member Name for files).
• 'OBJECT_TYPE' - Returns the Object type. 
• 'ENTRY_DATA' - Data Captured in the Journal. This field of type 'BLOB' (Binary Large OBject).

Looking at the above result, 'ENTRY_DATA' is displayed as HEX values. This needs to be converted to the required data type. 

In the above example, we are only passing Journal Name and Library as the parameters and query returns all the data that is present in the current journal receiver. This is fine because I am using test journal. 

However, this becomes extremely difficult in any real applications as there would be many objects added to the Journal and query may take large time to process. It is best to pass the as much information as possible in the parameters so that query can return the specific results and can run faster. 

In the below example, we will pass the specific file name and journal entry type as parameters and cast the 'ENTRY_DATA' to return the data as character.

In this example, we are converting the Entry data to character format and passing parameters to select the data for a specific file, journal entry types with in specified time range. 

• Converting (CAST) the ENTRY_DATA from BLOB to character (VARCHAR) format.
• In the above query, we are using 'CAST' two times to convert the data to character format. 
• With just one CAST statement "CAST(ENTRY_DATA AS VARCHAR(50) CCSID 37) AS JOURNAL_DATA", Character conversion between CCSID 65535 and CCSID 37 not valid.
• So, we are first converting the data as character and using another CAST to change the CCSID.
  
  
• We are passing more number of parameters here (compared to the previous example) so that results can be specific. Below are some of the additional parameters we are passing.
• OBJECT_NAME - Name of the object we are retrieving the data for.
• OBJECT_LIBRARY - Name of the library object is present in. Special values '*LIBL', '*CURLIB' can be specified as well.
• OBJECT_OBJTYPE - Type of the object passed (like *FILE, *DTAQ, *DTAARA or *LIB).  
• OBJECT_MEMBER - Name of the member, this is only required for Object Type '*FILE'. Special values like '*ALL', '*FIRST' or '*NONE' can be specified as well. 
• JOURNAL_ENTRY_TYPES - Journal entry types we are retrieving the data for. Multiple entries can be specified by separating them with space.
• STARTING_RECEIVER_NAME - Name of the starting journal receiver name. Special values like '*CURRENT', '*CURCHAIN' or '*CURAVLCHN' can be specified as well. If no value is specified '*CURRENT' is considered by default. 
• STARTING_TIMESTAMP & ENDING_TIMESTAMP - Starting and Ending timestamps to retrieve the data from Journal.
• There are many other parameters that can be specified to narrow down the results.

Above example is perfectly suited for a table with just one character field. We will see another example for a table with decimal field. 

In this query, we are converting the data for each field separately by taking the substring using CAST & INTERPRET*.

• In this example, we are retrieving the data for a table with two character fields (Order Number & Customer Name) and one decimal field (Order Value).
• Lines 4 - 8: CAST function to convert the substring of BLOB to character and INTERPRET function to make sure the query will return the data in the required format for both the character fields.
• Lines 10 - 11: CAST function to convert the substring of BLOB to character and INTERPRET function to interpret the data as packed decimal value from the character value.
• Packed decimal field (5, 2) would only have the buffer length of 3 digits. So, we are taking the substring of 3 digits and converting it as character. 
• Interpret function would convert the 3 digits as the packed decimal value of (5, 2). 

*INTERPRET function is only available since IBM i 7.3 (TR8) and IBM i 7.3 (TR2). For the previous versions we can continue to use CAST. But, CAST wouldn't convert the data as packed decimal.

This would display the decimal value as character only (suffixed with 'F') and doesn't show the period (.) to differentiate decimal values. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.