Using IBM App Connect Enterprise on IBM Cloud to retrieve data from an Oracle database

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.

• 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.

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.

• Click here to read the detailed steps to import the project interchange file

  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:

  1. Download the project interchange file by clicking the link above and saving the file to a local directory.
  2. Open the IBM App Connect Enterprise Toolkit.
  3. To import the project into the toolkit, click File > Import, expand IBM Integration, select Project Interchange, and then click Next.
  4. Browse to select the downloaded project interchange file, and then click Finish.
     

     (Click image to view full size.)

  5. A new project called CustomerDatabase, which implements a REST service, is displayed in the IBM App Connect Enterprise Toolkit:
     

     (Click image to view full size.)

     You can view the REST API definition by double-clicking REST API Description.

     

     (Click image to view full size.)

     The REST API has one subflow called getCustomers.subflow that implements a ‘GET from database’ operation. The message flow performs the following actions:

     

     (Click image to view full size.)

     The Oracle DB Get action 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.

     

     (Click image to view full size.)

1. Download the zip file CustomerDatabase-OracleDB-and-DDL.zip and extract the BAR file named CustomerDatabase.bar from it.
2. 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.
3. From the IBM App Connect Enterprise on IBM Cloud dashboard, click New > Import a BAR file and select the BAR file that you extracted. Then click Import.
   

   (Click image to view full size.)

   The integration server that is created as a result of the import is displayed in the dashboard.

   

You need to create an ODBC policy so that property overrides can be specified.

1. To open the Policies view, click the Policies icon .
2. Click New policy.
3. Create an Oracle(ODBC) policy with the following details:
   1. Policy name: Enter ORACLEDB. This should be the same as the Data Source Name (DSN) defined in the Compute Node.
   2. Host: This should be the Oracle Database hostname provided by Oracle Database administrator.
   3. Port: This should be the Oracle Database port provided by Oracle Database administrator.
   4. Service name: This is the Oracle Service instance name provided by Oracle Database administrator.
   5. User name: This is the Oracle Database User name used to connect to the database and perform query and transactions.
   6. Password: This is the Oracle Database password used to connect to the database.
   7. 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.
   8. 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.
   IBM recommends that the following values are included for all ODBC connections to an Oracle database:

   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:

   • EncryptionTypes=AES128,AES192,AES256,RC4_40,RC4_56,RC4_128,RC4_256,DES,3DES112,3DES168
   • EncryptionLevel=3
   • DataIntegrityTypes=SHA1,MD5
   • DataIntegrityLevel=3
   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.

   

   (Click image to view full size.)

4. Click Create and then return to the IBM App Connect Enterprise on IBM Cloud dashboard.
5. 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.
   

   (Click image to view full size.)

6. In the pop-up window check the 'CustomerDatabase' Integration Server and click Apply.
   

   (Click image to view full size.)

7. Once selected, the value for 'Used by' will change from '0 integration server to '1 integration server.
   

   (Click image to view full size.)

8. 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.
   

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

1. From the dashboard, click on the integration server tile to open the integration.
   

   (Click image to view full size.)

2. Click Show API Explorer.
   

   (Click image to view full size.)

3. Only one possible API is shown . Click on that (Get /Customers) followed by clicking on 'Try It' tab.
   

   (Click image to view full size.)

4. Click Send and success should be returned. The result should look like this:
   

   (Click image to view full size.)

   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 .

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.

(Click image to view full size.)