Testing with IBM® Db2® on z/OS® systems

With the Agent for z/OS® working together with IBM® Debug for z/OS®, you can record SQL statements to Db2® for z/OS® databases from COBOL and PL/I programs that run on z/OS®.

Occasionally, IBM® releases technical preview or beta versions of upcoming test product capabilities. Technical previews or betas are provided to give you a chance to evaluate them, test them, and provide feedback to IBM®.

Note: Disclaimer: You are permitted to use the information only for evaluation purposes and not for use in a production environment. IBM® provides the information without obligation of support and "as is" without warranty of any kind.

The programs can run within CICS®, or as a step within a batch job. You can store the results of these SQL statements in a simulation database, which you can use to virtualize calls from z/OS® programs to Db2®. You can use either Db2® for z/OS®, Db2® for Linux®, UNIX®, and Windows™, or an integrated Derby database as the simulation database. The Agent for z/OS® uses the Profile Service in IBM® z/OS Debugger to select which programs to intercept.

Agent for z/OS® software requirements

The Agent for z/OS® supports these versions of software:
  • z/OS®: Versions 2.1, 2.2, 2.3
  • Db2®: Versions 11.1, 12.1
  • CICS®/TS: Versions 5.2, 5.3, 5.4, 5.5
  • Enterprise COBOL: Versions 4.2, 5.1, 5.2, 6.1, 6.2, 6.3
  • Enterprise PL/I: Versions 4.5, 5.1, 5.2, 5.3
  • In addition, the Agent for z/OS® relies on IBM® z/OS® Debugger to intercept the EXEC SQL statements. IBM® z/OS® Debugger is included with the following products:
    • IBM® Debug for z/OS® Version 14
    • IBM® Developer for z/OS® Enterprise Edition
    • IBM® Application Delivery Foundation for z/OS®
Note: During the beta period, you must obtain a beta version of IBM® z/OS® Debugger from IBM DB2 Virtualization - Rational Integration Tester. You can ask questions, report problems, or provide feedback from the Support tab after you log in to IBM DB2 Virtualization - Rational Integration Tester.

HCL OneTest™ API software requirements

As part of the support for Db2® for z/OS®, HCL OneTest™ API connects directly to Db2® for z/OS®. This connection requires a Db2® Connect license (db2jcc_license_cisuz.jar).

General limitations

  • Only IBM® Data Server Driver for JDBC and SQLJ is supported for use by Rational® Integration Tester and the Agent for z/OS® to connect to Db2®.
  • You can use the JDBC driver when the Oracle compatibility mode of a Db2® instance is turned on. However, the following limitations apply:
    • The following Oracle data types are not supported: BFILE, BINARY_FLOAT, BINARY_DOUBLE, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE.
    • If a stored procedure has a parameter in FLOAT type, Db2® converts it to DOUBLE type. Therefore, you must use DOUBLE type when you set input parameters and register output parameters.
    • A user-defined array type cannot be used as the column type.
    • If HAVING and GROUP BY are used in the same SQL statement, GROUP BY must precede HAVING.
  • DBRM (Database Request Module) data sets for the application must be made available to the Agent for z/OS®.
    • When you create a filter within HCL OneTest™ API, you must specify the DBRM data set name. Alternatively, if you update your batch job JCL to invoke IBM® z/OS® Debugger, you must set the EQA_DBG_DBRM z/OS® environment variable to specify the data set that contains the DBRM data sets.
    • Only DBRM data sets with Entry Statement format 2 as specified in DSNXNBRM are supported.
    • The DBRM data sets must use the same name as the corresponding program name.
  • If the result set returned by a stored procedure is scroll-sensitive and dynamic, the Agent for z/OS® cannot learn the stored procedure call.

SQL statements and data types

You can use the agent to record and stub the following EXEC SQL commands with the following limitations:

EXEC SQL Command Considerations when you use Db2® as the simulation database Considerations when you use the integrated simulation database
ALLOCATE CURSOR
ALTER INDEX
ALTER SEQUENCE
ALTER TABLE
ASSOCIATE RESULTSET
CALL
CLOSE CURSOR
COMMIT
CREATE INDEX
CREATE SEQUENCE
CREATE TABLE CHAR columns with length greater than 254 are not supported.
CREATE VIEW
DELETE
DROP INDEX
DROP SEQUENCE
EXECUTE
EXECUTE IMMEDIATE
FETCH
INSERT
MERGE
OPEN CURSOR
PREPARE
RELEASE SAVEPOINT
ROLLBACK
ROLLBACK SAVEPOINT
SELECT Use of the DISTINCT keyword in the WHERE clause is not supported.
SET
UPDATE
VALUES INTO

You cannot use these SQL statements and behaviors to record, learn, or virtualize:

  • FETCH CURRENT CONTINUE
  • PREVIOUS VALUE FOR clause within SELECT, INSERT, and UPDATE statements
  • ALTER INDEX with REGENERATE or ADD INCLUDE clause
  • ALTER VIEW

The following features are not supported:

  • Learning with UPDATE or DELETE with cursor statement
  • Learning or virtualizing SELECT, INSERT, or UPDATE statements that refer to columns that have data types:
    • TIMESTAMP(0)
    • TIMESTAMP(P)
    • TIMESTAMP(0) WITH TIME ZONE
    • TIMESTAMP(P) WITH TIME ZONE
    • BLOB file reference
    • CLOB file reference
    • DBCLOB file reference
    • XML BLOB file reference
    • XML CLOB file reference
    • XML DBCLOB file reference
    • Result set locator
    • Table locator
    • DBCLOB locator
    • Virtualizing the use of ROWID columns for direct row access

Encoding limitations

The following encoding limitations apply for character-string host variables:
  • CLOB, DBCLOB, XML_CLOB, and XML_DBCLOB host variable data are interpreted as CCSID 37. All the other character string host variable types are interpreted by using CCSID defined in the DECLARE :HV1 VARIABLE CCSID ccsid SQL statement for that variable.
  • If the host variable does not include a DECLARE :HV1 VARIABLE CCSID ccsid SQL statement, it is interpreted by using the CCSID specified by the CODEPAGE compile option. It does not matter whether the application was compiled by using the CCSID0 option (for PL/I applications), the NOSQLCCSID option (for COBOL/PL/I applications), or both.
  • When DBCLOB (Database Character Large Object) file references are interpreted as a single-byte CCSID (Coded Character Set Identifiers) that contains an odd number of characters, a garbage character might be appended at the end. DBCLOB values in PL/I applications might be truncated to half of their original lengths.
  • If you want to create stubs for tables that contain VARGRAPHIC columns, the Db2® MIXED installation parameter must be set to YES.

PL/I limitations

The following limitations apply to PL/I programs:

  • Programs must be compiled with the TEST option
  • Programs compiled with the RENT option are not supported