Contents

The document tracking feature is configured as part of FES to track SBW events into the database running within Enterprise Server.

Viewing and Configuring the SBW Manager properties

Configuring the SBW Manager properties in eStudio

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

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

    Icon

    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.

    Icon

    Refer to the 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.

Configuring SWB Database in Fiorano Dashboard

In the left navigation panel, go to Document Tracking > SBW DB Configuration, provide the database-specific values, click the Save Configurations button and restart the server.

 

Icon

1. After configuring a profile to use some database, other than the default database, jdbc driver for that database needs to be added under <java.classpath> tag in server startup configuration file (either $FIORANO_HOME/esb/server/bin/server.conf or $FIORANO_HOME/esb/fes/bin/fes.conf, whichever is applicable) before starting Enterprise server.

2. Use the same settings to connect to the DB when using a third-party tool. All the database queries used for retrieving workflow related data is kept in sbwdml.sql file.

3. When using MS SQL for document tracking, mssql_jdbc.cfg may need to be configured according to the database driver being used. MSSql 2000 driver follows SQL 99 conventions which quote the SQLState string for table not found exception as 42S02. On the other hand, MSSQL 2005 driver follows XOPEN SQLState conventions which quote the same SQLState string as S0002. By default, all fes profiles are configured according to the standards followed by MSSql 2000 driver. If someone uses MSSql 2005 database or uses MSSql 2005 driver for MSSql 2000 (2005 driver is backward compatible with 2000 driver, hence it can be used), then the file has to be reconfigured accordingly.

Icon

It is strongly recommended that the user employ a commercial-grade DB in a production system.

For file-based databases like apache and HSQL, the default location is in the ESB_USER_DIR (which is set in fiorano_vars script). Provide the complete path with these variables resolved when using the JDBC URL in a third party tool.

Example

Icon

The default H2 db JDBC URL is configured as ESB_DEFAULT_DB_DIR/doctracking_db;create=true which resolves to ESB_USER_DIR/EnterpriseServers/<profilename>/FES/doctracking_db and further into something like C:\Documents and Settings\All Users\runtimedata\esb\<BUILD_NUMBER>\EnterpriseServers\FES\doctracking_db depending on the actual settings.

SBW Database Property descriptions

Database NameJDBC DriverJDBC Connection URL (Default Formats)JDBC Properties
H2org.h2.Driverjdbc:h2:ESB_DEFAULT_DB_DIR/doctracking_db/sbwh2_jdbc.cfg
Oracleoracle.jdbc.driver.OracleDriverjdbc:oracle:thin:<ip-address>:<port>:<databaseName>oracle8_jdbc.cfg
IBM DB2sun.jdbc.odbc.JdbcOdbcDriverjdbc:odbc:sampledb2_jdbc.cfg
MS Accesssun.jdbc.odbc.JdbcOdbcDriverjdbc:odbc:Driver={Driver do Microsoft Access (*.mdb)};DBQ=D:\\tif\\bin\\sp\\sbw.mdbmsaccess_jdb.cfg
HSQLorg.hsqldb.jdbcDriverjdbc:hsqldb:ESB_DEFAULT_DB_DIR/doctracking_db/sbwhsql_jdbc.cfg
MS SQL 2000com.microsoft.jdbc.sqlserver.SQLServerDriverjdbc:microsoft:sqlserver://<ip-address>:<port>;SelectMethod=Cursormssql_jdbc.cfg

MS SQL 2005

or later

com.microsoft.sqlserver.jdbc.SQLServerDriverjdbc:sqlserver://<ip-address>:<port>;databaseName=<databaseName>;SelectMethod=Cursormssql_jdbc.cfg
MySQL
  1. sun.jdbc.odbc.JdbcOdbcDriver
  2. com.mysql.jdbc.Driver
  1. jdbc:odbc:sample
  2. jdbc:mysql://<ip-address>:<port>/<databasename>
mysql_jdbc.cfg
Sybasecom.sybase.jdbc2.jdbc.SybDriverjdbc:sybase:Tds:<ip-address>:<port>/<databaseName>sybase_jdbc.cfg
Apache Derbyorg.apache.derby.jdbc.EmbeddedDriver

jdbc:derby:ESB_DEFAULT_DB_DIR/doctracking_db/sbw;create=true

Icon

 

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

 

derby_jdbc.cfg
Postgre SQLorg.postgresql.Driverjdbc:postgresql://<ip-address>:<port>/<databasename>pgsql_jdbc.cfg

Descriptions of common properties are listed below:

PropertiesDescriptionsDefault Value
JDBC Login NameUsername to be used for jdbc database connection<DB dependent>
JDBC PasswordPassword to be used for jdbc database connection<DB dependent>
Scheme Name

By default, the schema name will be %, i.e., all schemas are searched.

Icon

Schema name is case sensitive

%
Catalog Name

By default, the catalog name will be %, all catalogs.

Icon

Catalog Name for MS SQL should be configured as database name.

%
Max Rows To FetchMaximum No Of Rows to Fetch.200
Max Rows To ReturnMaximum No Of Rows to Return.1000
Auto ReconnectBoolean to specify whether SP should try for reconnection with DB automatically.Enable/Disable
Try Reconnect IntervalInterval (in secs) after which SP tries to reconnect with DB in case of a break in connection.5
Insert Thread CountNumber of threads used for inserting SBW data into the database.1
Support Master TableEnable if backward compatibility with earlier SBW schema (1001) is required.Enable/Disable
Representable Data TypesEnable to store MESSAGE in representable data format.Enable/Disable
Socket TimeoutRead timeout while reading from the socket.
  • Oracle - 60000
  • MySQL - 60000
Num User IDs

Number of user-defined document IDs the user wants to use in the setup.

Icon
  • Num User IDs can be set at the creation of the database; to change this value, update the value and restart the server.
    • String property with name ESBX__SYSTEM__USER_DEFINED_DOC_ID_X has to be present in the message to set ud_id_X value. One can add this property in the message by using JMS funclets in route transformation or application context transformation.
1

Points to Note

Icon

1. After configuring a profile to use some database, other than the default database, jdbc driver for that database needs to be added under <java.classpath> tag in server startup configuration file (either $FIORANO_HOME/esb/server/bin/server.conf or $FIORANO_HOME/esb/fes/bin/fes.conf, whichever is applicable) before starting Enterprise server.

2. Use the same settings to connect to the DB when using a third-party tool. All the database queries used for retrieving workflow related data is kept in sbwdml.sql file.

3. When using MS SQL for document tracking, mssql_jdbc.cfg may need to be configured according to the database driver being used. MSSql 2000 driver follows SQL 99 conventions which quote the SQLState string for table not found exception as 42S02. On the other hand, MSSQL 2005 driver follows XOPEN SQLState conventions which quote the same SQLState string as S0002. By default, all fes profiles are configured according to the standards followed by MSSql 2000 driver. If someone uses MSSql 2005 database or uses MSSql 2005 driver for MSSql 2000 (2005 driver is backward compatible with 2000 driver, hence it can be used), then the file has to be reconfigured accordingly.

Icon

It is strongly recommended that the user employ a commercial-grade DB in a production system.

For file-based databases like apache and HSQL, the default location is in the ESB_USER_DIR (which is set in fiorano_vars script). Provide the complete path with these variables resolved when using the JDBC URL in a third party tool.

Example

Icon

The default H2 db JDBC URL is configured as ESB_DEFAULT_DB_DIR/doctracking_db;create=true which resolves to ESB_USER_DIR/EnterpriseServers/<profilename>/FES/doctracking_db and further into something like C:\Documents and Settings\All Users\runtimedata\esb\<BUILD_NUMBER>\EnterpriseServers\FES\doctracking_db depending on the actual settings.

Adaptavist ThemeBuilder EngineAtlassian Confluence