zowe › dbm-db2 › generate › ddl🖨️

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]

Required Options

• --object | -o (string)

  • Specifies the Db2 objects to be generated in DDL. Use the optional include clause to include related Db2 objects.

     Format: see the object\-file description
    
    Note: The \-\-object and \-\-object\-file options are mutually exclusive.
    

• --object-file | --of (string)

  • Specifies the name of the local input file that contains a list of Db2 objects to be generated in DDL. Use a semicolon to separate objects. Use the optional include clause 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[.version]>
    FUNCTION <schema.name[.version]>
    PROCEDURE <schema.name[.version]>

    The optional include clause (<related-object-type>,...) identifies one or more related Db2 object types to include in the object list.

    You can specify the following object types: 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.

• --source-db2 | --sd (string)

  • Specifies the source Db2 subsystem or data sharing group where the objects that you want to use in a command are located.

    Note: If you specify the data sharing group, the first active Db2 subsystem in the group is used.

Options

• --change-set | --cs (string)

  • Specifies the creator and name of an existing RC/Migrator global change set that changes Db2 objects during a command execution.

    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 that modify attributes of Db2 objects.

    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 <from-value> and <to-value>. If the same <object-attribute> is specified multiple times, the first occurrence takes precedence.

    For a full list of attributes, see Global Change Set Attributes in the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig

    Example:

    The following example demonstrates changes to the table 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.
    - The changeSetValues options-profile option has the same behavior as the change-set-file command option.

• --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)

  • Specifies the local output file name that contains the generated DDL for the objects that are identified by the --object-file or --object option.

    Default value: output.sql

• --error-file | --ef (string)

  • Specifies the local output error file in YAML format. The file 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

• --modification | -m (string)

  • Specifies 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 current set of default values.

    For more information about using the modification option, see the DBM Data Service documentation at https://techdocs.broadcom.com/db2mgmt

Profile Options

• --dbm-db2-options-profile | --dbm-db2-options-p (string)

  • The name of a (dbm-db2-options) profile to load for this command execution.

• --dbm-db2-profile | --dbm-db2-p (string)

  • The name of a (dbm-db2) profile to load for this command execution.

• --base-profile | --base-p (string)

  • The name of a (base) profile to load for this command execution.

DBM-Db2 Connection Options

• --protocol (string)

  • Specifies the communication protocol between zowe dbm-db2 client and DBM Data Service.

    Allowed values: http, https

• --environment-list | --el (string)

  • Specifies a string of one or more entries consisting of a Db2 subsystem ID and a DBM Data Service REST API server host name or TCP/IP address. Use a comma to separate entries. The same Db2 subsystem can be used in multiple DBM Data Service environments. For more information about configuring the DBM Data Service, see the Database Management Solutions for Db2 for z/OS documentation at https://techdocs.broadcom.com/db2mgmt

• --job-cards | --jc (array)

  • Specifies a string array of z/OS JCL JOB statements.

• --work-dataset-prefix | --wdp (string)

  • Specifies the prefix (high-level qualifier) in z/OS work data set names.

• --delete-work-datasets | --dwd (boolean)

  • Specifies whether to delete work data sets on a mainframe after the request is fulfilled.

• --overwrite-output-files | --oof (boolean)

  • Specifies whether to overwrite output files if they exist.

• --authid | -a (string)

  • Specifies the primary Db2 authorization ID that is used to establish a connection between Db2 and a process.

• --sqlid | -s (string)

  • Specifies the authorization ID that is used in generated SET CURRENT SQLID statements.

• --termination-character | --tc (string)

  • Specifies the SQL termination character to use in DDL generation when the default termination character conflicts with the SQL statements that are used in triggers, XML indexes, and routines (external SQL functions and native SQL stored procedures). The default termination character is a semi-colon (;). You cannot specify a comma, double quote, single quote, left or right parentheses, or an underscore.

• --base-path | --bp (string)

  • The base path for your API mediation layer instance. Specify this option to prepend the base path to all DBM DS resources when making REST requests. Do not specify this option if you are not using an API mediation layer.

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)

  • Reject self-signed certificates.

    Default value: true

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

    Note: The CLI does not support certificate files that require a password. For more information, search Troubleshooting PEM Certificates in Zowe Docs.

• --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 Copy

• 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 Copy

• 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 Copy