SQL Profiling 2.0.3: Configuration and Installation

Note:

You can request the SQL Profiling plugin from our support team on the SoftProject website under X4 BPMS > Downloads. Go to X4 BPMS 7.4.1 and on the X4 Plugins tab, select the Request button next to the plugin description.

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 has to 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.

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

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>		
		<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
`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:// 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`

How to install the plugin

Unpack the ZIP file with the plugin.
Copy the sql-profiling folder to the de\softproject\x4\plugins\ 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 folder <server_directory>\wildfly\modules\de\softproject\extensions\main.
Add the following line to the module.xml file:
<module name="de.softproject.x4.plugins.sql-profiling" export="true"/>
The file content should look like this:
Restart the X4 Server.
After restarting the X4 Server, the plugin will be available.