The equivalent in Mainframe Express of the XDB Database Technology (XDB) add-on for Workbench produced by Micro Focus is called SQL Option for DB2, or just SQL Option. It is an option that adds DB2 support to the IDE. It is based on XDB Database Technology.
This chapter tells you how to use your existing XDB Servers, gateways and database locations with SQL Option. It also tells you how to make Mainframe Express and XDB Database Technology coexist on the same computer.
To use this feature, you need to have installed SQL Option for DB2 when you ran Setup to install Mainframe Express.
SQL Option enables you to access DB2 databases on the mainframe and to create DB2-compatible databases on your workstation. The server supported by SQL Option is still called XDB Server. Mainframe Express supports XDB Database Technology only for emulating DB2 on the mainframe. It does not support Oracle, Sybase and Informix.
This section tells you how to use your existing XDB Servers, gateways and database locations with SQL Option, and how to make Mainframe Express and XDB Database Technology coexist on the same computer.
Mainframe Express now provides full compatibility between the IDE and XDB locations, emulating a complete international EBCDIC solution.
This section tells you how to migrate a location to Mainframe Express 2.0 from an earlier XDB product, that is, Workbench (with its XDB add-on) or Mainframe Express 1.1.
Subsections following this introductory section cover the possible scenarios in moving from Workbench. A link at the end of this introductory section takes you to equivalent scenarios in moving from Mainframe Express 1.1, in the chapter Migrating from Mainframe Express 1.1.
With this implementation, XDB EBCDIC locations created prior to Mainframe Express 2.0 can be accessed by Mainframe Express, if you follow the steps shown below. We strongly recommend that you alter these locations (using the alter location command) to be fully consistent with the new environment, or that you simply create a new-style EBCDIC location, populating the tables by following the steps shown in the chapter SQL Option for DB2 in your User's Guide.
ASCII (OEM) /ANSI locations need not be changed, and can be accessed from Mainframe Express, if you follow the steps shown below.
The subsections below tell you how to access existing locations from Mainframe Express. Before reading them, please note:
After making the changes described below to enable remote access, you can use Mainframe Express's SQL Wizard, Migrate and COBOL applications to access the remote location. You can access the location simultaneously from both Mainframe Express 2.0 and your earlier XDB system (Workbench or Mainframe Express 1.1).
After making the changes described below to place a location in the Mainframe Express environment, you can access the location within the entire Mainframe Express environment. In addition to running client applications, Mainframe Express's XDB Server manages database activity for this location.
You may decide to keep the location in the old environment initially, accessing it remotely from Mainframe Express, and then move it into Mainframe Express later.
A similar update pack for Workbench is also referred to in this chapter. If you need details of this, please contact your sales representative or Product Support.
If you are migrating from Mainframe Express 1.1, please now go to the sections following the section Migrating a Database Location in the chapter Migrating from Mainframe Express 1.1, instead of the subsections below.
These locations are created if you issue a create location command that does not specify a sort sequence of EBCDIC or USER. To access an ASCII (OEM) /ANSI location from Mainframe Express:
oemansiconv=0
to the [Client] section of the xdb.ini file:
Make sure that Mainframe Express's XDB Server is down, and the location's XDB server is up.
When you insert the SYSLOCALS and SYSLOCATIONS records, use the corresponding TUTORIAL record as your guide for field values - TUTORIAL is an example of the record contents for an ASCII (OEM)/ANSI location.
Over the years, XDB technology has supported three different types of EBCDIC locations in the Workbench environment. We will refer to these locations as follows:
Default EBCDIC | Uses the standard codeset translation provided by Micro Focus in the COBOL environment. No special EBCDIC/ASCII translations are used in this environment, and the XDB location is the result of the create location sort sequence ebcdic command. This is generally the case for all installations in the US. |
Pre-CODESET INTL EBCDIC | International sites who created their own EBCDIC/ASCII translation, either via CODESET or some other means. Typically, these were translations which used OEM codepage 850 as the ASCII component. The XDB COBOL precompiler pre-dates version 6.0.48, and the XDB location is the result of the standard create location sort sequence ebcdic command. |
CODESET INTL EBCDIC | Identical to pre-CODESET, but uses the XDB COBOL precompiler version 6.0.48 or higher. It may use user-defined XDB locations or the standard XDB EBCDIC location. |
SQL Option provides a utility program called codepage. Run this program in the Mainframe Express Command Prompt to find out what your OEM and ANSI codepages are. You will need this information later.
To access this type of location from Mainframe Express:
oemcp=437
to the [Client] section of the xdb.ini
file.
Make sure that Mainframe Express's XDB Server is down, and the location's XDB server is up.
When you insert the SYSLOCALS and SYSLOCATIONS records, use the corresponding MAINTAIN record as your guide for field values - MAINTAIN is an example of the record contents for a default EBCDIC location.
When you stop using Workbench, you will want to change this old-style location to the Mainframe Express format. To convert:
alter location zzzzz to sort sequence xxxxx
ebcdic
where zzzzz
is the name of the location and
xxxxx
is one of:
DUTCH | FRENCH |
SPANISH | ITALIAN |
UK | DANISH |
SWEDISH | GERMAN |
PORTUGUESE | US |
TURKISH |
Your location will then be fully compatible with Mainframe Express, and will use a standard EBCDIC collating sequence. It can then be accessed by all versions of Mainframe Express. For the location to be accessed by Mainframe Express 1.1, you must have installed the SQL Option FixPack and selected to install the internationalization fixes.
Because your Workbench environment has a non-standard EBCDIC collating sequence, we recommend that Workbench applications do not use this location after it is altered.
To access this type of location from Mainframe Express:
oemcp=nnnn
to the [Client] section
of the xdb.ini file, getting nnnn
from the
following table: Workbench EBCDIC Setting | OEMCP Setting |
---|---|
DUTCH, US, PORTUGUESE | 4133 |
FRENCH | 4393 |
SPANISH | 4380 |
ITALIAN | 4376 |
UK | 4381 |
DANISH, NORWEGIAN | 4373 |
SWEDISH, FINNISH | 4374 |
GERMAN, AUSTRIAN | 4369 |
INTERNATIONAL | 4596 |
For example, for German EBCDIC:
oemcp=4369
Make sure that Mainframe Express' XDB Server is down, and the location's XDB server is up.
When you insert the SYSLOCALS and SYSLOCATIONS records, use the corresponding MAINTAIN record as your guide for field values - MAINTAIN is an example of the record contents for a pre-CODESET INTL EBCDIC location.
When you stop using Workbench, you will want to change this old-style location to the Mainframe Express format. To convert:
alter location zzzzz to codepage yyyy with
sort sequence xxxxx ebcdic
where zzzzz
is the name of the location,
xxxxx
is one of:
DUTCH | FRENCH |
SPANISH | ITALIAN |
UK | DANISH |
SWEDISH | GERMAN |
PORTUGUESE | US |
TURKISH |
and yyyy
is one of:
Workbench EBCDIC Setting | yyyy (if OEM=850) |
yyyy (if OEM=437) |
---|---|---|
DUTCH, US, PORTUGUESE | 8229 | 4133 |
FRENCH | 8489 | 4393 |
SPANISH | 8476 | 4380 |
ITALIAN | 8472 | 4376 |
UK | 8477 | 4381 |
DANISH, NORWEGIAN | 8469 | 4373 |
SWEDISH, FINNISH | 8470 | 4374 |
GERMAN, AUSTRIAN | 8465 | 4369 |
INTERNATIONAL | 8692 | 4596 |
For example, for a location called personnel
in a
Swedish EBCDIC environment (with OEM codepage 850), issue the command:
alter location personnel to codepage 8470 with sort sequence
swedish ebcdic
Your location will then be fully compatible with Mainframe Express, and will use a standard EBCDIC collating sequence. It can then be accessed by all versions of Mainframe Express. For the location to be accessed by Mainframe Express 1.1, you must have installed the SQL Option FixPack and selected to install the internationalization fixes.
If you apply the Workbench update pack referred to in the introduction to this Migrating a Database Location section, your Workbench applications can also access this altered location. We strongly recommend that Workbench applications do not use this location after it is altered, unless you have installed this update.
To access this type of location from Mainframe Express:
Make sure that Mainframe Express' XDB Server is down, and the location's XDB server is up.
When you insert the SYSLOCALS and SYSLOCATIONS records, use the corresponding MAINTAIN record as your guide for field values - MAINTAIN is an example of the record contents for a default EBCDIC location.
If you have a user-defined XDB location, then when you insert the SYSXDB.SYSLOCALS record you must make sure that the field AO_CCSID is your OEM codepage (typically 850) and the field E_CCSID is the appropriate EBCDIC codepage listed below.
EBCDIC | Code Page |
---|---|
DUTCH, US, PORTUGUESE | 37 |
FRENCH | 297 |
SPANISH | 284 |
ITALIAN | 280 |
UK | 285 |
DANISH, NORWEGIAN | 277 |
SWEDISH, FINNISH | 278 |
GERMAN, AUSTRIAN | 273 |
INTERNATIONAL | 500 |
If you have a user-defined location, you do not need to convert your location; these locations are already compatible with the newer Mainframe Express format.
If you have a standard XDB location, you will want to change this old-style location to the newer Mainframe Express format when you stop using Workbench. To convert:
set location zzzzz to codepage yyyy
alter location zzzzz to sort sequence xxxxx ebcdic
where zzzzz
is the name of the location,
yyyy
is your OEM codepage, and xxxxx
is one of:
DUTCH | FRENCH |
SPANISH | ITALIAN |
UK | DANISH |
SWEDISH | GERMAN |
PORTUGUESE | US |
TURKISH |
Your location will then be fully compatible with Mainframe Express, and will use a standard EBCDIC collating sequence. It can then be accessed by all versions of Mainframe Express. For the location to be accessed by Mainframe Express 1.1, you must have installed the SQL Option FixPack and selected to install the internationalization fixes.
If you apply the Workbench update pack referred to in the introduction to this Migrating a Database Location section, your COBOL programs can also access this altered location. We strongly recommend that Workbench applications do not use this location after it is altered, unless you have installed this update.
To make SQL Option run against databases you created using XDB Database Technology, you need to determine the degree of compatibility. You must know the XDB Server technology used to create the location.
We strongly recommend that you back up your existing databases before running SQL Option against them, and that you implement a regular backup program.
SQL Option uses XDB Server 7.0 technology, and is fully compatible with the database format used by Maintain! 1.x, ExpressLane 1.x and 2.0, XDB Workbench 4.0, and XDB Server 5.0. SQL Option can coexist with these product versions, using the same database locations.
SQL Option is one-way compatible with database locations created with XDB Server 3.2 or XDB Server 4.0. If you run SQL Option against a database location created with XDB Server 3.2 or 4.0, SQL Option performs an automatic conversion on the database, rendering it unusable by the earlier product.
Locations created with XDB Server 3.0 are one-way compatible with SQL Option. When you run SQL Option against a database created with Server 3.0, it is automatically converted to the Server 6.0 format. If your location does not contain views, it is then ready to use. However, if the location contains views, you must also recreate them, because the view structure changed between version 3.0 and 6.0. To recreate the view, use your original DDL, or retrieve the view definition from the SYSIBM.SYSVIEWS table.
SQL Option is not compatible with database locations created by versions of XDB Server earlier than 3.0. If your database is an earlier format (the .ddv file contains a date earlier than May 1994), you will need to convert your databases manually, usually by exporting the data to an intermediate file format, and then importing it into SQL Option.
If you want to access databases controlled by a workgroup XDB Server, you need to set that server as your current server. The client software provided by SQL Option will run with XDB Server 5.0 or later, and is compatible with software provided in the following products:
Other XDB Server and XDB Link versions have not been certified. See your administrator for instructions on the protocol and server name to use for your connection.
To specify a workgroup server:
If you want to access existing network databases using server-to-server communication through your personal server, see your system administrator for instructions on how to set up the connection.
You may want to take a location from a previous XDB product, and move it to Mainframe Express. You can leave the location in its current location, or make a copy of it. We suggest making a copy, as this will prevent two different XDB servers from accessing the same location simultaneously.
To move a location from another machine or configuration, and register the location with the System location used by SQL Option:
For example, if your location is currently in a subfolder beneath an earlier product, you might want to make a copy of it beneath the Mainframe Express user configuration folder.
This enables you to insert a record into the table if you are positioned at the end of the record list.
You must use an AuthID with appropriate authority to change system tables.
If you want to access DB2 databases on the mainframe, you should follow the instructions provided by your system administrator.
If you have existing software such as Workbench or Maintain! installed on your machine, you can continue to use it after you install Mainframe Express. Unlike the SQL Option in Mainframe Express 1.1, which used the same win.ini file as Workbench and Maintain!, the SQL Option in Mainframe Express 2.0 uses the registry for its configuration. Therefore you can switch between it and Workbench or Maintain! at will.
If you have added XDB Server to the Startup group on your Start menu, you may want to remove it and run it only when needed.
Remember to fully bring down one product's XDB components before you use the other.
If you want to preserve your existing configuration when moving from XDB products to Mainframe Express, migrate your Client side and Server side environments as follows.
When XDB is installed with Workbench environments, the configuration files are typically placed in the subfolder \xdb. When Mainframe Express is installed, the configuration files for SQL for DB/2 are placed in the subfolder \mfuser\config (you can specify a different name for \mfuser25 while installing, but we will assume you have accepted the default).
To move your existing configuration for Workbench or Mainframe Express 1.1 to Mainframe Express 2.0:
Do not copy xdb.ini
To use existing compatible database locations in place of the System and tutorial locations created during Mainframe Express installation, first determine that the database location is compatible, as described in the section Version Compatibility.
Because the automatic database conversion performed by SQL Option is one-way, we strongly recommend that you back up your database locations before going ahead.
To use the location with SQL Option:
The next time you start the XDB Server it will point to the new System location, and you should have access to all of the database locations that it knows about.
If you have a multi-user server, the following settings may need to be migrated from your current XDB Server to your new XDB Server. Look them up in the Server Configuration utility of your current server and then migrate them into your new server, where required, using the Server Configuration utility of your new server.
The settings are listed according to the keys you press to access them in the Server Configuration utility:
F3 | SQL Engine Options | |
F1 | XDB-SERVER Security | |
F4 | Configure System Minimums/Maximums | |
F2 | Maximum number of users | |
F3 | Lock Table Size (Use the larger of the two values, old or new) | |
F5 | Multi-user configuration | |
F7 | TCP/IP Options |
Maintain! version 1 may have been installed with Workbench, but Maintain! version 2 is released with Mainframe Express. Locations created in Maintain! 1.x provide DB2V4 compatibility; those created in Maintain! 2.0 provide DB2V5 compatibility.
If you need DB2V5 functionality, the locations created in Maintain! 1.x can be migrated to Maintain! 2.0 format (DB2V5) as follows:
alter location location-name to version v51;
where location-name
is the name of the
location to be updated. If you are not using DB2V5 functionality, do
not use this command.
Do not copy tools or utilities provided with SQL Option to an existing Maintain! or ExpressLane installation - they might not be compatible. For example, the Gateway Profile and Bind utilities from SQL Option are known to conflict with the versions supplied with ExpressLane 2.x.
Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.