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.
Cross Posted from http://blogs.msdn.com/ricardom
Thanks Charles Ezzell (Escalation Engineer on the Host Integration Server Team) for writing the whitepaper entitled Creating a Self-Running BizTalk Stress Application using the BizTalk Adapter for DB2 which can be found here.
Most daily operational data resides in host files systems, such as mainframe VSAM and the AS/400 physical files. Often, legacy programs written in COBOL and RPG control access to these data sources. To improve your processes, you need to collect and analyze this vital information with up-to-date business intelligence tools in Microsoft SQL Server 2005. Integrating host-based data sources demands attention to issues such as security access, network deployment, and data conversion of complex data elements into SQL-accessible tables. This webcast shows how SQL Server can efficiently integrate legacy data sources using data and application technologies in Microsoft Host Integration Server.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032278607&EventCategory=5&culture=en-US&CountryCode=US
Much mission-critical data resides in file systems, such as IBM mainframe Virtual Storage Access Method (VSAM) data sets and AS/400 physical files. To reliably read and write to these data stores, enterprise developers––working with IBM host systems–must write custom COBOL or RPG programs. Yet, this development time can hamper enterprise IT rollout of new solutions. In this webcast, we examine Microsoft Visual Studio 2005 and Microsoft Host Integration Server 2006 (including the new Microsoft .NET Framework Data Provider for Host Files) technologies that allow enterprise developers to quickly and efficiently build new .NET Framework applications based on industry-standard XML Web Services.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032330318&EventCategory=5&culture=en-US&CountryCode=US
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
Cross Posted from http://blogs.msdn.com/ricardom
Organizations are using Microsoft BizTalk Server 2006 to integrate and orchestrate composite solutions that span multiple systems and applications. Key assets in most enterprise solutions are the line-of-business (LOB) data stores on heritage IBM host systems. These data stores include IBM mainframe zSeries Sequential Access Method (SAM), Virtual Sequential Access Method (VSAM), and Partitioned Data Set (PDS) data sets, in addition to IBM midrange iSeries or i5 (AS/400) physical files and members. Records within these files are often program-described, redefined, or recurring, making it difficult to read and write without complex data mapping and conversion. In this webcast, learn how to apply the tools and capabilities of the BizTalk Adapters for Host Systems (including BizTalk Adapter for DB2 and BizTalk Adapter for Host Files) to efficiently access existing LOB information. Based on underlying Microsoft .NET data providers for DB2 and host files, these adapters offer flexible configuration tools, send and receive port capability, and a solution that requires no programming for integrating BizTalk Server with existing heritage data stores.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032329834&EventCategory=5&culture=en-US&CountryCode=US
Enterprise computing is based on high-volume transaction and batch processing systems, such as IBM mainframe and midrange host computers. To modernize these heritage systems, organizations are using business process orchestration in Microsoft BizTalk Server 2006. Join this webcast to learn how the new BizTalk Adapter for Host Applications allows enterprises to connect BizTalk Server solutions to existing IBM mainframe zSeries (CICS and IMS) or midrange iSeries (AS/400) server programs. Based on technology from the Microsoft Transaction Integrator (TI), this adapter offers an intuitive Microsoft Visual Studio 2005 designer, including host COBOL and RPG source code import wizards for generating XML Schema (XSD) for BizTalk Server projects. We explain how the administration tools are integrated with the BizTalk Server port configuration and deployment tools. Attend this session to see how you can use the BizTalk Adapter for Host Applications to efficiently extend host programs with new solutions based on BizTalk Server 2006.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032330320&EventCategory=5&culture=en-US&CountryCode=US
Many enterprise organizations rely on IBM DB2 relational database servers to store their vital line-of-business (LOB) information. Enterprise developers must continually deliver new applications that require real-time access to these DB2 information stores, without compromising security or run time efficiency. In this webcast, we examine methods for direct data access to DB2, including industry-standard XML-based data integration services. Join us to learn how you can intelligently design and securely deliver scalable DB2 integration solutions using Microsoft Visual Studio 2005 and Microsoft Host Integration Server 2006, including the new Microsoft .NET Framework Data Provider for DB2.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032330316&EventCategory=5&culture=en-US&CountryCode=US
After completing this lab, you will be able to create a new BizTalk project, create an XML schema by using the BizTalk Editor, promote a schema property, create a flat file schema by using the BizTalk Editor, validate a schema and generate a sample instance message, create a strong name and assign it to an assembly, and build a schema project.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032371228&EventCategory=3&culture=en-US&CountryCode=US
After completing this lab, you will be better able to create a Transaction Integrator (TI) WIP assembly and a XSD representation using the TI designer, use TI manager to setup the remote environment and deploy and configure the assembly created by the TI designer, create a BizTalk application that uses ports, filters and the host application adapter, and administer the host application adapter.
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032369523&EventCategory=3&culture=en-US&CountryCode=US
