Skip to main content

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. 

Display Journal on IBM i

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).
Display Journal on IBM i

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.

Display Journal on IBM i

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.
Display Journal on IBM i

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

Display Journal on IBM i

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). 
Display Journal on IBM i

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

Display Journal on IBM i

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

Display Journal on IBM i

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


Post a Comment

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i