In this blog, we have provided steps on how to install PostgreSQL, connect from IBM App Connect Enterprise and create a sample message flow to retrieve data from employees database.
Steps to Install PostgreSQL Database Version 14
Install package:
[root@database1 ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Disable the default module that comes with Linux (usually not the latest version):
[root@database1 ~]# dnf -qy module disable postgresql
Install Postgres server (version 14 in this case):
[root@database1 ~]# dnf install -y postgresql14-server
Run the initial setup:
[root@database1 ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Enable the service:
[root@database1 ~]# systemctl enable postgresql-14
Start the service:
[root@database1 ~]# systemctl start postgresql-14
Set the password as password for the postgres user(automatically created during installation):
[root@database1 ~]# passwd postgres
Changing password for user postgres.
New password:
Enable to connect remotely:
[root@database1 ~]# find / -name "postgresql.conf"
./var/lib/pgsql/14/data/postgresql.conf
Open postgresql.conf file and replace line
listen_addresses = 'localhost'
With
listen_addresses = '*'
Find pg_hba.conf
[root@database1 ~]# find / -name pg_hba.conf"
/var/lib/pgsql/14/data/pg_hba.conf
Open and add the below line
[root@database1 ~]# vi /var/lib/pgsql/14/data/pg_hba.conf
host all all ::/0 trust
host all all 0.0.0.0/0 trust:wq
Restart the service:
[root@database1 ~]# service postgresql-14 restart
Create employees table:
create table employees
(
empid character varying(20),
name character varying(20),
location character varying(20),
state character varying(20)
);
Push sample data and retrieve the employees table:
[root@database1 ~]# psql -h 10.10.10.10 -U postgres
psql (14.10)
Type "help" for help.
postgres=# select * from employees;
empid | name | city | state
-------+----------+-----------+-----------
004 | Jacob | Kochi | Kerala
001 | Pvsreddy | Bangalore | Karnataka
002 | Jahnavi | Bangalore | Karnataka
003 | Edwin | Kochi | Kerala
(4 rows)
Steps to Connect PostgreSQL Database from IBM App Connect Enterprise:
Prerequisite:
App Connect Enterprise V12.0.11.0 running on Linux Environment installed at location: /home/pvsreddy/ace-12.0.11.0
Steps:
App Connect Enterprise provides templates for the odbc.ini and odbcinst.ini files.Make a copy of these two files to a directory of your choice (in our case /home/odbc/):
[root@appconnect1 ~]# cp /home/pvsreddy/ace-12.0.11.0/server/ODBC/unixodbc/odbc.ini /home/odbc/odbc.ini
[root@appconnect1 ~]# cp /home/pvsreddy/ace-12.0.11.0/server/ODBC/unixodbc/odbcinst.ini /home/odbc/odbcinst.ini
Set the group ownership of these two files to mqbrkrs:
[root@appconnect1 ~]# chgrp mqbrkrs /home/odbc/odbc.ini
[root@appconnect1 ~]# chgrp mqbrkrs /home/odbc/odbcinst.ini
Update the /home/odbc/odbc.ini file:
In our example below, the name of the ODBC data source is POSTGRESQL and it points to a PostgreSQL server running on 10.10.10.10. The database name is postgres:
odbc.ini
********************************************
[ODBC Data Sources]
POSTGRESQL=PostgreSQL ODBC datasource
[POSTGRESQL]
Driver=/home/pvsreddy/ace-12.0.11.0/server/ODBC/drivers/lib/UKpsql95.so
Description=DataDirect ODBC PostgreSQL Wire Protocol
Database= postgres
HostName=10.10.10.10
PortNumber=5432
[ODBC]
InstallDir=/home/pvsreddy/ace-12.0.11.0/server/ODBC/drivers
UseCursorLib=0
IANAAppCodePage=4
UNICODE=UTF-8
*************************************************
/home/odbc/odbcinst.ini
[ODBC]
;# To turn on ODBC trace set Trace=yes
Trace=no
TraceFile=/home/pvsreddy/odbc/odbctrace.out
Threading=2
Create an ODBCINI environment variable that points to the odbc.ini file and an ODBCSYSINI environment variable that points to the directory with odbcinst.ini:
[root@appconnect1 ~]# export ODBCINI=/home/odbc/odbc.ini
[root@appconnect1 ~]# export ODBCSYSINI=/home/odbc
Create a security identity for the user accessing PostgreSQL(Source the mqsiprofile to run the mqsi commands):
[root@appconnect1 ~]# mqsisetdbparms BRK120110 -n odbc::POSTGRESQL -u postgres -p password
BIP8071I: Successful command completion.
Note: Restart the broker.
Test the connection with the mqsicvp tool:
[root@appconnect1 ~]# mqsicvp BRK120110 -n POSTGRESQL
BIP8299I: User 'postgres' from security resource name 'odbc::POSTGRESQL' will be used for the connection to datasource 'POSTGRESQL'.
BIP8290I: Verification passed for the ODBC environment.
BIP8270I: Connected to Datasource 'POSTGRESQL' as user 'postgres'. The datasource platform is 'PostgreSQL', version '14.10.0000 PostgreSQL 14.10'.
===========================
databaseProviderVersion = 14.10.0000 PostgreSQL 14.10
driverVersion = 08.02.2291 (B1726, U1528)
driverODBCVersion = 03.52
driverManagerVersion = 03.52.0002.0003
driverManagerODBCVersion = 03.52
databaseProviderName = PostgreSQL
datasourceServerName = 10.10.10.10
databaseName = postgres
odbcDatasourceName = POSTGRESQL
driverName = UKpsql95.so
supportsStoredProcedures = Yes
procedureTerm = procedure
accessibleTables = Yes
accessibleProcedures = No
identifierQuote = "
specialCharacters = None
describeParameter = Yes
schemaTerm = schema
tableTerm = table
sqlSubqueries = 31
activeEnvironments = 0
maxDriverConnections = 0
maxCatalogNameLength = 0
maxColumnNameLength = 63
maxSchemaNameLength = 63
maxStatementLength = 0
maxTableNameLength = 63
supportsDecimalType = Yes
supportsDateType = No
supportsTimeType = No
supportsTimeStampType = No
supportsIntervalType = No
supportsAbsFunction = Yes
supportsAcosFunction = Yes
supportsAsinFunction = Yes
supportsAtanFunction = Yes
supportsAtan2Function = Yes
supportsCeilingFunction = Yes
supportsCosFunction = Yes
supportsCotFunction = Yes
supportsDegreesFunction = Yes
supportsExpFunction = Yes
supportsFloorFunction = Yes
supportsLogFunction = Yes
supportsLog10Function = Yes
supportsModFunction = Yes
supportsPiFunction = Yes
supportsPowerFunction = Yes
supportsRadiansFunction = Yes
supportsRandFunction = No
supportsRoundFunction = Yes
supportsSignFunction = Yes
supportsSinFunction = Yes
supportsSqrtFunction = Yes
supportsTanFunction = Yes
supportsTruncateFunction = Yes
supportsConcatFunction = Yes
supportsInsertFunction = No
supportsLcaseFunction = Yes
supportsLeftFunction = No
supportsLengthFunction = Yes
supportsLTrimFunction = Yes
supportsPositionFunction = Yes
supportsRepeatFunction = Yes
supportsReplaceFunction = Yes
supportsRightFunction = No
supportsRTrimFunction = Yes
supportsSpaceFunction = No
supportsSubstringFunction = Yes
supportsUcaseFunction = Yes
supportsExtractFunction = Yes
supportsCaseExpression = Yes
supportsCastFunction = Yes
supportsCoalesceFunction = Yes
supportsNullIfFunction = Yes
supportsConvertFunction = Yes
supportsSumFunction = Yes
supportsMaxFunction = Yes
supportsMinFunction = Yes
supportsCountFunction = Yes
supportsBetweenPredicate = Yes
supportsExistsPredicate = Yes
supportsInPredicate = Yes
supportsLikePredicate = Yes
supportsNullPredicate = Yes
supportsNotNullPredicate = Yes
supportsLikeEscapeClause = Yes
supportsClobType = No
supportsBlobType = No
charDatatypeName = character
varCharDatatypeName = character varying
longVarCharDatatypeName = text
clobDatatypeName = N/A
timeStampDatatypeName = N/A
binaryDatatypeName = bit
varBinaryDatatypeName = bit varying
longVarBinaryDatatypeName = bytea
blobDatatypeName = N/A
intDatatypeName = integer
doubleDatatypeName = double precision
varCharMaxLength = 0
longVarCharMaxLength = 0
clobMaxLength = 0
varBinaryMaxLength = 0
longVarBinaryMaxLength = 0
blobMaxLength = 0
timeStampMaxLength = 0
identifierCase = Lower
escapeCharacter = \
longVarCharDatatype = -1
clobDatatype = 0
longVarBinaryDatatype = -4
blobDatatype = 0
BIP8273I: The following datatypes and functions are not natively supported by datasource 'POSTGRESQL' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, TIMESTAMP, INTERVAL, CLOB, BLOB' Unsupported functions: 'RAND, INSERT, LEFT, RIGHT, SPACE'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within IBM App Connect Enterprise rather than being processed by the database provider.
Note that "functions" within this message can refer to functions or predicates.
BIP8071I: Successful command completion.
Create a message flow in App Connect Enterprise
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, deploy and execute the flow:
