| DDL Processor | Exporting Data | |
Host Compatibility Option provides a tool to generate DDL for existing SQL tables or copybooks for tables in COBOL, PL/I or C syntax.
The Generate Copybook tool (see Figure 10-1) appears when you select HCO > DCLGEN, on the IDE Tools menu.
Figure 10-1: Generate Copybook Tool
The following elements make up the Generate Copybook window:
The following list shows the buttons in the toolbar and the functions that they invoke:
| Button |
Function |
|---|---|
 |
Connect to a database selected from the drop-down list |
 |
Generate a copybook for table selected from the list box |
 |
Display the online HCO User's Guide, a hypertext file that describes how the Generate Copybook tool works. |
The menu bar is an alternate way of invoking Generate Copybook functions. The menu bar is composed of three menus: Database, Options and Help.
The Database menu is used to connect to a database, generate a copybook or to exit the tool. You can also display this menu by right-clicking in the Generate Copybook window (see Figure 10-3).
| Menu
Option |
Function |
|---|---|
| Generate copybook | Generate a copybook for a table selected from the list box |
| Generate DDL | Generate DDL for a table selected from the list box |
| Connect | Connect to a database selected from the drop-down list |
| Restrict list | Restrict tables included in the list box when connecting to a database. See the section Restricting Tables in List for more details. |
| Disconnect | Disconnect from the database you are currently connected to |
| Exit | Exit Generate Copybook tool |
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.
| Menu
Options |
Function |
|---|---|
| Display copybook | Display the copybook after generation |
| Type copybook | Generate copybook with one of the following syntaxes. Select from COBOL, PL/I or C. |
| Prefix - Table name | Generate host variables using table and column-names |
| Prefix - User value | Generate host variables using a prefix supplied by you. You can choose column name or number to make up rest of host variable. |
| Prefix - no prefix | Generate host variables using just column names |
| 4K Long varchar | Generate long varchar host variables as picture x(4096) rather than picture x(32698) |
| Enclose names | Generate table and column names with quotes around them |
| Miscellaneous | Change default copybook extension, suffix for null indicators and structure names |
| Include Schema in DECLARE | Include the schema name in the DECLARE TABLE definition |
The Generate Copybook tool automatically connects you to the database specified in the Mainframe Express project unless you have disabled this feature in the configuration file. You can still connect to other databases by:
The list box (see Figure 10-1) displays a list of tables to select from if the connection was successful.
Tip:
You can automatically connect to a database by using the auto-connect option. In configuration file, set:
| Default Setting |
Value |
|---|---|
| AUTO CONNECT TO DATABASE | Yes |
| DATABASE NAME | Name of database to connect to. Uses the database-name specified in your Mainframe Express project to override the value set in INI file. |
See the chapter Setting INI Options for more details on setting defaults.
The list box in Generate Copybook tool has a limit of 500 tables to include when you connect to a database. If the database contains a large number of tables or if you only want to include a specific set of tables, you can restrict the list. To do this:
The Restrict List window then appears (see Figure 10-2).
Figure 10-2: Restrict List Window
You can restrict the list using all or part of a schema name, all or part of a table name or both. When you have made your entries, click Restrict. In the example above, the list is restricted to only those tables that begin with DEMO.
Tip:
You can restrict the list and automatically connect to a database by using the auto-connect option. In the configuration file, set:
| Default Setting
|
Value |
|---|---|
| AUTO CONNECT TO DATABASE | Yes |
| DATABASE NAME | Name of the database to which to connect. Uses the database-name specified in your Mainframe Express project to override the value set in INI file. |
| SCHEMA | All or part of a schema name to which you want to restrict the list. For example, a value of "DEMO%" would restrict the list to only those tables beginning with DEMO. |
| TABLE NAME | All or part of a table name to which you want to restrict the list. For example, a value of "A%SD" would restrict list to those tables beginning with "A" and having an "SD" somewhere within name. |
You can still turn off auto-connect feature by updating the configuration file to disable this feature.
See the chapter Setting INI Options for more details on setting defaults.
To generate a copybook for a table:
Figure 10-3: Generate Copybook Selection
You see the Save DCLGEN To window (see Figure 10-4). This window is different on different operating systems but the function is the same.
Figure 10-4: Save DCLGEN To window
A default filename is built based on the table selected. You can change the filename to whatever you prefer or accept the default. When ready, click Save. The copybook is then generated.
Figure 10-5: HCO Log Tab
You can display the copybook immediately after it is generated without leaving the tool. The copybook is displayed in the HCO Log tab of the IDE (see Figure 10-5). You need to have Output selected on the View menu of the IDE for this to be displayed. This enables you to try various configuration options to determine which default values are appropriate for your company.
To generate DDL for a table:
Example 1
Example 1 creates the file ITEMS.CPY from table DEMO.ITEMS in database DB2DEMO and prefixes all COBOL field names with the table name.
The DECLARE TABLE part of copybook file would look something like this:
****************************************************************** * MFHCODCL OPTIONS: * DATABASE : DB2DEMO * SCHEMA : DEMO * PREFIX : TABLE NAME ****************************************************************** EXEC SQL DECLARE DEMO.ITEMS TABLE ( ORD_NO INTEGER NOT NULL ,ITEM_NO SMALLINT NOT NULL ,PROD_ID CHAR(4) ,QTY_ORDERED SMALLINT NOT NULL WITH DEFAULT ,QTY_SHIPPED SMALLINT NOT NULL WITH DEFAULT ,AMT_EACH DECIMAL(7, 2) NOT NULL WITH DEFAULT ,DATE_ENTERED TIMESTAMP NOT NULL WITH DEFAULT ,DATE_SHIPPED DATE ,ITEM_NOTES VARCHAR(560) ) END-EXEC.
The COBOL DECLARATION part of copybook file would look something like this:
****************************************************************** * COBOL DECLARATION FOR TABLE DEMO.ITEMS ****************************************************************** 01 DCLITEMS. 10 ITEMS-ORD-NO PIC S9(09) COMP. 10 ITEMS-ITEM-NO PIC S9(04) COMP. 10 ITEMS-PROD-ID PIC X(4). 10 ITEMS-QTY-ORDERED PIC S9(04) COMP. 10 ITEMS-QTY-SHIPPED PIC S9(04) COMP. 10 ITEMS-AMT-EACH PIC S9(05)v9(02) COMP-3. 10 ITEMS-DATE-ENTERED PIC X(26). 10 ITEMS-DATE-SHIPPED PIC X(10). 10 ITEMS-ITEM-NOTES. 49 ITEMS-ITEM-NOTES-LEN PIC S9(04) COMP. 49 ITEMS-ITEM-NOTES-TEXT PIC X(560). ****************************************************************** * COBOL INDICATOR VARIABLES FOR TABLE ****************************************************************** 01 DCLITEMS-NULL. 10 ITEMS-PROD-ID-NULL PIC S9(04) COMP. 10 ITEMS-DATE-SHIPPED-NULL PIC S9(04) COMP. 10 ITEMS-ITEM-NOTES-NULL PIC S9(04)COMP. ****************************************************************** * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9 ******************************************************************
Example 2
In Example 2, change the prefix to a user value of "HV" instead of using the table name as prefix and generate indicator values with a suffix of IND.
The COBOL DECLARATION part of copybook file would look something like this:
****************************************************************** * COBOL DECLARATION FOR TABLE DEMO.ITEMS ****************************************************************** 01 DCLITEMS. 10 HV-ORD-NO PIC S9(09) COMP. 10 HV-ITEM-NO PIC S9(04) COMP. 10 HV-PROD-ID PIC X(4). 10 HV-QTY-ORDERED PIC S9(04) COMP. 10 HV-QTY-SHIPPED PIC S9(04) COMP. 10 HV-AMT-EACH PIC S9(05)v9(02) COMP-3. 10 HV-DATE-ENTERED PIC X(26). 10 HV-DATE-SHIPPED PIC X(10). 10 HV-ITEM-NOTES. 49 HV-ITEM-NOTES-LEN PIC S9(04) COMP. 49 HV-ITEM-NOTES-TEXT PIC X(560). ****************************************************************** * COBOL INDICATOR VARIABLES FOR TABLE ****************************************************************** 01 DCLITEMS-IND. 10 HV-PROD-ID-IND PIC S9(04) COMP. 10 HV-DATE-SHIPPED-IND PIC S9(04) COMP. 10 HV-ITEM-NOTES-IND PIC S9(04) COMP. ****************************************************************** * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9 ******************************************************************
Example 3
In Example 3, change the prefix to a user value of "FIELD" and generate numbered fields instead of column names. Use configuration default instead of specifying CBL for language.
The COBOL DECLARATION part of copybook file would look something like this:
****************************************************************** * COBOL DECLARATION FOR TABLE DEMO.ITEMS ****************************************************************** 01 DCLITEMS. 10 FIELD-001 PIC S9(09) COMP. 10 FIELD-002 PIC S9(04) COMP. 10 FIELD-003 PIC X(4). 10 FIELD-004 PIC S9(04) COMP. 10 FIELD-005 PIC S9(04) COMP. 10 FIELD-006 PIC S9(05)v9(02) COMP-3. 10 FIELD-007 PIC X(26). 10 FIELD-008 PIC X(10). 10 FIELD-009. 49 FIELD-009-LEN PIC S9(04) COMP. 49 FIELD-009-TEXT PIC X(560). ****************************************************************** * COBOL INDICATOR VARIABLES FOR TABLE ****************************************************************** 01 DCLITEMS-NULL. 10 FIELD-003-NULL PIC S9(04) COMP. 10 FIELD-008-NULL PIC S9(04) COMP. 10 FIELD-009-NULL PIC S9(04) COMP. ****************************************************************** * THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9 ******************************************************************
Example 4
In Example 4, create the file ITEMS.PLI from table DEMO.ITEMS in database DB2DEMO and prefix all field names with the table name. Language is PL/I.
The DECLARE TABLE part of copybook file would look something like this:
/*-------------------------------------------------------------*/ /* MFHCODCL OPTIONS: */ /* DATABASE : DB2DEMO */ /* SCHEMA : DEMO */ /* PREFIX : TABLE NAME */ /*-------------------------------------------------------------*/ EXEC SQL DECLARE DEMO.ITEMS TABLE ( ORD_NO INTEGER NOT NULL, ITEM_NO SMALLINT NOT NULL, PROD_ID CHAR(4), QTY_ORDERED SMALLINT NOT NULL WITH DEFAULT, QTY_SHIPPED SMALLINT NOT NULL WITH DEFAULT, AMT_EACH DECIMAL(7, 2) NOT NULL WITH DEFAULT, DATE_ENTERED TIMESTAMP NOT NULL WITH DEFAULT, DATE_SHIPPED DATE, ITEM_NOTES VARCHAR(560) );
The PL/I DECLARATION part of copybook file would look something like this:
/*-------------------------------------------------------------*/ /* PL/I DECLARATION FOR TABLE DEMO.ITEMS */ /*-------------------------------------------------------------*/ DECLARE 1 DCLITEMS, 5 ITEMS_ORD_NO BIN FIXED(31), 5 ITEMS_ITEM_NO BIN FIXED(15), 5 ITEMS_PROD_ID CHAR(4), 5 ITEMS_QTY_ORDERED BIN FIXED(15), 5 ITEMS_QTY_SHIPPED BIN FIXED(15), 5 ITEMS_AMT_EACH DEC FIXED(5,2), 5 ITEMS_DATE_ENTERED CHAR(26), 5 ITEMS_DATE_SHIPPED CHAR(10), 5 ITEMS_ITEM_NOTES CHAR(560) VAR; /*-------------------------------------------------------------*/ /* PL/I INDICATOR VARIABLES FOR TABLE */ /*-------------------------------------------------------------*/ DECLARE 1 DCLITEMS_NULL, 5 ITEMS_PROD_ID_NULL BIN FIXED(15), 5 ITEMS_DATE_SHIPPED_NULL BIN FIXED(15), 5 ITEMS_ITEM_NOTES_NULL BIN FIXED(15); /*-------------------------------------------------------------*/ /* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9 */ /*-------------------------------------------------------------*/
Example 5
Example 5 creates the file ITEMS.C from table DEMO.ITEMS in database DB2DEMO and prefixes all field names with the table name. Language is C.
The copybook file would look something like this:
/*-------------------------------------------------------------*/
/* MFHCODCL OPTIONS: */
/* DATABASE : DB2DEMO */
/* SCHEMA : DEMO */
/* PREFIX : TABLE NAME */
/* C HOST VARIABLES FOR TABLE DEMO.ITEMS */
/*-------------------------------------------------------------*/
EXEC SQL BEGIN DECLARE SECTION;
struct dclitems
{
long items_ord_no;
short items_item_no;
char items_prod_id[4];
short items_qty_ordered;
short items_qty_shipped;
double items_amt_each;
char items_date_entered[26];
char items_date_shipped[10];
struct {
short len
char data[560];
} items_item_notes;
/*-------------------------------------------------------------*/
/* C INDICATOR VARIABLES FOR TABLE */
/*-------------------------------------------------------------*/
short items_prod_id_null;
short items_date_shipped_null;
short items_item_notes_null;
};
EXEC SQL END DECLARE SECTION;
/*-------------------------------------------------------------*/
/* THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 9 */
/*-------------------------------------------------------------*/
Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| DDL Processor | Exporting Data | |