## zowe › dbm-db2 › compare › ddl

Compare objects that are defined in a DDL file to objects that are defined on a Db2 subsystem and generate an update script to implement the necessary object changes. This command also generates a high-level change summary report and a parseable impact analysis report. You can execute the script that is generated from this command using the 'zowe dbm execute compare-script' 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 compare ddl <local-input-ddl-filename> [options]

#### Positional Arguments

- `local-input-ddl-filename` (string)

  - Specifies the local input file that contains DDL statements for the Db2 objects that you want to compare to Db2 objects on a target subsystem. Typically, this file is created by a 'zowe dbm generate ddl' command or retrieved from an archive. You can edit this file.

#### Required Options

- `--target-db2` | `--td` (string)

  - Specifies the target Db2 subsystem or data sharing group where you want to use a command.

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

#### Options

- `--match-set` | `--ms` (string)

  - Specifies the creator and name of an existing RC/Migrator automapping mask set. Matching is used to pair objects in a DDL file to objects that are defined on a Db2 subsystem. Matching determines whether the 'change-set' or 'rule-set' options are applied.

    Format:

    <match-set-creator.match-set-name>

    For more information about mask services, see the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig

    Note: If --match-set and --match-set-file are both specified, specifications in match-set-file take precedence.

- `--match-set-file` | `--msf` (string)

  - Specifies the name of the local input file that contains the mapping mask specifications. Matching is used to pair objects in a DDL file to objects that are defined on a Db2 subsystem. For example, a mask specification can account for different schema naming patterns across environments. Matching determines whether the 'change-set' or 'rule-set' options are applied.

    Format:

    <object-type> <source-name-mask> <target-name-mask>;

    STOGROUP <name> <name>
    DATABASE <name> <name>
    TABLESPACE <database.name> <database.name>
    TABLE <schema.name> <schema.name>
    INDEX <schema.name> <schema.name>
    VIEW <schema.name> <schema.name>
    SYNONYM <schema.name> <schema.name>
    ALIAS <schema.name> <schema.name>
    TRIGGER <schema.name> <schema.name>
    SEQUENCE <schema.name> <schema.name>
    FUNCTION <schema.name[.version]> <schema.name[.version]>
    PROCEDURE <schema.name[.version]> <schema.name[.version]>

    Note:
    - <name> must be between 1 and 128 characters. For DATABASE and TABLESPACE, <name> must be between 1 and 8 characters.
    - <schema> must be between 1 and 128 characters.
    - <version> must be between 1 and 64 characters.

    A mask specification can include the following wildcard characters:

    Percent sign (%) indicates that zero or more characters can occupy that position and all remaining positions to the end of the name, or to the next character. The percent sign can be used anywhere in the name. However, the source and target characters must match exactly.

    Hyphen or dash (-) indicates that any character can occupy that position, but a character must exist at that position, and the source and target character must match exactly. The hyphen can be repeated in several places in the name.

    Asterisk (*) indicates matching values. An asterisk cannot be used with other characters.

    Use a semicolon to separate mask specifications. Multiple mask specifications for the same object type are supported.

    Example:

    The following example demonstrates different ways of matching the table MYNAME.MYTABLE to the table YOURNAME.YOURTABLE:

    TABLE MY%.%TABLE YOUR%.%TABLE;
    TABLE MYN-M-.MYT% YOURN-M-.YOURT%;
    TABLE MYNAME.MYTABLE YOURNAME.YOURTABLE;
    TABLE *.MYTABLE *.YOURTABLE;

    For a list of mask specifications, see the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig

    Note:
    - If --match-set and --match-set-file are both specified, specifications in match-set-file take precedence.
    - The matchSetValues options-profile option has the same behavior as the match-set-file command option.

- `--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. Depending on the backend configuration, the changes apply to either ALL objects in the DDL or to NEW objects only as determined by match-set processing.

    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.

    Example:

    The following example demonstrates changes to the table schema (creator) and tablespace names:

    TBCR TEST% PROD%
    TBTS TESTTS% PRODTS%

    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.
    - The changeSetValues options-profile option has the same behavior as the change-set-file command option.

- `--rule-set` | `--rs` (string)

  - Specifies the creator and name of an existing RC/Migrator rule set that overrides Db2 object attributes in the target Db2 subsystem with the corresponding values from the input DDL file. The changes only apply to existing objects, as determined by match-set processing.

    Format:

    <rule-set-creator.rule-set-name>

    For more information about rule database services, see the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig

- `--id` (string)

  - Specifies the 1- to 8-character name of the RC/Migrator compare strategy that is created on the target Db2 subsystem during a command execution.

    Format: The name must begin with a non-numeric character and contain the following characters only: uppercase letters from A to Z, numbers from 0 to 9, and special characters $, #, and @.

- `--description` | `-d` (string)

  - Specifies a 1- to 25-character description for the RC/Migrator compare strategy.

- `--output-compare-script` | `--ocs` (string)

  - Specifies the name of the local output file that contains the update script to make changes to objects that reside on the Db2 subsystem.

    Default value: compare.txt

- `--output-summary-file` | `--osf` (string)

  - Specifies the name of the local output file that contains the change summary report in text format. This report tracks changes to Db2 objects on the target Db2 subsystem, but it does not describe how changes are made.

    Default value: summary.txt

- `--output-impact-file` | `--oif` (string)

  - Specifies the name of the local output file that contains the parseable impact analysis report in JSON or YAML format. This report identifies the significant DDL operations that are performed on Db2 objects. The report provides the total number and type of DDL operations that are performed on each object type, the list of DDL operations performed on each object instance, and the state of any pending changes.

    To determine the report format, supply one of the following extensions in the file name:
    - .json for JSON
    - .yaml or .yml for YAML
    If no extension is provided, the format is set to JSON.

    Default value: impact.json

    For more information about the impact analysis report, see the RC/Migrator documentation at https://techdocs.broadcom.com/db2rcmig

- `--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 a script to update objects on the 'TEST' Db2 subsystem with DDL definitions in myddl.sql file:

  - `zowe dbm-db2 compare ddl myddl.sql --target-db2 TEST`

- Generate a script to update objects and apply a rule set for the matched objects:

  - `zowe dbm-db2 compare ddl myddl.sql --target-db2 TEST --rule-set USER1.RULESET`

- Generate a script to update objects and apply a rule set for the objects matched as determined by the local mask specifications in the pair.txt file:

  - `zowe dbm-db2 compare ddl myddl.sql --target-db2 TEST --rule-set USER1.RULESET --match-set-file pair.txt`