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

Yes, you read it right! The Host Integration Server Team is going to Disneyland! Ok, not the entire team and it's definitely not on vacations! And we are not going to Disneyland exactly...

On the first two weeks of June we head over Orlando to participate on Tech*Ed. You will have the chance to meet individuals from the product team on both weeks: DEV and ITPRO.

Besides the usual booth duty, we will present the following sessions:

DEV Week:

• Delivering Enterprise Solutions Using Microsoft BizTalk Adapters for Host Systems
• Publishing and Extending Business Rules in Mainframe (CICS and IMS) and AS/400 Programs Using Microsoft Host Integration Server

ITPRO Week:

• SOA352 - Enabling Knowledge Workers to Access Mainframe Programs and Data
• SOA358 - Publishing and Extending Business Rules in Mainframe (CICS and IMS) and AS/400 Programs Using Microsoft Host Integration Server
• SOA56 TLC - Building Customer Care Solutions Using Microsoft Host Integration Server 2006 and BizTalk Adapters for Host Systems

Please join us for these sessions! It would be great to meet you folks in person!

Cheers,

Ricardo

QUESTION:

When there is a failure calling a Stored Procedure using the BizTalk Adapter for DB2, you may see that some other SP´s are also called. You can also see that there is no relationship between this extra calls and the one that fails.
A trace can look similar to this:

 Preparing Stored Procedure in SP section: SYSIBM.SQLPROCEDURECOLS, 337, 553, 2, DRDAProcedureColumns, sqlcallcontrol.cpp,

 Executing Stored Procedure: SYSIBM.SQLPROCEDURECOLS, 337, 553, 9, DRDAProcedureColumns, sqlcallcontrol.cpp,

 DRDA AR message: Name: SQLCA Message, SQLSTATE: 42724, SQLCODE: -444, Error Procedure: DSNX9CAC, Database: DBT1LOC, Errors: {0, 0, 0, -1, 0, 0}, Diagnostic Information: DSNAPCO8?, 337, 553, 4, DRDAProcedureColumns, messagesync.h,

 Preparing Stored Procedure in SP section: SYSIBM.SQLCAMESSAGE, 337, 553, 2, DRDAGetDiagRec, sqlcallcontrol.cpp,

 Executing Stored Procedure: SYSIBM.SQLCAMESSAGE, 337, 553, 9, DRDAGetDiagRec, sqlcallcontrol.cpp,

 DRDA AR message: Name: ENDUOWRM, Severity: Warning, Database: DBT1LOC, 337, 553, 4, DRDAGetDiagRec, messagesync.h,

 Preparing SQL Select Statement: SELECT ''  AS PROCEDURE_CAT, RTRIM(OWNER)  AS PROCEDURE_SCHEM, RTRIM(NAME)  AS PROCEDURE_NAME, RTRIM(PARMNAME)  AS COLUMN_NAME, ORDINAL AS ORDINAL_POSITION, RTRIM(ROWTYPE)  AS COLUMN_TYPE, RTRIM(TYPENAME)  AS TYPE_NAME, LENGTH AS COLUMN_SIZE, LENGTH AS, 337, 553, 4, DRDAExecDirectW, sqlselectcontrol.cpp,

 Executing SQL Select Statement: SELECT ''  AS PROCEDURE_CAT, RTRIM(OWNER)  AS PROCEDURE_SCHEM, RTRIM(NAME)  AS PROCEDURE_NAME, RTRIM(PARMNAME)  AS COLUMN_NAME, ORDINAL AS ORDINAL_POSITION, RTRIM(ROWTYPE)  AS COLUMN_TYPE, RTRIM(TYPENAME)  AS TYPE_NAME, LENGTH AS COLUMN_SIZE, LENGTH AS, 337, 553, 8, DRDAExecDirectW, sqlselectcontrol.cpp,

 Closing SQL Select Statement: SELECT ''  AS PROCEDURE_CAT, RTRIM(OWNER)  AS PROCEDURE_SCHEM, RTRIM(NAME)  AS PROCEDURE_NAME, RTRIM(PARMNAME)  AS COLUMN_NAME, ORDINAL AS ORDINAL_POSITION, RTRIM(ROWTYPE)  AS COLUMN_TYPE, RTRIM(TYPENAME)  AS TYPE_NAME, LENGTH AS COLUMN_SIZE, LENGTH AS PREC, 337, 553, 16, DRDAFreeStmt, sqlselectcontrol.cpp,

 Local Transaction Commit, 337, 553, 3, DRDAFreeStmt, transactioncontext.cpp,

ANSWER:
The call to SQLPROCEDURECOLS is executed in order to gather metadata (parameter information) from the stored procedure to be executed. If an error occurs or this system procedure is not available, we fall back to query the metadata using SELECT statements gainst the system catalog (see the three ocurrences of SELECT ''  AS PROCEDURE_CAT…). The SQLCAMESSAGE is a stored procedure that we call when a error occur so we can gather extra information about the error that occurred. With that said, the calls for the procedures SQLPROCEDURECOLS and SQLCAMESSAGE are expected and, in this case, don’t tell much about the error occuring on the original procedure that was going to be called.

The most important factors that impacts buffer pool configuration are a) structure of the database and b) structure of the queries running against this database. These two factors will ultimately define what will be the I/O profile of these database operations. Buffer pools will reduce I/O as they will cache data avoiding load on the I/O devices.

With that said, it’s really difficult to provide a ‘magic number’ for this configuration parameter. DBA’s will likely tune the buffer pool based on the actual load of the database in a test environment. This tuning operation can be executed with the help of DB2’s online commands, more specifically the DISPLAY BUFFERPOOL and ALTER BUFFERPOOL commands. A brief descriptions on how to use these commands to tune the buffer pools is available at: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2.doc.admin/dmtbp.htm#dmtbp

A load on the database can be created by activating the BizTalk Adapter for DB2 in a test environment and then further analysis of I/O activities profile can indicate the need to change the default buffer pool configuration.

Below you find the steps needed to collect trace data from a DB2 connection using the data providers shipped with HIS 2006: 

• Navigate to the Start menu, select Run, type snatrace and hit Enter;
• On the SNA Trace Utility window, click Clear All Traces and Purge All Trace Files;
• In the List of Trace Items select DB2 Network Library and click the Properties button;
• Click the Set All button in the Internal Trace, Message Trace and API Trace dialog boxes;
• Click OK, OK and YES;
• Execute the execute the operations that causes the error;
• Navigate to the Start menu, select Run, type snatrace and hit Enter;
• Click Clear All Traces;
• Click OK;
• In the trace folder (usually <install dir>\traces) you will find the DB trace files.

Interpreting traces will be the topic of a future post.