| Defining Segment Layouts for IMS Databases |
This chapter explains how you can use Mainframe Express to create and maintain DB2 applications on your PC.
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:
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.
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.
The Server Configuration utility enables you to change the configuration of your personal XDB Server. For example, you can change the following:
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.
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:
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.
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.
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 
.
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.
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:
Imports data from delimited (free-format) ASCII files.
Imports data from columnar (fixed-format) ASCII files.
Imports data from dBASE II, III and III+ files. dBASE IV files with the same format as dBASE III files can be imported using the dBASE III option.
Imports data downloaded from IBM's DB2 using IBM's DBMAUI facilities.
Imports data downloaded from IBM's DB2 using IBM's DSNTIAUL facilities.
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:
Exports data to delimited (free-format) ASCII files.
Exports data to columnar (fixed-format) ASCII files.
Exports data to dBASE II and dBASE III files.
Exports data to DBMAUI files. DBMAUI files can be read into DB2 systems.
Exports data to DSNTIAUL files. DSNTIAUL files can be read into DB2 systems.
Exports data to WordPerfect 4.2 and 5.0 secondary merge files.
Generates DDL commands that can be used to re-create a table, insert data into it and create indexes on the table. COMMENT, WHERE and SYNONYM statements can be included.
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.
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:
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.
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.
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.
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:
For configuring the connection between a client application (for example, SQL Wizard or a program that you have developed) and the XDB or DB2 database that it uses.
For specifying where SQL Wizard and other SQL Option files are to be found or written.
For specifying formats for dates, times and numeric fields.
For specifying options for a multi-user XDB Server in a networked environment. For example, you can control isolation levels and autocommit.
For specifying which database objects are shown in the Catalog Browser window.
For specifying default options for working with queries. For example, you can specify whether unqualified table-names can be used and whether Cartesian products are permitted. These options can be overridden for a particular query when you are editing the query.
For specifying options for running queries with SQL Wizard. For example, you can control whether the result is displayed in table or form view and limit the number of rows returned.
For specifying defaults for various SQL operational parameters, including compatibility and sort sequence. For example, you can set DB2 or SQL/DS compatibility and whether to use ASCII or EBCDIC sort sequences. You can also set the escape character and SysAuthID.
For specifying security options. You can switch on client security (required if you want to use an XDB Server that has security switched on), as well as controlling the enforcement criteria for user passwords.
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.
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.
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:
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.
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.
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.
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 |
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:
[SERVER]
XDBCP=codepage
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.
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.
Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Defining Segment Layouts for IMS Databases |