DB2 Universal Database is basically compatible with DB2 for OS/390, 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 OS/390 SQL statements to supported DB2 UDB syntax.
Additional compatibility is possible when using DB2 for OS/390 database servers via the DB2 Connect product.
Host Compatibility Option provides the following functions:
INCLUDE COBOLcopybooks of tables
SET host variable = SPECIAL REGISTERsyntax
COMPdata items for integer and small integer instead of the INTEL
BEGIN DECLARE SECTIONand
END DECLARE SECTIONstatements
CONCATcharacter ( | )
NOT OPERATOR(¬) within queries
As stated previously, the Host Compatibility Option pre-processor and DDL processor are designed to handle DDL statements that use DB2 for OS/390 syntax. Functions can be processed in one of the following ways:
For a list of these functions see the section Functions Processed Unchanged
For a list of these functions see the section Functions Changed to Comments
For a list of these functions see the section Functions That May Be Modified
Sometimes it is better to perform the function manually.
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:
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:
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:
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
are not supported by DB2 Universal Database and would be removed from the
statement. The rest of the statement is supported unchanged.
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
DSETPASS are not supported under DB2 Universal Database and
therefore will be removed from the command that is passed to DB2 Universal
CREATE TABLE statement, the syntax and options are
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
FIELDPROC, LIKE, IN DATABASE, EDITPROC and
VALIDPROC are not supported by DB2 Universal Database.
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.
DB2 Universal Database supports a number of DB2 for OS/390 functions, scalars and special registers.
DB2 Universal Database supports Date and Time arithmetic and the following Scalar and Column functions:
CONCATor the concatenation operator (||)
DB2 Universal Database supports the following special registers:
CURRENT EXPLAIN MODE
CURRENT EXPLAIN SNAPSHOT
CURRENT FUNCTION PATH
CURRENT QUERY OPTIMIZATION
See your DB2 Universal Database SQL Reference for details on any additional special registers that may be supported.
DB2 Universal Database is not completely DML compatible with DB2 for OS/390 v6.1.
DB2 Universal Database does not support the following special register:
CURRENT OPTIMIZATION HINT
You can set the current packageset but IBM has not implemented this as a special register.
DB2 Universal Database does not support the following statements:
CREATE AUXILIARY TABLE
CREATE GLOBAL TEMPORARY TABLE
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 Stored Procedures.
Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.