Skip to Content

Quick Start to SQL Risk Detection

Related topics

Getting started

This guide shows you how to proactively monitor and optimize SQL across IBM Db2 for z/OS environments using BMC AMI SQL Assurance for Db2. The product integrates seamlessly with continuous integration/continuous delivery (CI/CD) pipelines to automatically detect inefficient SQL statements and enforce SQL standards before code reaches production. You can incorporate SQL quality checks into your CI/CD workflows using tools like Jenkins, Azure DevOps, GitHub Actions, or GitLab CI/CD.

Before  you begin
•    Have working z/OSMF and Db2 environments on z/OS
•    Have BMC AMI SQL Explorer for Db2 working for your Db2 environment
•    Have credentials and network access to your Db2 subsystems
•    If you're using Jenkins, install and configure Jenkins on a host that can access your z/OSMF and Db2 environment on the mainframe
•    If you're using the Universal Connector, ensure a container runtime is installed and network connectivity is set up
•    Download the BMC AMI SQL Assurance for Db2 zip file package from BMC Support’s Electronic Product Distribution (EPD) site
•    Understand your organization’s SQL standards policies

1. Install and configure

You can install the product in Jenkins or Universal Connector. Choose the option that best fits your environment.

OptionWhen to useDownload and installation stepsVerification
Jenkins Plug-inChoose this if you already use Jenkins for CI/CD pipelines.

1. Download the Jenkins zip file package from BMC Support’s EPD site.

2. Unzip the downloaded package to access 2 Jenkins hpi files for BMC AMI SQL Assurance for Db2:

  • BMC-AMI-DevOps-Common-13.01.00.####.hpi
  • BMC-AMI-Db2-SA-13.01.00.####.hpi

3. Open Jenkins in your browser

  • Go to Manage Jenkins and then choose Plug-ins
  • Go to Advanced settings
  • Go to Choose file under Deploy Plugin and select the downloaded Jenkins hpi file and click Open.

Do this for both Common and SA hpi files.

4. Click Upload.
5. Restart Jenkins server, if prompted.
6. Configure SSL certificate for use by BMC AMI SQL Assurance. For more information, See Installing.

1. From the Dashboard in the Jenkins web user interface, navigate to Manage Jenkins > Plugins > Installed plugins tab.

2. Verify that:

  • The BMC AMI DevOps Common plug-in and the BMC AMI SQL Assurance for Db2 plug-in are available in the list.
  • The correct version of both the plug-ins are displayed.
  • The plug-ins are enabled. Verify that for both the plug-ins, the check box in the Enabled column is selected.

3. If the plug-ins are not installed correctly, reinstall them.

Universal Connector ContainerChoose this if you prefer a containerized setup or use a CI/CD tool other than Jenkins.

1. Download the Universal Connector (UC) zip file package from BMC Support’s EPD site.

2. Unzip the downloaded package.

3. Review the README.txt file for instructions on how to download the container image. Use the download script, download-sa-image.sh, as instructed in the README.txt file, which is also included in the unzipped package.

4. Push the container image to container registry, tag the image with the container registry host name (example command):

  • docker tag bmcImageTag newTag
    • bmcImageTag > BMC image tag in the download image script and pulled from the containers.bmc.com container registry. 
    • newTag > Container registry hostname to which to push the image. Confirm the new image name with your container registry administrator.

5. Configure SSL certificate for use by BMC AMI SQL Assurance. For more information, See Installing.

6. Configure pipeline/workflow for a private container registry. For more information, See Installing.

1. In the system where you pulled and pushed the container image, run the docker images command like below with the image that was pushed:

docker images | grep 13.01.00.####-GA

2. Verify the image is located where you expect.

2. Prepare for BMC AMI SQL Assurance configuration

After SQL Assurance is set up for Jenkins or Universal Connector, perform the following steps:

  • Configure required objects for explaining SQL statements:
    • User authorities
    • IBM stored procedures
    • Plan tables
  • Copy the object statistics from the Db2 catalog on production to the Db2 catalog that you are running against. You can do this via any of the following BMC products:
  • Customize the plug-in variables file.
    In the plug-in variables file that we have provided, AMI_DevOps.properties, customize the variable values.
    Depending on your CI/CD tool, select either:Preparing for a project (Jenkins) orPreparing for configuration (Universal Connector) in the SQL Assurance documentation.
  • Make sure that the plug-in variables file is available in the environment in which you run the deployment.

3. Enable rule sets for risk detection

  • Select or customize rule sets that align with your performance and compliance goals. You can review the SQL Assurance sample rules in the installation location at hlq.BMCSAMP(COBDFLT). For more information, see Customizing expert rules.
  • Create rules for common risks such as:
    • Missing indexes
    • Unbounded result sets
    • Inefficient joins

4. Create your first project or pipeline

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

Define a project or pipeline in Jenkins:
  • Create a new project or pipeline.
  • Add the BMC AMI DevOps Common - Authentication step to connect via z/OSMF to the mainframe and configure.
  • Add the BMC AMI SQL Assurance for Db2 - Analyze Static SQL step to run analysis for static SQL.
    • Configure the SQL Assurance step to point to your test Db2 subsystem, specify the explain method, object type, object name and the applicable inputs needed for the analysis.
  • Add the BMC AMI DevOps Common – Clean Up step.
  • Save the project or pipeline.
Define a pipeline job in your Universal Connector CI/CD tool:
  • Create a new config yaml file in your CI/CD tool repository and include the following application steps with applicable configuration:
    • Add the authentication step to connect via z/OSMF to the mainframe.
    • Add the static_sql_pg step to run analysis of static SQL.
    • Save the config yaml file.
  • Create a new pipeline/workflow yaml file in your CI/CD tool repository.
    • Define your trigger method, container image, variables, agent as applicable for your CI/CD tool.
    • Define the initialization commands to find and replace variables defined in the config and pipeline files.
    • Add each step to run from the config yaml file.
    • Save the pipeline yaml file.
Warning
Note

Use the sample config and pipeline yaml files to copy from for easier set up.

5. Run and review your first SQL evaluation

  • Trigger the project or pipeline manually.
  • Watch the console or job log for progress.
  • After the job completes, verify the results are acceptable in the console and job log.
  • Review the SQL Assurance violation report in the job output or via SQL Assurance Violation Details view in BMC AMI Command Center.
  • If any violations appear, fix the SQL, and re-run the SQL Assurance job.

Tips and best practices

  • Start with small, simple SQL to test your setup
  • Keep configuration files in version control
  • Enable detailed logging when troubleshooting
  • Run SQL validation in each environment

 

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI SQL Assurance for Db2 13.1