Setting Up the Online Bookstore Sample

Rob Caron
Microsoft Corporation

July 1999

Summary: Provides the sample files and describes how to install the Online Bookstore databases, components, and Web site. Covers system requirements, core setup, and interoperability setup. (25 printed pages)

Click to copy the Online Bookstore sample files.

Introduction

The Online Bookstore is an example of an application built using the Microsoft® Windows® Distributed interNet Application (Windows DNA) architecture application development model that is designed to interoperate with disparate data stores and applications running on different platforms. To set up the Online Bookstore sample application, review the requirements, complete and verify the core functionality, and then choose to what extent you will set up the various interoperability features.

Figure 1. Online Bookstore

The core installation makes it possible for you to use Microsoft SQL Server™, so you can become familiar with and test the installation before adding interoperability. You can then add the interoperability features that are available in your environment: Oracle, DB2, AS/400, or COMTI.

Requirements

To successfully set up this sample application, there are a few requirements that must be met. Review the requirements that apply to the core setup of the sample application, and make sure you have all of the necessary server applications and components available. Next, review the requirements that apply to the interoperability setup. The extent to which you will be able to meet the requirements of each interoperability feature will dictate which of the features you will be able to implement.

Prior to installing any of the server applications or service packs mentioned in the following requirements, please review "Sequences for Installing and Configuring Server Applications Running on Windows NT® Server 4.0," located at www.microsoft.com/ntserver/nts/deployment/planguide/Install.asp, to ensure the proper sequence is used.

Core Requirements

Although the purpose of this sample application is to demonstrate interoperability, it is capable of operating in a SQL Server environment only. The following are the basic requirements to run the sample application without using any of the interoperability features:

• Microsoft Windows NT Server 4.0 (SP5).
  
  
• Microsoft SQL Server 7.0 (SP1).
  
  
• Microsoft Internet Information Server (IIS) 4.0 including the FrontPage 98 Server Extensions. (Use Custom installation of the NT Option Pack.)
  
  
• Microsoft Transaction Server (MTS) 2.0 (SP1).
  
  
• Microsoft Data Access Components (MDAC) 2.1 or later.
  
  
• Microsoft Visual Studio® 6.0 (SP3) or, Microsoft Visual Basic® 6.0 (SP3) and Microsoft Visual InterDev® 6.0 (SP3).
  
  
• Microsoft Internet Explorer 4.x or later.

Interoperability Requirements

The interoperability requirements, which are in addition to the core requirements listed earlier, will depend upon which interoperability features of the sample application are implemented. Use the following table to determine the requirements for your chosen implementation:

Core Setup

These setup instructions assume the core requirements have been met as stated earlier. The core setup will establish the data, business, and presentation layers. At the completion of the core setup, the application should work end-to-end.

The compressed download file contains the following file structure:

Setting Up the Data Layer

The data layer setup will use the Query Analyzer to process a script that creates the SQL Server login used by the sample application (OnlineBookstore), four databases (IntOpSQL, IntOpDB2, IntOpOracle and IntOp400), all the required tables in each database, and the stored procedures used in the IntOpSQL database. Next, the SQL Server Data Transformation Services (DTS) are used to import the initial data into the appropriate databases in SQL Server. It is best to complete this setup at the destination SQL Server machine.

To create the schema in SQL Server

1. In Query Analyzer, open the schema.sql script from the database directory of the download.
   
   
2. Run the script by pressing F5 or clicking Play. When finished, your query output will resemble Figure 2.

   

   Figure 2. SQL Server output after running schema.sql

Note that this SQL script creates the databases without regard to tuning. A proper installation, for example, would place the transaction log on a separate physical drive from the drive holding the data.

To import the initial data

1. Copy the contents of the Database/dts directory from the download to the SQL Server machine.
   
   
2. Run the batch file, rundts.bat, on the SQL Server machine to import the data from the Microsoft Access database, interop.mdb. This script takes very little time to complete and, when finished, will report "DTSRun: Package execution complete."

Setting Up the Business Layer

The business layer setup will compile and deploy the data and business components. The components are written in Visual Basic and will require some minor customization prior to compilation to work with your SQL Server environment.

To create the data component

1. In Visual Basic 6.0, open the Interop Visual Basic Project Group, interop.vpg, found in the components directory of the download.
   
   
2. Open the cData.cls module in the TierData project, and locate the General/Declarations section.
   
   
3. Replace the value for m_SQL_SERVER_NAME with the name of your SQL Server machine. If you modified the schema.sql script to use a different login than OnlineBookstore, replace the value for m_SQL_SERVER_USERNAME and m_SQL_SERVER_PASSWORD accordingly.
   
   
4. Save the project.
   
   
5. Compile TierData by clicking Make TierData.dll on the File menu.
   
   
6. In the Make Project dialog box, click Options to display the Project Properties dialog box.

   

   Figure 3. TierData Project Properties

7. This project uses conditional compilation to compile a DLL that is capable of functioning at the desired level of interoperability. Take note of the Conditional Compilation Arguments as shown in Figure 3. For now, verify the variables are defined as zero, and click OK.
   
   
8. To compile the DLL, click OK.

To create the business component

1. Open the Globals.bas module in the TierLogic project.
   
   
2. On the Project menu, click References.
   
   
3. In the References dialog box, select the TierData check box to add the component you compiled in the previous steps as a reference (see Figure 4).

   

   Figure 4. TierData Project References

4. After adding the reference, click OK to close the dialog box and continue.
   
   
5. Save the project.
   
   
6. Compile TierLogic by clicking Make TierLogic.dll on the File menu.
   
   
7. In the Make Project dialog box, click the Options button to display the Project Properties dialog box. This project also uses the same conditional compilation to compile the DLL as the TierData DLL.
   
   
8. Verify all of these arguments are set to zero, and click OK.
   
   
9. To compile the DLL, click OK.

Packages running in MTS must run under a specific valid Windows NT user account, or they can use the credentials of the currently logged-in user, which is referred to as the interactive user. Although the interactive user is the default, it is recommended to use a specific user when creating MTS packages. On the machine that will be used as your Web server, create a new user account using the following credentials to be used by the Online Bookstore package.

To create an account for running MTS packages

1. In the User Manager, click Select Domain on the User menu.
   
   
2. Enter the machine name in the Domain box, and click OK.
   
   
3. Click New User on the User menu.
   
   
4. Set Username to OnlineBooks.
   
   
5. Set User's Full Name to Online Bookstore Component Acct.
   
   
6. Set Description to Acct for running Online Bookstore components
   
   
7. Set Password.
   
   
8. Clear User Must Change Password at Next Logon.
   
   
9. Select Password Never Expires.
   
   
10. Click Add to add the new user.

On the IIS machine, open Microsoft Transaction Server Explorer and create an empty MTS package as follows:

To create an MTS package

1. In the left pane of MTS Explorer, select the computer for which you want to create a package.
   
   
2. Open the Packages Installed folder for that computer.
   
   
3. On the Action menu, point to New, and choose Package.
   
   
4. Click the Create an empty package button in the Package Wizard to create an empty package.
   
   
5. Type the name for the new package, Interop, and click Next.
   
   
6. In the Set Package Identity dialog box, do not use the default account (interactive user). Instead, use the account you created earlier for running the Online Bookstore components. Enter the user credentials, or click the Browse button to locate the desired user.
   
   
7. Click the Finish button to create the empty package.
   
   
8. In the left pane of MTS Explorer, select the package you just created.
   
   
9. On the Action menu, click Properties.
   
   
10. On the Properties dialog box that appears, click the Security tab.
    
    
11. Select the Enable authorization checking check box. By selecting this option, MTS declarative security is activated. Click the OK button.

Next, add a role to the package you just created, and then add the IIS anonymous request account (IUSR_computername) and the IIS Web Application Manager account (IWAM_computername) to that role as follows:

To create a role in the MTS package

1. In the left pane of MTS Explorer, open the package you just created.
   
   
2. Select the Roles folder.
   
   
3. On the Action menu, point to New, and choose Role.
   
   
4. Give the new role a name (for example, Online Bookstore Web User).
   
   
5. In the left pane of MTS Explorer, open the role you just created.
   
   
6. Select the Users folder.
   
   
7. On the Action menu, point to New, and choose User.
   
   
8. Map the IUSR_computername and IWAM_computername accounts to this role and click OK.

For ease of administration, it is recommended you use a central location to store your MTS components, such as C:\Program Files\MTS Components\Online Bookstore. To do that, add your components to the MTS package.

To add components to the MTS package

1. Copy the TierData and TierLogic DLLs to the IIS machine.
   
   
2. In the left pane of MTS Explorer, select and open the package you just created, and then open the Components folder.
   
   
3. Open Windows Explorer, and navigate to the location where you copied TierData.dll and TierLogic.dll.
   
   
4. Drag both DLLs to the right pane of MTS Explorer. This will automatically add all the required components to the package you created. Alternatively, you can add components by opening the Components folder, pointing to New and choosing Component on the Action menu, and using the New Component Wizard.

After all eight components have been added to the package, you must add the Role Membership for each component.

To add a role to each component

1. Open the component, and select the Role Membership folder.
   
   
2. On the Action menu, point to New, and choose Role.
   
   
3. Select the Role you created earlier, and click OK.
   
   
4. Repeat the previous three steps for each component in the package.

Setting Up the Presentation Layer

The Web site that constitutes the presentation layer of this application was built using Visual InterDev 6.0. The presentation layer setup will use Visual InterDev to create the Web site and upload the files to the Web server. Visual InterDev uses the FrontPage® 98 Server Extensions to interact with IIS, which can be installed from the NT Option Pack. When installing the NT Option Pack, choose Custom install and select FrontPage 98 Server Extensions.

To create the Web project

1. In Visual InterDev, click New Project on the File menu.
   
   
2. Create a new project called OnlineBookstore.
   
   
3. Click Open in the New Project dialog box to launch the Web Project Wizard.
   
   
4. Specify your web server's name in the first step (this should be the same server where you registered your MTS components in the previous steps) and click Next.
   
   
5. In the second step, click Finish to accept the default name for the new Web application. The wizard will create the Web site on the Web server and create a basic file structure.

After the project is loaded, copy the Web site files to your newly created Web site.

To upload files to the Web server

1. Create two new folders in the Web project—one called Styles and the other called Shockwave.
   
   
2. In the Project Explorer, select the project name as shown in Figure 5.

   

   Figure 5. Selecting the project in Project Explorer

3. Right-click on the project name, point to Add, and click Add Item.
   
   
4. In the resulting dialog box, click the Existing tab, and then navigate into the Website/root folder of the download.
   
   
5. In the Files of type list, click All Files (*.*).
   
   
6. Select all the files in this folder and click OK. This will copy all the ASP and JScript files to the root directory of the newly created Web site.
   
   
7. Next, copy the contents of the download folders, Website/Images, Website/Styles, and Website/Shockwave to their respective folders on the Web site in the same manner.

By default, a Web application in IIS runs in the IIS process space. If a Web application runs in a separate process space, the application will not affect other Web applications or IIS if it fails. Configure this Web application to run as an isolated process as follows:

To modify IIS settings

1. In the left pane of the Internet Services Manager, open the OnlineBookstore Web application.
   
   
2. Click Properties on the Action menu.
   
   
3. Enter OnlineBookstore in the Name box.
   
   
4. Select Run in separate memory space (isolated process).
   
   
5. Click OK. Internet Services Manager will create a new package in MTS for your Web application.

You are ready to test the result of your setup process. Connect to the Web site using Internet Explorer 4.0 or later. The default page should appear in your browser.

Interoperability Setup

These setup instructions assume the core setup has been completed and both the core and interoperability requirements have been met. The interoperability setup is an a la carte setup. It is only necessary to complete those steps that will provide the desired level of interoperability.

The sample application was originally built using an Oracle database on a Sun SPARC/Solaris, an IBM DB2 database running on an IBM MVS mainframe, physical files stored on an AS/400, and a CICS LINK Remote Environment on a IBM MVS mainframe.

Configuring the SNA Server

For the purposes of this sample, you will use the SNA protocol provided by SNA Server to attach to a DB2 database, an AS/400, and an OS/390.

The AS/400 Wizard will help you create an AS/400 connection, a Remote APPC LU for that connection, and a Local APPC LU if required. The wizard will prompt you for information, such as the AS/400 Network Name and the AS/400 Control Point Name, that is obtainable from your AS/400 Administrator.

To create an AS/400 connection, Remote APPC LU, and Local APPC LU

1. In SNA Server Manager, click AS/400 Wizard on the Tools menu to run the wizard.
   
   
2. Follow the directions in the wizard to configure your AS/400 connection, and then click Finish to create the connection.

The Mainframe APPC/LU6.2 Wizard is a two-part wizard that will create the local and remote APPC LU definitions you require. After completing Part 1 of the wizard, you will be presented with a worksheet containing the corresponding host configuration information. Have the mainframe personnel complete this worksheet and return it to you. This information is required to complete Part 2 of the wizard.

To create the local and remote mainframe APPC/LU6.2 definitions

1. In SNA Server Manager, click Mainframe APPC/LU6.2 Wizard on the Tools menu to run the wizard.
   
   
2. Follow the directions in the wizard to complete Part 1 of the wizard.
   
   
3. After completing Part 1 of the wizard, print the worksheets provided, and have the mainframe personnel provide the requested information. Use this information to complete Part 2 of the wizard.
   
   
4. After completing Part 2 of the wizard, you will be presented with a summary of the settings chosen in the wizard. Review the settings with the completed worksheets and, if satisfied, click Finish to create the local and remote APP LU definitions.

Setting Up Oracle Interoperability

The Oracle interoperability portion of the Online Bookstore utilizes an Oracle database to store the customer data that is found in the IntOpOracle database on the SQL Server.

To utilize an Oracle database, the OLE DB Provider for Oracle and the Oracle Client software must be installed on the server hosting the business objects.

To provide an Oracle database in place of the IntOpOracle database, use Microsoft SQL Server Data Transformation Services (DTS) to export the data to the destination Oracle database.

To export data to Oracle

1. In the left pane of SQL Enterprise Manager, select the IntOpOracle database.
   
   
2. On the Action menu, point to All Tasks, and click Export Data to start the DTS Export Wizard.
   
   
3. Click the Next button to proceed to the first step of the wizard.
   
   
4. The first step in the wizard is to select the data source from which to export data. By default, the specified Source is Microsoft OLE DB Provider for SQL Server, and the current SQL Server and database are specified.
   
   
5. Select Use SQL Server Authentication, and provide the credentials for the OnlineBookstore sysadmin account.
   
   
6. Click the Next button to continue.
   
   
7. The second step of the wizard is to select the destination for the data being exported. In the Destination list, select Microsoft OLE DB Provider for Oracle.
   
   
8. Enter the appropriate Server, Username, and Password to connect to the destination Oracle database server.
   
   
9. Click the Next button to continue.
   
   
10. The third step of the wizard is to specify whether to copy tables, or to create a query to specify the data to transfer. The default is to copy tables, which is what you want. Click the Next button to continue.
    
    
11. The fourth step of the wizard is to select what tables to copy. Click Select All to select all of the tables in IntOpOracle. To make sure the proper table names are used when creating the tables in the Oracle database, make the following modifications:
    1. In the Destination Table column, it is necessary to modify the table names from the default values provided by the DTS Export Wizard. Change the values to CustomerId and Customers (without quotation marks) as shown in Figure 6.

       

       Figure 6. DTS Export Wizard

    2. For the CustomerId table, click the Transform button (…) to view the Column Mappings and Transformations dialog box.
       
       
    3. Click the Edit SQL button to view the Create Table SQL Statement dialog box.
       
       
    4. Remove the quotes before and after the table name, and click OK.
       
       
    5. Click OK to close the Column Mappings and Transformations dialog box.
       
       
    6. Use the same procedure to modify the create table SQL statement for the Customers table.
12. Click the Next button to continue.
    
    
13. The next step gives the opportunity to save, schedule and replicate the DTS package you just defined. The default is to simply run the DTS package immediately. Click the Next button to continue to the last step.
    
    
14. The last step is a confirmation message with a summary of what the DTS package will do. After reviewing the summary, click the Finish button to complete the wizard and run the DTS package. While the DTS package is running, a progress bar and status window will keep you advised of how well the process is going. At the end, a dialog box will display either success or failure of the DTS package execution. Assuming success, two new tables and their data should now exist on the Oracle database server.
    
    
15. Click OK to close the dialog box, and Done to close the wizard.

Now that the data is on the Oracle database, you must make it possible for the Web application to use the tables on the Oracle database instead of the IntOpOracle database on the SQL Server. To do this, the data and business components must be reconfigured and recompiled as follows:

To configure the data component to use Oracle

1. In Visual Basic 6.0, open the Interop Visual Basic Project Group, interop.vpg, found in the Components directory of the download.
   
   
2. In the TierData project, open the cData module, and locate the OracleConn function.
   
   
3. Locate the values for the Oracle connection properties as shown in Figure 7, and change them to the values required to connect to your Oracle database.

   

   Figure 7. OLE DB Provider for Oracle properties

4. Save the file.
   
   
5. Click TierData Properties on the Project menu.
   
   
6. When presented with the Project Properties dialog box, click the Make tab.
   
   
7. In the Conditional compilation arguments text box, set the USE_ORACLE argument to a non-zero value (that is, USE_ORACLE = 1).
   
   
8. Click the Component tab.
   
   
9. Select Binary compatibility, and click OK.
   
   
10. Click Make TierData.dll on the File menu.
    
    
11. Click OK in the Make Project dialog box to compile the DLL.
    
    
12. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To configure the business component to use Oracle

1. Open the TierLogic project.
   
   
2. Click TierLogic Properties on the Project menu.
   
   
3. When presented with the Project Properties dialog box, click the Make tab.
   
   
4. In the Conditional compilation arguments text box, set the USE_ORACLE argument to a non-zero value (that is, USE_ORACLE = 1).
   
   
5. Click the Component tab.
   
   
6. Select Binary compatibility, and click OK.
   
   
7. Click Make TierLogic.dll on the File menu.
   
   
8. Click OK in the Make Project dialog box to compile the DLL.
   
   
9. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To copy these new DLLs to the machine running MTS, it may be necessary to stop the package to successfully copy over the existing DLLs.

To update the components in the MTS package

1. In the left pane of MTS Explorer, open the OnlineBookstore package.
   
   
2. Click Shut down on the Action menu.
   
   
3. Copy the TierData and TierLogic DLLs to the IIS machine, overwriting the previous versions.

Running the application will now utilize the Oracle database instead of the IntOpOracle database in SQL Server.

Setting Up DB2 Interoperability

The DB2 interoperability portion of the Online Bookstore utilizes a DB2 database to store the customer data that is found in the IntOpDB2 database on the SQL Server.

To utilize a DB2 database, the OLE DB Provider for DB2 must be accessible to the server hosting the business objects.

To provide a DB2 database in place of the IntOpDB2 database, use SQL Server Data Transformation Services (DTS) to export the data to the destination DB2 database.

To export data to DB2

1. In the left pane of SQL Enterprise Manager, select the IntOpDB2 database.
   
   
2. On the Action menu, point to All Tasks, and click Export Data to start the DTS Export Wizard.
   
   
3. Click the Next button to proceed to the first step of the wizard.
   
   
4. The first step in the wizard is to select the data source from which to export data. By default, the specified Source is Microsoft OLE DB Provider for SQL Server, and the current SQL Server and database are specified.
   
   
5. Select Use SQL Server Authentication, and provide the credentials for the OnlineBookstore sysadmin account.
   
   
6. Click the Next button to continue.
   
   
7. The second step of the wizard is to select the destination for the data being exported. In the Destination list, select Microsoft OLE DB Provider for DB2.
   
   
8. The connection properties for DB2 can be specified manually or by loading a predefined Universal Data Link (UDL) file. To create the connection manually, click the Properties button to view the Data Link Properties dialog box.
   
   
9. Click the All tab to view all of the connection properties.
   
   
10. Edit the following values:
    • Data Source—optional parameter used to describe the connection.
      
      
    • Default Schema—name of the collection where the provider looks for catalog information. In OLE DB, Default Schema is the schema name for the target collection of tables and views.

      For DB2 (MVS, OS/390), this is the target AUTHENTICATION (User ID or owner).

      For DB2/400, this is the target COLLECTION name.

      For DB2 UDB, this is the SCHEMA name.

    • Initial Catalog—the first part of a three-part, fully qualified table name.

      In DB2 (MVS, OS/390), this property is referred to as LOCATION.

      In DB2/400, this property is referred to as RDBNAM.

      In DB2 UDB, this property is referred to as DATABASE.

    • Network Transport Library—SNA to connect using SNA LU6.2 or TCP/IP.
      
      
    • Package Collection—the name of the DRDA COLLECTION where you want the provider to store and bind DB2 packages. This could be the same as the Default Schema, or use the default MSPKG.
      
      
    • Password—a valid password for the accompanying DB2 User ID.
      
      
    • User ID—a valid DB2 User ID.

    If the specified Network Transport Library is SNA, specify the following:

    • APPC Local LU Alias—name of the local LU alias configured in SNA Server.
      
      
    • APPC Mode Name—the APPC mode that matches the host configuration and SNA Server configuration. The default is QPCSUPP.
      
      
    • APPC Remote LU Alias—name of the remote LU alias configured in SNA Server.

    If the specified Network Transport Library is TCP/IP, specify the following:

    • Network Address—the TCP/IP address or TCP/IP host name/alias associated with the DRDA port.
      
      
    • Network Port—the DB2 DRDA service access port. The default is 446.
11. Click the Connection tab.
    
    
10. Click the Test Connection button to test the connection that was just defined.
    
    
11. If the test is successful, click the OK button to close the Data Link Properties dialog box. If the test fails, an error message will be displayed that should indicate where the problem lies.
    
    
12. Click the Next button to continue.
    
    
13. The third step of the wizard is to specify whether to copy tables, or to create a query to specify the data to transfer. The default is to copy tables, which is what you want. Click the Next button to continue.
    
    
14. The fourth step of the wizard is to select what tables to copy. Click Select All to select all of the tables in IntOpDB2. The destination tables will use the same table names. To ensure the proper column mappings at the destination DB2 database, make the following modifications:
    • For the OrderDetails table, click the Transform (…) button to display the Column Mappings and Transformations dialog box. For the Price column, change the precision from 19 to 5, and change the Scale from 4 to 2. Click OK to continue.
      
      
    • For the Orders table, click the Transform (…) button to display the Column Mappings and Transformations dialog box. For the OrderDate column, change the data type from TIMESTAMP to DATE, and clear the Nullable check box. Click OK to continue.

    Click the Next button to continue.

15. The next step gives the opportunity to save, schedule and replicate the DTS package you just defined. The default is to simply run the DTS package immediately. Click the Next button to continue to the last step.
    
    
16. The last step is a confirmation message with a summary of what the DTS package will do. After reviewing the summary, click the Finish button to complete the wizard. While the DTS package is running, a progress bar and status window will keep you advised of how well the process is going. At the end, a dialog box will display either success or failure of the DTS package execution. Assuming success, four new tables and their data should now exist on the DB2 database server.
    
    
17. Click OK to close the dialog box, and Done to close the wizard.

Now that the data is on DB2, you must make it possible for the Web application to use the tables on the DB2 database instead of the IntOpDB2 database on the SQL Server.

To configure the data component to use DB2

1. In Visual Basic 6.0, open the Interop Visual Basic Project Group, interop.vpg, found in the components directory of the download.
   
   
2. In the TierData project, open the cData module, and locate the DB2Conn function.
   
   
3. Locate the values for the DB2 connection properties, and change them to the values required to connect to your DB2 database. The default connection type is LU6.2. To use a TCP/IP connection, follow the directions in the code for making the change.
   
   
4. Save the file.
   
   
5. Click TierData Properties on the Project menu.
   
   
6. When presented with the Project Properties dialog box, click the Make tab.
   
   
7. In the Conditional compilation arguments text box, set the USE_DB2 argument to a non-zero value (that is, USE_DB2 = 1).
   
   
8. Click the Component tab.
   
   
9. Select Binary compatibility, and click OK.
   
   
10. Click Make TierData.dll on the File menu.
    
    
11. Click OK in the Make Project dialog box to compile the DLL.
    
    
12. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To configure the business component to use DB2

1. In the TierLogic project, open the Globals.bas module, and locate the General/Declarations section. Edit the value of the global constant g_DB2_COLLECTION to match the value provided for the Default Schema in cData module's DB2Conn function.
   
   
2. Save the file.
   
   
3. Click TierLogic Properties on the Project menu.
   
   
4. When presented with the Project Properties dialog box, click the Make tab.
   
   
5. In the Conditional compilation arguments text box, set the USE_DB2 argument to a non-zero value (that is, USE_DB2 = 1).
   
   
6. Click the Component tab.
   
   
7. Select Binary compatibility, and click OK.
   
   
8. Click Make TierLogic.dll on the File menu.
   
   
9. Click OK in the Make Project dialog box to compile the DLL.
   
   
10. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To update the MTS package with the new components

1. In the left pane of MTS Explorer, open the OnlineBookstore package.
   
   
2. Click Shut down on the Action menu.
   
   
3. Copy the TierData and TierLogic DLLs to the IIS machine, overwriting the previous versions.

When compiled in this manner, the cOrder.GetOrderStatus function in the TierLogic project will use a stored procedure, spGetOrderDetails_DB2, that creates a distributed query across a linked server.

In the database directory of the download is a script that will create the Linked Server to the DB2 database, and create the stored procedure in IntOpSQL. This stored procedure will be used to create a distributed query across the linked DB2 and IntOpSQL for obtaining a customer's order status.

To create the linked server and the stored procedure

1. In Query Analyzer, open the linked.sql script from the database directory of the download.
   
   
2. Follow the directions in the script for modifying the values that correspond to your DB2 database as configured earlier.
   
   
3. Run the script by pressing F5 or clicking Play.

An instance of the OLE DB Provider for DB2 cannot be created out-of-process, so it must be created as an in-process server inside the SQL Server process space. By default, instances of OLE DB providers are created out-of-process in SQL Server. For this linked server to function, you must modify a setting to have SQL Server create an instance of the OLE DB Provider for DB2 in-process. Once this setting is made, any future linked servers created using this provider will inherit this setting automatically.

To set the Microsoft OLE DB Provider for DB2 to run in-process

1. In SQL Server Enterprise Manager, open the Linked Servers node under the Security folder.
   
   
2. Select the linked server you just created.
   
   
3. Click Properties on the Action menu to display the properties of this linked server.
   
   
4. Just below the provider name locate, and click the Options button.
   
   
5. Select the Allow InProcess check box.
   
   
6. Click OK to accept this change.
   
   
7. Click OK on the property sheet to close it.

Running the application will now utilize the DB2 database instead of the IntOpOracle database in SQL Server. In addition, the spGetOrderDetails_DB2 stored procedure, which uses a distributed query, will be used instead of the spGetOrderDetails stored procedure.

Setting Up AS/400 Interoperability

The AS/400 interoperability portion of the Online Bookstore utilizes externally described, AS/400 keyed physical files to store the shipping data that is found in the IntOp400 database on the SQL Server.

To access the AS/400 physical files, the OLE DB Provider for AS/400 and VSAM must be accessible to the server hosting the business objects.

To create the files on the AS/400 for use in place of the IntOp400 database, you will use the OLE DB Provider for DB2. The OLE DB Provider for AS/400 and VSAM is implemented as a source-only Distributed Data Management (DDM) Server. The DDM architecture defines the facilities for accessing data using APPC and LU6.2. AS/400 physical and logical files are externally described by the system, meaning that the metadata that defines the layout of records is kept by the system. Because the DB2/400 database is integrated with the operating system, data stored in a DB2/400 database is stored in externally described, keyed physical files accessible using DDM. Currently, the OLE DB Provider for AS/400 and VSAM does not provide for file creation. By using the OLE DB Provider for DB2 to export the data to the AS/400, you are essentially creating files you can access using the OLE DB Provider for AS/400 and VSAM. Use the OLE DB Provider in conjunction with SQL Server Data Transformation Services (DTS) to export the data to the destination AS/400.

To export the data to the AS/400

1. In the left pane of SQL Enterprise Manager, select the IntOp400 database.
   
   
2. On the Action menu, point to All Tasks, and click Export Data to start the DTS Export Wizard.
   
   
3. Click the Next button to proceed to the first step of the wizard.
   
   
4. The first step in the wizard is to select the data source from which to export data. By default, the specified Source is Microsoft OLE DB Provider for SQL Server, and the current SQL Server and database are specified.
   
   
5. Select Use SQL Server Authentication, and provide the credentials for the OnlineBookstore sysadmin account.
   
   
6. Click the Next button to continue.
   
   
7. The second step of the wizard is to select the destination for the data being exported. In the Destination list, select Microsoft OLE DB Provider for DB2.
   
   
8. The connection properties for DB2 can be specified manually, or by loading a predefined Universal Data Link (UDL) file. Click the Properties button to view the Data Link Properties dialog box and create the connection manually.
   
   
9. Click the All tab to view all of the connection properties.
   
   
10. Edit the following values:
    • Data Source—optional parameter used to describe the connection.
      
      
    • Default Schema—name of the collection where the provider looks for catalog information. In OLE DB, Default Schema is the schema name for the target collection of tables and views.

      For DB2 (MVS, OS/390), this is the target AUTHENTICATION (User ID or owner).

      For DB2/400, this is the target COLLECTION name.

      For DB2 UDB, this is the SCHEMA name.

    • Initial Catalog—the first part of a three-part, fully qualified table name.

      In DB2 (MVS, OS/390), this property is referred to as LOCATION.

      In DB2/400, this property is referred to as RDBNAM.

      In DB2 UDB, this property is referred to as DATABASE.

    • Network Transport Library—SNA to connect using SNA LU6.2, or TCP/IP.
      
      
    • Package Collection—the name of the DRDA COLLECTION where you want the provider to store and bind DB2 packages. This could be the same as the Default Schema, or use the default MSPKG.
      
      
    • Password—valid password for the accompanying DB2 User ID.
      
      
    • User ID—valid DB2 User ID.

    If the specified Network Transport Library is SNA, specify the following:

    • APPC Local LU Alias—name of the local LU alias configured in SNA Server.
      
      
    • APPC Mode Name—the APPC mode that matches the host configuration and SNA Server configuration. The default is QPCSUPP.
      
      
    • APPC Remote LU Alias—name of the remote LU alias configured in SNA Server.

    If the specified Network Transport Library is TCP/IP, specify the following:

    • Network Address—the TCP/IP address or TCP/IP host name/alias associated with the DRDA port.
      
      
    • Network Port—the DB2 DRDA service access port. The default is 446.
11. Click the Connection tab.
    
    
12. Click the Test Connection button to test the connection that was just defined. If the test is successful, click the OK button to close the Data Link Properties dialog box. Click the Next button to continue. If the test fails, an error message will be displayed that should indicate where the problem lies.
    
    
13. The third step of the wizard is to specify whether to copy tables, or to create a query to specify the data to transfer. The default is to copy tables, which is what you want. Click the Next button to continue.
    
    
14. The fourth step of the wizard is to select what tables to copy. Click Select All to select all of the tables in IntOp400. The destination tables will use the same table names. Click the Next button to continue.
    
    
15. The next step gives you the opportunity to save, schedule, and replicate the DTS package you just defined. The default is to simply run the DTS package immediately. Click the Next button to continue to the last step.
    
    
16. The last step is a confirmation message with a summary of what the DTS package will do. After reviewing the summary, click the Finish button to complete the wizard. While the DTS package is running, a progress bar and status window will keep you advised of how well the process is going. At the end, a dialog box will display either success or failure of the DTS package execution. Assuming success, three new tables and their data should now exist on the DB2 database server.
    
    
17. Click OK to close the dialog box, and Done to close the wizard.

Now that the data is on the AS/400, make it possible for the Web application to use these files instead of the IntOp400 database on the SQL Server.

To configure the data component to use AS/400

1. In Visual Basic 6.0, open the Interop Visual Basic Project Group, interop.vpg, found in the components directory of the download.
   
   
2. In the TierData project, open the cData module, and locate the AS400Conn function.
   
   
3. Locate the values for the OLE DB Provider for AS/400 and VSAM connection properties and change them to the values required to connect to your AS/400. The default connection type is LU6.2. To use a TCP/IP connection, follow the directions in the code for making the change.
   
   
4. Save the file.
   
   
5. Click TierData Properties on the Project menu.
   
   
6. When presented with the Project Properties dialog box, click the Make tab.
   
   
7. In the Conditional compilation arguments text box, set the USE_AS400 argument to a non-zero value (that is, USE_AS400 = 1).
   
   
8. Click the Component tab.
   
   
9. Select Binary compatibility, and click OK.
   
   
10. Click Make TierData.dll on the File menu.
    
    
11. Click OK in the Make Project dialog box to compile the DLL.
    
    
12. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To configure the business component to use AS/400

1. Open the TierLogic project.
   
   
2. Click TierLogic Properties on the Project menu.
   
   
3. When presented with the Project Properties dialog box, click the Make tab.
   
   
4. In the Conditional compilation arguments text box, set the USE_AS400 argument to a non-zero value (that is, USE_AS400 = 1).
   
   
5. Click the Component tab.
   
   
6. Select Binary compatibility, and click OK. 
   
   
7. Click Make TierLogic.dll on the File menu.
   
   
8. Click OK in the Make Project dialog box to compile the DLL.
   
   
9. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To update the MTS package with the new components

1. In the left pane of MTS Explorer, open the OnlineBookstore package.
   
   
2. Click Shut down on the Action menu.
   
   
3. Copy the TierData and TierLogic DLLs to the IIS machine, overwriting the previous versions.

If all has gone well, the application will be utilizing the files on the AS/400 instead of the IntOp400 database in SQL Server.

Setting Up COMTI for CICS and IMS Interoperability

The COMTI for CICS and IMS interoperability portion of the Online Bookstore utilizes a COBOL CICS application to process credit card authorizations. This feature has no comparable non-interoperability component. If the feature is not implemented, credit card authorizations are skipped when processing orders.

A COBOL source code file, chkcred.cob, is included with the sample application download. To use the COMTI for CICS and IMS interoperability feature, you must compile and link edit a host program and install it for execution in a CICS region.  The program will be invoked by an LU6.2 connection using DPL. If you do not know how to do this, contact your mainframe system administrator. Since mainframe installations vary so much from each other, it is beyond the scope of this document to provide complete detail.

To gather the necessary information

1. Determine which CICS region in which you will install the host code.  It must be one for which you can install a transaction program and that has APPC connectivity for COMTI.
   
   
2. Review the JCL in the sample source program, and determine your local procedures for translating, compiling, and linking a CICS transaction program written in COBOL. The sample program will work with or without Language Environment ("LE").

To establish the COMTI configuration for connectivity to the CICS region, you must have a Remote Environment definition on WinNT, and a "Connection" and "Sessions" resource in the CICS region. If you have already completed the setup for the Cedar Bank sample application that comes with SNA Server, this sample application can use all the same communications resources.

To define a remote environment

1. In the left pane of COMTI Manager, open the COM Transaction Integrator.
   
   
2. Select the Remote Environments folder.
   
   
3. On the Action menu, point to New, and click Remote Environment.
   
   
4. Select CICS LINK using LU6.2, and click OK to start the Remote Environment Wizard.
   
   
5. On CICS, use CEDA DEFINE CONNECTION and CEDA DEFINE SESSIONS commands to create the Connection-Sessions pair of resources used by CICS to communicate using LU6.2. Detailed properties for these resources are included in the worksheets.

To prepare the sample source program

1. Upload CICS program, chkcred.cob, to any convenient location on the host. You can use the 3270 applet IND$FILE capability, for instance.
   
   
2. Update the JCL to suit your local procedures. Make sure the output link module goes into a loadlib that is in the DFHRPL concatenation of your selected CICS region, so the region can load it.
   
   
3. Run the job, producing an executable load module.
   
   
4. To create the CICS program definition to install the sample program, use the CEDA DEFINE PROGRAM command on CICS to register the program.

Now that the CICS program is installed on the host, it is time to add the COMTI component to MTS.

To add the COMTI component to MTS

1. From the components directory of the download, copy creditcard.tlb to the same location as the TierData and TierLogic components on the web server.
   
   
2. In the left pane of MTS Explorer, open the Interop package.
   
   
3. Drag creditcard.tlb from its location on the Web server to the right pane of MTS Explorer. This will add automatically the COMTI component to the Interop package and register the component with MTS.
   
   
4. In the left pane of MTS Explorer, open the CreditCard.Merchant.1 component.
   
   
5. On the Action menu, click Properties.
   
   
6. Click the Remote Environment tab.
   
   
7. Select the appropriate remote environment, and click OK.
   
   
8. Select the Role Membership folder for this component.
   
   
9. On the Action menu, point to New, and choose Role.
   
   
10. Select the OBWebUser role, and click OK.

Next, add a reference to the creditcard.tlb type library in the TierLogic project, and compile a new version.

To configure the business component to use the COMTI component

1. Open the TierLogic project in Visual Basic 6.0.
   
   
2. Click References on the Project menu.
   
   
3. Add components/creditcard.tlb as a reference.
   
   
4. Click OK.
   
   
5. Click TierLogic Properties on the Project menu.
   
   
6. When presented with the Project Properties dialog box, click the Make tab.
   
   
7. In the Conditional compilation arguments text box, set the USE_COMTI argument to a non-zero value (that is, USE_COMTI = 1).
   
   
8. Click the Component tab.
   
   
9. Select Binary compatibility, and click OK.
   
   
10. Click Make TierLogic.dll on the File menu.
    
    
11. Click OK in the Make Project dialog box to compile the DLL.
    
    
12. Since this DLL has been compiled before, a dialog box will appear asking you if you want to replace the existing DLL. Click Yes.

To update the MTS package with the new component

1. In the left pane of MTS Explorer, open the OnlineBookstore package.
   
   
2. Click Shut down on the Action menu.
   
   
3. Copy the TierLogic DLL to the IIS machine, overwriting the previous version.

If all has gone well, the application will utilize the CHKCRED CICS program in the remote region to authorize credit cards. Because this is a sample application, credit card authorizations are simulated. The CICS program will disapprove credit cards ending in 0 or transactions greater than $99,999.00.