Creating or Editing a Database Activity Endpoint

Endpoints provide the database connection information that Studio and the Integration Appliance use to connect to a database.  

Select or edit a database endpoint

  1. In the Create Endpoint or Edit Endpoint pane, configure the fields as described in the following tables.
    Note: Changing the configuration properties of an endpoint affects all orchestrations that use that endpoint in the project. The edits are global, not local.
  2. Click Test Connection to confirm that you can connect to the database. Click OK.

Database options

Table 1. Database options
Field Description
Database Type Specifies the database type. If you select the Generic JDBC driver or MySQL, you must install the module providers into Studio. To add JDBC drivers into Studio, you add them to the Web Management console and then complete the following steps in the WMC.
  1. Go to Settings.
  2. Select Design.
  3. Select Libraries Environment and set it to the environment that you loaded the compressed files into.
  4. Restart Studio.
Note: For JDBC drivers to access database endpoints for App Connect Generic JDBC support, the JDBC driver must be:
  • Type 4.
  • Must implement the JDBC 2.0 or greater specification.
  • Must provide support for the DataSource feature API.
Additional Parameters may be required for a Generic JDBC Driver. You should check the documentation for the driver you intend to use to see if any additional parameters are required.
For more information about installing the JDBC libraries into the WMC, see the WMC Online help.
Database Name Specifies the name of the Database to which you want to connect. If connecting to Oracle RAC, see For Oracle RAC:.
Note: For AS/400 DB2® databases, this is the collection name defined during installation.
Network Location: Server Specifies the name or IP address of the machine where the database server resides. For Informix®, log into the Informix Server Administrator and select the Configuration > SQLHOST menu option to find the hostname. For Sequelink, enter the name or the IP address of the Sequelink server.
Network Location: Port Specifies the port number of the database server listener. For Sequelink, enter the port number of the Sequelink server listener.
User Name Specifies the username that the Integration Appliance uses to connect to the database.
Note: For DB2, the user must have the permissions to create a package.
Password Specifies the password associated with the specified username.
Additional Parameters Specifies the database-specific information for an endpoint. See Working with Database Parameters for details. By default, the MaxPooledStatements parameter is automatically defined and set with a default value for all types of Database Endpoints.
Note: For some of the fields in the endpoint, you can define configuration properties to supply the values for these fields.  The
icon displays when you click in a field that supports a configuration property and a configuration property of the same type has already been configured for the project.

For Informix Databases

To find the port number for an Informix database:
  1. In an editor, open the following file: $INFORMIXSERVER/etc/sqlhosts
  2. Note the servicename found in the SERVER column.
  3. In an editor, open the appropriate file for your system:
    • UNIX - /etc/services  
    • Windows - \Winnt\system32\drivers\etc\services
  4. In the file, search for the servicename to find the associated port number.

For DB2 AS/400 Databases

For some EBCDIC-US columns on DB2 AS/400, Studio cannot determine the correct size and type.
To pass an EBCDIC-US encoded parameter:
  1. In Studio, open the orchestration containing a Database Connector activity.
  2. Change CHAR type columns to VARBINARY type. The EBCDIC-encoded columns display as CHAR type columns in the data type of the outbound connector.
  3. Use a map before the Database Connector to pad an EBCDIC-encoded parameter with spaces, so that the value of the parameter equals the length of the column.

    For example, if the column length is 10 and the value is "abc", pad the column with 7 spaces.

    If the EBCDIC-encoded value is not padded with spaces, the Database Connector pads the value with NULL. This can create inconsistent data and errors can occur.

To read an EBCDIC-US encoded column from a result set:
  1. In Studio, open the orchestration containing the Database Connector Parameterized Query operation.

    When you use a parameterized query to select an EBCDIC-encoded column from a database, the Database Connector returns values padded with spaces if the value is shorter than the column length.

    For example, if the column length is 10 and the value is "abc", the Database Connector pads the column with 7 spaces.

  2. Create a map after the Parameterized Query operation to trim the padded spaces from the XML result set.

For Oracle RAC:

App Connect supports additional properties in the connection string that enable Oracle RAC connectivity. Use the following additional  properties to connect with Oracle RAC:
  1. AlternateServers=(servername1[:port1]
    [;property=value[;...]],
    servername2[:port2]
    [;property=value[;...]],...) 

    Where:

    servername is the IP address of the server

    port is the port number of the server

    property=value enter the ServiceName which is used as the Database Name. Entering a SID is optional.

  2. LoadBalancing=true|false

    Determines whether the driver will use client load balancing in its attempts to connect to a list of database servers, primary and alternate. The list of alternate servers is specified by the AlternateServers property. If set to true, client load balancing is used and the driver attempts to connect to the list of database servers, primary and alternate servers, in a random order. If set to false (the default), client load balancing is not used and the driver connects to each server based on their sequential order (primary server first, then, alternate servers in the order they are specified).

  3. Use the service name in the Database Name of the end-point.

Connection Pool, Encoding, TimeOut, and Security Options

Default values are provided for the connection pool options. Change them as needed.
Table 2.
Field Description
Connection Pool Options  
Initial Pool Size Specifies the initial number of database connections that the Integration Appliance opens and places in the connection pool.
Minimum Connections Specifies the number of database connections that the Integration Appliance always keeps open in the connection pool. Specifying 0 is supported.
Maximum Connections Specifies the maximum number of database connections that the Integration Appliance can have open in the connection pool. Specifying 0 means the number of connections is unbounded.
Maximum Idle Time Specifies the amount of time (in minutes) that a database connection can be open and idle before being closed by the Integration Appliance. Specifying 0 means that the connections are closed when they are returned to the pool - no idle time.
Reclaim Connections Specifies how often the Integration Appliance checks the connection pool to determine if any connections should be closed. For example, if the Reclaim Connections is set to 6 minutes, the Integration Appliance checks the connection pool every six minutes to determine if the total number of open connections in the pool are greater than the minimum number of connections. If the number of open connections are greater than the specified minimum number, the Integration Appliance closes any excess connections (over the number of Minimum Connections) for any connections that have exceeded the Maximum Idle Time.

For example, the Minimum Connections is set to 2, Maximum Connections is set to 6, the Reclaim Connections is 6 minutes, and the Maximum Idle Time set to 2 minutes. A total of five connections are currently open in the connection pool. When the Reclaim Connections of 6 minutes is reached, the Integration Appliance checks the connection pool and determines that one connection has been idle for 3 minutes, one connection has been idle for 4 minutes, and three connections are still active. The Integration Appliance closes the two idle connections. After another 6 minutes, the Integration Appliance checks the connection pool again.

Default Encoding  
Encoding   Specifies the default character Encoding to use when connection to this database. To set the encoding type, choose from one of the following options:
  • From the Encoding drop-down list, select a default encoding type.  
  • Enter your encoding type directly by clicking in the Encoding field and typing in your encoding type.
Note: Some double-byte characters are not converted using the SHIFT-JIS encoding. For more information, see Using the Shift-JIS encoding.
Quoted Identifiers  
Delimit the identifiers for tables, columns, and objects with quotes Specifies if quotes are used around the identifiers for database tables, columns, and objects in the generated SQL statements that the Integration Appliance and Studio uses to communicate with the database. Quoting an identifier makes the identifier case-sensitive. For example, the identifier string: myTable could refer to table named mytable or MYTABLE but the identifier string with quotes: "myTable" only refers to a table named myTable. Quoting an identifier also allows characters not normally found in database names such as spaces.
Security  
Enable Encryption Specifies that the connection should use SSL.
Validate Server Certificate Specifies that the client should validate the database server certificate. See Testing an SSL Database Connection.
Require Client Authentication Specifies that the client present a certificate to the database server.
Connection Timeout  
Time out after ___ second(s) when establishing a connection to the Endpoint. Specifies the amount of time in seconds before a timeout occurs between the Integration Appliance attempting to establish a connection to the Endpoint and the time it takes for the Endpoint to respond to the connection request. For a database activity, the amount of time the Integration Appliance attempts to establish a connection to the database before timing out. A connection timeout is different from an activity timeout because an activity timeout occurs after the connection to the Endpoint has already been established. An activity timeout specifies the amount of time in seconds before a timeout occurs between the activity invoking a request on an Endpoint and the time it takes the Endpoint to respond. You set the activity timeout in Retry task of any of the following database activities:



Feedback | Notices


Timestamp icon Last updated: Tuesday, 27 September 2016


http://pic.dhe.ibm.com/infocenter/wci/v7r0m0/topic/com.ibm.wci.doc/db_creating_editing_a_database_activity_endpoint.html