Quick start to automating Db2 schema changes and validation with BMC AMI DevOps

This topic explains how to automate schema changes between IBM Db2 for z/OS environments and to validate schema requirements by using BMC AMI DevOps for Db2. This process uses a continuous integration/continuous delivery (CI/CD) project or pipeline integrated with schema change management via tools such as Jenkins, Azure DevOps, GitHub Actions, and GitLab CI/CD.

Before you begin

• Make sure that you have working z/OSMF and Db2 environments on z/OS.
• Make sure that you have BMC AMI Change Manager for Db2 working for your Db2 environment.
• Make sure that you have credentials and network access to your Db2 subsystems.
• If you're using Jenkins, install and configure it on a host that can access your z/OSMF and Db2 environment on the mainframe.
• If you're using the Universal Connector, make sure that a container runtime is installed and network connectivity is set up.
• Download the BMC AMI DevOps for Db2 zip file package from the BMC Support Electronic Product Distribution (EPD) site.
• Understand your organization’s schema standards and change management policies.

1. To install, configure, and verify

Choose the option that best fits your environment:

• Jenkins plug-in: Select this option if you already use Jenkins for CI/CD pipelines.
• Universal Connector container: Select this option if you prefer a containerized setup or use a CI/CD tool other than Jenkins.

2. To prepare for BMC AMI DevOps configuration

1. Create partitioned data sets (PDSs) for the following items on the mainframe (if they don't already exist) with LRECL=80 and unique names. 

   • CDL file
   • DDL file
   • Execution JCL
   • Analysis impact report
   • Worklist

2. Create rollback PDSs with unique names on the mainframe for the following items:

   • CDL file
   • Execution JCL
   • Analysis impact report
   • Worklist

3. Customize the plug-in variables file.
   In the plug-in variables file provided in the downloaded package, AMI_DevOps.properties, customize the variable values. Depending on your CI/CD tool, select either: Using BMC AMI DevOps in Jenkins or Using BMC AMI DevOps with Universal Connector.

4. Make sure that the plug-in variables file is available in the environment in which you run the deployment.

3. To create your first project or pipeline

After the setup, you can test a simple schema change through a CI/CD project or pipeline.

Step 1: To prepare a sample DDL

Create a small SQL file. For example:

Save it in a Git repository that your pipeline can access.

Step 2: To define a project or a pipeline Job

Define a project or pipeline in Jenkins:

1. Create a new project or pipeline.

2. Add the BMC AMI DevOps Common - Authentication step to connect via z/OSMF to the mainframe and configure.

3. Add the BMC AMI DevOps Common - File Transmission step and configure to upload the DDL file to the mainframe.

4. Add the BMC AMI DevOps Schema Mgmt for Db2 - Schema Change Migration step to perform the following steps:

   1. Run a compare of the Db2 catalog with the sample DDL file.
   2. Configure the compare step to point to your test Db2 subsystem
   3. Specify the comparison type of Local Catalog to DDL File with DDL File as scope and the applicable inputs needed for the compare.

5. Add the BMC AMI DevOps Schema Mgmt for Db2 - Output Transmission step to download the compare artifacts to the Jenkins workspace.

6. Add the BMC AMI DevOps Common – JCL Execution step to run the JCL from the Schema Change Migration step and run the JCL from the Mainframe PDS member. For this test, select the Disable Build step so that it won’t immediately run the changes.

7. Add the BMC AMI DevOps Common – Clean Up step.

8. Save the project or pipeline.

Define a pipeline Job in your Universal Connector CI/CD tool:

1. Create a new config yaml file in your CI/CD tool repository.

2. Use the sample config yaml file, Config Schema Migration_ALL_Steps.yml, found in the downloaded zip file from the BMC EPD site, copy the following application steps and give the applicable configuration details:

   1. Add the authentication step to connect via z/OSMF to the mainframe.
   2. Add the fileUpload step to upload the DDL file to the mainframe.
   3. Add the localCatToDDL step to run a compare of the Db2 catalog with the sample DDL file.
   4. Add the outputTransmission step to download the compare artifacts to the tool’s workspace.
   5. Save the config yaml file.

3. Use the sample pipeline or workflow yaml file, Pipeline/Workflow_Schema Migration_Sample.yml, found in the downloaded zip file from the BMC EPD site, copy the file and update the following details:

   1. Define your trigger method, container image, variables, agent as applicable to your CI/CD tool.
   2. Define the initialization commands to find and replace variables defined in the config and pipeline files.
   3. Add each step to run from the config yaml file.
   4. Save the pipeline yaml file.

Step 3: To run and review

1. Trigger the project or pipeline manually.

2. Watch the console or job log for progress.

3. After the job finishes running, verify that the results are acceptable in the console, job log, and the compare artifacts.

4. To configure and apply schema standards

Schema standards help ensure that database changes follow your organization’s rules.

Step 1: To create schema rules

Using the Product Tools perspective in BMC AMI Command Center for Db2, follow these steps:

1. Create schema standards rule set.
2. Create schema rules required in your organization:
   • Rule name
   • Rule explanation
   • Object type
   • Rule expression
   • (optional) When condition 

We provide a sample rule set with rules for trial purposes.

Step 2: To create schema standards project or pipeline

1. Depending on whether your using Jenkins or UC, create a new Jenkins project or pipeline or UC config yaml file.

2. Add the authentication step

3. Add the applicable tool’s Schema Standards step: BMC AMI DevOps Schema Mgmt for Db2 - Schema Standards or schemaStandards (stepName: schema_standards_ddl).

   1. Configure the step by using the Schema Standards rule set name, source of schema: DDL, and the DDL PDS data set and member.
   2. Save the project or config yaml file.

4. If you're using UC, create a new UC pipeline yaml file.

   1. Configure with the authentication and schemaStandards application step.
   2. Save the pipeline yaml file.

Step 3: To run and review

1. Trigger the project or pipeline manually.

2. Watch the console or job log for progress.

3. After the job completes, verify the console and job log results.

4. Review the Schema standards violation report in the job output or via Schema Standards Violation Details view in BMC AMI Command Center.

5. If any violations appear, fix the issue, and rerun the Schema Standards job.

5. To promote changes across environments

After your test project or pipeline succeeds, update your Jenkins project or pipeline or UC config file, and pipeline files:

1. Update to the target QA, staging, or production Db2 subsystems.
2. Add the Schema standards step.
3. Enable the JCL Execution step to make the schema changes.

Always review logs and test results before promoting to the next environment.

6. To verify and troubleshoot

After deployment:

1. Check the Db2 catalog to confirm that the change was applied successfully.
2. Review the logs generated by Jenkins or the CI/CD tool for errors or warnings.
3. If you encounter problems:
   • Verify network access to Db2.
   • Verify that your credentials have the correct privileges.
   • Review your schema standards file for incorrect rules.

For more information, see Troubleshooting.

Tips and best practices

• Start with small, simple changes to test your setup.
• Keep configuration files in version control.
• Enable detailed logging when troubleshooting.
• Run schema validation before every deployment.
• Back up critical data before promoting major changes.