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:
![](https://dw1.s81c.com//IMWUC/MessageImages/817c3c3b3b8445358abd7cbbfdc51d54.png)
Select HTTP Input Node, Go to Properties > Basic > Path suffix for URL* as /employees:
![](https://dw1.s81c.com//IMWUC/MessageImages/6973f7337d804f34a326fd61475c0a88.png)
Select Retrieve Employees compute Node > From Properties tab > Basic > Provide Data source as POSTGRESQL:
![](https://dw1.s81c.com//IMWUC/MessageImages/cd70aa34e72d4574b3b7431d01d83e56.png)
Double click Retrieve Employees compute node and update as shown below:
![](https://dw1.s81c.com//IMWUC/MessageImages/d84404b539e746c3a221f8f07bbf61e8.png)
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
![](https://dw1.s81c.com//IMWUC/MessageImages/7eed8d07bc4c471baa37e60c292ace56.png)
Click on Import BAR > Import the ExampleDatabaseproject.bar file:
![](https://dw1.s81c.com//IMWUC/MessageImages/62655b86a0064e06887c36703bc59ea1.png)
From the App Connect Dashboard > click on configuration:
![](https://dw1.s81c.com//IMWUC/MessageImages/1e5d01c5875f4c0986ce6f28d30023d8.png)
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:
![](https://dw1.s81c.com//IMWUC/MessageImages/fcab9dfccb854f528484cc0f25345e63.png)
Select Dashboard and Deploy Integrations:
![](https://dw1.s81c.com//IMWUC/MessageImages/42f54a074e8a4c77969224af904177c2.png)
Under Deploy integrations
select Quick Start Integration
and select Next
![](https://dw1.s81c.com//IMWUC/MessageImages/bfe94b3035c94cdb92abf83ab735d86e.png)
Use an existing BAR file: select ExampledatabseProject from the list:
![](https://dw1.s81c.com//IMWUC/MessageImages/8a00a1283f4f401eb38b016ae7aa7cbf.png)
Select the postgres-odbcini and postgresql-setdbparms and click Next :
![](https://dw1.s81c.com//IMWUC/MessageImages/bf2af394c03b4030a0e8bee6ff139004.png)
Under Deploy integrations
Name: postgresql-employee
![](https://dw1.s81c.com//IMWUC/MessageImages/1a3b26d34aa64b9392545b507313b782.png)
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:
![](https://dw1.s81c.com//IMWUC/MessageImages/7bed7baa76864b299f0a71b01584ba68.png)
Open the browser and hit the Endpoint Location :http://<hostname>/employees:
![](https://dw1.s81c.com//IMWUC/MessageImages/ccb315170b444869a3ae740c4c5b0d64.png)