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.
- You will need to populate DB2 on Cloud with some sample data. To do this, you can follow the Populating Db2 on Cloud section of the recently published tutorial Using IBM App Connect enterprise capabilities with IBM Db2 on Cloud
- You will need to download the DB2 Client jar, db2jcc4.jar
Download the attached Project Interchange file, 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
- Into your App Connect Enterprise Toolkit, import the contents of the attached Project Interchange file JDBCinACE
- 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”
- Select the “View credentials” drop down on the credential. This should present a number of fields. Please make a note of the following:
- 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://
: / :user= ;password= ;â€ť, for example jdbc:db2://dashdb-txn-sbox-yp-lon02-01.services.eu-gb.bluemix.net:50000/BLUDB:user=hbg87jy8yg;password=oijoijij98uo8987
- â€ś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
- 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
- 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
- Click on the “Credentials” link and then “Copy credentials” to copy the credentials into your clipboard
- 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>“