Skip to main content

Problem:


  • Which databases are supported within Enterprise Server?
  • How do I build the XA switch modules?
  • How do I configure the XA switch modules?
  • What name should be entered for the External XA Resource Manger for Oracle / DB2/ SQL Server?
  • How do I diagnose XA switch module errors when starting Enterprise Server?
  • Starting Enterprise Server, I get a Switch Load module name failed to load error.
  • Starting Enterprise Server, I get a SQLCODE -998 subcode X within the console log.

Resolution:

This article attempts to cover all aspects of database access support within Enterprise Server, under the following sections. We strongly recommend reading the >RDBMS vendor reference material

For details on executing JES-initiated transactions under Enterprise Server, refer to Knowledge Base article #32549.

Documentation

The Enterprise Server Help summarises the difference between application- and container-managed services. See the Configuration and Administration Guide for details, within the section Introduction -> Resource Management .

To use container-managed services, you must have previously configured Enterprise Server to define the resource managers required, which must be XA-compliant. The configuration parameters required by ES are documented in full within the Configuration and Administration Guide, within the section Configuration -> Enterprise Servers -> Resource Managers , or search for Resource Managers in the index.

We strongly advise that you read the appropriate Enterprise Server documentation to get a basic understanding on the types of services, and how to configure Enterprise Server

Supported RDBMSs for Application-managed Services

If your application is deployed as an application-managed service, then it will run as-is today, regardless of how it connects to the database. Note that you must not have a resource manager defined for your target database.

We strongly advise that the initial application module performing EXEC SQL syntax is compiled with the SQL(INIT=PROT) directive if your application uses OpenESQL, DB2(INIT=PROT) if using the DB2 ECM to compile, or the CSTOP option if using Cobsql. Note that the Cobsql CSTOP option is not supported on UNIX platforms.

This effectively installs a CBL_EXIT_PROC to perform an EXEC SQL ROLLBACK in case of abnormal program termination. If this is not done and the application terminates abnormally, it might retain locks on the database requiring Enterprise Server to be recycled.

If your application does not fit into one of the above categories, we recommend that you code the CBL_EXIT_PROC manually.

You must only compile one of the application modules with this directive

Supported RDBMSs for Container-managed Services

If you want your application to participate in an XA transaction, your application needs to be deployed as a container-managed service. For that model, we only support applications precompiled with the DB vendor's precompiler, per the list below.

Certain SQL functionality is not supported within an XA transaction, e.g. CONNECT, DISCONNECT, COMMIT, ROLLBACK; hence you might receive non-zero return codes if such statements are executed.

Under Oracle, any Data Definition Language (DDL) statements, such as CREATE TABLE, perform an implicit COMMIT; hence these statements are also not allowed within a container-managed transaction.

For further information, refer to the Database vendor's documentation, which you can access from the Reference section below.

With the Net Express, Server Express and Studio Enterprise Edition releases, we support the following. Please refer to the Product Readme for the definitive information for your platform.

  • IBM DB2 LUW applications either compiled with the DB2 ECM, or precompiler using IBMs command-line precompiler (db2 prep)
  • Oracle applications either compiled using Cobsql to use Oracle, or using Oracle's Pro*COBOL precompiler
    • Pro*COBOL does not support Connection Pooling.
    • On UNIX, to resolve calls to Oracle entry points, refer to Knowledge Base article #4093
    • Relinking the runtime will have no effect, as unlike outside of Enterprise Server, where you'd use cobrun[_t] to invoke your application -- in turn using $COBDIR/bin/rts32[_t] or $COBDIR/bin/rts64[_t] -- within Enterprise Server, your application is invoked from the CASSI process. Hence, you need to create a callable shared object containing the Oracle support modules, and then compile your Oracle application service with the INITCALL mycsoname compiler directive, in order for it to execute within Enterprise Server.
    • You must ensure that this module is linked with the -z,sys,nounload cob flags, rather than -z , to ensure that the COBOL run-time does not directly unload this shared object from memory. (The OS will do this on process termination).
      A script is available to build this module for you. You can download it from Knowledge Base article #4093.

      Note:

      The callable shared object must reside in a directory on LIBPATH/SHLIB_PATH/LD_LIBRARY_PATH at the time that the Micro Focus Directory Server (mfds) process starts.

  • Net Express and Studio Enterprise Server on Windows additionally provides solutions for use with OpenESQL applications compiled with SQL(DBMAN=ODBC) :
    • Generic one-phase commit switch module for ODBC
      • This module is designed to be used with container-managed applications which have been compiled with the SQL(DBMAN=ODBC) compiler directive.
      • This solution is not supported with applications compiled with SQL(TARGETDB=ORACLEOCI).
      • This solution only supports one-phase commit, so it cannot be used in conjunction with other resource managers; it can only be used itself for a single database connection.
      • If your application utilizes multiple database connections, this solution will not work for you.
      • When using this module, the following SQL operations are not permitted within your deployed application:
        • CONNECT
        • DISCONNECT
        • BEGIN TRANSACTION
        • COMMIT
        • ROLLBACK
      • The Name and ID fields are free-format.
      • The Open String format for this module is defined as:

        DSN=odbc_dsn[,USRPASS=user.password]

        odbc_dsn The ODBC Data Source Name to be used to connect to the data source as defined within the ODBC Administrator Utility
        user User ID required to connect to the data source
        password Password required for User user to connect to the data source

        The USRPASS field is optional, for example in cases where SQL Server authentication is being used.

        Alternatively, the DSN, user ID and password can be coded programmatically within the COBOL source, ESODBCXA.CBL, to ease any security concerns of having the information viewable via a web browser. Search for CUSTOMIZE to see where to apply the changes.

    • SQL Server XA switch module
      • This solution is designed to be used with container-managed applications which have been compiled with SQL(DBMAN=ODBC) to access Microsoft SQL Server.
      • This switch module can be used to connect to one or more SQL Server databases via ODBC Data Source Names (DSNs).
      • When using this module, the following SQL operations are not permitted within your deployed application:
        • CONNECT
        • DISCONNECT
        • BEGIN TRANSACTION
        • COMMIT
        • ROLLBACK
      • This solution provides two-phase commit support utilizing the Microsoft Distributed Transaction Coordinator (MS DTC), and ensures that any database connections made using this switch module will all participate within a single XA transaction. Due to this, MS DTC must be running on both the client machine where the service is deployed, as well as on any machines hosting the SQL Server databases which your application needs to connect to.
      • The Name and ID fields are free-format.
      • The Open String format for this module is defined thus:

        DSN=odbc_dsn[,USRPASS=user.password][,CONNECTNAME=connname][,DTCSERVER=dtcsrvr]

        odbc_dsn The ODBC Data Source Name to be used to connect to the data source as defined within the ODBC Administrator Utility
        USRPASS Optional, for example in cases where trusted SQL Server connections are being used
        user User ID required to connect to the data source
        password Password required for User user to connect to the data source
        connname Specifies the name used by OpenESQL to identify the connection. This parameter must be specified if multiple connections are used.
        dtcsrvr Optional name of the machine hosting the Microsoft Distributed Transaction Coordinator (MS DTC), to be used to create and manage the transaction objects. If not specified, the local machine is used.

        The USRPASS field is optional, for example in cases where trusted SQL Server connections are being used.

        If the CONNECTNAME parameter is specified within the Open String, within the deployed container-managed service, you must then code:

        EXEC SQL SET CONNECTION connname END-EXEC

        where connname is the name specified within the Enterprise Server XA Open String.

        Alternatively, the DSN, user ID and password can be coded programmatically within the COBOL source, ESMSSQL.CBL, to ease any security concerns of having the information viewable via a web browser. Search for CUSTOMIZE to see where to apply the changes.

Refer to the product readme from your Micro Focus documentation for additional information regarding Database support inside and outside of Enterprise Server for the platform in question.

For other Resource Managers, you should refer to the documentation from your RDBMS vendor, for details on how to access the XA switch structure and make that available to the Transaction Manager , Enterprise Server.

Reference

We strongly suggest reading the documentation from the relevant RDBMS vendor to determine any other requirements or limitations imposed by the software being used.

  • Oracle
  • IBM DB2 LUW
    • IBM DB2 documentation portal
    • Administration Guide: Planning which covers the xa_open string
    • Application Development Guide: Programming Client Apps which covers (un)supported functionality
  • IBM Informix
    • Informix product family library . Use the links under Product Documentation on the right hand side of the page.
    • Embedded SQL TP/XA Programmer's Manual covers most XA-related issues. Page 2-5 will help with the creation of an appropriate switch module.
    • You might also need to refer to the SQL Reference for further information.
    • In their release notes, IBM documents:
      • Previous releases of ESQL/COBOL contained the XA libraries. This was done by mistake and was misleading to some customers. This version of ESQL/COBOL no longer contains the XA libraries.
      • ESQL/COBOL does not support XA; ESQL/C does. Some customers have successfully used XA in conjunction with ESQL/C in an environment that includes ESQL/COBOL.
      • However, the success of these environments does not imply that ESQL/COBOL supports XA. If for some reason you rely on ESQL/COBOL distributing the XA libraries, you will be able to get these libraries, as you always have able to, from either the 7.2x ESQL/C product, the 9.x ESQL/C that is part of the Client SDK product, or the 7.2x or 2.x Client SDK/Connect products.

Resource Manager Configuration

In summary, to configure Enterprise Server for use with deployed services which will access RDBMS software, you need to:

  1. Ensure that your database environment is set up before starting the Micro Focus Directory Server.
    • On Windows, this should be done automatically by the DB2 LUW/Oracle installer.
    • On UNIX, you might need to perform this step manually. Refer to the RDBMS vendor's documentation for details of the required environment variables and settings.
  2. Ensure that your database client can communicate to the database server, using the database vendors' tools. For example:
    • IBM DB2 LUW

      Syntax: db2 connect to database_alias user db2_instance_id using db2_instance_password

      Example: db2 connect to SAMPLE user db2inst1 using ibmdb2

    • Oracle

      Syntax: sqlplus userid/password[@Oracle_TNS_Alias]

      Example: sqlplus scott/tiger@oracle

      If this is not working correctly, refer to the Database vendor's documentation for instructions on how to resolve any errors shown.

  3. Create a switch module which will pass the XA switch structure back to Enterprise Server.
    • Windows - we provide COBOL sources for IBM DB2 LUW, Oracle, SQL Server XA and one-phase commit ODBC, along with a build script. These can be found under Install_dir\\BASE\\SOURCE\\ENTERPRISESERVER\\XA.
    • UNIX - the COBOL sources and build script are located under $COBDIR/src/enterpriseserver/xa.
    • Other data sources - please refer to your RDBMS-vendor's documentation for details on how to make the XA structure accessible to the Transaction Manager, Enterprise Server in this case.
  4. Under the Enterprise Server configuration page, either from under the XA Resources tab, or from the free-format configuration text field, you need to define the parameters for each switch module, thus:
    [ESXRM]rmtype=xarmlabel=resource-manager-idrmname=resource-manager-namermmodule=switch-module-namermopenstring=open-stringrmclosestring=close-string

    resource-manager-id A resource manager ID used internally to identify a particular XA configuration. This must be unique within the enterprise server.
    resource-manager-name The name by which the resource manager is known. It should match the name returned in the name field of the xa_switch_t structure. For IBM DB2 LUW, this is DB/2, and for Oracle, Oracle_XA.The value of this field is case sensitive.
    switch-module-name The location of the switch module executable file (.dll/.so/.sl) that contains the entry point that returns the xa_switch_t structure to the Enterprise Server.
    open-string The string that is passed to the resource manager on the xa_open() call.
    • It typically contains at least the database name, and the user ID and password for connecting to the database.
    • The contents of this string are database-vendor specific.
    • For DB2, the expected values for these parameters are documented in IBM's DB2 Administration Guide; for additional information see the Application Programmer's Guide.
    • For Oracle, see the Oracle Application Developer's Guide - Fundamentals book.
    • Where the resource manager supports dynamic registration, and the database vendor requires the name of the module that provides the ax_reg function, you should specify casaxlib. You must specify the file extension to casaxlib, e.g. .dll, .so or .sl, depending on your platform.
    close-string The string that is passed to the resource manager on the xa_close() call. Refer to your RBDMS vendor's documentation to see if this field needs to be populated.

    Oracle for Windows example:
    [ESXRM]>rmtype=xarmlabel=XAORA10rmname=Oracle_XArmmodule=c:\\netexpress\\base\\source\\enterpriseserver\\xa\\esora10xa.dllrmopenstring=Oracle_XA SesTm=100 SqlNet=connect_string Acc=P/OracleUserID/OraclePasswdrmclosestring=
    Oracle for UNIX example:
    [ESXRM]rmtype=xarmlabel=XAORA10rmname=Oracle_XArmmodule=/opt/microfocus/cobol/src/enterpriseserver/xa/ESORAXA.sormopenstring=Oracle_XA SesTm=100 SqlNet=connect_string Acc=P/OracleUserID/OraclePasswdrmclosestring=

    DB2 LUW for Windows example:

    [ESXRM]rmtype=xarmlabel=XADB2rmname=DB/2rmmodule=c:\\netexpress\\base\\source\\enterpriseserver\\xa\\esdb2xa.dllrmopenstring=db=sample,uid=Db2InstanceId, pwd=Db2InstancePasswd, axlib=casaxlib.dllrmclosestring=

    DB2 LUW for UNIX example:

    [ESXRM]rmtype=xarmlabel=XADB2rmname=DB/2rmmodule=/opt/microfocus/cobol/src/enterpriseserver/xa/ESDB2XA.sormopenstring=db=sample,uid=Db2InstanceId, pwd=Db2InstancePasswd, axlib=casaxlib.sormclosestring=
    Note:

    You must specify the file extension for the switch module. For DB2 LUW you must also specify the casaxlib library. File extensions are .dll for Win32, .sl for HP-UX PA-RISC, and .so for all other UNIX platforms supported.

  5. Start Enterprise Server.
    • On HP-UX platforms only (PA-RISC and Itanium), you must set the LD_PRELOAD environment variable before either starting mfds or invoking casstart. For more information, see Knowledge Base article #4093.
    • On starting your server region, within the ES Console, you should see a message along the lines of the following:
      23940 ESDEMO CASXO0015I a XA interface initialized successfully 10:25:29
      which indicates that the switch module loaded correctly. If, however you get a message along the lines of the following: 
      23920 ESDEMO CASXO0002S Switch Load Module for resource xa failed to load

      you might have specified an incorrect name in the rmmodule field.

    • If you see:
      23940 ESDEMO CASXO0001S XA Resource Manager initialization error. Resource xa open failed, reason -00003 10:29:56

      then you should verify that the open string passed in is correct.

    • If you see:
      23940 ESDEMO CASXO0002S Switch Load Module for resource DB2 failed to load 14:11:43

      then it implies that the environment under which MFDS is running might not be configured correctly.

    • UNIX platforms - ensure that your database environment has been set up before invoking mfds or casstart. On HP-UX, per the comment above, ensure that you have set the LD_PRELOAD environment variable.
    • Windows Terminal Services/Citrix platforms - by default, the Micro Focus Directory Server service runs under the Local System Account. On Citrix-type systems, this ID doesn't have the same permissions as a system account on a regular PC, meaning that it will not inherit the Database environment as required. To resolve this, change the startup settings for the Micro Focus Directory Server service to run under an account with Administrator (or Power User) privileges.
      Note: If your machine logs into a domain, the account you specify must be a domain account rather than a local account, e.g. MYDOMAIN\\myid, rather than myid.
    • An error could occur if you have just installed RDBMS software. You might need to reboot your machine to ensure that the RDBMS environment becomes available to Enterprise Server.
    • If you see:
      060214 15284488 2704 JCLDEMO CASXO0003S Resource Manager for resource ORAT Transaction start failed: reason -00006

      and you are working with Oracle and JCL on Windows platforms, then you should:

      • Update the XA switch module source ESORAXA.CBL to use dynamic registration rather than static.
      • Edit that source module, and replace any occurrences of 'xaosw' with 'xaoswd'. Then, rebuild the switch module, and re-start your Enterprise Server.

    • If the application needs to explicitly COMMIT or ROLLBACK, do not specify the ... WORK RELEASE clause, as this will explicitly disconnect from the database. Simply use EXEC SQL COMMIT or EXEC SQL ROLLBACK.
    • If either within the Enterprise Server console log, or on executing a deployed DB2 service, you receive a SQLCODE of -998 with a subcode, you should:
      • Refer to the DB2 first failure log, db2diag.log, to obtain the subcode
      • Refer to the IBM documentation for the meaning of this error, and on how to resolve it.

Deployment

When deploying to a production (standalone) Enterprise Server, please be aware that the Micro Focus Server product does not include COBOL compiler support. You must therefore build any appropriate XA switch modules as part of the Development product for your target Operating System, and ensure that the resultant modules are available to your production Server (via the Module Name field within the XA configuration dialog).

When deploying a DB2 LUW application using the IMTK, you might need to re-bind your application to the database after using the IMTK. By not doing this, you might encounter a SQLCODE of -818 when executing EXEC SQL statements within your deployed application.

Remote Deployment

If deploying your application to an Enterprise Server hosted on another machine, you must ensure that:

  • The machine hosting the Enterprise Server has the necessary DB client software installed, and configured correctly (outside of Enterprise Server).
  • If you are developing your application on a different machine to the one hosting the ES, you must also have the same version of the DB client installed on your development machine.
  • If you want to deploy using the Interface Mapping Toolkit (IMTK), in addition to the above, the development and target platforms must be identical, as output from RDBMS precompilers is platform-specific.
  • If the development and target platforms are not the same, you must deploy manually, and compile the application on the target machine. Refer to the documentation for how to deploy an application manually.
  • If using IBM DB2 LUW, you will need to re-bind your application against the target database server.
  • You deploy your service source files, in addition to the built modules. This applies to any remote deployment of services.

Supported SQL syntax

COMMIT/ROLLBACK statements must not be coded within applications deployed as container-managed services, as it is the responsibility of the Transaction Manager -- typically Enterprise Server -- to handle Transaction control. You will most likely encounter SQLCODE/SQLSTATE errors if such EXEC SQL statements are left in your program.

You should refer to the Database Vendors' documentation to determine what syntax is (un)supported. If using either the SQL Server XA or one-phase Commit ODBC switch modules, please also refer to the Open String definitions above.

Diagnostics

In addition to errors which might be reported within the Enterprise Server Console Log -- see the Configuration section above -- you might also encounter errors from the database while executing your application.

Referring to SQLCODE/SQLSTATE/SQLERRMC within your application should help determine the problem, perhaps also the solution. You should refer to the appropriate Database Vendor's Error Message manual for descriptions of the Error codes (and perhaps potential causes/solutions).

The XA specification, which includes details of the error codes, can be located here (Adobe Acrobat Reader required).

  • IBM DB2 LUW
    • You might also find error messages stored in either the system message log, or perhaps the DB2 first failure log, db2diag.log. On Win32, this file can be located under DB2-install-dir\\DB2. On UNIX, the file can be located under DB2-instance-ID/sqllib/db2dump.
    • Typically, errors returned are SQLCODE -998 (Transaction error), along with a reason code and subcode. If you see this, or other SQLCODE errors within db2diag.log, please refer to the DB2 Error Messages manual for these codes, their meanings, and often how to resolve them.
    • On AIX platforms, you can additionally configure DB2 to write to the AIX syslog. See TX Series documentation for details on how to enable this functionality.
    • You can also enable DB2 CLI tracing, useful for both CLI and ODBC applications. See the IBM Information Center for details on how to enable and view the CLI trace files (search for "CLI Trace Files").
  • Oracle
    • You can enable XA tracing by specifying an option within the Open String (as defined to Enterprise Server). Refer to the Oracle documentation (Application Developer's Guide - Fundamentals) for details of the settings, LogDir and DbgFl.
    • The name of the trace file is xa_db_nameDate.trc, where db_name is the database name specified in the XA Open String, and Date is the date when the information was logged. If a database name was not specified within the Open String, Oracle defaults to NULL.
    • If you did not specify LogDir within the Open String, Oracle attempts to create the trace file under $ORACLE_HOME/rdbms/log. Otherwise, it will be created within the directory specified by LogDir.
  • Generic one-phase commit switch module/SQL Server XA switch module
    • Both of these switch modules have been enabled to use the Consolidated Tracing Facility, to aid in diagnosing any errors returned during operation.

Configuring and enabling CTF tracing:

  1. A sample CTF configuration file, ctf.cfg is provided with the sources under the BASE\\SOURCE\\ENTERPRISESERVER\\XA directory on Windows, and $COBDIR/src/enterpriseserver/xa on UNIX. Change the Location parameter on line 6 to an appropriate directory on your system to store any trace files created.
  2. Set the MFTRACE_CONFIG environment variable to the full pathname to the configuration file. For example, C:\\Program Files\\Micro Focus\\Net Express 5.0\\BASE\\SOURCE\\ENTERPRISESERVER\\XA\\ctf.cfg. Be sure to set this environment variable globally, under Control Panel > System > Advanced > Environment variables. /LI>
  3. Restart Enterprise Server.

Once Enterprise Server is restarted, the SQLCODE, SQLSTATE and any associated SQL error messages are written to the trace file to aid with problem diagnosis. The trace file name is:

cassi.textfile.processid.log

where processid is the Windows process ID under which the Enterprise Server Server Execution Process (SEP) is running.

The component properties of these CTF entries that you need to be aware of are:

Property Description
mftrace.comp.mf.esodbcxa#all Enable diagnostics for one-phase commit module
mftrace.comp.mf.esmssql#all Enable diagnostics for SQL Server XA switch module
mftrace.comp.mf.esora#all Enable diagnostics for Oracle XA switch module
mftrace.comp.mf.esdb2#all Enable diagnostics for DB/2 XA switch module

There are lower-level properties, but you shouldn't need to reference them; you might need to either enable or disable the diagnostics.

The diagnostic assistance for the Oracle and DB/2 switch modules is minimal. They simply call a single third-party function. If that call fails, the issue is most likely environmental, i.e. the third party modules cannot be located on PATH (Windows) or LD_LIBRARY_PATH/LIBPATH/SHLIB_PATH (UNIX).

Invoking the mfsupport utility on UNIX from the session where mfds was started, or checking the system PATH on Windows will help isolate such an issue.

Old KB# 3157

#EnterpriseServer
#StudioEnterpriseEdition
#COBOL
#Enterprise
#ServerExpress
#Server
#netexpress
Terms & ConditionsAccessibility statement