IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > JDBC > Configure the module for deployment > Configure the module for outbound processing > Selecting and configuring business objects

Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

To select and configure business objects for tables, views, and synonyms or nicknames for use in your module, you specify the configuration properties for the business object.

To perform this task, you need to understand the structure of the data in the database and know what database objects the module needs to access. Specifically, you need to know the following information:

This task is performed through the external service wizard.

You start in the Find Objects in the Enterprise System window and then work in a Specify the Configuration Properties for 'object' window that is specific to the business object you are configuring.


Procedure

  1. In the Discovered objects list of the Find Objects in the Enterprise System window, select one or more tables, views, or synonyms and click the > (Add) button to add the object or objects to the Selected objects list.

    1. The following two figures display a typical Specify the Configuration Properties for 'object' window for a table, view, synonym, or nickname business object. The first figure displays a typical window for the first table or group of tables that you select.

      The following figure displays a typical window for subsequent tables you select. After you select and configure at least one table, the Specify the Configuration Properties for 'object' window for subsequent tables displays an area where you can optionally define a parent-child hierarchy between tables.

      As you configure the object, choices that require advanced configuration might present additional fields in this window, causing the window to scroll. Be sure that you examine all fields on the window before you click OK.

    2. The following figure displays a typical window for enabling the overwrite of database schema name at run time:

      The following are the important points to be taken care to connect to a new schema:

      • This feature is applicable for DB2 and Oracle databases only.

      • For schema change, the environment variable must be declared at the server level in the IBM BPM or WebSphere Enterprise Service Bus.

      • If multiple tables are selected from multiple schemas, a new environment variable must be defined for every change in the schema name.

      • The environment variable defined for a schema is applicable for all the objects under that schema. When a new environment variable is created, you must restart the server for the variable to be available for the applications.

      • You must not change the business object name while storing the event details in the event table.

    3. If multiple tables are selected from multiple schemas, for every change in the schema name a new environment variable needs to be defined.

  2. To define environment variables for tables, views, and synonyms under the database schemas:

    1. Select the Overwrite the Schema Name at runtime using the WAS Environment variable check box to enable overwriting of the schema name at run time. The Environment Variable Name field is now available for editing.

    2. Specify the environment variable name.

      The environment variable defined for a schema is applicable for all the objects under that schema. If multiple tables are selected from multiple schemas, multiple environment variables need to be defined. The value of the environment variable name contains the new schema name. Use the environment variable name to specify the target schema name. You must restart the server in order to view the environment variable in the administrative console. For more information about enabling the adapter to connect to a new schema at run time, see Enabling IBM WebSphere Adapter for JDBC to connect to a new schema at run time.

  3. If the table has a column used to indicate logical deletes:

    1. Select the column name in the Name of the column used to perform logical deletes field.

    2. In the Value used to indicate a deleted object field, type the value that indicates that a row is logically deleted. You can get this value from your database administrator.

  4. If the Select primary key for table table_name area is displayed, click Add, select the column to be used as the primary key for the table business object, and then click OK.

    If the table has a composite key, you can select multiple columns. The Select primary key for table table_name area is displayed only when the database table does not have a column designated as the primary key. Each table business object must have a primary key, even if the associated database table does not have a key. If the primary key is defined in the database, this section of the window is not displayed.

  5. Optional: Define a parent-child relationship between business objects.

    To build a parent-child hierarchy, configure the parent table first, and return to the Find Objects in the Enterprise System window to select and configure the child tables.

    Configure the parent-child relationship using the area of the Specify the Configuration Properties for 'object' window shown in the following figure. These fields are not displayed for the first table you configure.

    1. In the Choose parent table field, select the name of the parent table you are configuring. If you do not see the parent table in the list, the parent table has not yet been configured. Go back and configure the parent object before configuring the child objects.If you have defined a foreign key reference in the database, the adapter automatically discovers and displays the parent-child relationship between the tables after you select the parent table. If the table has a single-cardinality relationship with the parent table, the Single cardinality check box is automatically selected.

    2. Specify the cardinality of the relationship:

      • If the table has a single-cardinality relationship with the parent table, select the Single cardinality check box. In a single cardinality relationship, a parent can have only one child business object of this type. A single-cardinality relationship can be used with ownership to represent a true child or without ownership to represent lookup tables or other peer objects in a database.

      • If the table has a multiple-cardinality relationship, do not select the Single cardinality check box. In a multiple-cardinality relationship, a parent can have an array of child business objects of this type.

    3. Build the foreign key relationship between the parent and child by specifying for each child column whether it is a foreign key in the parent table.

      • If the child column is not a foreign key, select NONE.

      • If a child column is a foreign key, select the column in the parent table that corresponds to the child column.

        The wizard can configure only a single parent table. If the child table has multiple parent tables, use the business object editor to configure the remaining parent tables after exiting the wizard.

    4. If the parent object owns the child object, then the child objects in the database are deleted when the parent is deleted. To indicate that this child is owned by its parent, select the Parent object owns child object (cascade delete) check box. Otherwise, clear this option to prevent child objects, such as lookup tables, from being deleted when their parent is deleted.

    5. If you do not want child objects to be deleted as part of an Update operation, select the Preserves child_table_name when the parent is updated check box.

      When a parent table is updated, the adapter compares the child business objects present in the input with the child business objects returned from the database. By default, the adapter deletes any child objects returned from the database that are not present in the input business object.

    6. By default, you can perform operations on parent business objects without specifying the child business objects. If you want to ensure that a parent business object specifies its child business objects when the parent is submitted for a change, select the Child_table_name required for operations on parent check box.

  6. An operation can be performed using either a standard SQL statement generated by the adapter or using stored procedures or stored functions from the database. If you want to use stored procedures or stored functions:

    1. Click Add.

    2. In the Add window, select the type of the stored procedure you want to run. For each operation, you can select a stored procedure that performs the operation, as well as stored procedures that run before or after the operation.

      For example, for the Create operation, you can specify any of these stored procedures: CreateSP, BeforeCreateSP, and AfterCreateSP.

      If you configure the table with RetrieveAllSP, ensure that the stored procedure returns only one result set. Set the ResultSet ASI for the stored procedure to true to avoid any of these exceptions being generated at run time: No resultset found associated with the stored procedure, No resultset returned or More than one resultset returned.

      1. For Oracle database, WebSphere Adapter for JDBC supports stored procedures with OUT parameter of Cursor type only. It does not support stored procedures with IN or INOUT parameter of Cursor type.

      2. For DB2 and MSSQLServer databases, WebSphere Adapter for JDBC does not support stored procedures with IN, OUT, and INOUT parameters of Cursor type.

    3. Click OK. The Specify the Configuration Properties for 'object' window now shows the stored procedure types you selected and expands to display an area where you configure each one. It might be necessary to scroll down to see the new areas.

      In a hierarchical business object, if you want the stored procedure to be performed for each business object in the hierarchy, you must separately associate a stored procedure with the top-level business object and each child business object or array of business objects. If you associate a stored procedure with the top-level business object but do not associate it with each child business object, then the top-level business object is processed with the stored procedure, but the child business objects are processed using the standard SQL query.

  7. For each stored procedure type that you selected, specify the name of the stored procedure in the database and then configure the business object.

    1. In the Schema name field, select the name of the schema that contains the stored procedure.

    2. Specify the name of the stored procedure or stored function.

      1. In the Stored procedure name or pattern field, either type the name of the stored procedure or stored function, or type a name pattern. Use the question mark or underscore (? or _ ) to match a single character and the asterisk or percentage sign (* or %) to match multiple characters.

      2. In the Stored procedure name field, select the name of the procedure you want.

      The Specify the Configuration Properties for 'object' window expands to provide an area where you configure the stored procedure. The wizard automatically generates the list of parameters by examining the stored procedure in the database.

    3. For each parameter in the stored procedure (on the left), select the table column (on the right) to pass to the stored procedure in that parameter. The following figure shows a portion of the window after a stored procedure has been configured.

  8. To specify the data type mapping for each column in the table:

    1. Click Advanced.
    2. Expand Table columns. For each column in the table, the default data type mapping is displayed. For Oracle databases, if the table contains any user-defined or complex data type such as an array, structure, nested structure or table, the type name and the child attribute details are also automatically discovered and displayed. The following figure displays the type name and child attribute details of an Oracle table containing complex data types.

    3. Review the mapping and make changes if required.

  9. When all fields in the window are completed, click OK to save the configuration of the business object. The table, view, synonym, and nickname business objects you defined are now listed in the Find Objects in the Enterprise System window.

  10. To change the configuration of an object from the Selected objects list, select the object name and then click the

    (Edit) icon.


What to do next

In the Find Objects in the Enterprise System window, continue to select and configure other types of business objects.

When you are finished, click Next to set global properties and configure wrapper business objects.

Selecting and configuring business objects


Related concepts:

Business object hierarchies

Business objects

Database tables with multiple parent tables

Stored procedure business object overview

Business objects

Create operation

Update operation


Related tasks:

Selecting and configuring stored procedures and stored functions

Selecting and configuring batch SQL business objects

Selecting and configuring query business objects


Related reference:

Business object information

Business object attributes

Solutions to common problems

Business object attributes