Database

1. Click the Connectors.

2. Search for Database in the Available Connectors and select Database.

3. Click New account.

4. Provide the action name and description for the account in the Add account page.

5. Enter the following details in the Add account wizard -

   • Name -Name for the account you want to add.

   • Description- Short description for the account you want to add.

   • Database - Database you want to connect to.

   • Driver Group - Driver group used to connect to the database. Lists the pre-bundled drivers available and the drivers uploaded in the Database connector. For more information, see Certified Databases and JDBC Driver Jars. You can upload a new JDBC driver by selecting the Add icon next to the Driver Group dropdown list. You can upload only certified jars. For more information, see Adding Driver.

   • DataSource Class - Datasource class to be used. This field specifies the name of the JDBC driver’s datasource class.

   • Server Name- Name of the server that hosts the database.

     Note: If the tenant cannot connect to the cloud database, then check the security settings of the cloud database.

   • User Name- Username associated with the account on the database server.

   • Password- Password for the specified user name.

   • Database Name- Name of the database to which you are connecting.

   • Port Number- Port number used for connecting to the database.

   • Truststore Alias - Alias of the truststore configuration. The truststore contains trusted certificates that are used to determine the trust for the remote server peer certificates. You can also add a new Truststore certificate by selecting the Add icon next to the Truststore Alias dropdown list. See more information on Work with projects .

   • Keystore Alias- Alias for the keystore configuration.

     You can add a new Keystore certificate by selecting the Add icon next to the Keystore Alias dropdown list.

     For more information, see Work with projects and How to generate a private-public key pair using OpenSSL.

     Note: Two-Way SSL is supported in Database connector. For Two-Way SSL authentication, you must configure the Keystore Alias and Truststore Alias. See more information on Overview.

   • Network Protocol- Network protocol that the connection must use when connecting to the database. Type TCP or TCPS to indicate the network protocol.

   • Other properties- Configure the Property Name and Property Value by selecting from the drop-down list or typing the name if not listed, and enter the corresponding value; click the ‘+’ button to add a new property.

     • Property Name - Name of the property. You can select the property from the drop-down list or type the property name if it is not listed.
     • Property Value - Value for the property.
     Note: Properties listed for you to select from the dropdown list are driver-dependent.

     Example 1- Use this field to choose a property such as TableFilter. You can either select or type the TableFilter property in the dropdown list and enter the <current catalog>.Accounting in the input text field. Use {} to configure a combination of multiple key-value pairs.

     Example 2 -

     *connectionProperties={oracle.jdbc.V8Compatible=true,includeSynonymns=true }

     By default, the loginTimeout is set to 60, which is the time (seconds) that a connection waits while attempting to connect to a database. The <current catalog> represents the default catalog associated with an account. The <current schema> represents the default schema associated with an account.
6. 1. Click Next. The Add account > Advance Setting page appears.
   2. Provide the following details in Advance Setting page to configure connection pooling manually -
      Note: Connection pooling is not enabled by default.

      • Enable connection pooling- Toggle the slider to the right to enable manual configuration of the connection pooling details. Disable the connection pooling to use system defined values.

      • Minimum and maximum Pool Size- Minimum and maximum connection pool size for this account.Minimum Pool Size: Number of connections to create when the connection is enabled. The system maintains the specified Minimum Pool Size of connections, irrespective of whether these connections remain idle. Maximum Pool Size- Maximum number of connections that can exist at a time in the connection pool.
      • Block timeout- Number of milliseconds that IBM® webMethods Integration waits to obtain a connection before it times out and returns an error. For example, you have a pool with maximum pool size of 20. In the event of receiving 30 simultaneous connection requests, 10 requests will be queued, awaiting a connection from the pool. If you set the Block timeout value to 5000, the 10 requests will wait for 5 seconds to establish a connection before they time out and return an error. If the services using the connections require 10 seconds to complete and return connections to the pool, the pending requests will encounter failure and return an error message stating the unavailability of the connections. If you set the Block timeout value too high, you may encounter problems resulting in error. If a request contains errors that delay the response, other requests will not be sent.

        This Block timeout must be tuned in conjunction with the Maximum Pool Size setting to accommodate such bursts in processing.

      • Expire timeout- Number of milliseconds that an inactive connection can remain in the pool before it is closed and removed from the pool. The connection pool removes inactive connections until the number of connections in the pool is equal to the Minimum Pool Size. The timer for an inactive connection is reset when the connection is used. If you set the Expire timeout value too high, the connection pool might accumulate numerous unused and inactive connections. This not only consumes local memory but also ties up a connection on your backend resource. This could have an adverse effect if your resource has a limited number of connections. If you set the Expire timeout value too low, the performance could degrade because of the increased activity of creating and closing connections.

        This Expire timeout must be tuned in conjunction with the Minimum Pool Size setting to avoid excessive opening and closing of connections during processing.

   3. Click Next.
   4. Verify the details in the Account and Other Properties tab.
   5. Click Test connection to verify the database connection. A success message appears if the connectivity is successful.
   6. Review the account details and click Enable.
   7. Click Done. This will redirect you to the Predefined Connectors page containing the list of connectors created.

The Add Driver screen gives you an option to upload a custom driver jar for a particular database. This uploaded driver can be used when you create an account.

To add a new driver

1. In the Account page, from the Driver Group, select the "+" button.

2. In the Add Driver page, select the supported Database.

3. Type the driver group name in the Driver Group.

4. Browse the jar in the Select the Driver field for a particular database. The driver jar gets uploaded for the corresponding database.

   Note:

   • You can upload only certified jars. For more information, see Certified Databases and JDBC Driver Jars.
   • The uploaded driver jars can be used across the projects in the tenant.

Pre-bundled driver jars are provided to connect to the databases like Microsoft SQL Server, Oracle, PostgreSQL. You cannot delete these pre-bundled driver group. The below table shows the driver groups and supported databases.

You can upload only the following list of certified driver jars:

To use Database connector, you must select the actions to execute. Actions allow you to connect to the database and initiate an action on the database from IBM webMethods Integration.

You call database actions to perform database operation on tables, views, or synonyms. The database actions are performed by calling JDBC APIs.

Actions are based on templates provided with Database connector. Each template represents an SQL statement for performing an action on a database. For example, use Select action to retrieve specified information from the database tables.

Creating a new action from a template is straightforward. You have to create an account for creating a new action. When creating a new action, select the action template and configure the action using the wizards.

Database connector provides the following action templates:

• Select: Retrieves specified information from the database table.

• Insert: Inserts new information into a database table.

• Update: Updates the existing information in a database table.

• Delete: Deletes rows from a database table.

• Stored Procedure: Calls a stored procedure. It obtains the stored procedure's input/output parameters by introspecting when you configure the action.

• Batch Insert: Inserts new information into a database table. Use this action when you insert a large volume of data into a single table.

• Batch Update: Updates the existing information in a database table. Use this action when you update a large volume of data in a single table.

• Custom SQL: Defines and executes custom SQL to perform database operations. You can run almost any SQL statement required, such as data management statements and data definition statements. The following table describes the restrictions when creating an SQL query statement:

  • Specify the table alias along with the table name if the SQL query contains more than one table.

    select d.deptno, e.empno from dept d,emp e where d.deptno = ?

  • Use different column name alias for columns in the SQL query.

    select firstname as f_name, lastname as l_name from emp

  • Column names in the SQL query should not be enclosed in quotes.

    select partno as partno from emp where partno=1

  • Use the "as" keyword when you specify an alias for a column.

    select city as dummy from emp

  • Irrespective of the database type, the query syntax must follow the SQL standards.

    insert into example_default_now set id=?, data=?

• Dynamic SQL: Configures a dynamic SQL statement, part of which you set at run time using input fields. At run time, the Dynamic SQL action will create the SQL statement by combining the contents of the input fields and then executing it. This is useful when you need the flexibility to set all or part of a SQL statement at run time, instead of at design time. DynamicSQL uses $ to map a part of the SQL statement to the input field. At design time, the action template generates an input field with INPUT_FIELD_NAME. At run time, the Dynamic SQL action parses the statement and replaces the $ with the actual contents of the input field. For variable names, use the ? variable placeholder for each variable. For example:

select employee_name where StaffID = ? and Dept = ?

Example 1:

select * from table1 ${where} 

At run time, the *where* input field is set to *where col1 > 100*. The final SQL query will be:

select * from table1 where col1 > 100 

Example 2:

select * from table1 ${where}

At run time, the *where* input field is set to *where col1 > ?*. As you are using placeholder *?* to receive input value for *col1*, you need to configure an input field to set value for column *col1*. The final SQL query will be:

select * from table1 where col1 > ?

Example 3:

<pre><code>select ${output_fields} from table1 ${where}</code></pre><br/>

At run time, the *where* input field is set to *where col1 > 100 * and *output_fields* input field is set to * col1,col2,col3 *. The final SQL query will be:

select col1,col2,col3 from table1 where col1>100

The following options provide additional capabilities for Database operations: