Generate CREATE or DROP DDL statements for specified Db2 objects into an output DDL file. For example, use this command to generate CREATE TABLE definitions that you can then edit and use to update the table definition on a Db2 subsystem with the 'zowe dbm compare ddl' command.
For details about command usage for automation, see DBM-Db2 samples at https://github.com/BroadcomMFD/dbm-db2-for-zowe-cli
Usage
zowe dbm-db2 generate ddl [options]
Options
-
--object
| -o
(string)
-
Specifies the Db2 objects for which you want to generate DDL statements. Use the optional include syntax to include related Db2 objects.
Format: see object-file description.
Note: The --object and --object-file options are mutually exclusive.
-
--object-file
| --of
(string)
-
Specifies the local input file that contains a list of Db2 objects, separated by a semicolon, for which you want to generate DDL statements. Use the optional include syntax to include related Db2 objects.
Format:
<object-type> <object-name> [include(<related-object-type>,...)];
The required clause <object-type> <object-name> identifies the specific Db2 (base) object:
STOGROUP <name>
DATABASE <name>
TABLESPACE <dbname.name>
TABLE <schema.name>
INDEX <schema.name>
VIEW <schema.name>
ALIAS <schema.name>
SYNONYM <schema.name>
MQT <schema.name>
SEQUENCE <schema.name>
TRIGGER <schema.name>
FUNCTION <schema.name[.version]>
PROCEDURE <schema.name[.version]>
The optional clause include(<related-object-type>,...) identifies one or more, comma separated related object types that you want to include in the generated DDL statements. You can specify STOGROUP, DATABASE, TABLESPACE, TABLE, INDEX, VIEW, SYNONYM, MQT-ALL, TRIGGER, ROUTINE, PARENTS, CHILDREN, and ALL.
Example:
table sysibm.systables include (tablespace, database, index);
database db1 include(children);
Note: The --object and --object-file options are mutually exclusive.
-
--change-set
| --cs
(string)
-
Specifies the creator and name of an existing RC/Migrator global change set to be used to modify Db2 object attributes when generating the DDL.
Format:
<change-set-creator.change-set-name>
For more information about global change services, see the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig.
Note: If change-set and change-set-file are both specified, specifications in change-set-file take precedence.
-
--change-set-file
| --csf
(string)
-
Specifies the name of the local input file that contains the global change specifications to modify Db2 object attributes when generating DDL.
Format:
<object-attribute> <from-value> <to-value>
The <object-attribute> consists of four characters. The first two characters identify the object type. The last two characters identify the specific attribute. Wildcard characters are supported in the <from-value> and <to-value>. The first occurrence in multiple specifications for the same <object-attribute> has the highest precedence.
For a list of global change set attributes, see the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig.
Example:
The following example demonstrates changes to table’s schema (creator) and tablespace names:
TBCR TEST% PROD%
TBTS TESTTS% PRODTS%
Note: If change-set and change-set-file are both specified, specifications in change-set-file take precedence.
-
--type
| -t
(string)
-
Specifies the type of DDL statements that you want to generate. You can generate CREATE or DROP statements.
Default value: create
Allowed values: drop, create
-
--output-ddl-file
| --odf
(string)
-
--modification
| -m
(string)
-
Identifies a named set of server-managed default parameter values that control the execution behavior of the zowe dbm-db2 commands. For example, you can use a modification to identify a set of default values that differ from the set of values that are normally used.
For more information about using the modification option, see the DBM Data Service documentation at https://techdocs.broadcom.com/db2mgmt.
-
--error-file
| --ef
(string)
-
Specifies the local output error file in YAML format that contains basic execution information, connection details, dbm-db2 profile parameters, used arguments, and errors that occurred during execution of the command.
Default value: error.log
Required Options
Profile Options
Base Connection Options
-
--host
| -H
(string)
- Host name of service on the mainframe.
-
--port
| -P
(number)
- Port number of service on the mainframe.
-
--user
| -u
(string)
- User name to authenticate to service on the mainframe.
-
--password
| --pass
| --pw
(string)
- Password to authenticate to service on the mainframe.
-
--reject-unauthorized
| --ru
(boolean)
-
--token-type
| --tt
(string)
- The type of token to get and use for the API. Omit this option to use the default token type, which is provided by 'zowe auth login'.
-
--token-value
| --tv
(string)
- The value of the token to pass to the API.
-
--cert-file
(local file path)
- The file path to a certificate file to use for authentication
-
--cert-key-file
(local file path)
- The file path to a certificate key file to use for authentication
Examples
-
Generate CREATE DDL statements for a tablespace and its child table:
zowe dbm-db2 generate ddl --object "tablespace my.tbsp include(table)" --source-db2 SRC --output-ddl-file objects.sql
-
Generate CREATE DDL statements for a database and its children and apply changes as defined in the change set:
zowe dbm-db2 generate ddl --object "database my.db include(children)" --source-db2 SRC --change-set USER1.CHANGSET --output-ddl-file objects.sql
-
Generate DROP DDL statements only for the input object list:
zowe dbm-db2 generate ddl --object-file objects.txt --source-db2 SRC --type drop --output-ddl-file output.sql