Postgresql with IBM App Connect Enterprise

Question

I am trying to use IBM APP Connect Enterprise to connect to a Postgresql Datasource and execute Database complex queries (Complex SELECT, INSERT, UPDATE Statements) All I can find is the loopback node which is using limited (select, insert and update) statements. Is there any option that I can include an esql having a PASSTHRU function as the one used with ODBC (Oracle datasources)?

Answer 1

IBM App Connect enterprise can connect to Postgresql Datasource via JDBC. It can be done using Policy Project(As configurable services are removed as part of IIB to ACE ).

Create a policy Project with the template type as JDBC providers and a template as DB2_91.

Provide database name, host and port details. An important point to notice is to provide XA driver (org.postgresql.xa.PGXADataSource) for datasource driver field.

Answer 2

You could use App Connect for the interactions with Postgresql: https://www.ibm.com/docs/en/app-connect/containers_cd?topic=examples-connecting-app-connect-postgresql

You should be able to call your App Connecto flow using a Callable Flow (via the Switch Server): https://www.ibm.com/docs/en/app-connect/12.0?topic=pecf-preparing-environment-split-processing-between-app-connect-enterprise-app-connect-cloud

Answer 3

You should be able to add a postgresql database as an ODBC database through the UnixODBC layer (i.e. add an entry to the odbcinst.ini file), it's not directly supported though, so if you find an issue you'd need to reproduce with a supported database. I haven't tried it myself yet, I should hopefully have time in the not too distant future. Searching the internet for "unixodbc postgres odbcinst.ini" gave some good results, which is where I'd start.