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:
CHARSET(EBCDIC)
INCLUDE COBOL
copybooks of tables SET host variable = SPECIAL REGISTER
syntax
DECLARE TABLE
statements COMP
data items for integer and small
integer instead of the INTEL COMP-5
format BEGIN DECLARE SECTION
and END DECLARE SECTION
statements CONCAT
character ( | ) 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:
COMMENT
CREATE ALIAS
CREATE VIEW
CREATE SYNONYM
GRANT
REVOKE
DROP TABLE
DROP INDEX
DROP VIEW
DROP ALIAS
DROP SYNONYM
SET
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
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.
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:
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
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.
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 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.
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 UDBSome 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.