Installing and configuring BMC AMI SQL Assurance

This page outlines the installation, setup, configuration, and tasks required to implement and operate BMC AMI SQL Assurance for Db2 and its supporting components.

The following task list outlines tasks, roles, and frequency required for setup and use of SQL Assurance and related components:

1. Install BMC AMI SQL Assurance back-end products

Install the back-end mainframe products: BMC AMI SQL Explorer for Db2 and BMC AMI Command Center for Db2.

Tasks:

• Installing SQL Explorer
• Installing Command Center

Role: Db2 System Programmer (Db2 SYSPROG)

Frequency: One-time

2. Configure BMC AMI SQL Explorer

Configure BMC AMI SQL Explorer for Db2 after installation.

Tasks:

• SQL Performance Customizing after installation
• Setting up SQL Explorer

Role: Db2 SYSPROG

Frequency: One-time

3. Configure user authorities and Explain setup

Configure user authorities, IBM stored procedures, and plan tables required to run an Explain.

Tasks:

• SQL Explorer and Db2 authority requirements 
• Required authorities for Explains and the What-If Index feature.

Role: Db2 SYSPROG and Db2 DBA

Frequency: One-time

4. Confirm setup completion with Explain test

Use Change Manager to run an Explain to verify setup completion.

Task:

• Explaining SQL statements

Role: Db2 SYSPROG and Db2 DBA

Frequency: One-time

5. Configure BMC AMI Command Center

Configure BMC AMI Command Center for Db2 for use with SQL Assurance to review violations and define VTI rules.

Tasks: 

1. Define common infrastructure component (UIM or CIS) for connection with Command Center.

   • User interface middleware (UIM):Installing and configuring the UIM server
   • Common interface server (CIS):Installing and configuring Common Interface Server

   Role: z/OS SYSPROG

   Frequency: One-time

2. Grant user access for common infrastructure component (UIM or CIS) and Command Center superuser authorization. For more information, seeManaging User Access 

   Role: z/OS Security administrator  

   Frequency: One-time 

3. Customize Command Center. For more information, seeCommand Center Customizing after installation

   Role: Db2 SYSPROG 

   Frequency: One-time 

6. Confirm z/OSMF operation and port details

Install and verify that it's running. Note the port number. 

Task:

• IBM z/OS Management Facility Configuration Guide

Role: z/OS SYSPROG

Frequency: One-time

7. Create z/OSMF security authorizations

Create z/OSMF security authorizations.

Task:

• Planning
• IBM z/OS Management Facility Configuration Guide 

Role: z/OS SYSPROG

Task frequency: One-time

8. Download installation files from BMC EPD

Download the applicable zip file (Jenkins or Universal Connector (UC)) from the BMC Electronic Product Distribution (EPD) site and unzip.

Task:

• Downloading the BMC AMI SQL Assurance installation files

Role: Linux administrator
Frequency: Quarterly or as needed

9. Select tasks based on your CI/CD platform

Pick one task depending on your CI/CD platform:

Role: Linux administrator
Frequency: Quarterly or as needed

10. SSL configuration

Configure the SSL certificate.

Task:

• Configuring an SSL certificate for use by BMC AMI SQL Assurance

Role: z/OS Security administrator and Linux administrator
Frequency: One-time or as needed

11. SQL Assurance rules setup

Define SQL Assurance rules, if not using the default rules (COBDFLT).

Tasks:

• Jenkins:SQL Assurance rules set
• Universal Connector:SQL Assurance rules set for Universal Connector
• Customizing expert rules

Role: Db2 DBA
Frequency: One-time or as needed

12. Select tasks based on your CI/CD platform

Pick tasks depending on your CI/CD platform: 

13. After running SQL Assurance

After running SQL Assurance, review any violations reported.

Tasks:

• BMC AMI Command Center for Db2:Viewing expert rule violations
• Job output:Sample SQL Assurance report (AS$RSLT)

Role: Db2 DBA and Application developer

Frequency: Ongoing