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?’
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 and the BizTalk Adapter for DB2.
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 two send ports, one 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, and the other to read the contents of the physical file using the BizTalk Adapter for DB2.
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.