IBM i Data Integration

Turn IBM i Journal Entries into Application Columns with SQL

Build a controlled journal-data workflow with QSYS2.CREATE_DATA_JOURNAL_READER, including typed application columns, before and after images, transaction handling, checkpoints, receiver retention, schema changes, authority, and downstream processing.

IBM i journals preserve a detailed history of database changes, but consuming record images has traditionally required knowledge of journal layouts, entry-specific data, null indicators, and file formats. QSYS2.CREATE_DATA_JOURNAL_READER can generate a table function that exposes supported fields from one journaled file as normal SQL columns.

The generated reader can make journal data easier to use for:

• troubleshooting
• operational auditing
• change analysis
• downstream integration
• data-repair investigation
• application lifecycle tracking
• controlled change-data capture

The function does not turn journaling into a complete replication or event-delivery platform by itself.

A production design must still handle:

Record-image meaning
Commit and rollback
Checkpoint durability
Receiver availability
Duplicate prevention
Schema changes
Authority
Retention
Downstream failure

The problem with generic journal entry data

QSYS2.DISPLAY_JOURNAL exposes rich journal metadata and the raw entry-specific data.

For a database-record operation, the application values can be embedded inside:

ENTRY_DATA
NULL_VALUE_INDICATORS

A generic reader must understand:

• the physical record layout
• field offsets
• data types
• null indicators
• before and after images
• minimized entry data
• schema evolution

That is manageable for a purpose-built program, but it creates friction for analysts, support teams, and SQL-driven integrations.

CREATE_DATA_JOURNAL_READER uses the current column definitions of a journaled file to generate a file-specific SQL table function.

Create the reader

Suppose this table is journaled:

PRODLIB/ORDERS

Create the reader in JRNTOOLS:

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

The scalar function returns:

 1   Reader created successfully
-1   Reader creation failed

The generated function name is:

DISPLAY_JOURNAL_PRODLIB_ORDERS

and it is created in:

JRNTOOLS

Query it with:

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

What the generated reader returns

The result includes a column for each supported column in the source file.

It also includes journal metadata such as:

ENTRY_TIMESTAMP
OPERATION
USER_NAME
RRN
SEQUENCE_NUMBER
JOURNAL_ENTRY_TYPE
MEMBER_NAME
JOB_NAME
JOB_USER
JOB_NUMBER
THREAD
JOURNAL_IDENTIFIER
JOURNAL_LIBRARY
JOURNAL_NAME
RECEIVER_LIBRARY
RECEIVER_NAME
PROGRAM_LIBRARY
PROGRAM_NAME
COMMIT_CYCLE
NESTED_COMMIT_LEVEL
SYSTEM_NAME
SYSTEM_SEQUENCE_NUMBER
TRIGGER
MINIMIZED_ENTRY_DATA
NULL_VALUE_INDICATORS
ENTRY_DATA

This means a journal entry can be queried like an application row:

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    JOURNAL_ENTRY_TYPE,
    ORDER_NUMBER,
    CUSTOMER_NUMBER,
    ORDER_STATUS,
    ORDER_TOTAL,
    JOB_USER,
    PROGRAM_NAME,
    SEQUENCE_NUMBER
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS()
)
ORDER BY
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER;

The exact application columns depend on the file definition used when the reader is generated.

Unsupported 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 source file can contain those columns, but they are not exposed as typed application columns by the generated function.

The generic journal metadata, including ENTRY_DATA, is still returned.

Do not assume that a reader provides a complete business row when the file contains unsupported types.

Document excluded columns and decide whether they require:

• a separate lookup from the current table
• an object identifier
• a different journal interface
• a purpose-built RPG or C consumer
• a downstream document store
• no replication at all

The reader handles record-operation entries

The generated table function reads journal code:

R

which represents record-level operations.

Common record entry types include:

PT   Record written to a member
PX   Record added directly by relative record number
UB   Before-image of an updated record
UP   After-image of an updated record
DL   Record deleted
BR   Before-image updated during rollback
UR   After-image updated during rollback
DR   Record deleted during rollback

The exact entries available depend on:

• the operation
• journaling image settings
• rollback activity
• file attributes
• minimized-entry settings

Filter the reader

The generated function supports many of the filters used by QSYS2.DISPLAY_JOURNAL.

Supported parameters include:

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

For example, retrieve inserts and after-images of updates after a known sequence:

SELECT
    ENTRY_TIMESTAMP,
    JOURNAL_ENTRY_TYPE,
    ORDER_NUMBER,
    CUSTOMER_NUMBER,
    ORDER_STATUS,
    ORDER_TOTAL,
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    SEQUENCE_NUMBER
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_SEQUENCE   => 125000,
        JOURNAL_ENTRY_TYPES => 'PT PX UP'
    )
)
ORDER BY
    SEQUENCE_NUMBER;

For a bounded investigation:

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    JOURNAL_ENTRY_TYPE,
    ORDER_NUMBER,
    ORDER_STATUS,
    JOB_USER,
    PROGRAM_NAME,
    SEQUENCE_NUMBER
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            TIMESTAMP('2026-07-28-08.00.00'),
        ENDING_TIMESTAMP =>
            TIMESTAMP('2026-07-28-10.00.00')
    )
)
ORDER BY
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER;

Do not provide both a starting timestamp and a starting sequence in the same call.

Understand before and after images

IBM i can journal:

*AFTER
*BOTH

record images for a physical file.

With IMAGES(*BOTH), an update can produce:

UB   Before-image
UP   After-image

A delete can include the record image needed to understand what was removed.

With IMAGES(*AFTER), before-image entries are not available in the same way.

A reader query should state which image it expects.

Current value after an update

SELECT
    ORDER_NUMBER,
    ORDER_STATUS,
    ORDER_TOTAL,
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        JOURNAL_ENTRY_TYPES => 'UP'
    )
);

Value before an update

SELECT
    ORDER_NUMBER,
    ORDER_STATUS,
    ORDER_TOTAL,
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        JOURNAL_ENTRY_TYPES => 'UB'
    )
);

An audit report can pair UB and UP entries, but it must use reliable journal context rather than assuming adjacent rows always belong together.

Useful correlation columns include:

• journal identifier
• job
• thread
• commit cycle
• sequence
• record identity
• timestamp

Do not treat every record entry as committed

This is one of the most important production considerations.

Under commitment control, record-level entries are written while changes occur.

Commit and rollback control entries are separately written under journal code:

C

The generated file-specific reader focuses on journal code R.

It returns values such as:

COMMIT_CYCLE
NESTED_COMMIT_LEVEL

but it does not, by itself, convert every returned record image into a committed business event.

A transaction can:

Insert a row
Update a row
Delete a row
Roll back

The journal can contain the original record entries and rollback-related record entries.

A downstream system that publishes every PT, PX, UP, or DL row immediately can publish changes that do not represent the final committed outcome.

Build a commit-aware design

For committed-only change capture, correlate record entries with commitment-control entries from the underlying journal.

A conceptual workflow is:

1. Read record operations from the generated reader.
2. Identify entries with a commit-cycle value.
3. Read journal code C entries through QSYS2.DISPLAY_JOURNAL.
4. Determine whether the commit cycle completed with commit or rollback.
5. Publish only according to the approved transaction rules.
6. Handle rollback record entries deliberately.

Commit-control entries include:

C SC   Start commit cycle
C CM   Commit
C RB   Rollback

A simplified control-entry query can begin with:

SELECT
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER,
    JOURNAL_CODE,
    JOURNAL_ENTRY_TYPE,
    COMMIT_CYCLE,
    JOB_NAME,
    JOB_USER,
    JOB_NUMBER
FROM TABLE(
    QSYS2.DISPLAY_JOURNAL(
        JOURNAL_LIBRARY    => 'JRNLIB',
        JOURNAL_NAME       => 'APPJRN',
        JOURNAL_CODES      => 'C',
        JOURNAL_ENTRY_TYPES => 'SC CM RB'
    )
)
ORDER BY
    SEQUENCE_NUMBER;

Confirm the parameter names and available columns against the installed IBM i documentation and PTF level before deploying this pattern.

A robust transaction-aware consumer may need to buffer uncommitted changes until the corresponding commit boundary is known.

Autocommit and non-transactional changes

Not every record operation has a meaningful multi-statement commit cycle.

The consumer must distinguish:

• changes performed under commitment control
• autocommit changes
• native record I/O outside commitment control
• rollback-generated record entries
• system-generated activity

Do not invent a transaction boundary where the journal does not provide one.

Define how each category is handled.

Use a durable checkpoint

A continuous consumer needs to remember where processing stopped.

A weak checkpoint stores only:

SEQUENCE_NUMBER

Journal sequence numbers can be reset.

Receiver chains can also change, break, become unavailable, or contain saved-and-freed receivers.

A stronger checkpoint includes:

Journal library
Journal name
Receiver library
Receiver name
Sequence number
Entry timestamp
System identity
Consumer name
Checkpoint status

Example:

CREATE TABLE INTEGRATION.JOURNAL_CHECKPOINT
(
    CONSUMER_NAME        VARCHAR(50) NOT NULL,
    JOURNAL_LIBRARY      VARCHAR(10) NOT NULL,
    JOURNAL_NAME         VARCHAR(10) NOT NULL,
    RECEIVER_LIBRARY     VARCHAR(10) NOT NULL,
    RECEIVER_NAME        VARCHAR(10) NOT NULL,
    SEQUENCE_NUMBER      DECIMAL(21, 0) NOT NULL,
    ENTRY_TIMESTAMP      TIMESTAMP NOT NULL,
    SYSTEM_NAME          VARCHAR(8),
    UPDATED_TIMESTAMP    TIMESTAMP NOT NULL,
    UPDATED_BY_JOB       VARCHAR(28),
    STATUS               VARCHAR(20) NOT NULL,
    PRIMARY KEY
    (
        CONSUMER_NAME,
        JOURNAL_LIBRARY,
        JOURNAL_NAME
    )
);

The checkpoint should identify an exact position in the receiver chain.

Advance the checkpoint only after durable processing

A safe batch lifecycle is:

1. Read from the last durable checkpoint.
2. Write candidate events to a staging table.
3. Resolve commit and rollback status.
4. Deliver or apply events downstream.
5. Record downstream success durably.
6. Advance the checkpoint.

Do not advance the checkpoint before downstream work succeeds.

Otherwise, a delivery failure can create a permanent gap.

Make downstream writes idempotent

A consumer can restart after:

Reading entries
Writing some events
Sending an external message
Updating the downstream database
Crashing before the checkpoint update

On restart, the same journal entries may be read again.

Create an immutable source key such as:

System name
Journal library
Journal name
Receiver library
Receiver name
Sequence number
Journal identifier
Entry type

Example event ledger:

CREATE TABLE INTEGRATION.JOURNAL_EVENT
(
    EVENT_ID             BIGINT
                         GENERATED ALWAYS AS IDENTITY,
    SOURCE_SYSTEM        VARCHAR(8) NOT NULL,
    JOURNAL_LIBRARY      VARCHAR(10) NOT NULL,
    JOURNAL_NAME         VARCHAR(10) NOT NULL,
    RECEIVER_LIBRARY     VARCHAR(10) NOT NULL,
    RECEIVER_NAME        VARCHAR(10) NOT NULL,
    SEQUENCE_NUMBER      DECIMAL(21, 0) NOT NULL,
    JOURNAL_IDENTIFIER   VARCHAR(10),
    JOURNAL_ENTRY_TYPE   CHAR(2) NOT NULL,
    COMMIT_CYCLE         DECIMAL(21, 0),
    EVENT_STATUS         VARCHAR(20) NOT NULL,
    CREATED_TIMESTAMP    TIMESTAMP NOT NULL,
    DELIVERED_TIMESTAMP  TIMESTAMP,
    ERROR_MESSAGE        VARCHAR(2048),
    UNIQUE
    (
        SOURCE_SYSTEM,
        JOURNAL_LIBRARY,
        JOURNAL_NAME,
        RECEIVER_LIBRARY,
        RECEIVER_NAME,
        SEQUENCE_NUMBER,
        JOURNAL_ENTRY_TYPE
    )
);

The exact unique key should be tested against the journal and application design.

The downstream business operation should also be idempotent where possible.

Read bounded batches

Avoid reading an unbounded receiver history every cycle.

Use:

• a starting checkpoint
• an ending sequence captured at batch start
• a row-count limit in staging logic
• a maximum processing duration
• controlled receiver ranges

A bounded batch gives the consumer a stable unit of work.

Conceptual process:

Capture current high-water mark
Read checkpoint + 1 through high-water mark
Process and reconcile
Advance checkpoint to high-water mark

Account for sequence resets and receiver changes when defining the boundary.

Coordinate receiver retention with consumers

A journal consumer depends on receiver availability.

If a receiver is:

• deleted
• damaged
• unavailable
• saved with storage freed
• outside the available receiver chain

the consumer may not be able to read the expected historical entries.

Receiver deletion must therefore consider:

Backup and recovery needs
Commitment-control recovery
Replication
Audit retention
Integration checkpoints
Legal or compliance retention
Problem investigation

Do not delete a detached receiver merely because it is detached.

The consumer should expose:

Oldest required receiver
Current checkpoint receiver
Current attached receiver
Processing lag
Receiver deletion safe-through point

Monitor journal receiver state

Useful IBM i services include:

QSYS2.JOURNAL_INFO
QSYS2.JOURNAL_RECEIVER_INFO
QSYS2.JOURNALED_OBJECTS

A receiver-monitoring query can identify:

• attached receiver
• first and last sequence
• attach and detach timestamps
• receiver size
• previous and next receiver
• save status
• receiver availability

The exact columns depend on the service and release.

Build receiver cleanup around verified consumer checkpoints—not file-name patterns or age alone.

Sequence resets require explicit handling

A sequence reset can return journal numbering to a lower value.

If a consumer stores only the last sequence number, it may:

• skip new entries
• reread old entries
• reject valid entries as duplicates
• compare positions incorrectly

Use receiver identity with sequence.

Also preserve:

SYSTEM_SEQUENCE_NUMBER

when it is useful for distinguishing system-wide journal entry ordering.

Do not assume one sequence value is globally unique forever.

Schema changes require reader lifecycle management

The generated function is built from the source file’s column definitions at creation time.

When the file definition changes, treat reader regeneration as part of the database deployment.

Recommended deployment flow:

1. Identify impacted generated readers.
2. Pause or drain the consumer.
3. Apply the table change.
4. Regenerate the data journal reader.
5. Review function owner and authorities.
6. Validate result columns.
7. Test entries created before and after the change.
8. Update downstream mappings.
9. Resume processing.

Because the function is generated from current definitions, do not assume a reader created before the DDL change automatically exposes new or changed columns.

Also test how historical entries created under the previous record format are represented.

Track reader definitions

Create a catalogue:

CREATE TABLE INTEGRATION.JOURNAL_READER_CATALOG
(
    SOURCE_LIBRARY       VARCHAR(10) NOT NULL,
    SOURCE_FILE          VARCHAR(10) NOT NULL,
    OUTPUT_LIBRARY       VARCHAR(10) NOT NULL,
    FUNCTION_NAME        VARCHAR(128) NOT NULL,
    SOURCE_FORMAT_LEVEL  VARCHAR(64),
    GENERATED_TIMESTAMP  TIMESTAMP NOT NULL,
    GENERATED_BY         VARCHAR(128) NOT NULL,
    DEPLOYMENT_ID        VARCHAR(128),
    STATUS               VARCHAR(20) NOT NULL,
    PRIMARY KEY
    (
        SOURCE_LIBRARY,
        SOURCE_FILE
    )
);

Your deployment tooling can regenerate readers after approved DDL changes.

Existing generated functions are replaced

If the expected generated function already exists, CREATE_DATA_JOURNAL_READER replaces it.

That is convenient for deployment, but it also means regeneration is a change to an executable SQL object.

Review:

• owner
• function authority
• dependent views
• stored procedures
• applications using the function
• output columns
• deployment timing
• rollback strategy

IBM creates the generated function with:

*PUBLIC *EXCLUDE

and the caller owns it.

Grant only the access needed by approved consumers.

Required authority

To create the reader, the caller needs:

• *ADD and *EXECUTE authority to the output library
• authority required to create an SQL table function
• replace authority when the function already exists

To use the generated reader, the caller needs authority to:

• the generated service program
• the library containing the function
• the database file and its library
• the journal and its library
• every requested journal receiver and its library

This is intentionally broader than simple SELECT authority on the application table.

Use a dedicated consumer profile or controlled wrapper.

Do not grant broad journal-receiver access to every analyst.

Minimized entry data

A journal can be configured to minimize entry-specific data.

Minimization can reduce journal receiver size by recording only changed information when that produces a smaller entry.

The reader returns indicators including:

MINIMIZED_ENTRY_DATA
MINIMIZED_ON_FIELD_BOUNDARY

Do not assume every application field in a minimized update represents a complete row image.

IBM’s formatted journal interfaces can return default values for fields not recorded under field-boundary minimization, with null-indicator metadata identifying fields that were not present.

Test the exact journal configuration before using the output for replication or audit reconstruction.

Null values matter

The generated function returns typed application columns and also exposes:

NULL_VALUE_INDICATORS

Distinguish:

A column was recorded with NULL
A column was not present in a minimized entry
A column type is unsupported
The journal entry does not represent the image expected by the query

A downstream JSON event should preserve the difference between:

{
  "customerNote": null
}

and:

{
  "customerNotePresent": false
}

when that distinction is material.

Avoid current-row lookups as the only event source

A tempting pattern is:

Read journal entry
Take the primary key
SELECT the current row
Publish the result

This loses historical accuracy when the row has changed again or has been deleted.

It can still be valid for a “refresh current state” integration, but it is not equivalent to publishing the journaled record image.

Define whether the downstream contract is:

Historical event
Before image
After image
Current-state refresh
Delete marker
Transaction summary

Model deletes explicitly

A deleted row no longer exists in the source table.

The journal entry may be the only available business image.

A downstream event can include:

{
  "operation": "DELETE",
  "orderNumber": 12345,
  "eventTimestamp": "2026-07-28T10:31:22",
  "sourceSequence": 7654321
}

Do not represent a delete as a failed current-row lookup.

Separate journal extraction from delivery

Use distinct lifecycle stages:

EXTRACTED
TRANSACTION_PENDING
COMMITTED
ROLLED_BACK
READY
DELIVERING
DELIVERED
FAILED
DEAD_LETTER

This allows:

• transaction resolution
• delivery retry
• duplicate suppression
• poison-event handling
• replay
• audit reporting
• checkpoint reconciliation

A receiver-reading job should not block indefinitely because one external API is unavailable.

Use a staging table

Example:

CREATE TABLE INTEGRATION.ORDER_CHANGE_STAGE
(
    STAGE_ID             BIGINT
                         GENERATED ALWAYS AS IDENTITY,
    RECEIVER_LIBRARY     VARCHAR(10) NOT NULL,
    RECEIVER_NAME        VARCHAR(10) NOT NULL,
    SEQUENCE_NUMBER      DECIMAL(21, 0) NOT NULL,
    JOURNAL_ENTRY_TYPE   CHAR(2) NOT NULL,
    ENTRY_TIMESTAMP      TIMESTAMP NOT NULL,
    COMMIT_CYCLE         DECIMAL(21, 0),
    ORDER_NUMBER         BIGINT,
    CUSTOMER_NUMBER      BIGINT,
    ORDER_STATUS         VARCHAR(20),
    ORDER_TOTAL          DECIMAL(15, 2),
    PROCESS_STATUS       VARCHAR(20) NOT NULL,
    CREATED_TIMESTAMP    TIMESTAMP NOT NULL,
    UNIQUE
    (
        RECEIVER_LIBRARY,
        RECEIVER_NAME,
        SEQUENCE_NUMBER,
        JOURNAL_ENTRY_TYPE
    )
);

Load a bounded batch:

INSERT INTO INTEGRATION.ORDER_CHANGE_STAGE
(
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    SEQUENCE_NUMBER,
    JOURNAL_ENTRY_TYPE,
    ENTRY_TIMESTAMP,
    COMMIT_CYCLE,
    ORDER_NUMBER,
    CUSTOMER_NUMBER,
    ORDER_STATUS,
    ORDER_TOTAL,
    PROCESS_STATUS,
    CREATED_TIMESTAMP
)
SELECT
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    SEQUENCE_NUMBER,
    JOURNAL_ENTRY_TYPE,
    ENTRY_TIMESTAMP,
    COMMIT_CYCLE,
    ORDER_NUMBER,
    CUSTOMER_NUMBER,
    ORDER_STATUS,
    ORDER_TOTAL,
    'EXTRACTED',
    CURRENT TIMESTAMP
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_RECEIVER_LIBRARY =>
            :STARTING_RECEIVER_LIBRARY,
        STARTING_RECEIVER_NAME =>
            :STARTING_RECEIVER_NAME,
        STARTING_SEQUENCE =>
            :STARTING_SEQUENCE,
        ENDING_RECEIVER_LIBRARY =>
            :ENDING_RECEIVER_LIBRARY,
        ENDING_RECEIVER_NAME =>
            :ENDING_RECEIVER_NAME,
        ENDING_SEQUENCE =>
            :ENDING_SEQUENCE,
        JOURNAL_ENTRY_TYPES =>
            'PT PX UB UP DL BR UR DR'
    )
);

Parameter markers are shown conceptually. Use them through an SQL routine or host program that supports the required parameterization.

Preserve source metadata in every event

A downstream event should retain enough source context to support replay and investigation.

Useful metadata includes:

Source system
Source library and file
Journal and receiver
Sequence number
Entry timestamp
Entry type
Operation
Job and user
Program and module
Commit cycle
Journal identifier
Member
RRN

Do not publish only application values and discard the source position.

Verify lag

Track:

Latest journal position
Consumer checkpoint
Oldest unprocessed entry
Oldest pending commit cycle
Oldest failed event
Current attached receiver

Lag can be measured by:

• sequence distance
• entry timestamp age
• receiver count
• staged event count
• delivery queue age

Timestamp age is often easier for operators to understand:

Consumer is 4 minutes behind

Receiver-based lag is useful for retention safety.

Use cases

Troubleshooting

Find who changed an order and from which program:

SELECT
    ENTRY_TIMESTAMP,
    OPERATION,
    ORDER_NUMBER,
    ORDER_STATUS,
    JOB_USER,
    JOB_NAME,
    PROGRAM_LIBRARY,
    PROGRAM_NAME,
    RECEIVER_NAME,
    SEQUENCE_NUMBER
FROM TABLE(
    JRNTOOLS.DISPLAY_JOURNAL_PRODLIB_ORDERS(
        STARTING_TIMESTAMP =>
            CURRENT TIMESTAMP - 1 DAY
    )
)
WHERE ORDER_NUMBER = 12345
ORDER BY
    ENTRY_TIMESTAMP,
    SEQUENCE_NUMBER;

Audit review

Pair before and after images and preserve:

• user
• job
• program
• timestamp
• old value
• new value
• transaction outcome

Integration

Publish committed order changes to:

• an API
• Kafka or another event platform
• a data warehouse
• an external database
• a monitoring platform

Lifecycle tracking

Record when an application message or business row was:

Created
Updated
Submitted
Completed
Cancelled
Deleted

The journal can provide independent evidence of database changes.

When the generated reader is a strong fit

Use it when:

• one journaled file is the source
• typed application columns are valuable
• SQL is the preferred processing interface
• unsupported LOB columns are not required
• receiver retention can be coordinated
• transaction semantics are understood
• processing is batch or near-real-time rather than ultra-low-latency

When another interface may be better

Use QSYS2.DISPLAY_JOURNAL directly when:

• many object types must be read generically
• journal code C and other control entries are central
• raw entry data is required
• one query must span multiple files

Use RCVJRNE or QjoRetrieveJournalEntries when:

• continuous exit-program delivery is required
• pointer-based entry data is needed
• very large entry-specific data must be handled
• lower-level journal processing is appropriate

Use a replication product when:

• committed transaction replication is required
• ordering and restart are business critical
• conflict handling is needed
• high availability is the primary goal
• supported operational tooling is required

Do not rebuild a replication engine casually.

PTF requirement

QSYS2.CREATE_DATA_JOURNAL_READER is available at:

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 or 7.3 in the current IBM i Services matrix.

Detailed SQL reference

For the focused creation and query guide, see:

Create a Data Journal Reader for an IBM i Table

For a related journal-storage incident, see:

IBM i High System ASP Usage: QRECOVERY Journal Receivers and an Open Transaction

Verify that the QRECOVERY article route matches the deployed Era of i slug before publishing this link.

Final takeaway

CREATE_DATA_JOURNAL_READER removes one of the largest barriers to using IBM i journal data from SQL:

It turns supported record fields into normal typed columns.

The generated reader solves record parsing.

A production journal-data process must still solve:

Commit and rollback
Exact source position
Restart
Duplicate prevention
Receiver retention
Schema evolution
Authority
Minimized data
Delivery
Monitoring
Recovery

The right design is not simply:

Read the journal and send every row.

It is:

Read a bounded, identifiable journal range; understand the record image and transaction outcome; process it idempotently; advance the checkpoint only after durable success; and retain the receivers needed to recover.