PreviousEditing a BMS Mapset JoinsNext"

Chapter 20: DB2 Applications (Host Compatibility Option)

Use Host Compatibility Option, together with IBM's DB2 database system, to include a DB2 application in a Mainframe Express project. You can also maintain a DB2 UDB database using a set of interactive tools. Alternatively, you can connect to an existing mainframe DB2 database using IBM's DB2 Connect product. You can compile, edit and debug your DB2 application in a DB2 environment on your PC.

SQL Option and Host Compatibility Option are alternative facilities for handling a DB2 application. See the chapter DB2 Applications (SQL Option) for a session using SQL Option.

You need to have read the chapter Start Here for the Tutorials and worked through the first session, Using Mainframe Express, before you do this session.

You need to have installed Host Compatibility Option to do this session. Note that Setup will only have offered you the option to install Host Compatibility Option if you ran it with the /hco command line parameter. Otherwise it will have offered SQL Option instead.

We assume you are familiar with DB2 on a mainframe.

You must also have IBM's DB2 Universal Database system installed.

20.1 Overview

Host Compatibility Option is a set of tools that enable you to do DB2/MVS and DB2/VM development and testing on your PC using some of IBM's family of DB2 Universal Database products.

This session takes you through maintaining and running an DB2 application on your PC.

The demo application used in this session is a simple DB2 application that you might have downloaded from a mainframe. In this session, you get it working on your PC.

20.2 Preparation

In the following, if you need to open the DB2 Command Window, do so by clicking Start, then Programs, then DB2, then Command Window.

  1. Issue a DB2START command if you have not already done so. In the DB2 Command Window, enter:

    db2start

  2. If this tutorial has been run before, and you want to run it from scratch, you can delete the database it created. In the DB2 Command Window, enter:

    db2 drop db db2demo

    If this tutorial has not in fact been run before, this command results in a message saying the database cannot be found. You can ignore this message.

  3. You can now close the DB2 Command Window.

  4. If you have closed Mainframe Express, open it as before. If any project window or other windows are open, close them.

20.3 Sample Session

In this session you:

20.3.1 Creating a Project

To create the project:

  1. Click New on the File menu, then select Project on the New dialog box and click OK.

  2. Enter hcodemo as the name of the project and change the name of the Project Folder to d:\mfuser\projects\gsdemo\hcodemo, then click Next.

  3. Accept the default, Using this wizard, and click Next.

  4. Leave the default folder for the catalog unchanged and click Next.

    You now get a page asking which of the installed options this project uses.

  5. Ensure that only the SQL check box has a check mark, and click Next.

    Because you have said this project uses SQL, and you installed Host Compatibility Option (rather than SQL Option), you now get a page asking the name of the DB2 database.

  6. Enter DB2DEMO and click Next.

  7. Click Finish.

20.3.2 Adding Files to the Project

To add your files to the project:

  1. Click Add Files on the Project menu.

  2. In the Add Files to Project dialog box, open the folder \mfuser\projects\gsdemo\hcodemo\source. Ensure the Files of Type field is set to Source Files.

  3. Click Add All, then click Yes on the confirmation message.

    The files are added to the project.

  4. Click Done.

20.3.3 Creating a DB2 Database

You must create a database before you can do anything else. The database is where all tables are defined and data stored.

  1. Click HCO on the Tools menu, then click Create Database.

    The Create Database dialog box appears.

  2. Type DB2DEMO in the Database field and Demo database in the Description field, then click g on the dialog box.

  3. When it finishes, click OK on the message saying "Request completed...", and then close the Create Database dialog box using its g button.

    A new tab has appeared in the Output window, displaying the Host Compatibility Option log. It has a tab HCO Log.

    If this session has been run before, and you did not delete the database while following the section Preparation above, the log will include a message that the database alias "DB2DEMO" already exists. You can ignore this messsage.

20.3.4 Adding a Table

You use the supplied DDL file to add the table and data.

  1. Click HCO on the Tools menu, then click DDL Processor.

    The DDL Processor Tool appears. It auto-connects to the database DB2DEMO specified in the project.

  2. Click Select DDL on the File menu of the DDL Processor Tool, or click on its toolbar.

  3. In the Select DDL File dialog box, open the folder \mfuser\projects\gsdemo\hcodemo\data. Ensure the Files of Type field is set to sql Files (*.sql).

  4. Select hcomvs.sql and click Open.

    This sets the DDL File field to the name of this file.

  5. Click g on the DDL Processor Tool.

  6. When it finishes, click OK on the message saying "DDL processing completed", and then close the DDL Processor using its button.

    The log file is displayed in the HCO Log view of the Output window. Don't worry if it shows a non-zero return code. The DDL starts with some DROP TABLE statements to delete these tables if they have been created previously. These statements will return non-zero codes if you are running the demo from scratch.

20.3.5 Importing Data

We need to import some data into the table. We will import data into the DEMO.PRODUCT table from a file that has data stored in DSNTIAUL format.

  1. Click HCO on the Tools menu, then click Import Data.

    The Import Data Tool appears. It auto-connects to the database DB2DEMO specified in the project.

  2. Click Format on the Options menu of the Import Data Tool, then click DSNTIAUL.

  3. In the Character Delimiter dropdown list, select single quote (') (you may have to click the to see it), then click Set.

  4. In the listbox, click PRODUCT.

  5. Click Import on the Database menu of the Import Data Tool, or click on its toolbar.

  6. Select Product.dsn and click Open.

    This sets the Import from field to the name of this file.

  7. Click g on the DDL Processor Tool.

  8. When it finishes, click OK on the message saying "Import completed, ...", and then close the Import Data Tool using its button.

    The log file is displayed in the HCO Log view of the Output window.

20.3.6 Generating a COBOL Copybook

To generate a COBOL copybook:

  1. Click HCO on the Tools menu, then click DCLGEN.

    The Generate Copybook Tool appears. It auto-connects to the database DB2DEMO specified in the project.

    We will generate a copybook that has the table name as prefix for all host variables.

  2. In the listbox, click ITEMS.

  3. Click Generate copybook on the Database menu of the Generate Copybook Tool, or click on its toolbar.

    In the Save dialog box that appears, notice that items.cpy is ready selected.

  4. Click Save.

  5. When it finishes, click OK on the message saying "DCLGEN created ITEMS.CPY", and then close the Generate Copybook Tool using its button.

    The copybook is displayed in the HCO Log view of the Output window.

20.3.7 Generating an Alias

To generate an alias for a table in the database:

  1. Click HCO on the Tools menu, then click Generate Alias.

    The Generate Alias Tool appears. It auto-connects to the database DB2DEMO specified in the project.

  2. Click Select All on the Options menu of the Generate Alias Tool.

  3. Click Generate DDL on the Database menu of the Generate Alias Tool, or click on its toolbar.

    In the Save dialog box that appears, notice that genalias.sql is ready selected.

  4. Click Save.

  5. When it finishes, click OK on the message saying "All tables processed...". Do not close the Generate Alias Tool.

    The ALIAS DDL statements created are displayed in the HCO Log view of the Output window.

    If you accidentally closed the Generate Alias Tool, don't worry. Just repeat from the beginning of this Generating an Alias section.

    Notice that the on the toolbar is now enabled (no longer grayed out). Now that you have created the ALIAS DDL statements, you use the DDL processor to run the DDL that actually creates the ALIASes. It is a two step process, since a Database Administrator may want to change the DDL to make it generic.

  6. Click or the equivalent Generate DDL on the Database menu.

    The Generate Alias Tool closes and the DDL Processor Tool opens. It auto-connects to the database DB2DEMO specified in the project.

    You can now generate DDL using the aliases just created.

  7. Click Select DDL on the File menu of the DDL Processor Tool, or click on its toolbar.

  8. In the Select DDL File dialog box, open the folder \mfuser\projects\gsdemo\hcodemo\data. Ensure the Files of Type field is set to sql Files (*.sql).

  9. Select genalias.sql and click Open.

    This sets the DDL File field to the name of this file.

  10. Click g on the DDL Processor Tool.

  11. When it finishes, click OK on the message saying "DDL processing completed", and then close the DDL Processor using its button.

    The log file is displayed in the HCO Log view of the Output window.

20.3.8 Building the Project

To build the project:

  1. Click Build on the Build menu.

    The build finishes with "Build finished with no errors".

    The COBOL programs in this application expect the tables to have a prefix of DEMO. In fact Host Compatibility Option by default used your user ID (which you specified the first time you started Mainframe Express - see the chapter Using Mainframe Express) as the prefix when creating the table. However, this works because we created aliases with the DEMO prefix.

    If you did not want to create the aliases, you would have had to set the DEMO prefix in the build settings. To do this, you would click COBOL in the left-hand pane, then the Project menu, then Build settings for COBOL, then the Preproc tab, then click by Exec SQL to put a check mark there, then click the More button to the right of Exec SQL, then enter DEMO in the Implicit Qualifier field.

20.3.9 Running the Application

To run the application:

  1. Click hcodyn.tso in the right-hand pane of the project window to select it.

  2. Click Run on the Debug menu.

  3. Click the TSO tab. The name of the CLIST file to run has defaulted to the one you selected.

  4. Click OK.

    The application runs. The Application Output window appears and displays the screen output from the application. The application displays a list of products, taken from the PRODUCTS table in the database.

    If you get a "***" line indicating more input is required, just press Enter to let the application complete.

    You can run the application in the same way using hcobatch.jcl instead of hcodyn.tso.

20.3.10 Exporting Data

The sections so far in this session created the database and then built and ran the application. Let's now look at a few more tools you can use on your database without you needing to create an application.

To export data from a table

  1. Click HCO on the Tools menu, then click Export Data.

    The Export Data Tool appears. It auto-connects to the database DB2DEMO specified in the project.

  2. In the listbox, click PRODUCT.

  3. Click Export table on the Database menu of the Export Data Tool, or click on its toolbar.

    In the Save dialog box that appears, notice that product.ixf is ready selected.

  4. Click Save.

  5. When it finishes, click OK on the message saying "Export completed...", and then close the Export Data Tool using its button.

    The log file is displayed in the HCO Log view of the Output window.

20.3.11 Executing an SQL Query

To execute an SQL Query:

  1. Click HCO on the Tools menu, then click SQL Query.

    The Query Tool appears. It auto-connects to the database DB2DEMO specified in the project.

  2. Click Open on the Query menu of the Query Tool, or click on its toolbar.

  3. Select Newqry.sql and click Open.

    This sets the Query listbox to the text of this query.

  4. Click g on the Query Tool.

  5. On the Set Value for Host Variable dialog box, set the Value field to 0004, then click Set.

  6. When it finishes, click OK on the message saying "Query completed...". Do not close the Query Tool.

    A new tab has appeared in the Output window, displaying the result of the query. It has a tab HCO Query.

    If you accidentally closed the SQL Query Tool, don't worry. Just repeat from the beginning of this Executing an SQL Query section.

    You can have the Query Tool explain the query.

  7. Click Explain on the Query menu of the Query Tool, or click on its toolbar.

  8. If you get a message asking whether to create Explain tables, click Yes.

  9. Close the Query Tool using its button.

    An explanation of the query is displayed in the HCO Log view of the Output window.

20.3.12 Other Tools

If you look at the submenu that comes up when you click HCO on the Tools menu in Mainframe Express, you'll see several more tools besides those covered in this session.

20.4 Before Continuing

Hide the Application Output window and close the project.

You can close DB2 if you wish.

The DB2DEMO database you created in this session is used in several subsequent sessions.

If you want to take a break before going on to the next session, you can close Mainframe Express.

Return to the Tutorials Map in the chapter Start Here for the Tutorials and choose which session to go on to next, depending on your interests.

Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.

PreviousEditing a BMS Mapset JoinsNext"