To open the SQL Collection agent configuration dialog from a workflow configuration, you can do either one of the following:
- double-click the agent icon
- select the agent icon and click the
Edit button
The Agent Configuration contains configurations related to the SQL query to use to retrieve information from the source database, as well as the UDR type to be created and how the UDRs are populated by the agent.
SQL collection agent configuration dialog
| Setting | Description | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Database | Profile name of the database that the agent will connect to and retrieve data from. For further information about database profile setup, see Database Profile. | |||||||||||||||||||
SQL Statement | The user enters an SQL statement specifying the query that the system should send to the database. By right clicking in the pane, selecting MIM Assistance..., the MIM Browser appears. The user can select a MIM value to be used in the SQL query. The value of the MIM will be used in the SQL query during execution. The name of the MIM Value for example "Workflow.Batch Count" will be displayed in blue color as "$(Workflow.Batch Count)" in the text field. There is support for Stored Procedures. When using the collection agent to produce output from the procedure the JDBC support for output parameters is used. The character "?" is used to mark an output parameter in the SQL statement in the agent. An example of a procedure with one input argument and one output argument could have a SQL statement looking like this:
The procedure will be called and the value of the output parameter ("?") will be assigned to the configured UDR field. When using output parameters only one UDR will be produced in the batch. The exact supported syntax for stored procedures varies between databases. For example calling an Oracle function can be done via:
| |||||||||||||||||||
UDR Type | Type of UDR mapped from the result set and routed into the workflow. When selecting the Browse button next to the field the UDR Internal Format Browser will open and one and only one UDR type can be selected. | |||||||||||||||||||
| ResultSet Size | The result set size gives the JDBC driver a hint to the number of rows to fetch from the database when more rows are required for the ResultSet object. If you set the ResultSet Size to 0, the JDBC driver decides the best size. | |||||||||||||||||||
UDR Fields | The table represents the mapping from the result set, returned when executing the statement entered in the SQL field to a specified Value in the UDR. |