Set up IBM Db2 and firewall rules inside IBM CIC VM

Create a database

1. Export the IBM Db2 path:

   export PATH=$PATH:/opt/ibm/db2/V12.1/bin

2. Define the IBM Db2 instance environment variable as your created instance:

   DB2INSTANCE=db2inst1

3. Export the IBM Db2 environment variable:

   export DB2INSTANCE

4. Switch to the IBM Db2 instance user:

   su - db2inst1

5. Start the IBM Db2 client:

   db2

6. Create a database using the SQL command:

   CREATE DATABASE lpardb

7. Create a table using the SQL command:

   CREATE TABLE EMPLOYEES (employee_id INT NOT NULL,employee_name VARCHAR(150) NOT NULL,location VARCHAR(150) NOT NULL);

Use the Ansible playbook to set up the rich text firewall

The setup described above can be automated within the IBM CIC VM by running the Ansible playbook below, which uses the OpenStack Ansible collection to connect to IBM CIC and run the firewall command.

Follow the steps below to run the Ansible playbook.

1. Create a Python virtual environment and install the required packages to run the Ansible playbook.

   pip install
   pip install ansible
   pip install openstacksdk

   Install the openstack.cloud collection from Ansible Galaxy.

   ansible-galaxy collection install openstack.cloud

2. Create a YAML file named cloud.yaml to authenticate to the IBM CIC Environment. By default, OpenStack checks the ~/.config/openstack directory for the configuration file.

   mkdir -p ~/.config/openstack
   touch ~/.config/openstack/clouds.yaml 

   vi clouds.yaml
   clouds:
     icic_cloud:
       auth:
         auth_url: <Authentication url for the OenStack keytone service(eg:IBM_CIC_url:5000/v3>
         username: <Username>
         password: <Password>
         project_name: <IBM CIC Project name>
         user_domain_name: Default     // OprnStack login
         project_domain_name: Default  // OprnStack login
       region_name: RegionOne    //region optional
       identity_api_version: 3
       verify: false  // optional to skip the certification process should be used just for testing.

   Note: For more information about the cloud.yaml file, check the provided link.

   Verification: To verify the connection to the IBM CIC, run the command below and ensure you receive a SUCCESS message.

   ansible localhost -m openstack.cloud.auth -a "cloud=icic_cloud"

   Output:

   ansible localhost -m openstack.cloud.auth -a "cloud=icic_cloud"
   [  WARNING]: No inventory was parsed, only implicit localhost is available
   localhost | SUCCESS => {
       "auth_token": "xxxxxxxxxxxxx",
       "changed": false}

3. Create a YAML file named ansible_rich_rule_playbook.yaml, paste the sample playbook below into it, and modify it as needed. Install sshpass so that Ansible can use it to connect to your virtual machines.

   dnf install sshpass

   vi ansible_rich_rule_playbook.yaml

   # This section connects to the IBM CIC environment mentioned in the previous step, retrieves all VM information, and adds their IP addresses to vm_groups..
   name: IBM CIC connection and collecting all the VMs
   hosts: localhost
   gather_facts: false
   tasks:
       - name: List of VMs in IBM CIC
       openstack.cloud.server_info:
           cloud: icic_cloud
       register: icic_vm_info
       - name: Adding VMs to group
       add_host:
           name: "{{ item.hostname }}"
           ansible_host: "{{ item.access_ipv4 }}"
           groups: vm_groups
       loop: "{{ icic_vm_info.servers }}"
   # Uses SSH to connect to each VM and execute the rich rule firewall command..    
   - name: Adding rich text command all the IBM CIC VMs  
   hosts: vm_groups
   gather_facts: false
   become: yes
   vars:
       ansible_user: <User_ID of IBM CIC VM>
       ansible_ssh_pass: <Password of IBM CIC VM>
       ips_ports:
       - ip: 100.0.0.50 # RedHat OpenShift compute node ip
           port: 25010    # IBM Db2 Database port
       - ip: 100.0.0.60  # RedHat OpenShift compute node ip
           port: 25010    # IBM Db2 Database port
   tasks:
       - name: Adding rich text rule in IBM CIC VMs
       command: >
           firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="{{ item.ip }}" port protocol="tcp" port="{{ item.port }}" accept'
       loop: "{{ ips_ports }}"
       - name: Firewall reload
       command: firewall-cmd --reload
       - name: rich text rule verification command
       command: firewall-cmd --zone=public --list-rich-rules
       register: ips_port_check
       - name: Verification results
       debug:
           msg: >
           {% for rule in ips_ports %}
               {% if 'source address="' ~ rule.ip ~ '"' in ips_port_check.stdout and 'port="' ~ rule.port ~ '"' in ips_port_check.stdout %}
               Rule for {{ rule.ip }}:{{ rule.port }} is present.
               {% else %}
               Rule for {{ rule.ip }}:{{ rule.port }} is missing.
               {% endif %}
           {% endfor %}

4. Run the following command to run the Ansible playbook.

   ansible-playbook ansible_rich_rule_playbook.yaml