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

Discovering database objects for outbound processing

After connecting to the database, run a query to search for the database objects. Browse the tree of discovered objects to understand the structure of objects in the database and use filters to display only the database objects you want to see. Define how many business objects you want to create for user-defined database queries and for user-defined batch SQL statements.

You must understand the data requirement of the program that needs to access the database.

For example, you need the following information about the database:

This task starts in the Find Objects in the Enterprise System window of the external service wizard.


Procedure

  1. In the Find Objects in the Enterprise System window, click Edit Query. The Specify the Query Properties window is displayed.

    Use the Specify the Query Properties window to perform the following tasks:

    • Reduce the search time by searching a subset of database schemas
    • Omit one or more types of database objects from the search criteria
    • Make the wizard prompt you for application-specific information that cannot be automatically determined based on information in the database

    • Specify the number of query and batch SQL business objects you want to create
    • Map the JDBC data types; Date, Time and Timestamp to date, time, and dateTime

    In version 6.2.x with fix pack 2, this window also allowed you to specify the number of wrapper business objects you want to create. Starting in version 7.5.0.2, the wizard prompts for wrapper information at a later time.

  2. To limit the number of database schemas that are retrieved, type the name of the schema or a name pattern in the Schema name or pattern field. Use the question mark or underscore (? or _) character to match a single character and the asterisk or percentage sign (* or %) to match multiple characters. Only schemas that start with that string or match that pattern are displayed when you run the query. If you do not specify a schema name pattern, all schemas in the database are displayed. Using a filter can speed up the discovery process, if your database contains many schemas.

  3. To omit one or more types of objects from the search, select the types of objects that you want to omit (tables, views, stored procedures and stored functions, and synonyms or nicknames) in the Supported database object types field, and click Remove. To add the object type back, click Add. If you need to access only specific types of database objects, omitting the ones you do not need can speed up the discovery process.

  4. The table, stored procedure, and stored function objects with the date, time and timestamp data types are mapped to the string data type by default. To map these objects to the actual data types that are supported by the JDBC driver such as the date, time, and datetime data types, select the Map JDBC Date/Time/Timestamp types to date/time/dateTime check box.

    The default data type mapping differs based on the different JDBC driver versions, for example, when using the Oracle JDBC driver, the Date data type is mapped to dateTime data type instead of mapping it to date. In such cases, the appropriate data type must be manually selected in the Specify the Configuration Properties for 'object' window.

  5. Select the Prompt for additional configuration settings when adding business object check box. Then, when you add a database object to the list of business objects to create, the wizard automatically prompts you for all user-configurable application-specific information for the object.

    For example, if you select this option, the wizard guides you through the process of building a simple parent-child hierarchy of business objects.

    Select this check box to enable the adapter to dynamically pick the database schema name at run time. For more information about overwriting the database schema name at run time, see Selecting and configuring tables, views, and synonyms or nicknames for inbound processingand Selecting and configuring tables, views, and synonyms or nicknames for outbound processing. For more information about enabling the adapter to connect to a new schema name at run time, see Enabling IBM WebSphere Adapter for JDBC to connect to a new schema at run time.

    If you need a hierarchy such that a table business object has two attributes which are referring to attributes in two different tables (that is, it has two parent business objects), complete the configuration in the assembly editor, a tool that is launched from IBM Integration Designer. In addition, if a foreign key reference is defined in the database, the adapter automatically discovers and displays the parent-child relationship between the tables.

    If you do not select this option, the wizard prompts only for required information.

    You must complete the configuration of the business objects using the assembly editor.In addition, if you have not defined a foreign key reference in the database, the adapter will not generate the parent-child relationship automatically.

  6. To create business objects to run user-defined database queries, select Create a query business object to build user-defined Select statements, and then type the number of query business objects you want to create. You specify only the number of business objects at this time; the wizard prompts for the name and other details about the business objects at a later time.

  7. To create business objects to run a sequence of SQL statements, select Create a batch SQL business object to build user-defined insert, update and delete statements, and then type the number of batch SQL business objects you want to create. You specify only the number of business objects at this time; the wizard prompts for the name and other details about the business objects at a later time.

  8. Click OK to save your changes to the database query.

  9. In the Find Objects in the Enterprise System window, click Run Query to use the query to discover database objects and to create templates for query and batch SQL business objects. The result of running a typical query is shown in the following figure.

    To restore an expired database connection, restart the external service wizard.

    The Discovered objects pane lists the database objects that are discovered.

  10. In the Discovered objects list, click + (the plus sign) to expand a schema node, and then expand Tables, Views, Stored Procedures, and Synonyms - Nicknames nodes following it, to see the database objects discovered by the wizard.

  11. Click + (the plus sign) to expand the nodes for Query Statements and Batch SQL Statements to display the templates for query and batch SQL business objects.


Results

The wizard displays the database objects you can access using the adapter and business object templates for query and batch SQL business objects.


What to do next

Continue working in the external service wizard. The next step is to select the objects you want to use in your module, configure each business object, and create hierarchies of business objects.

Configure the module for outbound processing


Next topic: Selecting and configuring business objects


Related concepts:

RetrieveAll operation

Query business object overview

Batch SQL business object overview

Business objects

Attribute application-specific information