Chapter 5: DDL Processor

The DDL Processor processes SQL DDL statements using one of the following methods:

Transforming DB2 for z/OS syntax DDL statements to DB2 Universal Database format
Processing the DDL statements unchanged

See the chapter Product Specifications for more information about supported statements and limitations.

Downloading Source DDL

DDL source files on the mainframe should be downloaded and converted to standard ANSI text files. The maximum input length per record accepted by the DDL processor is 80 bytes.

Note: If non-blank characters exist in columns 73 through 80, you must change maximum column to process to be 72 or they are treated as part of the DDL source statement. See the section Command Line Interface for more details.

Downloading Data to Load Tables

There are two tools in Host Compatibility Option to help you load test data exported from tables located on the mainframe:

The Export Data tool
The Import Data tool

See the chapters Importing Data and Exporting Data for more details.

Using the DDL Processor to Load Data

You can also use the DDL processor to load or update data into tables by placing one or more of the following DML statements in the same file as DDL statements:

INSERT
UPDATE
DELETE
COMMIT

This is a convenient way to load test data into tables.

Statements cannot contain any host variables and you cannot use quotes ( " ) around literals. You must use apostrophes ( ' ). You should also issue a COMMIT before you try to insert data into a table if it was created within the same unit of work. For example:

-- Create Available Product table
 CREATE TABLE MF.PRODUCT
  (PROD_ID  CHAR(04)    NOT NULL
  ,PROD_DESC  CHAR(40)    NOT NULL WITH DEFAULT
  ,VERSION  CHAR(6)    NOT NULL WITH DEFAULT
  ,PRIMARY KEY (PROD_ID) )
  IN DSB8G210.CISC0001
 ;
 COMMIT
 ;

 -- Sample data for Product table

 INSERT INTO MF.PRODUCT (PROD_ID ,PROD_DESC ,VERSION )
 VALUES ('SR02' ,'Host Compatibility Option ' ,'2.0.01' )

Command Line Interface

The command line syntax to run the tool is:

mfhco ddl db=databasename file=file [log=logfile]
    [max=colno] [mod=modify]

Put at least one space before each parameter in the command. There must be an equal sign (=) between some parameters and their values. If you omit parameters that have a default value, Host Compatibility Option uses the default value. See the section Examples for specific syntax.

Parameters

ddl	Specifies use of the DDL Processor. This parameter is positional.
databasename	Name of the database that you want to process DDL statements against.
file	The name of the file that contains DDL statements.
logfile	The name of log file to write processing details to. If this parameter is not specified, a logfile with a default logfile name is created using the project name. If you specify this parameter but specify no value for it (blank), this turns logging off.
colno	Column number to process DDL statements to. Must be a number less than or equal to 80.
modify	Set this parameter to "Y" if you want DDL statements converted to DB2 Universal Database format. Set this parameter to "N" if you want to process DDL statements unchanged. If you select this option, you must set the DDL delimiter value in the configuration file.

Example

mfhco ddl db=db2demo file=hcosetup.sql log=ddlproc.log