Creating a Self-Running BizTalk Stress Application using the BizTalk Adapter for DB2

Microsoft Corporation

Author: Charles Ezzell

Published: June 2008

Summary

This document describes how to configure Microsoft® BizTalk® Server 2006 with the BizTalk Adapter for DB2 to create a self-running stress application that mimics a real world situation.

The purpose of this document is to describe how to configure Microsoft® BizTalk® Server 2006 with the BizTalk Adapter for DB2 to create a self-running stress application that mimics a real world situation.

This document contains the following sections:

• Scenario Background
• Creating the DB2 Test Table and Stored Procedures
• Creating the Data Connection
• Creating the BizTalk Server Applications

This document is not meant to be an in-depth resource for creating BizTalk applications; rather, it is intended to help with setting up a test scenario that can simulate a real world situation.

In most cases, you should start with a basic project, such as the one presented in this document, and then build on that as necessary. One indication that everything is going as designed is if the samples work for you.

Background

You have data in DB2 that is constantly changing during the day. The data contains a field that marks if a row can be deleted when the system is done with this particular data. You want to use BizTalk Server to delete any rows of data that are marked for deletion. You know there will be times when there will be no rows to delete and other times when there may be many rows to delete.

The purpose of this document is to help setup a scenario that mimics this situation. You will use the BizTalk Adapter for DB2 that is part of the BizTalk Adapters for Host Systems, which ships with BizTalk Server 2006 R2.

the DB2 Test Table and Stored Procedures

To begin, you create a table in DB2 and also create the two stored procedures that will be used as support files to help reproduce the test scenario. There are other ways some of this could be done, but in this scenario, we will be using multiple BizTalk applications to do several things at one time.

Step 1: Create the DB2 Test Table

The data table consists of a column named DONE, a character data type that is initially set to n. If this character data type is set to y, the row can be deleted. The other columns in the table are used to represent a customer order and contain the Customer ID, the Product ID, the Order Date, and the Quantity being ordered. The table is created in the BIZTALK schema on the DB2 server.

The DDL to create the table is:

CREATE TABLE "BIZTALK"."ORDERS"(
                "DONE" CHAR (1),
                "CUSTOMER_ID" CHAR (10),
                "PRODUCT_ID" CHAR (10),
                "ORDER_DATE" DATE,
                "QUANTITY" INTEGER);
COMMENT ON TABLE "BIZTALK"."ORDERS" IS 'BizTalk Stress Test';

Step 2: Create the Insert Stored Procedure

After you create the table, you create a stored procedure to populate the data table. This stored procedure will be called by one of the BizTalk applications created later, to add data to the table on a regular basis.

CREATE PROCEDURE BIZTALK.INSERTSP ( )
SPECIFIC BIZTALK.INSERTSP
P1: BEGIN
Insert into BIZTALK.ORDERS VALUES ('n', '12345', '23456', CURRENT_DATE, 10);
Insert into BIZTALK.ORDERS VALUES ('n', '23456', '34567', CURRENT_DATE, 20);
Insert into BIZTALK.ORDERS VALUES ('n', '34567', '45678', CURRENT_DATE, 30);
Insert into BIZTALK.ORDERS VALUES ('n', '45678', '56789', CURRENT_DATE, 40);
Insert into BIZTALK.ORDERS VALUES ('n', '56789', '67890', CURRENT_DATE, 50);
Insert into BIZTALK.ORDERS VALUES ('n', '67890', '78901', CURRENT_DATE, 60);
Insert into BIZTALK.ORDERS VALUES ('n', '78901', '89012', CURRENT_DATE, 70);
Insert into BIZTALK.ORDERS VALUES ('n', '89012', '90123', CURRENT_DATE, 80);
Insert into BIZTALK.ORDERS VALUES ('n', '90123', '01234', CURRENT_DATE, 90);
Insert into BIZTALK.ORDERS VALUES ('n', '01234', '12345', CURRENT_DATE, 100);
END P1

Step 3: Create the Update Stored Procedure

Finally, you create a stored procedure that will change the DONE column from n to y. We do not want to update all of the rows. As in a real word situation, this would probably not be true, so our stored procedure will update five rows of the database. This stored procedure will be called by another BizTalk application.

The stored procedure below was created for DB2/NT v8, which was the initial test bed. Also included are two different stored procedures for DB2/400 V5R4 and V5R3. These stored procedures need to be separate due to differences in each version of DB2.

Note that you may need to modify the stored procedures for your specific version of DB2.

DB2/NT:

CREATE PROCEDURE BIZTALK.UPDATESP ( )
SPECIFIC BIZTALK.UPDATESP
P1: BEGIN
UPDATE (SELECT DONE FROM BIZTALK.ORDERS
WHERE DONE = 'n' FETCH FIRST 5 ROWS ONLY)
SET DONE = 'y';
END P1

DB2/400 V5R4:

CREATE PROCEDURE BIZTALK.UPDATESP ( )
       LANGUAGE SQL
       SPECIFIC BIZTALK.UPDATESP
       NOT DETERMINISTIC
       MODIFIES SQL DATA
       CALLED ON NULL INPUT
       COMMIT ON RETURN YES
       SET OPTION ALWBLK = *ALLREAD,
       ALWCPYDTA = *OPTIMIZE,
       COMMIT = *NONE,
       DECRESULT = (31, 31, 00),
       DFTRDBCOL = *NONE,
       DYNDFTCOL = *NO,
       DYNUSRPRF = *USER,
       SRTSEQ = *HEX
       BEGIN
FOR LV AS TEMP_CUR CURSOR FOR
SELECT * FROM
(SELECT DONE,
ROW_NUMBER() OVER (ORDER BY 1) AS ROWNUM
FROM BIZTALK.ORDERS WHERE DONE = 'N'
) AS FOO
WHERE ROWNUM < 6
FOR UPDATE DO
UPDATE BIZTALK.ORDERS
SET DONE = 'Y'
WHERE CURRENT OF TEMP_CUR;
END FOR;
END;

DB2/400 V5R3:

CREATE PROCEDURE BIZTALK.UPDATESP ( )
   LANGUAGE SQL
   SPECIFIC BIZTALK.UPDATESP
   NOT DETERMINISTIC
   MODIFIES SQL DATA
   CALLED ON NULL INPUT
   COMMIT ON RETURN YES
   BEGIN
         DECLARE NUMRECORDS INTEGER DEFAULT 1;
         DECLARE INUMBER CHAR (10);
         DECLARE ILOOP INTEGER;
         DECLARE MAXLOOP INTEGER;
         DECLARE C1 CURSOR FOR
SELECT DONE FROM BIZTALK.ORDERS WHERE DONE = 'n'
FOR UPDATE OF DONE;
         DECLARE EXIT HANDLER FOR NOT FOUND
         SET ILOOP = 666;
         SELECT COUNT (*) INTO NUMRECORDS
FROM BIZTALK.ORDERS
WHERE DONE = 'y';
         IF NUMRECORDS > 5 THEN
SET MAXLOOP = 5;
ELSE
SET MAXLOOP = NUMRECORDS;
         END IF;
         OPEN C1;
         SET ILOOP = 0;
         WHILE (ILOOP < MAXLOOP) DO
FETCH C1 INTO INUMBER;
IF (ILOOP < MAXLOOP) THEN
UPDATE BIZTALK.ORDERS SET DONE = 'y'
WHERE CURRENT OF C1;
SET ILOOP = ILOOP + 1;
END IF;
END WHILE;
CLOSE C1;
END;

the Data Connection

First, create your UDL(s) to the DB2 server using the Data Access Tool. This will make it easier to set the connection string property when we create the receive locations later. For more information on using the Data Access Tool, you may want to review these documents:

• Setting up the BizTalk Adapter for DB2 Using Send Ports at http://go.microsoft.com/fwlink/?LinkId=120194.
• Setting up the BizTalk Adapter for DB2 Using Receive Ports at http://go.microsoft.com/fwlink/?LinkId=120195.

Note

When creating the UDL, it is advisable to set the Default Qualifier. DB2 may have some problems finding the stored procedure.

the BizTalk Server Applications

For the tests, we are going to create four different applications. You could do this all-in-one if you wanted. For this test scenario, we wanted each receive location in its own application for testing purposes.

The following information should apply with any version of DB2.

The goal, as previously stated, is to create a self-running scenario that requires no user input (after initial set up). The four BizTalk Server applications you will create are the following:

• Application #1: Stress_DB2Deletes - Every 60 seconds, this application runs a query to see if DONE is set to y. If any rows are set, it will delete those rows from the database.
• Application #2: Stress_DB2Inserts - Every 40 seconds, this application calls a stored procedure that inserts ten rows of data into the database. ID_NUMBER is set to x when the records are inserted. There is no output from this application.
• Application #3: Stress_DB2Query - Every 30 seconds, this application issues a Select * against the database. Output is to XML.
• Application #4: Stress_DB2Updates - Every 20 seconds, this application calls a stored procedure that takes five of the records where ID_NUMBER is set to x and sets this to 1 (marking them for deletion). There is no output from this application.

To put this into a better perspective, ten records will be inserted every 40 seconds. Every 20 seconds five records are updated and marked for deletion. Every 60 seconds, records will be deleted (if marked), and every 30 seconds, a query is issued to see what records exist at that particular time-frame.

So let’s begin!

Creating the Stress_Db2Deletes Application

To create the Stress_DB2Deletes application

1. In Application Manager, expand the Stress_DB2Deletes application.

2. Create a one-way receive port named DB2ReceivePort_SDEL.

3. Create a Receive Location named DB2ReceiveLocation_SDEL.

4. Set the Type to DB2, then click configure, and then point this to your UDL.

5. In the DB2 Transport Properties window, configure the following properties:

6. Create a static one-way send port named FileSendPort_SDEL.

7. Set the Type to FILE, and point to a directory on your hard drive.

8. For File Name, enter “DB2Delete_%MessageID%.xml.”

9. For filters, set BTS.ReceivePortName to DB2ReceivePort_SDEL (created in step 2 above).

You are now done creating the Stress_DB2Deletes application.

To create the Stress_DB2Inserts application

1. In Application Manager, expand the Stress_DB2Inserts application.

2. Create a one-way Receive Port named DB2ReceivePort_SINS.

3. Create a Receive Location named DB2ReceiveLocation_SINS.

4. Set the Type to DB2, then click configure, and then point this to your UDL.

5. In the DB2 Transport Properties window, configure the following properties:

You are now done creating the Stress_DB2Inserts application.

To create the Stress_DB2Query application

1. In Application Manager, expand the Stress_DB2Query application.

2. Create a Receive Port named DB2ReceivePort_SQRY.

3. Create a Receive Location named DB2ReceiveLocation_SQRY.

4. Set the Type to DB2, then click configure, and then point this to your UDL.

5. In DB2 Transport Properties, configure the following properties:

6. Create a Send Port named FileSendPort_SQRY.

7. Set the Type to FILE, then point to your directory, and then set the file name to DB2Query_%MessageID%.xml.

8. For filters, set BTS.ReceivePortName to DB2ReceivePort_SQRY.

You are now done creating the Stress_DB2Query application.

Creating the Stress_DBUpdates Application

To create the Stress_DB2Updates application

1. In Application Manager, expand the Stress_DB2Updates application.

2. Create a Receive Port named DB2ReceivePort_SUPD.

3. Create a Receive Location named DB2ReceiveLocation_SUPD.

4. Set Type to DB2 and point to your UDL.

5. In DB2 Transport Properties, configure the following properties:

You are now done creating the Stress_ DB2Updates application.

It is important to test each application to verify it is setup correctly.

1.First, start the Stress_DB2Inserts application. After it has started, stop it. When it first starts, it will call the stored procedure. At this time, we only want to makes sure things are working correctly.  

2.Next, start the Stress_DB2Query application. This should immediately (within seconds) return an XML file that contains the data that was inserted. It should appear similar to the following:  

<?xml version="1.0" ?>
- <DB2QueryResult xmlns="DB2Query">
<ORDERS DONE="n" CUSTOMER_ID="01234" PRODUCT_ID="12345" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="100" />
<ORDERS DONE="n" CUSTOMER_ID="12345" PRODUCT_ID="23456" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="10" />
<ORDERS DONE="n" CUSTOMER_ID="23456" PRODUCT_ID="34567" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="20" />
<ORDERS DONE="n" CUSTOMER_ID="34567" PRODUCT_ID="45678" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="30" />
<ORDERS DONE="n" CUSTOMER_ID="45678" PRODUCT_ID="56789" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="40" />
<ORDERS DONE="n" CUSTOMER_ID="56789" PRODUCT_ID="67890" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="50" />
<ORDERS DONE="n" CUSTOMER_ID="67890" PRODUCT_ID="78901" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="60" />
<ORDERS DONE="n" CUSTOMER_ID="78901" PRODUCT_ID="89012" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="70" />
<ORDERS DONE="n" CUSTOMER_ID="89012" PRODUCT_ID="90123" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="80" />
<ORDERS DONE="n" CUSTOMER_ID="90123" PRODUCT_ID="01234" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="90" />
</DB2QueryResult>

3.Next, start the Stress_DB2Updates application, leaving the Stress_DB2Query application running.  

4.After some time, the query output should show and XML file with results similar to this. Note that the Done column has been changed to ‘y’:  

<?xml version="1.0" ?>
- <DB2QueryResult xmlns="DB2Query">
<ORDERS DONE="y" CUSTOMER_ID="01234" PRODUCT_ID="12345" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="100" />
<ORDERS DONE="y" CUSTOMER_ID="12345" PRODUCT_ID="23456" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="10" />
<ORDERS DONE="y" CUSTOMER_ID="23456" PRODUCT_ID="34567" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="20" />
<ORDERS DONE="y" CUSTOMER_ID="34567" PRODUCT_ID="45678" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="30" />
<ORDERS DONE="y" CUSTOMER_ID="45678" PRODUCT_ID="56789" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="40" />
<ORDERS DONE="y" CUSTOMER_ID="56789" PRODUCT_ID="67890" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="50" />
<ORDERS DONE="y" CUSTOMER_ID="67890" PRODUCT_ID="78901" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="60" />
<ORDERS DONE="y" CUSTOMER_ID="78901" PRODUCT_ID="89012" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="70" />
<ORDERS DONE="y" CUSTOMER_ID="89012" PRODUCT_ID="90123" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="80" />
<ORDERS DONE="y" CUSTOMER_ID="90123" PRODUCT_ID="01234" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="90" />
</DB2QueryResult>

5.Finally, start the Stress_DB2Deletes application. You should get back a DB2Delete xml file that contains the same data as above.  

<?xml version="1.0" ?>
- <DB2QueryDeleteResponse xmlns="DB2QueryDelete">
<ORDERS DONE="y" CUSTOMER_ID="12345" PRODUCT_ID="23456" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="10" />
<ORDERS DONE="y" CUSTOMER_ID="23456" PRODUCT_ID="34567" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="20" />
<ORDERS DONE="y" CUSTOMER_ID="34567" PRODUCT_ID="45678" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="30" />
<ORDERS DONE="y" CUSTOMER_ID="45678" PRODUCT_ID="56789" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="40" />
<ORDERS DONE="y" CUSTOMER_ID="56789" PRODUCT_ID="67890" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="50" />
<ORDERS DONE="y" CUSTOMER_ID="67890" PRODUCT_ID="78901" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="60" />
<ORDERS DONE="y" CUSTOMER_ID="78901" PRODUCT_ID="89012" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="70" />
<ORDERS DONE="y" CUSTOMER_ID="89012" PRODUCT_ID="90123" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="80" />
<ORDERS DONE="y" CUSTOMER_ID="90123" PRODUCT_ID="01234" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="90" />
<ORDERS DONE="y" CUSTOMER_ID="01234" PRODUCT_ID="12345" ORDER_DATE="3/24/2008 12:00:00 AM" QUANTITY="100" />
</DB2QueryDeleteResponse>

At this point, you can restart the Stress_DB2Inserts application and let things run.

It is important to watch the hard drive space because two of the applications will create XML files but not deleting them.

The polling times for each application can be varied as needed, but we advise not putting them too close together, as you could potentially end up in a situation that causes a deadlock or other unexpected results. With the timings as set, on average, you should see 15 records being deleted every minute. If each application’s startup is staggered, there should also be times when the delete query comes back with no records, and sometimes when you may have 20 or more records to be deleted. If no records are deleted, you should see a 2 minute gap between DB2Delete*.xml files. You can easily adjust the timings on the update command to achieve this. The Stress_DB2Query should always return two xml files each minute with the timings as configured in this document.

This document explained how to use the Data Access Tool to create a data connection, and how to create several self-running BizTalk applications to mimic real world scenarios that use receive ports and send ports.

For more information about the Microsoft BizTalk Adapters for Host System, see http://go.microsoft.com/fwlink/?LinkId=98340.

For more information about the Microsoft BizTalk Adapter for DB2, see http://go.microsoft.com/fwlink/?LinkId=91515.

For more information about Microsoft Host Integration Server 2006, see http://www.microsoft.com/hiserver/default.mspx.