Display Journal Activity (DSPJRNACT) Directory

Directory .ZIP

These items go into making a DSPJRNACT command, a command to help with viewing journaled changes to records in a file by converting the record image in the journal entry to fully described fields. Numerous APIs and SQL features are involved, all of which are standard system functions and available with only a license for the operating system. No optional licensed features are needed.

This is a minimally functional demonstration. The DSPJRNACT command coulld be enhanced to include date range, selected entry types or other parameters. This demo leaves those elements out to focus on exposing the field definitions in the JOESD -- entry specific data.

Please read the @GENERAL DISCLAIMER document for any items you should be aware of if you download any of these items.


DSPJRNACT -- Display Journal Activity

This program uses the Retrieve File Description (QDBRTVFD) API to extract a physical file's journal name to set up a DSPJRN command that places relevant journal entries into a file.

The file can then be queried. If the 'entry specific data' portion of the entry is interpreted using the journaled file's field descriptions, the query can show the details of the entry along with the fields from the record. The program uses some simple SQL techniques to apply those field descriptions.


DSPJRNACT -- Display Journal Activity:

The command definition object source for DSPJRNACT. This command accepts the file name and library and passes those into the DSPJRNACT program.

SQL source for RUNSQLSTM

Create temporary view for DSPJRNACT:

The SQL is input for a RUNSQLSTM command. It contains a CREATE VIEW statement that provides the list of journal detail fields that will be used when journal entry fields are used along with the file fields.

A system model file is used by the CL in order to create a base file to place the journal entries in. Since it's a system model file, we can be reasonably certain we know what fields will be present, though they tend to remain constant from release to release as long as we use the same format. We use the *TYPE4 format.

One field must be left off of the list of journal entry fields -- JOESD, the field that contains the record image of the file that's journalled. Rather than use JOESD, later we'll use the field definitions from the file.

SQL source for STRQMQRY

Create combined fields file:

The SQL is used to create a record format that contains all journal entry fields except JOESD plus all of the fields from the journaled file. It contains a SQL SELECT statement that has all journal entry fields from the view that we created plus all fields from the journaled file. The CRTQMQRY command can create a QM query object from this source. The STRQMQRY command is used in the CL to execute this QM query.

The 'trick' has two parts. The SELECT field list references A.* and B.* -- i.e., all fields from 'A' and from 'B'. The 'A' corelation is the journal fields (minus JOESD); the 'B' corelation is all fields from the journaled file. By specifying 'B' last in the field list, all of these fields come in the area that would normally be simply a record image from JOESD. The second 'trick' is the WHERE clause. Since the clause specifies all rows where '1<>1' which should never be true, we ensure that no rows are selected. The result is a file description with no rows in it.

The DSPJRNACT CL program issues a CPYF command that puts the rows into the file. The CPYF command specifies FMTOPT(*NOCHK). This essentially causes the data to be copied without regard to format level-checks. The record images that are held in the JOESD field get overlain on the fields that we brought in from the journaled file. The result is that JOESD is converted into a set of individually described fields. We use standard system features to get the work done automatically.