Chapter 2: Product Specifications

DB2 Universal Database is basically compatible with DB2 for z/OS, with some exceptions in DML syntax and functions supported and certain DDL statements that are hardware/operating system specific. Host Compatibility Option tries to address some of the main areas of incompatibility where possible by converting DB2 for z/OS SQL statements to supported DB2 UDB syntax.

Additional compatibility is possible when using DB2 for z/OS database servers via the DB2 Connect product.

For more information on the differences in syntax supported by the various versions of DB2, see IBM's SQL Reference for Cross-Platform Development.

HCO Functions

Host Compatibility Option provides the following functions:

Support of programs checked with CHARSET(EBCDIC)
Support of DB2 for z/OS syntax DDL statements in programs and via a batch utility
SQLCODE map function to translate SQLCODEs automatically from DB2 Universal Database value to a new, designated value
A utility to do DCLGENs to produce INCLUDE COBOL copybooks of tables
A DSNTIAR subroutine to translate SQLCODEs into expanded SQL error messages
Quotes can be used as literals within SQL statements
Dashes can be used within cursor definitions
Support of the syntax
```
SET host-variable = SPECIAL REGISTER
```
Support of DECLARE TABLE statements
Support of host variables with data structures (COBOL level numbers 01 through 48)
Ability to use COMP data items for integer and small integer instead of the INTEL COMP-5 format
Removal of the need to identify all host variables in Working-Storage via BEGIN DECLARE SECTION and END DECLARE SECTION statements
Configurable CONCAT character ( | )
Support of the NOT OPERATOR (¬) within queries
Insertion of missing colon in fields when the SQL statement parameter must be a host variable
Support of NOFOR compile option which allows updateable cursors to be defined without using the FOR UPDATE clause
Support for alternate special register names, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP

DDL DB2 for z/OS Syntax Supported

As stated previously, the Host Compatibility Option pre-processor and DDL processor are designed to handle DDL statements that use DB2 for z/OS syntax. Functions can be processed in one of the following ways:

With no changes
For a list of these functions see the section Functions Processed Unchanged
Changed to comments as their syntax is not supported under DB2 Universal Database
For a list of these functions see the section Functions Changed to Comments
With only slight modifications to remove unsupported options
For a list of these functions see the section Functions That May Be Modified

Sometimes it is better to perform the function manually.

Functions Processed Unchanged

The following is a list of functions that are not modified or changed since they are supported by DB2 Universal Database V7.2 or later:

COMMENT
CREATE ALIAS
CREATE VIEW
CREATE SYNONYM
GRANT
REVOKE
DROP TABLE
DROP INDEX
DROP VIEW
DROP ALIAS
DROP SYNONYM
SET

Functions Changed to Comments

DB2 Universal Database does not use STOGROUP when creating databases and tables. Therefore, this syntax is changed to comments. Some syntax such as TABLESPACE is supported by DB2 Universal Database but the usage is machine specific depending on the type of DB2 server being used so this syntax is also removed. Other functions, such as DROP DATABASE can be done using other DDL Processor options.

The following is a list of functions that are changed to comments:

ALTER TABLESPACE
ALTER INDEX
ALTER STOGROUP
CREATE DATABASE
CREATE STOGROUP
CREATE TABLESPACE
DROP DATABASE
DROP STOGROUP
DROP TABLESPACE
EXPLAIN

Functions That May Be Modified

Host Compatibility Option auto-senses which SQL engine it is running with. In addition, certain options such as FIELDPROC are not supported under DB2 Universal Database. Consequently, all columns defined with this option must be modified. The following is a list of functions that are supported and modifications are made to statements as needed to run under DB2 Universal Database:

ALTER TABLE
CREATE TABLE
CREATE INDEX

For example, the ALTER TABLE statement has the following syntax and options:

ALTER TABLE table-name  
    ADD column-name data-type FIELDPROC
    (program-name or constant)
    ADD column-name data-type NOT NULL WITH DEFAULT VALIDPROC
      program or NULL
    AUDIT NONE or CHANGES or ALL
    PRIMARY KEY ( column-name )
    FOREIGN KEY constraint-name ( column-name )
    REFERENCES table-name ON DELETE
    RESTRICT or CASCADE or SET NULL
    DROP PRIMARY KEY
    DROP FOREIGN KEY constraint-name

The options FIELDPROC, VALIDPROC and AUDIT are not supported by DB2 Universal Database and would be removed from the statement. The rest of the statement is supported unchanged.

Similarly, the CREATE INDEX statement has the following syntax and options:

CREATE unique INDEX index-name
ON table-name
    ( column-name ASC or DESC )
    USING VCAT catalog-name
    USING STOGROUP stogroup-name
    PRIQTY integer SECQTY integer
    ERASE no or yes
    FREEPAGE integer
    PCTFREE integer
    CLUSTER ( PART integer VALUES (constant )
    or using-block or free-block )
    SUBPAGES 1 or 2 or 4 or 8 or 16 
    BUFFERPOOL BP0 or BP1 or BP2
    CLOSE yes or no 
    DSETPASS password

The options USING VCAT, USING STOGROUP, FREEPAGE, PCTFREE, CLUSTER, SUBPAGES, BUFFERPOOL, CLOSE and DSETPASS are not supported under DB2 Universal Database and therefore will be removed from the command that is passed to DB2 Universal Database.

In the CREATE TABLE statement, the syntax and options are as follows:

CREATE TABLE table-name
     ( column-definition data-type FIELDPROC
    program-name or constants
    , column-definition data-type NOT NULL
    , column-definition data-type NOT NULL WITH DEFAULT
    , PRIMARY KEY (column-name)
    , FOREIGN KEY constraint-name ( column-name )
         REFERENCES table-name
         ON DELETE RESTRICT or CASCADE or SET NULL
    LIKE table-name or view-name )
    IN DATABASE database-name
    IN database-name.tablespace-name
    EDITPROC program-name
    VALIDPROC program-name
    AUDIT NONE or CHANGES or ALL

The options FIELDPROC, LIKE, IN DATABASE, EDITPROC and VALIDPROC are not supported by DB2 Universal Database.

DB2 Universal Database Syntax DDL

HCO can also process DDL statements in DB2 Universal Database syntax without modifying the statements. See the chapter DDL Processor for more details on how to do this.

Features Supported by DB2 Universal Database

DB2 Universal Database supports a number of DB2 for z/OS functions, scalars and special registers.

Column Functions and Scalars

DB2 Universal Database supports Date and Time arithmetic and the following Scalar and Column functions:

AVG
COUNT
MAX
MIN
SUM
CHAR
DATE
DAY
DAYS
DECIMAL
FLOAT
INTEGER
HOUR
LENGTH
MICROSECOND
MINUTE
SECOND
SUBSTR
TIME
TIMESTAMP
YEAR
CONCAT or the concatenation operator (||)
VALUE
DIGITS
HEX

Special Registers

DB2 Universal Database supports the following special registers:

USER
CURRENT DATE
CURRENT DEGREE
CURRENT EXPLAIN MODE
CURRENT EXPLAIN SNAPSHOT
CURRENT FUNCTION PATH
CURRENT NODE
CURRENT PATH
CURRENT QUERY OPTIMIZATION
CURRENT SERVER
CURRENT TIME
CURRENT TIMESTAMP
CURRENT TIMEZONE
CURRENT SQLID

See your DB2 Universal Database SQL Reference for details on any additional special registers that may be supported.

Features Unsupported in DB2 Universal Database

DB2 Universal Database is not completely DML-compatible with DB2 for z/OS.

Special Registers

DB2 Universal Database does not support the following special register:

CURRENT LC_TYPE
CURRENT OPTIMIZATION HINT
CURRENT PRECISION
CURRENT PACKAGESET
CURRENT RULES

You can set the current packageset but IBM has not implemented this as a special register.

Other Functions

DB2 Universal Database does not support the following statements:

ALLOCATE CURSOR
ALTER FUNCTION
ALTER PROCEDURE
CREATE AUXILIARY TABLE
CREATE GLOBAL TEMPORARY TABLE
DECLARE
DESCRIBE CURSOR
DESCRIBE INPUT
DESCRIBE PROCEDURE
HOLD LOCATOR
LABEL ON
SET SPECIAL REGISTER where the register is not supported by DB2 UDB

Some SQL syntax especially dealing with stored procedures is supported by DB2 UDB via other languages such as C++ or JAVA but has not been implemented using COBOL.

For additional information about coding and testing stored procedures and supported/not supported features, see the chapter Stored Procederes in your Database Access book..