Creating an Oracle data source

How to create an Oracle data source. When you define an Oracle data source, the connection can be ODBC or JDBC.

Before you begin

To create an Oracle data source, the DataPower® Gateway requires different features based on your server configuration.
  • Basic support requires the SQL ODBC feature.
  • Support for Oracle complex data types requires the following features.
    • Database Connectivity feature
    • SQL ODBC feature
    When Oracle object support is enabled to support the following Oracle objects, the DataPower Gateway runs the SQL configuration in a runtime environment. To define the requirements for Oracle object support, configure the SQL runtime settings in the default domain. For more information, see Tuning SQL runtime settings.
    • OBJECT
    • VARRAY
    • TABLE
  • Support for Oracle Real Application Clusters (RAC) Fast Application Notification (FAN) events requires the following features.
    • Database Connectivity feature
    • Extended Oracle support for the Database Connectivity feature
    • SQL ODBC feature

    Oracle RAC FAN uses ancillary server. Ancillary servers provide high availability and load distribution. Define each ancillary server to specify a remote Oracle Notification Services (ONS) daemon on an Oracle RAC node. For more information, see SQL data source for an Oracle RAC.

    Attention: For an Oracle JDBC data source, the connection requires hostname validation when the connection validates the server certificate. The hostname in the certificate is the Subject Alternative Name (SAN), not the Common Name (CN). If you do not specify the hostname that the certificate must contain, hostname validation uses the value of the Data source host property.

Procedure

  1. In the search field, enter sql.
  2. From the search results, click SQL data source.
  3. Click Add.
  4. Define the basic properties - Name, administrative state, and comments.
  5. From the Database type list, select Oracle.
  6. Set the Enable object support - Oracle property to enable support for Oracle objects.
  7. Define connection details.
    1. In the Connection username field, enter the username to establish the connection.
      The data server maintains this information, not the DataPower Gateway.
    2. From the Connection password alias list, select the password alias.
    3. From the Data source type - Oracle list, select the identifier type for the Oracle data source.
      Service name
      The identifier is an Oracle service name. The service name is a domain-qualified database name. This global database name is defined during installation or database creation. Use this value to connect to Oracle Real Application Clusters (Oracle RAC).
      SID
      The identifier is an Oracle system identifier (SID). The SID is a unique name for an Oracle database instance. This setting is the default value.
    4. In the Data source ID field, enter the database alias, the database name, or the identifier for the data source.
      This property depends on the type of database.
    5. In the Data source host field, enter the IP address or hostname of the data server of the data source.
    6. In the Data source port field, enter the listening port of the data source.
  8. Optional: In the Ancillary servers - Oracle area, define the servers for high availability or load distribution.
    When you define an ancillary server, you must specify its IP address or hostname, its listening port, the type of ancillary server, and its service name or SID. The type of ancillary server is one of the following choices.
    • Oracle listener
    • Oracle ONS server
    • Unspecified
  9. Optional: Enable TLS.
    1. From the Encryption method - Oracle lists, select TLS.
    2. From the Validate server certificate list, select whether to validate the server certificate.
    3. In the Hostname in certificate field, enter the hostname that the certificate must contain for hostname validation.
      Hostname validation provides extra security against man-in-the-middle (MITM) attacks by ensuring that the connection is to the requested server.
    4. From the Truststore list, select the identification credentials for client authentication.
    5. From the Keystore list, select the validation credentials for server authentication.
  10. Optional: Define limits for the data and connections.
    1. Set the Limit returned data property to limit the data size limit that a SQL SELECT statement can return. Without an explicit limit, the size is 128 KB.
    2. In the Returned data size limit field, enter the data size limit.
    3. In the Max connections field, enter the maximum number of concurrent connections to allow.
  11. Optional: Define timeouts.
    The connection timeout must be less than the query timeout. With this configuration, the initial query has time to establish the connection to the remote data server.
    1. In the Connection timeout field, enter the duration in seconds that the DataPower Gateway waits to establish a new connection to the remote data server. The minimum value is 0, which disables the timeout. The default value is 15.
    2. In the Query timeout field, enter the duration in seconds that the DataPower Gateway waits for an SQL request to complete. The measured duration is from when the DataPower service sends the request to and receives the results from the remote data server. The minimum and default value is 0. The value of 0 uses the standard timeout in the user agent.
    3. In the Idle connection timeout field, enter the duration in seconds that a connection from the connection pool can remain idle before the DataPower Gateway releases the connection. The minimum value is 0, which disables the timeout. The default value is 180.
  12. Optional: Click the Advanced configuration parameters tab to define extra valid connection configuration parameters.
  13. Click Apply to save changes to the running configuration.
  14. Click Save to save changes to the persisted configuration.