Supported SQL statements for DDL file extraction

The DDL file extraction program supports a subset of the SQL statements that are supported by Db2 for z/OS®.

DDL statements that are submitted for processing by the DDL file extraction program must be in the format that is supported by SPUFI or DSNTEP2:
  • Input must be in columns 1-72.
  • Phrases can span records. For example, column 1 of an input record immediately follows column 72 of the previous record.
  • Comments can be included and are indicated by two consecutive dashes (--).
  • The generated statement terminator was the question mark ( ? ) for releases earlier than Db2 Admin Tool Version 11.1 and is the grave accent ( ` ) for Db2 Admin Tool Version 11.1 and later releases.
Restriction: The DDL reader does not communicate with Db2. Therefore, the DDL reader is unable to get the defaults that are established by the user for table space buffer pool, compression and index buffer pool, and pad index. The defaults that are used are those used before DB2® 9.

The following SQL statements are supported:

  • ALTER DATABASE
  • ALTER FUNCTION
  • ALTER INDEX
  • ALTER PROCEDURE
  • ALTER SEQUENCE
  • ALTER STOGROUP
  • ALTER TABLE
    ALTER TABLE restrictions:
    • The ALTER statement is not supported for auxiliary tables.
    • Constraint names are not compared (and differences not reported) because constraint names can be either explicitly specified or, if they are not explicitly specified, generated by Db2. If the constraint names are generated by Db2, the constraint names could be different between source and target, even if the DDL for the object might be the same for source and target.
    • ALTER TABLE ROTATE PARTITION statements have the following restrictions:
      • The maximum number of ALTER TABLE statements that can be processed to rotate partitions is n-1, where n is the number of partitions.
      • If a rotate operation occurred and new partitions have also been added, the rotate operation is not detected.
      • If a rotate operation occurred and the limit keys were altered, the rotate operation might not be detected.
  • ALTER TABLESPACE
  • COMMENT ON
    COMMENT ON restriction: The COMMENT ON statement is not supported for auxiliary tables.
  • COMMIT
  • CREATE ALIAS
  • CREATE AUX TABLE
  • CREATE DATABASE
  • CREATE DISTINCT TYPE
  • CREATE FUNCTION
  • CREATE INDEX
  • CREATE PROCEDURE
  • CREATE SEQUENCE
  • CREATE STOGROUP
  • CREATE SYNONYM
  • CREATE TABLE
    CREATE TABLE restrictions:
    • Start of changeFor CREATE TABLE LIKE statements, the DDL must also include the definition of the table in the LIKE clause.End of change
    • Constraint names are not compared (and differences not reported) because constraint names can be either explicitly specified or, if they are not explicitly specified, generated by Db2. If the constraint names are generated by Db2, the constraint names could be different between source and target, even if the DDL for the object might be the same for source and target.
  • CREATE TABLESPACE
  • CREATE TRIGGER
  • CREATE VARIABLE

    If a dependent object such as a procedure (native stored procedure), PL/SQL function, trigger, view, column mask, or row permission in the data set references a global variable, the CREATE VARIABLE statement should be included in the DDL data set.

  • CREATE VIEW
  • DROP ALIAS
  • DROP DATABASE
  • DROP DISTINCT TYPE
  • DROP INDEX
  • DROP SEQUENCE
  • DROP SPECIFIC FUNCTION
  • DROP STORED PROCEDURE
  • DROP SYNONYM
  • DROP TABLE
  • DROP TABLESPACE
  • DROP TRIGGER
  • DROP VARIABLE
  • DROP VIEW
  • GRANT collection privileges
  • GRANT database privileges
  • GRANT distinct type or JAR privileges
    GRANT JAR restriction: The GRANT USAGE ON JAR statement is not supported in change management or in Db2 Object Comparison Tool.
  • GRANT function or procedure privileges
  • GRANT package privileges
  • GRANT plan privileges
  • GRANT schema privileges
  • GRANT sequence privileges
  • GRANT system privileges
  • GRANT table or view privileges
  • GRANT use privileges
  • GRANT variables
  • GRANT restriction: For objects that exist on both the source and the target, Db2 Object Comparison Tool compares and reports the authorization differences, but does not propagate the differences from the source to the target. Db2 Object Comparison Tool does not propagate the differences to avoid corrupting the target authorizations. During the apply job, the GRANT statements from the source are ignored and the GRANT statements from the target are read.
  • LABEL ON
    LABEL ON restriction: The LABEL ON statement is not supported for auxiliary tables.
  • RENAME INDEX

    The DDL of the index must be included in the source DDL along with the RENAME INDEX statement.

    RENAME INDEX restriction: Rename of an implicit index is not supported.
  • RENAME TABLE
  • SET CURRENT PATH
  • SET CURRENT SQLID