SQL Profiling 2.1.9: Configuration and Installation

Note:

You can request the SQL Profiling plug-in in the download area of the SoftProject website (SoftProject | Software X4 BPMS) on the X4 Add-ons tab.

The SQL Profiling plugin is used to store profiling data into a relational SQL database. To do so, you need to connect the SQL Profiling plugin to a relational SQL database. The plugin must be activated in the module.xml file in the <server_directory>\wildfly\modules\de\softproject\plugins\main folder.

The first start of the profiling must be executed by a database user who has the permission to create database tables. After all tables required have been created, a user with fewer permissions can take over.

Note:

When you enable the SQL Profiling, the default profiling in the memory is turned off.

How to install the plugin

Unzip the plugin ZIP file.
Copy the sql-profiling folder to the <server_directory>\wildfly\modules\ folder.
Open the module.xml file in the <server_directory>\wildfly\modules\de\softproject\x4\plugins\sql-profiling\main folder.
Copy the plugin name specified in line 2:
Open the module.xml file in the <server_directory>\wildfly\modules\de\softproject\plugins\main folder.
Add the following line to the module.xml file:

The file content should look like this:
Restart the X4 Server.
After restarting the X4 Server, the plugin will be available.

How to configure SQL Profiling

In the <server_directory>/configurations folder, create the sql_profiling_config.xml file.

Insert the following content.

XML

<?xml version="1.0"?>
<Configuration>
	<DatabaseConfiguration>		
		<ConnectionString>JDBC-CONNECTION</ConnectionString>	
      	<!--OPTIONAL-->
        <JndiDatasource>java:/X4TEST_DS</JndiDatasource>
		<UserName>USERNAME</UserName>		
		<Password>PASSWORD</Password>		
		<MaxPoolSize>36</MaxPoolSize>		
		<MinPoolSize>8</MinPoolSize>		
		<InitialPoolSize>24</InitialPoolSize>
		<!--OPTIONAL-->
		<AdditionalConnectionPoolConfig>
            			<CheckoutTimeout>20000</CheckoutTimeout>
        </AdditionalConnectionPoolConfig>
	</DatabaseConfiguration>
    <WriteCycleTime>		
        <Duration>10</Duration>		
        <TimeUnit>SECONDS</TimeUnit>
    </WriteCycleTime>
    <KeepLogs>		
        <Duration>100</Duration>
        <TimeUnit>DAYS</TimeUnit>
    </KeepLogs>
    <KeepCrashedProcessLogs>		
        <Duration>100</Duration>
        <TimeUnit>DAYS</TimeUnit>
    </KeepCrashedProcessLogs>
    <RunCleanUp>		
        <Duration>240</Duration>
        <TimeUnit>MINUTES</TimeUnit>
    </RunCleanUp>
	<FastDeleteEnabled>true</FastDeleteEnabled> 
	<GlobalStepLogging>ALL</GlobalStepLogging> 
</Configuration>

Enter the required values in the sql_profiling_config.xml file.

Database Configuration

Element	Description	Possible values
`DatabaseConfiguration`	Wrapper for the database connection
`ConnectionString`	JDBC connection string for the connection to the database For more information, see the official JDBC database documentation.	Connection URL for the JDBC database
`JndiDatasource`	Name of the JNDI datasource to be used to establish the database connection. The JNDI datasource is configured directly in the `standalone.xml` file of WildFly. Note: The `<ConnectionString>` element, which can be used to specify a direct database connection from within the configuration, must not be used concurrently with `<JndiDatasource>`. Connection pool settings (for example, pool size) in the configuration do not affect a JNDI datasource. Make these settings directly in the definition of the JNDI datasource in the `standalone.xml` of WildFly. For more information about setting up a JNDI datasource, see the WildFly documentation.	JNDI name that is configured in the datasource definition in WildFly
`UserName`	User name for the login to the database	Any string
`Password`	Password for the login to the database	Any string
`MaxPoolSize`	Maximum pool size of the connection pool. Recommended value: 15	Integer
`MinPoolSize`	Minimum pool size of the connection pool.	Integer
`InitialPoolSize`	Initial size of the connection pool. Recommended value: 8	Integer
`AdditionalConnectionPoolConfig`	Additional settings to the underlying connection pool. The `CheckoutTimeout` elements how long a client will wait for a connection in milliseconds. `0` is default and means the client will wait indefinitely for a connection. For more information, see https://www.mchange.com/projects/c3p0/#configuration.	Integer
`WriteCycleTime`	Specifies the cycles in which data is written to the SQL database. Data is only written to the database for the selected write cycle (WriteCycle). If the server crashes within the cycle, the unwritten data is lost.	The element contains the following elements: `Duration` `TimeUnit`
`KeepLogs`	Specifies how long entries are stored before they are cleared automatically.	The element contains the following elements: `Duration` `TimeUnit`
`KeepCrashedProcessLogs`	Sets the period of time for which entries for crashed processes are stored before they will be cleared automatically. Crashed processes are not covered by the `KeepLogs` element.	The element contains the following elements: `Duration` `TimeUnit`
`RunCleanUp`	Sets the cycles in which the automatic cleanup is performed.	The element contains the following elements: `Duration` `TimeUnit`
`FastDeleteEnabled`	Deletes logs in a faster way. If this is activated, the `KeepCrashedProcessLogs` configuration has no effect. Any process log that is older than the period defined in `KeepLogs` is deleted, regardless of whether the process has crashed or not. Possible values: `true` (default) `false`
`GlobalStepLogging`	Defines which process steps are logged. Possible values: `ALL`: Logs all process steps as before. `NONE`: Logs no process steps at all. `ERROR_ONLY`: Logs process steps that finished with an error state. With `ERROR_ONLY`, also steps that ended with negative status numbers will be logged.

WriteCycleTime

Element	Description	Possible values
`Duration`	Period in which the cached profiling data is written to the database	Integer
`TimeUnit`	Unit of the period of time	`SECONDS` `MINUTES` `HOURS` `DAYS`

KeepLogs

Element	Description	Possible values
`Duration`	Period of time for which the profiling data is to be stored in the database	Integer
`TimeUnit`	Unit of the period of time	`SECONDS` `MINUTES` `HOURS` `DAYS`

KeepCrashedProcessLogs

Element	Description	Possible values
`Duration`	Period of time for which the profiling data about crashed processes is to be stored in the database	Integer
`TimeUnit`	Unit of the period of time	`SECONDS` `MINUTES` `HOURS` `DAYS`

RunCleanUp

Element	Description	Possible values
`Duration`	Period of time after which the automatic clean-up will delete the process logs	Integer
`TimeUnit`	Unit of the period of time	`SECONDS` `MINUTES` `HOURS` `DAYS`