SQL Statement Execution and Connection Timeouts
The CloudStreams event-sending component relies on the JDBC pools connection properties for configuring the DataDirect driver properties.
It is expected that any DataDirect driver settings will be configured via property settings in the connection URL. Minimally, it is recommended that timeouts be configured for connections and SQL statement execution since by default the DataDirect driver uses "0" for its default settings (which implies that no timeouts are used).
Following is a sample connection URL with the property settings QueryTimeout and LoginTimeout:
jdbc:wm:oracle://<server>:<1521|port>;serviceName=<value>;QueryTimeout=<seconds>;LoginTimeout=<seconds>
Note the following:
The
LoginTimeout property controls connection timeouts.
If LoginTimeout is not specified, the connection pool will use the value of the global Integration Server property watt.server.jdbc.loginTimeout to attempt to connect to the database.
The watt.server.jdbc.loginTimeout property is located in IntegrationServer_directory\instances\instance_name\config\server.cnf. This property sets the maximum time, in seconds, that the JDBC driver on Integration Server is to wait for a response while attempting to connect to a database. If Integration Server does not receive a response in the allotted time, it terminates the request, logs the error and moves on. The default value is 60 seconds.
Note: The DataDirect property LoginTimeout takes precedence over the global Integration Server property watt.server.jdbc.loginTimeout, which means you can override the global property on a case-by-case basis.
The
QueryTimeout property controls timeouts for queries and for inserting events.
Although the name QueryTimeout suggests its scope is limited to queries, it actually applies to all SQL statements, including INSERTs.
If The QueryTimeout property is not specified, there is no timeout for SQL statements executing on connections in this pool. So it is recommended that you specify a QueryTimeout value for this pool.
If you experience timeouts while inserting events, you should consider increasing the QueryTimeout property (and/or reducing the CloudStreams property pg.jdbc.batchSize, which is described below).
The
CloudStreams property
pg.jdbc.batchSize controls the size of the events batch.
CloudStreams uses the optimized JDBC batch API to send its events to the relational database using prepared statements. The CloudStreams property pg.jdbc.batchSize controls how many events the sender can insert into a given batch (25 events by default). So, a batch of events is sent to the database every time the value of pg.jdbc.batchSize is reached. Increasing the batch size can improve database performance, but will also increase the statement execution time.
The pg.jdbc.batchSize property is located in:
IntegrationServer_directory\instances\instance_name\packages\WmCloudStreams\config\resources\wst-config.properties
Additionally, if more than 3 seconds elapse since the last batch was sent, any received events will be sent to the database, even if the batch threshold has not been reached. This ensures there is not a substantial delay between the time an event is created and when it is inserted into the table.