Make Sense of Journaled Tables with SQL

Test Setup

I had a number of tables that had been journaled and I wanted to analyse what had changed during a fixed time. This is quite simple, the essential parts are sections 2, 4 and 5. The table created in section 4 can be kept and reused, one for each table, if required.

N.B. The fields listed in section 2 and 4 must match up to ENTRY_DATA and TBL.*.

To make this simple, I will be using IBM’s sample library, which automatically Journals all the tables.

If you already have a library with your databases journaled, skip to Start Here and change TMPSAMP for ALIBRARY and ‘EMPLOYEE for ATABLE which can also be a physical file.

All code is executed in ‘Run SQL Scripts’ in ACS.

If you don’t already have the sample library, you can create it by executing the following.

N.B. You can change TMPSAMP to be any library name you wish (just change the following library names too).

SQL

call QSYS.CREATE_SQL_SAMPLE ('TMPSAMP');

The journal already has insert entries from the library creation, but we will add some updates, a delete and an insert.

SQL

   update TMPSAMP.EMPLOYEE
      set SALARY = SALARY + 1000
      where SALARY < 16000;

   update TMPSAMP.EMPLOYEE
      set BONUS = BONUS + 50.00
      where SALARY < 16500;

   delete from TMPSAMP.EMPLOYEE  
      where EMPNO ='200340';

   insert into TMPSAMP.EMPLOYEE
      values('200350', 'TERRY', ' ', 'SMITH', 'E21', '9876', '2023-12-01', 'ANALYST', 17, 'F',
         '1968-07-06', 32000.00, 650.00, 2650.00);

Start Here

The above was just to setup a test. The fun starts here.

To find the Journal and the current Receiver use the following.

Journaled_Receiver_Objects has been available since May 2022, so you may not have the correct PTFs, but usually Journal Receivers are in the same library as the Journal.

SQL

   -- Find the Journal
   select JOURNAL_LIBRARY as JRNLIB, JOURNAL_NAME as JRNNAME, 
          OBJECT_LIBRARY as OBJLIB, OBJECT_NAME as OBJNAME,
          JOURNAL_IMAGES as IMAGES
      from QSYS2.JOURNALED_OBJECTS
      where OBJECT_NAME = 'EMPLOYEE';

JRNLIB	JRNNAME	OBJLIB	OBJNAME	IMAGES
TMPSAMP	QSQJRN	TMPSAMP	EMPLOYEE	*BOTH

Excerpt from runSQL Scripts

SQL


   -- Find the Journal Receiver
   select JOURNAL_LIBRARY as JRNLIB, JOURNAL_NAME as JRNNAME,
          JOURNAL_RECEIVER_LIBRARY as RCVLIB, 
          JOURNAL_RECEIVER_NAME as RCVNAME, STATUS
      from QSYS2.JOURNAL_RECEIVER_INFO
      where JOURNAL_LIBRARY = 'TMPSAMP'
         and STATUS = 'ATTACHED';

JRNLIB	JRNNAME	RCVLIB	RCVNAME	STATUS
TMPSAMP	QSQJRN	TMPSAMP	QSQJRN0001	ATTACHED

Excerpt from runSQL Scripts

2. Extract the journal records for the required table into a temporary table in QTEMP. The data of the records will not be easily readable at this point. Note that the longer the time period specified, the more records will be searched and extracted. It may be advisable to start with short time periods. The *CURCHAIN can also be changed to *CURRENT, but you only get recent records. The amount depends on the maximum journal receiver size and the number kept on disk.

N.B. The ENTRY_DATA size of 32581 is the maximum I could go to without an error, you may have to lower this if you add more Journal fields to the list, similarly it can be increased if you reduce the number of Journal fields. If your table row is longer, you may have a problem.

SQL

   -- Get the Journal records
   create table QTEMP.TMPJRN as (     
      select ENTRY_TIMESTAMP, SEQUENCE_NUMBER, JOURNAL_CODE, 
             JOURNAL_ENTRY_TYPE as JRNENTYP, COUNT_OR_RRN, USER_NAME,
             JOB_NAME, JOB_USER, JOB_NUMBER, THREAD, PROGRAM_NAME, 
             PROGRAM_LIBRARY, COMMIT_CYCLE, RECEIVER_NAME,
             RECEIVER_LIBRARY, cast(ENTRY_DATA as CHAR(32581)) as ENTRY_DATA
         from table(QSYS2.DISPLAY_JOURNAL(
                      OBJECT_LIBRARY=>'TMPSAMP', OBJECT_NAME=>'EMPLOYEE',
                      OBJECT_OBJTYPE=>'*FILE', OBJECT_MEMBER=>'*FIRST',
                      JOURNAL_LIBRARY=>'TMPSAMP', JOURNAL_NAME => 'QSQJRN',
                      STARTING_RECEIVER_LIBRARY => 'TMPSAMP', 
                      STARTING_RECEIVER_NAME => '*CURCHAIN',
                      STARTING_TIMESTAMP => '2024-01-02 00:00:00.000000',
                      ENDING_TIMESTAMP => '2024-01-02 23:59:59.999999'
            ) ) as JRN
         where JOURNAL_CODE = 'R'
   ) with data;

3. Check that there is data in the extract. You should get 52 records if you created and used TMPSAMP library within the given date range, as 42 records were added to Employee when it was created. The last 10 records are the changes that were applied at the start.

SQL

   select * from QTEMP.TMPJRN;

4. Create a new empty table which consists of the Journal fields, followed by the Table fields, in this case EMPLOYEE. The SQL may give you a warning that this table is not being Journaled, but that is ok.

SQL

   -- Create an empty table to receive Journals
   create or replace table TMPLIB.JRN_EMPLOY as (     
      select ENTRY_TIMESTAMP, SEQUENCE_NUMBER, JOURNAL_CODE, 
             JOURNAL_ENTRY_TYPE, COUNT_OR_RRN, USER_NAME, 
             JOB_NAME, JOB_USER, JOB_NUMBER, THREAD, PROGRAM_NAME,
             PROGRAM_LIBRARY, COMMIT_CYCLE, RECEIVER_NAME,
             RECEIVER_LIBRARY, TBL.*
         from table(QSYS2.DISPLAY_JOURNAL(
                      OBJECT_LIBRARY=>'TMPSAMP', OBJECT_NAME=>'EMPLOYEE',
                      OBJECT_OBJTYPE=>'*FILE', OBJECT_MEMBER=>'*FIRST',
                     JOURNAL_LIBRARY=>'TMPSAMP', JOURNAL_NAME => 'QSQJRN'
            ) ) as JRN,
      TMPSAMP.EMPLOYEE   TBL
   ) with no data;

5. Copy the temporary table with data into the empty table. The journaled table fields should map correctly. The Journal entry type of IL does not have a record image and so is not of interest. It is the Increment Record Limit.

SQL

   -- Copy journal table to empty table (or replace contents)
   cl: CPYF FROMFILE(QTEMP/TMPJRN)      
           TOFILE(TMPLIB/JRN_EMPLOY)       
           MBROPT(*REPLACE)              
           FMTOPT(*NOCHK)
           INCCHAR(JRNENTYP 1 *NE IL);

6. Delete the temporary table

SQL

   drop table QTEMP.TMPJRN;

7. Examine the journals.

SQL

select * from TMPLIB.JRN_EMPLOY;

Of course, you will probably want to add at least a where clause.

SQL

   -- Extract certain fields where the records were changed after creation
   select ENTRY_TIMESTAMP, JOURNAL_ENTRY_TYPE as ENTRY, 
          SEQUENCE_NUMBER as SEQ, EMPNO, LASTNAME, SALARY, BONUS
      from TMPLIB.JRN_EMPLOY
      where SEQUENCE_NUMBER > 714;

ENTRY_TIMESTAMP	ENTRY	SEQ	EMPNO	LASTNAME	SALARY	BONUS
2024-01-02 16:30:37.710	UB	715	290	PARKER	15340	300
2024-01-02 16:30:37.710	UP	716	290	PARKER	16340	300
2024-01-02 16:30:37.711	UB	719	310	SETRIGHT	15900	300
2024-01-02 16:30:37.711	UP	720	310	SETRIGHT	16900	300
2024-01-02 16:30:37.711	UB	723	200310	SPRINGER	15900	300
2024-01-02 16:30:37.711	UP	724	200310	SPRINGER	16900	300
2024-01-02 16:30:39.028	UB	728	290	PARKER	16340	300
2024-01-02 16:30:39.028	UP	729	290	PARKER	16340	350
2024-01-02 16:30:40.094	DL	733	200340	ALONZO	23840	500
2024-01-02 16:30:41.339	PX	735	200350	SMITH	32000	650

Excerpt from runSQL Scripts

As you have seen in the above examples I have used the TMPSAMP schema/library and EMPLOYEE as the table/physical file. Both these names can be changed to suit your applications if you so desire.

Informing the IBM Community

Make Sense of Journaled Tables with SQL

Test Setup

Start Here

Comments

Leave a Reply Cancel reply