Configuring SBW Callout

Prerequisite

A stored procedure in the desired database has to be created, which contains the business logic to be performed on the data coming from Fiorano. For example, a stored procedure stores the callout parameters in a predefined database table.

Configuring Database Callout file

To view or configure the database, perform the following actions on the Profile Management perspective:

1. Load the profile and navigate to FES > Fiorano > Esb > Sbw > SBWManager. The Properties of SBWManager dialog box on the right displays all the database properties along with their default values.

   

   Property values are editable only for inactive profile nodes. To edit an active profile (a running server), stop the server so that the values become editable.

   

   

   Refer to the Configuring SBW Callout#SBW Database Property descriptions for descriptions of the SBW database properties.

2. Select the required database from the Database Name property drop-down and modify values of other properties too accordingly.

   

   
   
   

3. Click the Save button or press CTRL+S to save the profile changes.

SBW Database Property descriptions

Callout Database Name	Callout JDBC Driver	Callout JDBC Connection URL (Default Formats)
H2	org.h2.Driver	jdbc:h2:ESB_DEFAULT_DB_DIR/doctracking_db/sbw
Oracle	oracle.jdbc.driver.OracleDriver	jdbc:oracle:thin:<ip-address>:<port>:<databaseName>
IBM DB2	sun.jdbc.odbc.JdbcOdbcDriver	jdbc:odbc:sample
MS Access	sun.jdbc.odbc.JdbcOdbcDriver	jdbc:odbc:Driver={Driver do Microsoft Access (*.mdb)};DBQ=D:\\tif\\bin\\sp\\sbw.mdb
HSQL	org.hsqldb.jdbcDriver	jdbc:hsqldb:ESB_DEFAULT_DB_DIR/doctracking_db/sbw
MS SQL 2000	com.microsoft.jdbc.sqlserver.SQLServerDriver	jdbc:microsoft:sqlserver://<ip-address>:<port>;SelectMethod=Cursor
MS SQL 2005 or later	com.microsoft.sqlserver.jdbc.SQLServerDriver	jdbc:sqlserver://<ip-address>:<port>;databaseName=<databaseName>;SelectMethod=Cursor
MySQL	sun.jdbc.odbc.JdbcOdbcDriver com.mysql.jdbc.Driver	jdbc:odbc:sample jdbc:mysql://<ip-address>:<port>/<databasename>
Sybase	com.sybase.jdbc2.jdbc.SybDriver	jdbc:sybase:Tds:<ip-address>:<port>/<databaseName>
Apache Derby	org.apache.derby.jdbc.EmbeddedDriver	jdbc:derby:ESB_DEFAULT_DB_DIR/doctracking_db/sbw;create=true DerbyDB will be created within the user directory (set in fiorano_vars) ESB_DEFAULT_DB_DIR will be resolved to ESB_USER_DIR/EnterpriseServers/<profilename>
Postgre SQL	org.postgresql.Driver	jdbc:postgresql://<ip-address>:<port>/<databasename>

Descriptions of common properties are listed below:

Properties	Descriptions	Default Value
Callout JDBC Login Name	Username to be used for JDBC database connection	<DB dependent>
Callout JDBC Password	Password to be used for JDBC database connection	<DB dependent>
Callout Try Reconnect Interval	Interval (in secs) after which SP tries to reconnect with DB in case of a break in connection.	5
Callout Ping SQL	Ping SQL is used to determine a broken connection.	1
Callout Procedure	After the Connection Properties are set, provide the value of CALLOUT_PROCEDURE as follows: CODE call <qualified name of the stored procedure > (?,...) The number of ?s should be the same as the number of parameters for the stored procedure configured in the required database.	{call tempdb.dbo.WriteEvent(?, ?, ?, ?, ?, ?, ?, ?)}
Callout Parameters	Provide the PARAMETER_INFO as a semicolon-separated list of parameters with each parameter information in the following format:\ CODE <FirstParamNameInStoreProcedureDefinition>,IN|OUT|INOUT,<datatype> Supported datatypes are varchar, int, datetime (to be used in conjunction with InTime value source as explained below), and boolean. Information about all parameters of stored procedure must be provided, and names should match the ones defined in the stored procedure.	Param1,IN,varchar;Param2,IN,varchar;Param3,IN,varchar;Param4,IN,varchar;Param5,IN,varchar;Param6,IN,datetime;Param7,IN,int;RtnOut,IN,int;

Adding DB Support Jar path

Navigate to the path from which Enterprise server is started ($FioranoHome/esb/fes/bin or $FioranoHome/esb/server/bin) and open fes.conf or server.conf accordingly. Provide the path of the support jar along with the support jar name under the section <java.classpath>.

Enabling Callout at Server Level

Callout can be enabled by any of the following options.

Server restart is not required for the first two methods given below.

Dashboard

1. Start Enterprise Server (FES) and login to Fiorano Esb Web Console.
2. Navigate to Advanced > Properties > ADVANCED PROPERTIES.
3. Expand the SBW option.
   
   

   

   
   
   
4. Enable the Attribute value property of the EnableCallout attribute.
5. Click the Save icon under the Actions column.

Connection Management

1. Start Enterprise Server (FES) and open eStudio .
2. Open Connection Management.
3. Log on to FES-JMX, navigate to Fiorano > Esb > Sbw > SBWManager > SBWManager > Config.
4. Enable viewing Expert Properties.
5. Under Attributes, set the value of EnableCallout to 'true' and save the configuration.

Profile Management

1. Stop the Enterprise Server (FES) if it is running and then open eStudio .
2. Open Profile Management perspective and open FES profile.
3. Navigate to Fiorano > Esb > Sbw > SBWManager.
4. Check the checkbox Show Expert Properties.
5. Check the Enable Callout check box.
6. Save the profile and restart the server.

Configuring callout in an Event Process

1. Create an Event Process with Feeder > Display.
2. Select output port of Feeder and navigate to Properties > General.
3. Set Enable Callout to YES. Click Callout Parameters button.
   
   

   

   
   
   
4. The parameters will be loaded based on the configuration provided in callout.cfg file.
   
   

   

   
   
   
5. Click ValueSource drop-down as shown below to provide mapping from the monitoring data to the parameters of the stored procedure.
   
   

   

6. ValueSource drop-down contains the below values which can be chosen as per the given scenario:
   1. Body: Stores entire message body in DB column.
   2. XPath: Stores specific element of the message body based on XPath.
      1. To configure XPath, select 'XPath' from ValueSource drop-down.
         
         

         

      2. Click Value ellipsis

         

         button to provide appropriate Xpath.
         

         

      3. Configure XPath corresponding to the element to be saved.
         
         

         

   3. AppContext: Passes the content of Application content as a parameter to the procedure.
   4. AppContextXPath: AppContextXPath is used to map specific elements from the AppContext. How it is configured is similar to how XPath is configured above.
   5. Property: The value of a JMS property that is present in the message can be mapped using this. This option can be used to map different details about the flow such as component name, event process name, event process environment, or any custom properties used in the flow.
   6. Constant: Maps Constant value provided in the 'Value' field.
   7. Intime: The timestamp at which the message entered a component can be logged by selecting this. The data type in stored procedure should be 'datetime' for this. The value will be available at the output port of the component.

   8. ExecutionTime: The amount of time that the component took can be mapped using this. The time is returned in milliseconds.

      

      Choice of ValueSource value purely depends on your requirements.

After the above actions are performed, Callout Configuration gets completed. After launching the Event Process, the database configured will be updated by the data from the parameters mapped in Callout Parameters.