IBM i: The SQL Way · #11

Create a Data Journal Reader for an IBM i Table

Use QSYS2.CREATE_DATA_JOURNAL_READER to generate a table function that returns journaled record images as individual columns instead of an unparsed entry-data value.

DISPLAY_JOURNAL can return record-level journal entries, but the record image is normally contained in entry-specific data. CREATE_DATA_JOURNAL_READER generates a file-specific table function that exposes supported table fields as individual SQL columns.

The service is:

QSYS2.CREATE_DATA_JOURNAL_READER

It is a scalar function that creates another table function.

The generated table function is based on the column definition of one journaled file.

Why this matters

Without a file-specific reader, a query may return:

ENTRY_DATA

as a large binary value that must be interpreted according to the record format.

The generated reader can return:

CUSTOMER_ID
ORDER_NUMBER
ORDER_STATUS
ORDER_TOTAL

alongside journal metadata such as:

ENTRY_TIMESTAMP
OPERATION
JOURNAL_ENTRY_TYPE
JOB_NAME
USER_NAME
PROGRAM_NAME
COMMIT_CYCLE

That makes the journal easier to query using normal SQL.

Example source table

Assume:

Library: PRODLIB
File:    ORDERS

The file must already be journaled.

The FILE_NAME parameter requires the system object name.

Generate the reader

VALUES QSYS2.CREATE_DATA_JOURNAL_READER(
    LIBRARY_NAME   => 'PRODLIB',
    FILE_NAME      => 'ORDERS',
    OUTPUT_LIBRARY => 'MYTOOLS'
);

A successful call returns:

1

An unsuccessful call returns:

-1

The generated table function is named:

MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS

If a function with the generated name already exists, IBM replaces it.

Query the generated reader

SELECT *
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS()
);

This can return many rows.

For normal investigation, restrict the time or receiver range.

Query the last hour

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    JOURNAL_ENTRY_TYPE,
    USER_NAME,
    JOB_NUMBER,
    JOB_USER,
    JOB_NAME,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    ORDER_ID,
    CUSTOMER_ID,
    ORDER_STATUS,
    ORDER_TOTAL
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 1 HOUR
    )
)
ORDER BY ENTRY_TIMESTAMP DESC;

Replace the example application columns with the actual columns in the journaled file.

Limit record-operation types

The generated reader supports a JOURNAL_ENTRY_TYPES parameter.

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    JOURNAL_ENTRY_TYPE,
    USER_NAME,
    JOB_NUMBER,
    JOB_USER,
    JOB_NAME,
    ORDER_ID,
    ORDER_STATUS
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 24 HOURS,
        JOURNAL_ENTRY_TYPES =>
            'DL,PT,PX,UP'
    )
)
ORDER BY ENTRY_TIMESTAMP DESC;

Common record-operation entry types include:

DL   Delete
PT   Record added
PX   Record added with previous-image context
UP   Update after image

The exact entries available depend on the journaling configuration and operation.

Use QSYS2.JOURNAL_CODE_INFO when a descriptive entry-type reference is needed.

Find changes to one business key

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    USER_NAME,
    JOB_NUMBER,
    JOB_USER,
    JOB_NAME,
    PROGRAM_NAME,
    ORDER_ID,
    ORDER_STATUS,
    ORDER_TOTAL,
    COMMIT_CYCLE
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 7 DAYS
    )
)
WHERE ORDER_ID = 100245
ORDER BY
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER;

This can help answer:

• when a row changed
• which job changed it
• which user was active
• which program made the change
• whether several changes belonged to the same commit cycle

Compare before and after images

Whether both images are available depends on the journaled object and journal configuration.

A query can return entry types in sequence:

SELECT
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER,
    JOURNAL_ENTRY_TYPE,
    OPERATION,
    ORDER_ID,
    ORDER_STATUS,
    ORDER_TOTAL
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 1 DAY
    )
)
WHERE ORDER_ID = 100245
ORDER BY SEQUENCE_NUMBER;

Do not assume every update has both a before and after image. Confirm the journal settings and entry types being deposited.

Available filter parameters

The generated reader supports many of the same filters as QSYS2.DISPLAY_JOURNAL, including:

STARTING_RECEIVER_LIBRARY
STARTING_RECEIVER_NAME
STARTING_TIMESTAMP
STARTING_SEQUENCE
JOURNAL_ENTRY_TYPES
OBJECT_MEMBER
USER
JOB
PROGRAM
ENDING_RECEIVER_LIBRARY
ENDING_RECEIVER_NAME
ENDING_TIMESTAMP
ENDING_SEQUENCE

A value cannot be supplied for both a starting timestamp and starting sequence in the same request.

Query one job

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    ORDER_ID,
    ORDER_STATUS,
    PROGRAM_NAME,
    COMMIT_CYCLE
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 1 DAY,
        JOB =>
            '123456/APPUSER/ORDERJOB'
    )
)
ORDER BY ENTRY_TIMESTAMP DESC;

Query one program

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    USER_NAME,
    JOB_NUMBER,
    JOB_USER,
    JOB_NAME,
    ORDER_ID,
    ORDER_STATUS
FROM TABLE(
    MYTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 1 DAY,
        PROGRAM =>
            'ORDUPD'
    )
)
ORDER BY ENTRY_TIMESTAMP DESC;

Unsupported application column types

IBM documents that the generated reader does not return application columns with these data types:

CLOB
DBCLOB
BLOB
XML
DATALINK
user-defined types

The journal metadata columns are still part of the generated interface, but those file columns are not exposed as decoded application columns.

Row and column access control

Journal data can contain sensitive historical values.

QSYS2.DISPLAY_JOURNAL recognizes active row permissions and column masks for the target table when returning entry data.

Still, do not assume journal access is harmless.

Journal history may expose:

• previous values
• deleted values
• user activity
• business transactions
• data retained longer than the live row

Apply least privilege to the generated function and underlying journal objects.

Creation authority

The caller creating the reader needs:

• *ADD and *EXECUTE authority to the output library
• the authority required by CREATE FUNCTION
• replacement authority when the generated function already exists

The generated table function is:

Owned by the caller
Created with *PUBLIC *EXCLUDE

That is a good secure default.

Grant access deliberately.

Reader authority

A user invoking the generated function needs authority to:

• the generated function service program
• the function library
• the database-file library
• the database file
• the journal library
• the journal
• all requested journal receivers

A missing-authority error can occur even when the user can query the live table.

Regenerate after a schema change

The generated function is based on the file definition at creation time.

After adding, removing, renaming, or changing table columns, regenerate it:

VALUES QSYS2.CREATE_DATA_JOURNAL_READER(
    LIBRARY_NAME   => 'PRODLIB',
    FILE_NAME      => 'ORDERS',
    OUTPUT_LIBRARY => 'MYTOOLS'
);

Because the existing function is replaced, review dependent objects and grants as part of the deployment process.

SQL long names

FILE_NAME must be the system object name.

For an SQL table with a long name, find the system name:

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    SYSTEM_TABLE_SCHEMA,
    SYSTEM_TABLE_NAME
FROM QSYS2.SYSTABLES
WHERE TABLE_SCHEMA = 'PRODLIB'
  AND TABLE_NAME = 'CUSTOMER_ORDERS';

Pass SYSTEM_TABLE_NAME to CREATE_DATA_JOURNAL_READER.

Native option versus generated reader

Use DSPJRN when:

• reviewing entries interactively
• working from a green screen
• copying journal data to an output file
• using an established operational procedure

Use QSYS2.DISPLAY_JOURNAL when:

• querying several object types
• interpreting entry data manually
• building a general-purpose journal utility

Use a generated data journal reader when:

• one file’s record values must be exposed as columns
• application keys need to be filtered
• historical row values need SQL analysis
• a support or audit query will be reused

Release and PTF requirement

IBM lists the service as new with:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

It is not listed as supported on IBM i 7.4.

A practical workflow

1. Confirm the file is journaled.
2. Identify its system library and system file name.
3. Choose a controlled output library.
4. Generate the reader.
5. Grant only required users access.
6. Query a narrow timestamp or receiver range.
7. Filter by business key, job, user, or program.
8. Validate entry types and journal configuration.
9. Regenerate after file-definition changes.
10. Protect journal history as sensitive data.

Final takeaway

CREATE_DATA_JOURNAL_READER converts a file’s journal history from generic entry data into a file-aware SQL interface.

It does not replace journaling knowledge, receiver management, or authority controls—but it makes record-level investigation dramatically easier when the business columns are available directly in the result.