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 LUW. 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 LUW:

• 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 LUW 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 LUW and therefore will be removed from the command that is passed to DB2 LUW.

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 LUW.