Using SQL to Interrogate Journal Objects

An exciting enhancement has been added to IBM i. Through a new Db2 for i provided user defined table function (UDTF), IBM i users can use SQL to interrogate journal objects (drum roll please …). The enhancement has been provided via PTFs to V5R4M5, IBM i 6.1 and 7.1, and is available in all subsequent releases.

Mark Anderson and the Db2 for i team provided this SQL support in response to customer requests for improved (SQL) access methods for dealing with journals. Mark provides the following description and primer for using this support:

QSYS2/Display_Journal is a table function that allows the user to view entries in a journal by running a query. There are many input parameters of the table function that can (and should) be used for best performance in limiting the journal entries returned. For more information of the special values, see the QjoRetrieveJournalEntries() API in the Knowledge Center. Here is a brief summary:

Journal_Library and Journal_Name

The Journal_Library and Journal_Name must identify a valid journal. *LIBL and *CURLIB are NOT allowed as a value of the Journal_Library. 

Starting_Receiver_Library and Starting_Receiver_Name

If the specified Starting_Receiver_Name is the null value, empty string, of blanks the *CURRENT will be used and the Starting_Receiver_Library is ignored. If the specified Starting_Receiver_Name contains the special values *CURRENT, *CURCHAIN, or *CURAVLCHN, the Starting_Receiver_Library is ignored. Otherwise, the Starting_Receiver_Name and Starting_Receiver_Library must identify a valid journal receiver. *LIBL and *CURLIB may be used as a value of the Starting_Receiver_Library. The ending journal receiver cannot be specified and is always *CURRENT. 

Starting_Timestamp

If the specified Starting_Timestamp is the null value, no starting timestamp is used. A value for Starting_Timestamp and Starting_Sequence cannot both be specified at the same time. However, both values can be queried when querying the table function.

Starting_Sequence 

If the specified Starting_Sequence is the null value, no starting sequence number is used. If the specified Starting_Sequence is not found in the receiver range, an error is returned. A value for Starting_Timestamp and Starting_Sequence cannot both be specified at the same time. However, both values can be queried when querying the table function.

Journal_Codes 
 
If the specified Journal_Codes is the null value, or an empty string, or a blank string, *ALL is used. Otherwise, the string may consist of the special value *ALL, the special value *CTL, or a string containing one or more journal codes. journal codes may be separated by one or more separators. The separator characters are the blank and comma. For example, a valid string may be ‘RJ’ or ‘R J’ or ‘R,J’ or ‘R, J’. 

Journal_Entry_Types 

If the specified Journal_Entry_Types is the null value, or an empty string, or a blank string, *ALL is used. Otherwise, the string may consist of the special value *ALL, the special value *RCD, or a string containing one or more journal entry types. journal entry types may be separated by one or more separators. The separator characters are the blank and comma. For example, a valid string may be ‘RJ’ or ‘R J’ or ‘R,J’ or ‘R, J’. 

Object_Library, Object_Name, Object_ObjType, and Object_Member 

If the specified Object_Name is the null value, or an empty string, or a blank string, no object name is used and the Object_Library, Object_ObjType, and Object_Member are ignored. If the specified Object_Name contains the special value *ALL, the Object_Library, must contain a library name. Object_ObjType, and Object_Member are ignored.  Otherwise, the Object_Library, Object_Name, Object_ObjType, and Object_Member must identify a valid object. *LIBL and *CURLIB may be used as a value of the Object_Library. The Object_ObjType must be one of *DTAARA, *DTAQ, *FILE, or *LIB (*LIB is 6.1 only). The Object_Member may be *FIRST, *ALL, *NONE or a valid member name. If the specified object type was not *FILE, the member name is ignored. Only one object may be specified.

User

If the specified User is the null value, or an empty string, or a blank string, *ALL is used. Otherwise, the User must identify a valid user profile name.

Job

If the specified Job is the null value, or an empty string, or a blank string, *ALL is used. Otherwise, the Job must identify a valid job name a specific job where the first 10 characters are the job name, the second 10 characters are the user name, and the last 6 characters are the job number.

Program

If the specified Program is the null value, or an empty string, or a blank string, *ALL is used. Otherwise, the Program must identify a valid program name.

Here are a couple starter examples:

-- Select all entries from the *CURRENT receiver of journal QSYS2/QSQJRN.
select * from table (Display_Journal(
  'QSYS2',     'QSQJRN',        -- Journal library and name
  '', '',                       -- Receiver library and name
  CAST(null as TIMESTAMP),      -- Starting timestamp
  CAST(null as DECIMAL(21,0)),  -- Starting sequence number
  '',                -- Journal codes
  '',                -- Journal entries
  '','',             -- Object library, Object name
  '','',             -- Object type, Object member
  '',                -- User
  '',                -- Job
  ''                 -- Program        
) ) as x;

Example output:

— Select all entries from the *CURCHAIN of journal mjatst/qsqjrn whose time 
— is equal or after 2010-03-31-19:01.15 with user MJA.

set path system path, mjatst;   -- Change mjatst to the library you chose above

select * from table (mjatst.Display_Journal(
  'MJATST',     'QSQJRN',  -- Journal library and name
  ' ','*CURCHAIN',        -- Receiver library and name
  CAST('2010-03-30-19.01.15.000000' as TIMESTAMP), -- Starting timestamp
  CAST(null as DECIMAL(21,0)), -- Starting sequence number
  '',               -- Journal codes
  '',   0;          -- Journal entries
  '','',            -- Object library, Object name
  '','',            -- Object type, Object member
  'MJA',            -- User
  '',               -- Job
  ''                -- Program        
) ) as x;

Notes:

• A WHERE clause can be added to a query of a table function to further limit the rows returned. For example, to search for only entry specific data with the words “System I” you would add the predicate WHERE Entry_Data LIKE ‘%System i%’ . However, any processing in the WHERE clause will happen after entries are returned from the table function to SQE, so for best performance use the parameters of the table function to limit the rows processed.
• Entry Data that contains pointers
  • Since journal entries can be very large, when returning the entry data in a query, you might consider restricting the amount of data being returned by using a SUBSTR scalar function.
  • When retrieving journal entries that are very large, QjoRetrieveJournalEntries API returns pointers to the data. The table function handles these pointers and returns the actual data in the Entry_Data BLOB column. For Record entries and the Initialize entry, the data will be appended after the record buffer in the BLOB column and a separator of QQQQQQQQQQQQQQQ is added between the data of each column. For other entries, the data is returned in the format it was initially written to the journal without the 16 byte pointer.
  • The maximum size of a BLOB is 2 gigabytes. If the entry specific data is larger than 2 gigabytes, the first 2 gigabytes will be returned along with a truncation warning.
• One may wish to create a view over the table function to make it easier to query the journal data assuming that the same parameters are going to be passed each time. For example, assume that I wish to query the audit journal and limit the Entry_Data I return to only 100 bytes:
  create view mjatst.Audit_Journal as 
SELECT Entry_Timestamp, Sequence_Number, Journal_Code, Journal_Entry_Type, SUBSTR(Entry_Data,1,100) as Entry_Data, Object, Object_Type, "CURRENT_USER", Job_Name, Job_User, Job_Number, Program_Name, Program_Library, System_Name
from table (QSYS2.Display_Journal(
  'QSYS',     'QAUDJRN',        -- Journal library and name
  CAST(null as VARCHAR(10)),    -- Receiver library
  CAST(null as VARCHAR(10)),    -- Receiver name
  CAST(null as TIMESTAMP),      -- Starting timestamp
  CAST(null as DECIMAL(21,0)),  -- Starting sequence number
  '',                 -- Journal codes
  '',                 -- Journal entries
        '','',        -- Object library, Object name
        '','',        -- Object type, Object member
  '',                 -- User
  '',                 -- Job
  ''                  -- Program        
) ) as x;

• In Run SQL Scripts the Entry_Data column is displayed in hexadecimal because it is a BLOB. To interpret the data as characters, in 5.4, you would need to create your own table function in your library and change the return column of ENTRY_DATA to CLOB(2G). In 6.1 and beyond, you can use the VARCHAR function to limit the length of the Entry_Data and to interpret it as characters instead of bytes and then use the Translation option in the client to translate 65535 to characters.
  
  For example: 
  
  create view mjatst.Audit_Journal as 
SELECT Entry_Timestamp, Sequence_Number, Journal_Code, Journal_Entry_Type, CAST(entry_data as varchar(100) ) as Entry_Data, Object, Object_Type, "CURRENT_USER", Job_Name, Job_User, Job_Number, Program_Name, Program_Library, System_Name
from table (QSYS2.Display_Journal(
  'QSYS',     'QAUDJRN',       -- Journal library and name
  CAST(null as VARCHAR(10)),   -- Receiver library
  CAST(null as VARCHAR(10)),   -- Receiver name
  CAST(null as TIMESTAMP),     -- Starting timestamp
  CAST(null as DECIMAL(21,0)), -- Starting sequence number
  '',                 -- Journal codes
  '',                 -- Journal entries
        '','',        -- Object library, Object name
        '','',        -- Object type, Object member
  '',                 -- User
  '',                 -- Job
  ''                  -- Program        
) ) as x;

Service Information
Below for the detail on PTF levels.

IBM i V5R4M5
Db2 for IBM i Group PTF
SF99504: 540 DB2 for IBM i – Level 27 (released August 2010)

or
PTF ‘5722SS1 V5R4M0 SI39814’
PTF ‘5722SS1 V5R4M0 SI39816’
PTF ‘5722SS1 V5R4M0 SI39848’

Distribution Requisite:
PTF ‘5722999 V5R4M5 MF50076’

IBM i 6.1 (V6R1M0 and V6R1M1)

Db2 for IBM i Group PTF
SF99601: 610 DB2 for IBM i – Level 15 (released September 2010)

or 
PTF ‘5761SS1 V6R1M0 SI39822’
PTF ‘5761SS1 V6R1M0 SI39823’

Distribution Requisites:
PTF ‘5761999 V6R1M0 MF50055’
PTF ‘5761999 V6R1M1 MF50056’

IBM i 7.1

Db2 for IBM i Group PTF
SF99701: 710 DB2 for IBM i – Level 3 (released August 2010)

or 
PTF ‘5770SS1 V7R1M0 SI39820’
PTF ‘5770SS1 V7R1M0 SI39821’

This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.