Message Image  

Connecting integrations to JDBC endpoints on App Connect

 View Only
Thu July 23, 2020 06:03 AM

Published on January 15, 2019

There are two recent changes to App Connect Enterprise which now enable users of the cloud service to connect directly (without any need for an additional software installation) to JDBC based endpoints.

  • JDBC Jar files can now be placed into an application. Instead of placing the jar files within the product installation, you can now supply jar files within the application
  • Connection information can now be defined within a “JDBC Policy” which can reference jar files within the application and also contain all connection details such as usernames and passwords

Below is an example configuration showing how to achieve this.

Pre-requisites

Sample Application

Download the attached Project Interchange file - JDBCinACE.zip, which you can import into your App Connect Enterprise Toolkit. This file contains two projects:

  • A simple Application project named JDBCinACEoC containing a message flow named JDBC.msgflow with the message flow nodes HTTP Input -> Java Compute -> HTTP Output. This message flow queries a database and returns the response to the user.
  • A Policy project named DefaultPolicies containing a JDBC policy named BLUDB.policyxml for connecting to DB2 on Cloud

Configuration

  • Into your App Connect Enterprise Toolkit, import the contents of the attached Project Interchange file JDBCinACE.zip
  • Import the JDBC jar file db2jcc4.jar which you downloaded into the application JDBCinACEoC
  • Open your DB2 on Cloud instance and find out your security credentials:
    • Select the “Service credentials” section on the left menu
    • If you have not created any credentials before:
      • Select “New credentials”
      • Select “Add”

      Screenshot_2020-06-03_at_10_58_30.png

    • Select the “View credentials” drop down on the credential. This should present a number of fields. Please make a note of the following:
      • hostname
      • password
      • port
      • db
      • username

      Screenshot_2020-06-03_at_10_59_19.png

  • In your App Connect Enterprise Toolkit, open the JDBC policy named BLUDB.policyxml and edit the following parameters
    • “Name of the database” should contain the value from the db field shown in the above screenshot, for example BLUDB
    • “Connection URL format” should contain values from multiple fields shown in the above screenshot in the format “jdbc:db2://
    • “Database server name” should contain the value from the hostname field shown in the above screenshot, for example dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net
    • “Database server port number” should contain the value from the port field shown in the above screenshot, for example 50000
    • Make sure that “User JAR files that have been deployed in a .bar file” is set to true

    Screenshot_2020-06-03_at_11_00_00.png

  • Create a new bar file with both the application and policy. Once you have created the bar if you select the “Manage” tab you should see its contents like the following screenshot

Screenshot_2020-06-03_at_11_01_18.png

Testing

    • Deploy and start the created bar file to the App Connect (enterprise plan).
    • Open the integration in the cloud ui and make a note of the HTTP input URL

Screenshot_2020-06-03_at_11_02_15.png

    • Click on the “Credentials” link and then “Copy credentials” to copy the credentials into your clipboard

Screenshot_2020-06-03_at_11_02_56.png

  • Use an application such as curl to test your application i.e. curl -u iib:<password> https://tgmesftb.ace.ibm.com/jdbc. This should result in a response such as: “<Results><Customer><Number>10</Number><Name>Rob</Name></Customer><Customer><Number>20</Number><Name>Dave</Name></Customer></Results>“

3 comments on"Connecting integrations to JDBC endpoints on App Connect"

  1. Feliciano Ramos May 31, 2019

    Hi, I found this small tutorial about JDBC drivers to connect to DB2. Is it to connect to DB2 Databases on-premises? I am trying to connect from APP Connect to a DB2 database on-premises and I only found this one. I was also searching a way to do it through API Connect and IBM Cloud Functions, but can’t find anything :/ (just found a way on API Connect through a connector, but I realized is using ODBC instead :/).
    If this is really for the purpose I mentioned above, is there a way to configure it without having to install the toolkit on my local machine?

    Thank you!

    Reply (Edit)
  2. ArisVS January 15, 2019

    Thanks! I figured this out by myself just today:| Is it necessary to use always DefaultPolicies in ACE on Cloud as a default PolicyProject?

    Reply (Edit)
    • BenThompsonIBM January 25, 2019

      Hi Aris,

      Glad to hear that you have been using this new feature. Regarding your question about “DefaultPolicies” … I guess the original article should have been clearer: No, you can choose any name you like when defining the name of your Policy Project in the App Connect Enterprise Toolkit, it is just this example happened to use a project interchange file which had a policy project of this name. This Policy Project, and the policy it contains, are placed entirely under your control because you can choose in your Application and Flow which Policy / Policy Project you wish to refer to, and deployment of both the Policy Project and the Application and Flow are all handled in the BAR file itself.

      In the Cloud service, you can also use the menus to define policies – this part of the cloud service was available when we were still branded as IBM Integration Bus on Cloud, and actually pre-dates the ACEv11 software capabilities in the area of policies. These policies, which are defined exclusively through the UI (web browser) of the cloud service, result in additional definitions being placed into the ACE containers which the service starts up on your behalf when you deploy your BAR files.

      This means that as a user, you now have two separate ways available to get configuration information into your running containers – both the very controlled way through our web browser UI, and a much more free-form method (discussed in this JDBC example) of using policies inside policy projects which are carried inside the BAR files which you upload. For scenarios like this one, where you also want to carry a JDBC driver into the service as well, the more free-form method gives you many more functional opportunities!

      Cheers,
      Ben

      Reply (Edit)

#JDBC
#policy
#AppConnectEnterprise(ACE)
#Db2onCloud
#DASHDB