Chapter 27: DB2 Applications (SQL Option)

Use SQL Option to include a DB2 application in a Mainframe Express project.You can also maintain a DB2 database using an interactive tool, the SQL Wizard. 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 (Host Compatibility Option) for a session using Host Compatibility 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 SQL Option to do this session.

We assume you are familiar with DB2 on a mainframe.


27.1 Overview

The full name of this option is SQL Option for DB2. We will generally refer to it as SQL Option. It is a relational database that supports the debugging and testing of DB2 applications.

This session takes you through maintaining and running a 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.

27.2 Preparation

If you installed both SQL Option and Host Compatibility Option, ensure SQL Option is active, as follows:

  1. Ensure Mainframe Express is closed.

  2. Click Start > Programs > Micro Focus Mainframe Express > Configuration > Database.

  3. Ensure XDB is selected.

  4. Click OK.

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

27.3 Sample Session

In this session you:

27.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 sqldemo as the name of the project and change the name of the Project Folder to d:\mfuser\projects\gsdemo\sqldemo, 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 SQL Option (rather than Host Compatibility Option), you now get a page asking for details of the SQL server the project uses.

  6. Ensure that all check boxes contain check marks, and click Next.

  7. Click Finish.

    Mainframe Express checks that an XDB Server is running on your computer. When it finds that none is, it displays a dialog box asking if you want to start one, and if it should check in future.

    You need the server running so you can use the SQL Wizard and run the application. You could start it later from the Tools menu, but it's convenient to start it here.

  8. Leave the check boxes unchanged, that is, with two checked, and click OK.

    The XDB Server starts, with its window minimized. You can click it on the Windows taskbar if you want to see it, but as you don't need to watch it you can leave it minimized.

27.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\sqldemo\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.

27.3.3 Building the Project

To build the project:

  1. Click Build on the Build menu.

    As you saw in the chapter Using Mainframe Express, the correct compiler is automatically called for each source file - in this case, for the COBOL and CLIST files. For the COBOL file, the SQL preprocessor is called first, followed by the COBOL compiler.

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

27.3.4 Starting the XDB Server

To start the XDB Server on Windows NT:

  1. Settings > Control Panel on the Windows NT Start menu, then double-click Micro Focus (or MERANT) XDB Server for MFE.

  2. Click Start. If the Start button is not available, the XDB Server is already running.

  3. Click Close.

To start the XDB Server on Windows 95:

  1. Click SQL For DB2 > Start Server on the Tools menu. If the Start Server menu choice is not available, the XDB Server is already running.

27.3.5 Running the Application

To run the application:

  1. Click sqltest.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. This application simply inserts an employee record into a database, fetches the surname and displays it, and then deletes the record. It displays messages showing its progress.

    You can leave the Application Output window visible, as the next section uses it as well.

27.3.6 Debugging the Application

To debug the application:

  1. Ensure sqltest.tso is still selected, then click Start Debugging on the Debug menu.

  2. Click the TSO tab, then click OK.

    You see the same things happening on your screen as when running the application, but in addition a source view window appears, showing the source of sqltest.cbl. The application has not yet displayed its first screen in the Application Output window, because execution is paused on the first executable statement of the COBOL application. (This is the second statement in the Procedure Division, because the first, EXEC SQL DECLARE...CURSOR, is executed at compile time.)

    Notice that the source you see is the original source and not the source after preprocessing.

  3. Click a few times.

    This demonstrates that you single-step a DB2 application in the same way as any other. All the same debugging features are available for a DB2 application as for any other application. We will not debug this application further, but will simply complete the run.

  4. Click .

    The application completes without debugging, and exits. As before, the Application Output window shows the messages displayed by the application.

27.3.7 Closing the XDB Server

When you have finished, you shut down the XDB Server:

  1. Click SQL for DB2 > Stop Server on the Tools menu.

27.3.8 Setting up Access to the Mainframe Using XDB Link

To connect to the mainframe directly, without using the XDB Server:

  1. Use the Gateway Profile utility to log information about the mainframe DB2 location, as follows:

    1. Click SQL For DB2 > XDB Linkon the Options menu

    2. Login to local XDB server as user INSTALL with no password

    3. Click REGISTER

    4. Consult your mainframe DBA and HELP information to fill out relevant fields.

  2. Use the Options utility to define the location and protocol you wish to use, as follows:

    1. Click SQL For DB2 > CLIENTon the Options menu

    2. Click the Security tab and enable Client Security

    3. Click the Connect tab and select DRDA as Communcation Protocol

    4. Ensure Connect Location and System Location are the values specified in GPROF utility

27.4 Before Continuing

Close the source view window and hide the Application Output window.

The Sqldemo project is used in the next session, Maintaining a DB2 Database. If you're planning to go onto that session, you can keep the project open.

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.


Comments on the books? Click Send Us Comments.


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