Cloud Pak for Integration

 View Only

How to retrieve data from postgresSQL database using IBM App Connect Enterprise running on Cloud Pak for Integration

By P VENKATA SUBBA REDDY posted Sun February 11, 2024 11:01 AM

  

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:


0 comments
21 views

Permalink