Using IBM App Connect Enterprise on IBM Cloud to retrieve data from an Oracle database
Learn how to use IBM App Connect Enterprise on IBM Cloud to retrieve data from an Oracle Database over the public internet.
Your company wants to process some business data stored within an Oracle database and access it over the public internet. An integration solution needs to be constructed that securely retrieves the database records and returns them to the user after some processing. In this tutorial an existing integration solution, that is a REST API, is imported to run in IBM App Connect Enterprise on IBM Cloud to do such processing.
Additionally in this scenario the communication between IBM App Connect Enterprise on IBM Cloud and the database that is hosted in the client data center will be direct and will not use the IBM App Connect Enterprise Switch Server. This means that you must make additional security considerations and configuration. For whilst traffic through the Switch Server to the agent is automatically encrypted this is not the case when the traffic is sent directly, as in this case. This additional configuration is discussed in the tutorial.
If you extend the scenario then you may choose to have the ODBC connection routed to a secure gateway facility for transmission over the internet. This is discussed at the end of this tutorial.
This scenario presents one type of usage but it can easily be extended to meet your own needs.
First, find or create everything you need:
- An Oracle Database instance with some test data.
You'll need the following information, that will be provided by Oracle Database Administrator:
- Host name of the server; for example, demodb.orainst.net.
- Port used by the database manager for TCP/IP communication; for example, 1521.
- Database name; for example, ORACLEDB.
- Database Username for example, demouser.
- Database Password.
- Since the data is flowing over the public internet, a port in the firewall must be open to allow the traffic to flow. Also, some ODBC properties such as EncryptionMethod, EncryptionLevel, DataIntegrityMethod, DataIntegrityLevel etc. must be provided to ensure the data is secured and encrypted in transit.
- Use the DDL file (OracleDB.ddl) that is located within this link CustomerDatabase-OracleDB-and-DDL.zip to create the table in the database.
- An IBM Cloud account with an IBM App Connect service that provides enterprise integration capabilities; for example, the Lite or Custom Enterprise plans.
- Optionally, you can also install IBM App Connect Enterprise for Developers if you want to look at and change the deployed flow. It is not necessary for running the tutorial, but it is useful for examining the flow to understand how it works. Note: The enterprise integration project for this tutorial was developed with the IBM App Connect Enterprise Toolkit, but can be examined with the Integration Toolkit of IBM Integration Bus V10.)
- If you want a bit more information before you start, you can read more about the App Connect Enterprise Developer edition on the following page: Get started with IBM App Connect Enterprise.
Import the integration project into IBM App Connect Enterprise, and take a look at the message flow:
You do not need to complete this step unless you want to look at or change the integration processing. A separate BAR file is also provided for deploying the integration into IBM App Connect Enterprise on IBM Cloud, as described in a later step.
All the integration service resources required for this tutorial are provided in a project interchange file named CustomerDatabase-OracleDB.zip and this file is located within the zip file CustomerDatabase-OracleDB-and-DDL.zip.. Extract CustomerDatabase-OracleDB.zip from the attached file before attempting to import the project interchange file.
The steps to import the integration processing into the IBM App Connect Enterprise Toolkit are given below:
- Download the project interchange file by clicking the link above and saving the file to a local directory.
- Open the IBM App Connect Enterprise Toolkit.
- To import the project into the toolkit, click IBM Integration, select Project Interchange, and then click Next. , expand
- Browse to select the downloaded project interchange file, and then click Finish.
- A new project called CustomerDatabase, which implements a REST service, is displayed in
the IBM App Connect Enterprise Toolkit:
You can view the REST API definition by double-clicking REST API Description.
The REST API has one subflow called getCustomers.subflow that implements a ‘GET from database’ operation. The message flow performs the following actions:
Oracle DB Getaction requests all customer data from an Oracle Database.Important:
In the Compute node, notice that the Data source value is set to ORACLEDB. You'll need to specify this same name as the name of your database policy, which you'll create later.
Configure the integration in IBM App Connect Enterprise on IBM Cloud:
- Download the zip file CustomerDatabase-OracleDB-and-DDL.zip and extract the BAR file named CustomerDatabase.bar from it.
- If necessary, sign in to IBM Cloud. From the IBM Cloud dashboard, select and then launch your IBM App Connect Enterprise on IBM Cloud service instance.
- From the IBM App Connect Enterprise on IBM Cloud dashboard, click
The integration server that is created as a result of the import is displayed in the dashboard.
select the BAR file that you extracted. Then click
You need to create an ODBC policy so that property overrides can be specified.
- To open the Policies view, click the Policies icon .
- Click New policy.
- Create an Oracle(ODBC) policy with the following details:
IBM recommends that the following values are included for all ODBC connections to an Oracle database:
- Policy name: Enter
ORACLEDB. This should be the same as the Data Source Name (DSN) defined in the Compute Node.
- Host: This should be the Oracle Database hostname provided by Oracle Database administrator.
- Port: This should be the Oracle Database port provided by Oracle Database administrator.
- Service name: This is the Oracle Service instance name provided by Oracle Database administrator.
- User name: This is the Oracle Database User name used to connect to the database and perform query and transactions.
- Password: This is the Oracle Database password used to connect to the database.
- Check the 'Connect to Oracle via the public internet' checkbox. This modifies the configuration to not use the IBM App Connect Enterprise Switch server and allow the direct connection to the database that was discussed earlier.
- Custom Properties: This is an optional section and it is used when you need to provide additional configuration information for the ODBC connection or where you need to change the default values.
CatalogOptions=0 EnableStaticCursorsForLongData=0 ApplicationUsingThreads=1 EnableDescribeParam=1 OptimizePrepare=1 WorkArounds=536870912 ProcedureRetResults=1 ColumnSizeAsCharacter=1 LoginTimeout=0 EnableNcharSupport=0
All of the above values are included by default for an Oracle ODBC Connection from IBM App Connect Enterprise on IBM Cloud instance.
Additionally the following security parameters are recommended to make the connection from IBM App Connect Enterprise on IBM Cloud to the Oracle database secure:
Note: Setting EncryptionLevel and DataIntegrityLevel to the value of 3 sets a value of Required as described in the Oracle documentation.
When setting these options within the ODBC configuration for IBM App Connect Enterprise on IBM Cloud you will also need to ensure that the corresponding settings are made to the Oracle database for the encryption to work. This configuration is not covered in this tutorial.
The level of encryption that is required is going to differ from company to company and so if you choose to apply this scenario in your own environment be sure to select settings that are appropriate to your own company's security requirements.
- Policy name: Enter
- Click Create and then return to the IBM App Connect Enterprise on IBM Cloud dashboard.
- To attach the policy to the uploaded BAR file, search for the policy name in the search bar and select 'Attach policy' from the actions.
- In the pop-up window check the 'CustomerDatabase' Integration Server and click Apply.
- Once selected, the value for 'Used by' will change from '0 integration server to '1 integration server.
- Return to the dashboard. Then start the integration by opening the options menu [⋮] for the
integration server, and then clicking Start. When the integration shows Running, the integration is
running and ready to use.
Finally, test your integration:
To test the integration you will run a GET operation on the getCustomers REST API to retrieve data from the data base that is then formatted as an XML message before being returned to the user
- From the dashboard, click on the integration server tile to open the integration.
- Click Show API Explorer.
- Only one possible API is shown . Click on that (Get /Customers) followed by clicking on 'Try It' tab.
- Click Send and success should be returned. The result should look like this:
If all of these steps work, then you have the tutorial up and running successfully. If it fails, then an error message with the reason will be returned. Check the integration log for any errors; To open the Logs view, click the Logs icon .
Connecting to Oracle Database hosted on-premises or on a private cloud using IBM Secure Gateway (Optional):
If the Oracle Database is hosted on-premises or on a private cloud (i.e. not accessible using the public internet), and you do not want to open a separate port on the firewall, then you need a service that is able to provide secure access between the different networks. IBM Secure Gateway Service is such a service. It provides connectivity to on-premise private networks or environments available in private cloud and it can be used to securely access the database. You can read more about the Secure Gateway on the following page: Getting started with IBM Secure Gateway
This diagram shows the configuration that you would use when using IBM Secure Gateway Service.
Secure Gateway Service Configuration
From the IBM Cloud, complete the following steps to configure the Secure Gateway service to connect to the Oracle database over the public internet:
- Create one gateway in an IBM Secure Gateway service instance provisioned in IBM Cloud.
- Ask the Oracle Administrator to install the Secure Gateway Client in the database environment (Oracle Cloud in the example above). See the section Secure Gateway Client Installation and Configuration below to see more details on this task.
- Share the gateway id and security token with the Oracle Administrator to configure it in the IBM Secure Gateway client. Once configured, the Gateway will be connected to the client using the provided gateway ID.
- Ask the Oracle Administrator to provide the database host name/IP and database port details, that will be configured in the destination.
- Create a destination in the IBM Secure Gateway with the database host and database port provided by the Oracle Administrator.
- In the case that an 'Access is blocked' sign appears on the destination ask the Oracle
Administrator to add the database host and database port entry in the ACL to allow the
The next step is to configure the Secure Gateway client
Secure Gateway Client Installation and Configuration
The Secure Gateway client needs to be installed on a system that is adjacent to the Oracle database. This would most likely be on an on-premise system or other cloud environment that is remote from the IBM App Connect on Cloud instance. Before installing the client you should ensure that the requirements needed to run it are met. These are listed here https://cloud.ibm.com/docs/SecureGateway?topic=SecureGateway-client-requirements.
Note: The Secure Gateway client uses outbound ports 443 and port 9000 to connect to the npm registry and the IBM Cloud™ environment. Accordingly, make sure that the fire-wall is open for both ports on the system where the Secure Gateway client is to be installed.
To install and configure the client perform the following steps;
- Install the Secure Gateway client on the required system. Follow the installation instructions that are given at https://cloud.ibm.com/docs/SecureGateway?topic=SecureGateway-client-install
- Add the gateway ID and Security token that were received from the IBM Cloud Administrator to the Secure Gateway client configuration file.
- Check the network connectivity between the system that the database resides on and the system where the Secure Gateway Client is installed.
- Add an entry of the database host and port in the Access Control list (ACL) of the Secure Gateway client.
- Start the Secure Gateway client.
- Check the logs for any errors that may have occurred. Resolve that problem and then restart the Secure Gateway client.
Additional documentation that will help when configuring and using the Secure Gateway client can be found here:
When the Oracle database connectivity is tested, the policy in IBM App Connect Enterprise on IBM Cloud can be created. In that policy, the host and port values that are specified should be the cloud generated host and port from the secure gateway destination. The rest of the information will remain same.