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.
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.
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.
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.