PreviousDefining Segment Layouts for IMS Databases

Chapter 38: SQL Option for DB2

This chapter explains how you can use Mainframe Express to create and maintain DB2 applications on your PC.

38.1 Overview

With SQL Option, you can compile, debug and run programs that contain Embedded SQL statements from the Mainframe Express IDE without requiring access to a mainframe or LAN-based database system.

SQL Option allows the EBCDIC environment on the PC to be fully compatible with your mainframe's EBCDIC environment. Refer to the section SQL Option NLS Environment for details

SQL Option uses the same technology as the popular XDB database system. An XDB database behaves exactly like a mainframe DB2 database but it runs on the PC. A personal XDB Server is installed on your PC, providing you with a completely self-contained development and test environment for DB2 applications.

If you are developing in a workgrouping environment, your system administrator can configure SQL Option so that you can access one or more shared, LAN-based XDB Servers in addition to your personal XDB Server. This two-tier configuration provides you with several further options for testing your application. For example, you can:

In addition, with appropriate configuration, you can access mainframe DB2 databases seamlessly with SQL Option's connectivity features. With this third tier configured, you have access to the full range of development and test options, giving you complete freedom to devise an effective workflow customized to your environment. For example, you can:

38.2 SQL Option Components

SQL Option for DB2 consists of an embedded SQL precompiler and a number of configuration tools and graphical data utilities:

You can access all these components except for the Options utility on the Tools menu of the Mainframe Express IDE. Help for SQL Option tools is available from the components or on the IDE Help menu. From this menu you can also access Error Messages help and a full SQL Reference.

You can find the Options utility by selecting SQL on the Options menu.

38.3 XDB Server

XDB Server is the server component that performs all database operations and emulates a DB2 system. A personal XDB Server is installed on your machine when you install SQL Option for DB2. This local copy of XDB Server is the default server. It must be running before you can debug or run client applications against a local XDB database. You can start it manually on the Tools menu or you can configure your project to start it automatically (if it isn't already running) when the project is loaded.

If you want to access mainframe DB2 data directly, your system administrator must first install and configure a number of SQL connectivity components. Once this is done, your system administrator can advise you how to connect to mainframe data. Detailed information on the DB2 Link feature is available by clicking SQL For DB2 Help > Link To DB2 on the IDE Help menu.

38.3.1 Server Configuration Utility

The Server Configuration utility enables you to change the configuration of your personal XDB Server. For example, you can change the following:

38.3.2 Server Administration Options

The SQL for DB2 option on the Mainframe Express IDE Tools menu enables you to control your connection to an XDB Server.

You can use Start Server to run your personal XDB Server.

Log On enables you to log on interactively to an XDB Server when client security is switched on. Client security is controlled from the Security tab of the Options utility. Client security must be switched on to access an XDB Server that has security switched on.

You would typically use Log On if your XDB Server has security switched on and you want to use a number of the SQL Option utilities without having to log on separately for each one. You do not need to log on explicitly if your XDB Server does not have security switched on.

Log Off enables you to undo a previous log on. If, for example, you log on to an XDB Server that has security switched on and then log off, the next time you try to access the XDB Server, you have to enter an AuthID and password to gain access. You do not need to log off explicitly if your XDB Server does not have security switched on.

38.4 SQL Wizard

SQL Wizard is a graphical utility that makes it easy to create, maintain and query XDB and DB2 databases. You can use SQL Wizard to:

38.4.1 Managing System Security and Priorities

SQL Option is provided with the security and administration features you would expect of a database system. Depending on how you intend to use it, however, you might not need to enable the security features. For this reason, your personal XDB Server and its client tools and utilities are initally installed with security switched off.

Security is controlled separately at the server and at the client. If you switch security on for an XDB Server, it can only be accessed by clients that also have security switched on. (In this context, a client is one of the XDB configuration tools or graphical data utilities. The client applications that you develop are expected to handle authorization in the usual way by means of CONNECT statements). If you are developing a DB2 application that handles passwords, you may want to work with a test environment that has security switched on to enable user authentication.

Use the Admin menu of SQL Wizard to manage system security. You can control security at three levels:

When security is switched off for an XDB Server, all users are effectively superusers because:

Server security status is set using the Server Configuration utility on the IDE Tools menu. Once security has been enabled, users must log on with a valid AuthID and, if required, password. The user's actual AuthID replaces the shared one, which means that a user cannot access a database unless they have been granted the appropriate access privileges by the database owner using GRANT and REVOKE statements.

An AuthID that has been assigned superuser status can change user, group and priority settings that affect all databases on an XDB Server. Initially an AuthID called INSTALL is set as the superuser. If you log on with this AuthID, you can create further superusers as required by clicking Users on the Admin menu and giving each user the appropriate status.

Ordinary users can use the Admin menu only to change their password. Ordinary users who want to change access privileges for specific databases and tables that they own should click New > SQL on the File menu and use the SQL statements GRANT and REVOKE to change access privileges. The AuthID of the creator of a table is deemed to be its owner.

As well as managing system security, a superuser can assign priorities to individual users or groups of users. Priorities control how much processing resource is available to the user or group, depending on criteria set by the superuser.

Note: When you install SQL Option, the default AuthID is set to TUTORIAL. This AuthID has user privileges and is not authorized to access system tables. This means that, if you switch security on for your personal XDB Server, you cannot use this AuthID to log on to the client utilities.

You should log on using the default superuser AuthID, INSTALL. You can then either set the TUTORIAL AuthID to have superuser status or you can grant it suitable authority on system tables, as appropriate. The INSTALL superuser AuthID has no password assigned initially: you should allocate one to it as soon as possible after switching XDB Server security on.

38.4.2 Managing Locations, Tables and Queries

SQL Wizard's Catalog Browser window provides an easy way to manage locations, tables and queries, including XDB Server system tables (providing your AuthID has suitable authority). You can open it by clicking any entry on the View menu of SQL Wizard or by clicking on the toolbar. The Catalog Browser has three tabs: Locations, Table and Query.

If you are a superuser, you can use the Locations tab to manage locations. A superuser can create, alter and drop locations, as well as set the currently active location. Other users can only view a list of available locations.

The Table tab displays the hierarchy of locations, AuthIDs, tables and columns. From this page you can create, open, alter and drop tables, as well as view table definitions and indexes, primary keys, foreign keys, views, aliases and synonyms. Note that only a superuser can edit system tables.

The Query tab displays all stored queries accessible to the current server under the Location, AuthID and Query hierarchy. From this page you can create, open, run and delete queries.

38.4.3 Creating and Running SQL Queries

You can create SQL queries either by entering SQL statements directly into an SQL window (click on the SQL Wizard toolbar), or by using a prompted query in the Query Design window (click on the toolbar). You do not need to understand SQL to create a prompted query. The Query Design window is split into two areas:

When you create a prompted query, you first select the table and columns. Then you apply conditions and sort criteria to the columns to achieve the final result set. Further choices are available from the menus, including joins, the creation of computed columns and the use of built-in functions such as minimum, maximum and average.

As you create a prompted query, the equivalent SQL statements are built up by SQL Wizard. To see them, click . You can edit the SQL statements directly but the changes you make do not appear in the Query Design window. To go back to the Query Design window, click . It's a good idea to save your prompted query before you try editing it in the SQL window. You can run your query and see the results at any time by clicking .

38.4.4 Entering Data Directly into a Table

SQL Wizard provides both a spreadsheet-like table view and a form view that enable you to enter and edit data directly. Use the table view to see many records at once and the form view to see one record at a time.

You must have appropriate access privileges to edit tables. Only a superuser can edit system tables. Ordinary users can edit tables created using either their own AuthID or a GroupID to which they belong. They can also edit tables to which they have specifically been given access using the GRANT statement. In all cases, you must select Allow Editing on the Record menu before editing is enabled.

You can open or create a table at any time from the Catalog Browser. See the section Managing Locations, Tables and Queries for more information. The Table view opens automatically to show the result of a query.

38.4.5 Importing and Exporting Data

SQL Option can import and export data in a variety of formats. You can specify import and export settings by clicking New > Import or New > Export on the File menu of SQL Wizard. You can optionally save these settings in an .imp or .exp file that you can subsequently run as a batch file for routine data conversions.

Use SQL Option's import capabilities to run programs against test data prepared in another file format. For example, you could export suitable test data held in a Microsoft Access database, or a Microsoft Excel or Lotus 1-2-3 spreadsheet, to delimited or fixed-field ASCII files. You could then import these files into XDB Server.

You can import data into an XDB database from any of the following sources:

Use SQL Option's export capabilities to transfer data from an XDB database to a mainframe DB2 database if you do not have gateway connectivity, or to create the DDL commands required to create a table.

You can export data from an XDB database into any of the following targets: Import/Export NLS Considerations

You need to be aware of character set conversion issues when importing or exporting data, especially if:

If you are importing or exporting EBCDIC data using SQL Wizard, we recommend using the DSNTIAUL data format as this enables you to specify an appropriate EBCDIC to ANSI or ANSI to EBCDIC conversion.

You specify the appropriate conversion by picking an entry in the code page window. For example, suppose you want to export a Swedish EBCDIC table. During the export, you would pick the ANSI to EBCDIC translation 1252 - 278. Or, to import a Spanish EBCDIC table, you would pick the EBCDIC to ANSI translation 284 - 1252.

38.4.6 Running Batch Scripts

You can use SQL Wizard to run the following kinds of batch scripts:

To run a batch script, click Run Batch on the File menu in SQL Wizard.

Several other SQL Option utilities can create batch files; you can also run these from the appropriate utility. For example:

38.5 Migrate Utility

If your system administrator has configured the mainframe connectivity facilities of SQL Option, you can use the Migrate utility to import or export data between XDB locations, or between an XDB location and a DB2 subsystem.

The Migrate utility simplifies the process of copying data from one location to another by eliminating intermediate file transfers and the hand-editing of indexes and foreign keys. Using the Migrate utility, you can copy keys, indexes and tables in a single step. You can also copy a subset of a table (selected columns or rows) by extracting data with a SELECT statement.

You can use the Migrate utility's referential-integrity feature to detect data dependencies automatically and copy tables in the correct order. You can also produce a preliminary "impact analysis" report, which describes the effect of a migration before it is performed. If you copy certain tables regularly, you can save the specification in a .mig file. You can then run this file in batch mode, enabling you to perform a migration without respecifying it manually.

For more information about the Migrate utility, click SQL For DB2 Help > Migrate on the IDE Help menu.

38.6 Execute SQL Option

You can use Execute SQL to execute an SQL file that is part of the currently open project. To use it, click on the file in the Project Workspace then click Execute SQL on the IDE Tools menu. The file must have an .sql extension.

38.7 Declaration Generator Utility

In order to access DB2 data from an application program, you need to create host variables that are common data items between the SQL statements in your query and in your program. A data item declaration in the program must have the same name as that used in your SQL statement and must conform to the relevant DB2 data type for the column concerned.

The Declaration Generator utility automates the process of creating copybooks to declare host variables. You specify the table for which you want to generate a copybook and the Declaration Generator creates a copybook with data declarations that match the data-names and types used in the XDB system. The Declaration Generator can be used interactively or in batch mode and can create a separate copybook for each table or one copybook containing declarations for many tables.

To open the Declaration Generator, click SQL For DB2 > Declaration Generator on the Tools menu.

38.8 Options Utility

Use the Options utility for configuring SQL Option for DB2 and its connectivity with XDB and DB2 database servers. The settings that you can change fall into the following categories:

The Options utility has a Summary page that enables you to obtain a list of all the current configuration settings. You cannot make changes to any of the options from the Summary tab but you can print it for reference purposes.

38.9 Bind Utility

The Bind utility enables you to create static SQL packages in a remote DB2 location accessed using DRDA, providing that the target location is registered appropriately at the DB2 Link gateway and that your AuthID has Bind authority at the target location.

The Bind utility creates a static SQL package on the remote system by processing database request modules (DBRM) stored in .dbr files that are created by the SQL precompiler. The precompiler creates a separate .dbr file for each program, with a separate DBRM entry in the file for each Embedded SQL statement in the program.

You do not need to bind your application if you use the default SQL precompiler settings, as these enable Embedded SQL statements in your programs to be run dynamically against the remote system. Running Embedded SQL dynamically is useful in situations where you want to access remote DB2 data but do not want to keep rebinding your application as it changes; for example, while debugging the application. You can optionally change the precompiler settings to produce static SQL database request modules to bind the application for deployment purposes. For more information on the relevant directives (DBRM, LOCATION, COLLECTION-ID and AUTOBIND), click SQL For DB2 Help > COBOL Precompiler > SQL Option Preprocessor > SQL Option Preprocessor Directives on the IDE Help menu.

A package at a location is identified by a collection identifier, a program identifier, a version label and a consistency token. Typically, you would use the collection identifier as a way of grouping the packages used in a single application consisting of one or more programs. The precompiler creates a program identifier for a package from the root of the program filename. You can specify a version label or accept the default value (01). You do not have to specify a version label, because the current package can be identified from its consistency token, generated from a timestamp at the start of precompilation. Note that each time you recompile a DB2 application, the timestamp changes and you must rebind the application to the DB2 host location if you are using static SQL packages. An alternative approach to avoid having to rebind your application each time you compile it is to debug against data stored in an EBCDIC location on your personal XDB Server. You can then test it against mainframe data when you have achieved a satisfactory level of stability.

The Bind utility enables you to specify a number of options for an application, including a version number and the isolation level of the application. These options can be stored in a file for future use.

For more information about the Bind utility, click SQL For DB2 Help > Bind on the IDE Help menu.

Note: The Bind utility does not appear on the SQL For DB2 submenu of the IDE Tools menu by default but you can add it to a submenu of the Tools menu yourself. See Bind in the online help index for more information.

38.10 SQL Option NLS Environment

SQL Option allows you to create XDB locations that emulate all the various mainframe EBCDIC code pages. When you access these locations with SQL Wizard or a COBOL program, your data in these locations appears identical to the data on the mainframe.

The five steps necessary to ensure complete compatibility with the mainframe and/or Mainframe Express' IDE are as follows:

  1. Decide which EBCDIC code page you want to emulate.

    Typically, you would use the mainframe's code page. (If you do not know what it is, you may be able to use XDB Link to find out.) If you do not have a mainframe, you can choose whichever code page you prefer.

  2. Create a local EBCDIC location for this code page.

    Use SQL Wizard's Create Location dialog box. Pick the EBCDIC code page you want in the Sort Sequence box. For more information, click SQL For DB2 Help > SQLWizard on the IDE Help menu.

  3. Ensure that your project's national language support (NLS) setting is the same as your EBCDIC location.

    When you compile a COBOL program with Mainframe Express, it is usually associated with a particular EBCDIC code page. (This is explained in the chapter National Language Support.) You can discover which code page you are using by looking on the NLS page of the General page of the Project Settings dialog box. This entry must be consistent with your location's EBCDIC code page to ensure complete compatibility.

  4. If you have XDB Link, configure it to access the mainframe.

    Use the Gateway Profile utility to define your DB/2 locations to the PC software, then specify the single Workstation Code Page as 911 (in the local Workstation Configuration). Next, use SQL Wizard to access the DB/2 location on the mainframe. A file named cpg_info.txt is created in your mfsql\bin folder, identifying the EBCDIC code page of the DB/2 location on the mainframe. The following table defines this EBCDIC code page number:

    Mainframe Express NLS Setting
    Code Page Number
    31 Dutch 37
    33 French 297
    34 Spanish 284
    39 Italian 280
    43 Austrian German 273
    44 English (UK) 285
    45 Danish 277
    46 Swedish 278
    47 Norwegian 277
    49 German 273
    351 Portugese 37
    358 Finnish 278
    437 English (US) 37
    500 International 500

  5. Populate your local EBCDIC location with data from the mainframe.

    If you have XDB Link for the mainframe, there are two ways to do this: using Migrate or using SQL Wizard.

    If you don't have XDB Link for the mainframe, extract the data from the mainframe into a file with DSNTIAUL format, then import the data into your local EBCDIC location using SQL Wizard's import function, citing the appropriate EBCDIC to ANSI code page translation.

XDB EBCDIC databases are stored using the default OEM character set for your machine (typically, code page 437 in the United States, or code page 850 almost everywhere else). The names of database objects such as tables and indexes are also validated using this character set. You can optionally specify that database object-names should be validated using any of the following character sets if, for example, you want to use accented characters in the names:

MS-DOS Code Page
Character Set
437 US Latin 1
850 International Latin 1
857 Turkish
863 French Canada
865 Nordic
860 Portuguese

To specify that an XDB Server should use one of the supported code pages for name validation, you must add two lines to the xdb.ini file in your \mfuser\config folder:


where codepage is the three-digit number of the code page you require.

Where a location has been created as an EBCDIC location (for example, the default location, MAINTAIN), data is automatically converted to EBCDIC before it is passed to the client application. Conversion between ASCII data on the PC and EBCDIC data on the mainframe is handled by the DB2 Link gateway if you are using the Migrate utility. If you are using the data import and export facilities in SQL Wizard, you can control the conversion used by choosing the DSNTIAUL data format and selecting an appropriate code page conversion. See the section Import/Export NLS Considerations for more information.

38.11 Using Existing XDB Data

If you already use XDB databases, you might be able to access them using SQL Option for DB2, providing that the locations in which they were created are compatible. For further information, see the chapter Migrating from XDB in your Migration Cookbook.

38.12 Tips

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

PreviousDefining Segment Layouts for IMS Databases