Cloud Pak for Integration

In this blog, will show case how to retrieve the data from employee database using IBM ACE capability running on CP4I

Prerequisite:

CP4I.v2023.4.1 running on OCP 4.12

ibm-appconnect.v11.1.0

ibm-integration-platform-navigator.v7.2.1

ibm-common-service-operator.v4.4

IBM App Connect V12.0.10.0 Toolkit

Create an message flow in App Connect Enterprise Toolkit

The flow is to retrieve the data from the employees table from the postgreSQL database.

Nodes used to create Employees message flow:

HTTP Input Node

Compute Node

HTTP Reply Node

Create Employees message Flow:

Select HTTP Input Node, Go to Properties > Basic > Path suffix for URL* as /employees:

Select Retrieve Employees compute Node  > From Properties tab > Basic > Provide Data source as POSTGRESQL:

Double click Retrieve Employees compute node and update as shown below:

Save the flow.

Use the mqsicreatebar command to generate bar file as ExampleDatabaseproject.bar

Refer more details:https://www.ibm.com/docs/en/app-connect/12.0?topic=commands-mqsicreatebar-command

Open the App Connect Dashboard:

Select BAR file

Click on Import BAR > Import the ExampleDatabaseproject.bar file:

From the App Connect Dashboard > click on configuration:

Create odbc.ini configuration:

Type :odbc.ini

Name:postgres-odbc

odbc.ini

[ODBC Data Sources]
POSTGRESQL=DataDirect ODBC PostgreSQL Wire Protocol
 
;# PostgreSQL Stanza
[POSTGRESQL]
Driver=/opt/ibm/ace-12/server/ODBC/drivers/lib/UKpsql95.so
Description=DataDirect ODBC PostgreSQL Wire Protocol
Database=postgres
Servername=10.10.10.10
PortNumber=5432

;##########################################
;###### Mandatory information stanza ######
;##########################################
 
[ODBC]
InstallDir=/opt/ibm/ace-12/server/ODBC/drivers
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8

Click on create.This will create the postgres-odbc under configuration.

Create mqsisetdbparms configuration:

Type :setdbparms.txt

Name:postgres-setdbparms

setdbparms.txt:

mqsisetdbparms -w /home/aceuser/ace-server -n odbc::POSTGRESQL -u postgres -p password


Click on create.This will create the postgresql-setdbparms under configuration.

Under configuration, you can see the postgres-odbcini and postgresql-setdbparms gets created:

Select Dashboard and Deploy Integrations:

Under Deploy integrations select Quick Start Integration and select Next

Use an existing BAR file: select ExampledatabseProject from the list:

 Select the postgres-odbcini and postgresql-setdbparms and click Next :

Under Deploy integrations 

Name: postgresql-employee

Under Deploy Integrations, select YAML Editor and add the below content and click create:

• apiVersion: appconnect.ibm.com/v1beta1
kind: IntegrationRuntime
metadata:
  labels:
    backup.appconnect.ibm.com/component: integrationruntime
  name: postgresql-employee
  namespace: cp4i-pvs
spec:
  license:
    accept: true
    license: L-UAZF-8ZYQDD
    use: CloudPakForIntegrationProduction
  replicas: 1
  template:
    spec:
      containers:
        - name: runtime
          resources:
            requests:
              cpu: 300m
              memory: 368Mi
  version: 12.0.10.0-r3
  barURL:
    - >-
      https://db-02-production-dash.cp4i-pvs:3443/v1/directories/ExampleDatabaseproject?5c66122f-234d-4c63-b687-ed10377129eb
  configurations:
    - postgres-odbcini
    - postgresql-setdbparms

Login to OCP console and check for the pods

PALLEs-MacBook-Pro:~ pvsreddy$ oc get po | grep employee
postgresql-employee-ir-db7d7858d-vjh6x                            1/1     Running                0               19s
PALLEs-MacBook-Pro:~ pvsreddy$

Go to Dashboard, click on postgresql-employee runtime, select ExampleDatabase Application  and select Employee Message flows and Endpoints:

Open the browser and hit the Endpoint Location :http://<hostname>/employees: