Defining the object selector

The object selector is an SQL statement that is used to retrieve objects from the database. It must be a valid SQL statement for the runtime RDBMS. It must return at least the ID of the object that is undergoing a state transition. The data returned by the object selector is made available to FSM actions at run time. For this reason, the data that is selected by the object selector should reflect the data needs of the actions that might result from the transitions within an FSM.

The following graphic shows an example of an object selector:
Figure 1. Example of an object selector
SELECT
     ID,
     CURRENCY,
     TRANSMISSION_ID,
     BATCH_ID,
     ISF_DATA
FROM
     $DBSchema.TXN_PAYMENT_V T                 [Note 1]
WHERE
     SUBTYPE.TYPE='PAYMENT_ORIGINATION' AND
     STATUS=?                                  [Note 2]
Note:
  1. The object selector above uses the notation $DBSchema in place of the database schema name. FTM substitutes $DBSchema with the name of the database schema that is configured in the broker flow at run time.
  2. The question mark (?) is a placeholder for the transition start status. When an event is received at run time, FTM identifies potential transitions that are triggered by that event and the question mark is replaced by the transition start state.
You must observe certain rules when defining an object selector:
  • The rules for defining an FSM object selector (Master object selector) are described in FSM Level Properties.
  • The rules for defining a Transition object selector (override selector) are described in Transition Properties.

The object selector is not the entire SQL statement that is used to select data. Each transition can have an optional object filter defined for it. This allows event context data (that is, additional information that is passed in the event) to be used to further filter the objects retrieved.

Additionally, the FTM core optimizes the select statement to make it more efficient at run time. It adds appropriate isolation options to prevent deadlocks.

Note: A badly designed where clause of an object selector might result in database waits or deadlocks, and can significantly affect the performance of the FTM application. For information about how to design a where clause, see Concurrency.