GENERATE DDL statement for the metadata utility

To generate DDL from the catalog, you can use Classic Data Architect. You can also use the metadata utility directly on the z/OS® system. The metadata utility can generate the same kinds of objects that Classic Data Architect can generate.

Table 1. Generated statements supported for each object type
Generated DDL	Object
DROP statements	TABLE, VIEW, INDEX, PROCEDURE
CREATE statements	TABLE, VIEW, INDEX, PROCEDURE
COMMENT ON statements	TABLE, VIEW, INDEX, PROCEDURE
GRANT statements	TABLE, VIEW, INDEX, PROCEDURE
ALTER statements	TABLE, VIEW
Indexes	TABLE

One situation where you might choose to create DDL from the metadata utility is in a migration. For example, you could generate DDL for objects on a test system and import those objects onto a production system. The DDL is output to the media that you specify for subsequent processing. The generated DDL is formatted for direct input into the metadata utility for creating or modifying catalog entries.

To generate DDL, the metadata utility first connects to an appropriate Classic data server specified in a CONNECT TO SERVER statement. Once connected, the catalog is accessible for queries necessary to gather data for the GENERATE DDL statements. The generated DDL is printed to SYSPRINT or recorded in the file associated with the DDLOUT DD in the CACMETAU JCL.

Syntax

Like-Clause

Object-Name

With-Options

Parameters

Like-Clause:

LIKE
LIKE ESCAPE charNOT LIKE ESCAPE char

Specify this clause when the object name contains wildcard characters. When the object name contains an underscore name character, an escape character must be defined to preserve the context. Once defined, place the escape character immediately preceding the underscore character to indicate that it is not a wildcard character.

GENERATE DDL FOR TABLE LIKE ESCAPE '!' "USER1"."IMS!_TAB%"
  WITH DROP,INDEX;

Object-Name:

object-name
schema-name.object-name

Specify the object with or without a schema name. If schema name is not specified, the schema defaults to the user ID assigned to the metadata utility job. When you specify objects after a like clause, you can use wildcard characters in the schema or object name. The supported wildcard characters are the same as those characters that are supported by the Query Processor (underscore and percent sign).

System catalog objects with a schema name of SYSIBM or SYSCAC cannot be generated using the GENERATE DDL statement. When the like clause is used, all system objects that qualify for the like processing are ignored. If you attempt to generate a specific object in the SYSIBM or SYSCAC schema, the following error is returned:

CACM004I Non-SQLCODE = 0x0071001E, ERROR: You cannot create or
generate a catalog object with an owner of SYSIBM or SYSCAC.

With-Options:

WITH option

The with options clause is used to describe additional DDL statement types to be included with the CREATE object statement that is generated. You can specify additional statement types individually or you can specify ALL, which generates all additional statement types that are applicable for the CREATE object statement. The Table 1 table shows the valid additional statement types that can be generated.

A redundant or duplicate specification in the with options clause returns an error during parsing (for example, if other options are specified with ALL). A specification of an invalid option returns an error during parsing (for example, when an index is specified on a view).

Considerations for the DDL output

By default, the generated DDL is written to SYSPRINT in the standard output format for the metadata utility report.
If you want to generate the DDL to a file, allocate the file to the DDLOUT DD in the CACMETAU JCL. When the metadata utility is run, the DDL is written to the data set referenced by the DDLOUT DD instead of SYSPRINT. And, SYSPRINT contains the following informational message for each statement:
```
CACM001I SQLCODE = 0,  INFO: The generated DDL was successfully written to  
        DDLOUT.
```
As with other metadata utility statements, you can choose to set up the SYSIN files for CACMETAU as two concatenated LRECL 80 files. The first file contains the CONNECT TO SERVER statement and the second contains the statements to run. For example:
```
//SYSIN  DD  DISP=SHR,DSN=&USRHLQ..SCACCONF(&CONNECT)
//       DD  DISP=SHR,DSN=&USRHLQ..SCACSAMP(&DDLIN)
```
Note: When concatenating with SYSIN, ensure that the files are consistent in their LRECL definitions.

The metadata utility can handle a SYSIN of 80 bytes or greater. Cases where you might require more than 80 bytes:
- If you specify DB2® tables and indexes using DB2 long names. (DB2 long names could require as much as 132 bytes.)
- If you specify COMMENT ON statements with long text strings.

Example 1

Generate DDL for table names belonging to USER1 and beginning with the characters VSAM_TAB. Include indexes defined on the resultant tables. Generate DROP statements for the resultant tables and indexes. Record the generated DDL in the specified z/OS data set associated with the DDLOUT DD.

Provide the following input to SYSIN, with a DDLOUT DD specified in the CACMETAU JCL:

GENERATE DDL FOR TABLE LIKE ESCAPE '!' USER1.VSAM!_TAB%
  WITH DROP,INDEX;

SYSTERM contains the following metadata utility output:


********************************* TOP OF DATA ************************************** 
  Start     End Processing
 Line #  Line #     Status Statement                 Object
      1       1         0  CONNECT TO SERVER         CACDAS  
CACM001I SQLCODE = 0, INFO: Statement execution was successful.

      2       3         0  GENERATE DDL FOR          LIKE
CACM001I SQLCODE = 0, INFO: The generated DDL was successfully written to DDLOUT.

    N/A     N/A         0  DISCONNECT FROM SERVER    CACDAS 
CACM001I SQLCODE = 0, INFO: Statement execution was successful.
******************************** BOTTOM OF DATA ************************************

SYSPRINT contains the following metadata utility output:

********************************* TOP OF DATA ************************************** 
LINE NO.    STATEMENT

      1     CONNECT TO SERVER CACDAS TCP/9.30.136.90/5002;
CACM001I SQLCODE = 0, INFO: Statement execution was successful.

LINE NO.    STATEMENT

2	GENERATE DDL FOR TABLE LIKE ESCAPE '!' USER1.VSAM!_TAB%
3	   WITH DROP,INDEX TO DSN:USER1.GENERATE.OUTPUT;
CACM001I SQLCODE = 0, INFO: The generated DDL was successfully written to DDLOUT
******************************** BOTTOM OF DATA ************************************

Only one table created by USER1 fulfilled the search criteria for a name beginning with the characters VSAM_TAB. The z/OS data set associated with the DDLOUT DD contains the following generated DDL:

********************************* TOP OF DATA ************************************** 
DROP INDEX USER1.VSAM_TABLE_IDX1;

DROP TABLE USER1.VSAM_TABLE;

CREATE TABLE USER1.VSAM_TABLE DBTYPE VSAM
   DS USER1.VSAM.VSAMFILE
(
   NAME SOURCE DEFINITION
	DATAMAP OFFSET 0 LENGTH 20
	DATATYPE UC
	USE AS CHAR(20),
   ADDRESS1 SOURCE DEFINITION
	DATAMAP OFFSET 20 LENGTH 20
	DATATYPE UC
	USE AS CHAR(20),
   ADDRESS2 SOURCE DEFINITION
	DATAMAP OFFSET 40 LENGTH 20
	DATATYPE UC
	USE AS CHAR(20),
   CITY SOURCE DEFINITION
	DATAMAP OFFSET 60 LENGTH 12
	DATATYPE UC
USE AS CHAR(12),
   STATE SOURCE DEFINITION
	DATAMAP OFFSET 72 LENGTH 2
	DATATYPE UC
	USE AS CHAR(2),
   ZIP SOURCE DEFINITION
	DATAMAP OFFSET 74 LENGTH 5
	DATATYPE UC
	USE AS CHAR(5),
   PLUS4 SOURCE DEFINITION
	DATAMAP OFFSET 79 LENGTH 4
	DATATYPE UC
	USE AS CHAR(4) );

CREATE INDEX USER1.VSAM_TABLE_IDX1 ON USER1.VSAM_TABLE (STATE
   ASC, ZIP ASC, NAME ASC);

******************************** BOTTOM OF DATA ************************************

Example 2

Generate DDL for table names belonging to USER1 and beginning with the characters IMS_TAB. Include indexes defined on the resultant tables. Generate DROP statements for the resultant tables and indexes. Record the generated DDL in the specified z/OS data set associated with the DDLOUT DD.

Provide the following input to SYSIN, with a DDLOUT DD specified in the CACMETAU JCL:

GENERATE DDL FOR TABLE LIKE ESCAPE '!' "USER1"."IMS!_TAB%"
  WITH DROP,INDEX;

SYSOUT contains the following metadata utility output:


********************************* TOP OF DATA ************************************** 
  Start     End Processing
 Line #  Line #     Status Statement                 Object
      1       1         0  CONNECT TO SERVER         CACDAS  
CACM001I SQLCODE = 0, INFO: Statement execution was successful.

      2       3         0  GENERATE DDL FOR          LIKE
CACM001I SQLCODE = 0, INFO: The generated DDL was successfully written to DDLOUT.

    N/A     N/A         0  DISCONNECT FROM SERVER    CACDAS 
CACM001I SQLCODE = 0, INFO: Statement execution was successful.
******************************** BOTTOM OF DATA ************************************

SYSPRINT contains the following metadata utility output:

********************************* TOP OF DATA ************************************** 
LINE NO.    STATEMENT

      1     CONNECT TO SERVER CACDAS "TCP/9.30.136.90/5002";
CACM001I SQLCODE = 0, INFO: Statement execution was successful.

LINE NO.    STATEMENT

2	GENERATE DDL FOR TABLE LIKE ESCAPE '!' "USER1"."IMS!_TAB%"
3	   WITH DROP,INDEX TO DSN:USER1.GENERATE.OUTPUT;
CACM001I SQLCODE = 0, INFO: The generated DDL was successfully written to DDLOUT
******************************** BOTTOM OF DATA ************************************

Only one table created by USER1 fulfilled the search criteria for a name beginning with the characters IMS_TAB. The z/OS data set associated with the DDLOUT DD contains the following generated DDL:

********************************* TOP OF DATA ************************************** 
DROP INDEX "USER1"."IMS_TABLE_IDX1";

DROP TABLE "USER1"."IMS_TABLE";

CREATE TABLE "USER1"."IMS_TABLE" DBTYPE IMS
   DS "USER1.IMS.IMSFILE"
(
   "NAME" SOURCE DEFINITION
	DATAMAP OFFSET 0 LENGTH 20
	DATATYPE UC
	USE AS CHAR(20),
   "ADDRESS1" SOURCE DEFINITION
	DATAMAP OFFSET 20 LENGTH 20
	DATATYPE UC
	USE AS CHAR(20),
   "ADDRESS2" SOURCE DEFINITION
	DATAMAP OFFSET 40 LENGTH 20
	DATATYPE UC
	USE AS CHAR(20),
   "CITY" SOURCE DEFINITION
	DATAMAP OFFSET 60 LENGTH 12
	DATATYPE UC
USE AS CHAR(12),
   "STATE" SOURCE DEFINITION
	DATAMAP OFFSET 72 LENGTH 2
	DATATYPE UC
	USE AS CHAR(2),
   "ZIP" SOURCE DEFINITION
	DATAMAP OFFSET 74 LENGTH 5
	DATATYPE UC
	USE AS CHAR(5),
   "PLUS4" SOURCE DEFINITION
	DATAMAP OFFSET 79 LENGTH 4
	DATATYPE UC
	USE AS CHAR(4) );

CREATE INDEX "USER1"."IMS_TABLE_IDX1" ON "USER1"."IMS_TABLE" ("STATE"
   ASC, "ZIP" ASC, "NAME" ASC);

******************************** BOTTOM OF DATA ************************************