Information service support data types

The data types that are required when you use the SQL support in an information service activity include data types for data source, set reference, and set variables.

Data sources

A data source is a collection of information that is used to connect to a particular JDBC compliant database in which data can reside and where data management operations run. For example, a relational database management system, such as DB2®, that contains tables against which SQL statements are run. Another example is WebSphere® II Classic Federation server that does not contain any data but allows you to run SQL statements by presenting a federated view of back-end systems.

The reference to a data source is of type tDataSource.

A data source is identified by its Java™ Naming and Directory Interface (JNDI) name. This is the name defined for the data source in WebSphere Process Server.

Authentication details can be specified when defining a data source in WebSphere Process Server. You need to create a J2EE Connector Architecture (J2C) authentication data entry containing a user ID and a password, and associate this with the data source for component-managed authentication.

You can define initial values for the data source variables. These variables are initialized at process instance start time.

Restriction: If you use an Oracle database management system as a data source against which you run SQL statements, special care must be taken when you model a process. Running data definition language (DDL) statements during process execution will lead to an exception if the data source JDBC driver is XA enabled. This situation can occur in the following cases:
  • When you specify DDL statements that run when a process is started and stopped
  • When you specify a DDL statement on the SQL snippet properties page
  • In preparation and cleanup statements for a table (a set reference) that is created at runtime
  • In preparation and cleanup statements that are inferred if a table is created at runtime and no preparation and cleanup statements have been defined for the table
To avoid these situations, make sure that DDL statements are processed outside the process execution context, for example, in a separate script or when the process is installed.

Set references

A set reference is an abstraction of a table name and refers to a database table (a set) which resides in a data source. A set reference contains:
  • The reference to the data source in which the table resides
  • The table name
  • Optionally: The schema name
  • Optionally: Statements to create and delete the table (preparation and cleanup statements)

A set reference is of type tSetReference. You can use set references instead of using static table names. Working with tSetReference type variables gives you the flexibility of specifying which tables to use dynamically at runtime and of sharing data by reference (and not by copying) across different activities or processes.

Set references can contain preparation statements that typically create a table and the corresponding indexes (for example, a CREATE TABLE and an ALTER TABLE statement), and cleanup statements that drop a table.

Set references can be used in SQL snippets. SQL snippets can access arbitrary set references stored in variables. For example, the table on which an SQL UPDATE performs its changes can be an (input) set reference. A snippet with an SQL SELECT query, on the other hand, specifies a set reference as its result, that is, the table denoted by the set reference is created (if required) and filled with the result of the SELECT statement. Tables in the FROM clause of a SELECT statement can also be input set references.

Note that a SELECT statement has exactly one result set reference and a stored procedure call may have 0 or more result set references. All other SQL statements do not have any result set references.

You can select to have the system generate a unique schema and table name for you if the name of the table is irrelevant.

You can set initial values for a set reference variable. If this includes set reference preparation and cleanup statements, you must ensure that when you define this set reference as a result set reference in an SQL snippet, you select to always create a new table and to drop it again at process instance end. If you do not define these property settings for the result set reference in the SQL snippet, the preparation and cleanup statements defined as initial values for the set reference will not run.

Sets

The content of a database table is called a set. A set variable is the variable into which the data referenced by a set reference variable is loaded. It is of type tSet.

You must define variables of type tSet when you are working with the retrieve set information service activity.

(C) Copyright IBM Corporation 2006. All Rights Reserved.