Accessing System i Journal Files

The Problem

Recently I got the following question from one of the Technical Specialists in our field team: ‘How can I expose the change-data-capture information from the System i journal files to a process running on BizTalk Server?’

The Solution

After a joyful day of research, I found a simple way to achieve this goal by using two BizTalk Adapters that are part of the Microsoft BizTalk Adapters for Host Systems package.

This blog post assumes you have familiarity with BizTalk as well as basic knowledge of System i administration.

Let’s start by creating sample data in the System i, so we can have a journal file to be used as a example. For this example, I will start creating a SQL Collection. By default, SQL Collections on the System i will automatically have a journal receiver. So, it’s time to connect to the System i by using a 5250 emulator and fire up some SQL Commands.

Enter the Interactive SQL dialog by typing STRSQL in the main System i menu. Using the STRSQL dialog, type the following command: CREATE COLLECTION JRNTST

This step creates a System i library named JRNTST. We will use this library in the subsequent examples.

The JRNTST library can be inspected by using the following command: WRKOBJ OBJ(JRNTST/*ALL)

We can see that a Journal (QSQJRN) and a Journal Receiver(QSQJRNXXXX) had been created automatically as part of the collection’s creation.

Any subsequent operation inside this collection will be stored in the journal and can be captured by reading the contents of it.

There are several ways one can read the contents of the journal. In this example, we will write the contents of the journal to a physical file (using the DSPJOB command) and then read this file. For this task, we will use the BizTalk Adapter for Host Files.

Now it’s time to start configuring the BizTalk side of the solution. Let’s begin by creating a new BizTalk Application.

Using BizTalk Server Administration console, create a new Application called Journal Test.

The next step, is to create one send port based in the BizTalk Adapter for Host Files that will be used to execute the CL command that captures the journal contents to a physical file. This same send port will enable us to read the contents of the physical file.

We will start by creating a new Static Solicit-Response Send Port named SendPortHF.

 

 

 

The Send Port configuration is straightforward: We need to provide a Connection String, Target Namespace and Root Element name.

 

 

 

The Connection Configuration dialog is launched. At this point, let’s create a new connection string.

 

 

We will be connecting to a System i machine over the TCP/IP protocol.

 

 

 

 

In the Network Connection Properties page, let’s provide the name of the server as well as the port in which the System i is listening to DRDA requests.

 

The next step is to specify the location and default library names. These values can be retrieved in the AS/400 by using the WRKRDBDIRE (Work with Relational Database Directory Entries) and WRKUSRPF (Work with User Profiles) System i commands.

The Locale page will allow you to specify CCSID settings.

 

 

 

 

 

You should specify your credentials using the Security page.

 

 

The remaining pages of the Wizard should be left with the default values. This completes the send port configuration.

At this point, we issue the commands to output the journal to a physical file as well as to read the contents of this file by submitting the following XML documents to the BizTalk Adapter for Host Files:

This will cause the execution of the command DSPJRN with a option to save the output to a specific file (in the example above, RICARDO/JRNTSTOUT).

Now we need to read the contents of the journal by submitting a XML document containing a query to read the contents of the output file created earlier:

Conclusion

By using the ability of BizTalk Adapter for Host Files to execute System i system commands as well as to read physical and logical files we are able to read the contents of the AS/400 journal files.

Remarks

This blog post did not described the creation of the metadata that describes the System i physical file. This will be published in a future blog post.

The process described here, shows how to get access to the raw journal content. Additional parsing needs to be done to extract the actual database operations from the raw journal data.

In order to keep this blog post concise, some of the environment configuration description was skipped.

 

Regards,

Ricardo