Chapter 10: DDL Processor

The DDL Processor processes SQL DDL statements by:

• Transforming DB2 for OS/390 syntax DDL statements to DB2 Universal Database format

  or

• Processing the DDL statements unchanged

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

10.1 DDL Processor Tool

The DDL Processor tool (see Figure 10-1) appears when you click HCO > DDL Processor on the Tools menu.

Figure 10-1: DDL Processor Window

The following elements make up the DDL Processor window:

• Menu bar
• Toolbar
• Status line
• Drop-down list of available databases to use with DDL statements
• Entry fields for DDL and LOG filenames

10.2 The Toolbar

The following list shows the buttons in the toolbar and the functions that they invoke:

Button	Function
	Select DDL file to be processed. You see a window (see Figure 10-2) where you can select a DDL file.
	Process DDL statements
	Display the online HCO User's Guide, a hypertext file that describes how the DDL Processor tool works.

10.3 The Menu Bar

The menu bar is an alternative way of invoking DDL Processor functions. It consists of three menus: File, Options and Help.

10.3.1 The File Menu

To select a DDL file, process the DDL file or exit from the tool, you can use the File menu. You can also display this menu by right-clicking with the mouse on the DDL Processor window (see Figure 10-1). The following list shows the menu options and the functions that they perform:

10.3.2 The Options Menu

You can override options set in the configuration file by selecting the Options menu. These options are pre-defined. See the chapter Setting INI Options for more details. The settings depend on your current environment and what you have set as defaults. The following list shows the menu options and the functions that they perform:

10.4 Processing DDL Statements

To process DDL statements:

1. From the drop-down list in Database select the database to run the DDL statements against
2. Specify in DDL File the name of the file that contains the DDL statements to be processed. See the section Selecting DDL Files for more details.
3. Click Run or select Process DDL on the File menu.

When the DDL file has been processed, the log file is displayed (see Figure 10-3) if the Display Log flag is set on.

10.5 Selecting DDL Files

There are two ways to provide DDL filenames. You can:

• Type the name of the file (including drive/folder if different from the current drive)
• Click Folder or select Select DDL on the File menu to see a list of files from which to select (see Figure 10-2)

The default list is built using the current Mainframe Express project \data folder and .ddl extension. See the chapter Setting INI Options for more details of setting defaults.

Select the file that you want to process from the left list box and then click on Open. You then see the filename in DDL File.

Figure 10-2: Select DDL File

10.6 Log File Entry Field

You can also specify the name of the log file where the output is to be written. The log file contains the original statement, the statement that it is converted to (if needed) and the return code from processing the statement. The default name for the log file is set in the configuration file. See the chapter Setting INI Options for more details. You overwrite the log file if you use the same filename. If you have Display Log checked, the log is displayed in the HCO Log tab of the IDE; Figure 10-3 shows the processing summary. You also need to have Output selected on the View menu of the IDE for this to be displayed.

Figure 10-3: HCO Log

10.7 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 chapter Setting INI Options for more details.

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

10.9 Using the DDL Processor to Load Data

You can also use the DDL processor to load or update data into tables. You can place 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 aCOMMIT 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' )

10.10 Command Line Interface

The DDL Processor tool can also be run as a batch function. The command line syntax to run the tool is:

MFECL /HCODDL projectname FILE=file LOG=logfile
    MAX=colno MOD=modify

At least one space must occur before each parameter in the command. There must be an "=" between some parameters and their values. See examples for specific syntax.

10.10.1 Required Parameters

10.10.2 Optional Parameters

You can omit optional parameters. Host Compatibility Option uses the default value set in configuration file.

10.11 Examples

Example 1

Example 1 processes DDL statements found in c:\ddl\sql.ddl using DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects. Log file, maximum column to process and DDL conversion options are defaults set in the configuration file.

MFECL /HCODDL D:\MFUSER\PROJECTS\HCODEMO.MVP 
    FILE=C:\DDL\SQL.DDL

Example 2

In this example, the DDL statements are found in c:\ddl\sql.ddl using DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects. The log file to write to is c:\ddl.log. Maximum column is 72. Notice with the new syntax that parameters can be in any order except for the first parameter.

MFECL /HCODDL D:\MFUSER\PROJECTS\HCODEMO.MVP 
    LOG=C:\DDL.LOG FILE=C:\DDL\SQL.DDL MAX=72

Example 3

In this example, the DDL statements are found in c:\ddl\sql.ddl using DB2DEMO database specified in Mainframe Express project hcodemo located in d:\mfuser\projects. DDL statements are not converted.

MFECL /HCODDL D:\MFUSER\PROJECTS\HCODEMO.MVP 
    FILE=C:\DDL\SQL.DDL MOD=N

10.12 Executing DDL from the IDE

If you add DDL files to your Mainframe Express project, you can select and execute DDL directly from the IDE via the Tools, HCO menu.

Figure 10-4: Executing DDL from the IDE

All processing options default to whatever is setup in the configuration file.

Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.