Campaign Management and Database

Database Issues

The following issues should be taken into consideration when planning and configuring the campaign database:

By default, the time between automatic refreshing of the schema cache is 30 minutes. This will be acceptable for most purposes.

Refreshing the schema cache involves querying all tables and views, then querying all columns for each table and view. On a small database this takes less than a second. On some large customer databases this can be tens of seconds. This cache is a shared resource and is locked for the duration of the update.

From 10.7.1968 - For those who would like a different refresh time, there is a Softdial Campaign Manager™ registry option SchemaCacheRefreshMinutes which allows the schema cache refresh time to be set between 1 and 60 minutes.

Alternatively, to force an immediate schema cache refresh for the DSN, send the Refresh Cache [HC] message specifying the data source name in the B1 parameter. This API can be consumed as SDMP or as a REST API.

Softdial Campaign Manager™ can only deal with data types where the data can be represented as a string or number. This might cause problems if the customer data contained blob types.

On installation, Softdial Campaign Manager™ is configured to support ISO SQL2008 datatypes. If you are using a database that does not support these datatypes you can define alternative datatype support by setting the DBType registry key. See Registry Settings.

Database updates will be processed in the order in which they are listed within the update string. There is no way to guarantee the time frame in which the update will be performed, but updates will always take place in the order they are submitted.

Softdial Campaign Manager™ benefits from having a single service connecting to the RDBMS to marshall database access on behalf of lots of clients. Nevertheless, care should be taken to ensure that no single query will take a significant amount of time to execute.

Softdial Campaign Manager™ marshalls all database communication for a given campaign through a single thread. If a database update takes some seconds to execute, it may cause cache depletion as the request to move the cursor on the next N rows is behind the update in the thread's message queue.

Softdial Campaign Manager™ has some resilience built in against this scenario but, in extreme cases, long database update times would lead to call starvation (Softdial CallGem™ would run out of numbers to dial).

There is also a mechanism to limit row count on column analysis cursor (DBMaxRowsCA) and main cursor (DBMaxRows) to improve performance when using a large number of records (e.g. more than 200,000). See Registry Settings.

Most ODBC driver implementations rely on a permanently open TCP socket connection between the client (in this case Softdial Campaign Manager™ Server) and the DBMS. In the event of WAN outage, database overload scenarios and LAN overload/ outage scenarios, this results in a permanent database handle failure, resulting in the need to perform a full reconnect to the database.

The effect of such a disconnect is failure to perform database updates, leading to data integrity issues.

From V10.5 - an enhancement has been introduced to provide a transaction rollforward mechanism that enables Softdial Campaign Manager™ to manage temporary outages of up to 1 minute in duration.

Softdial Campaign Manager™ will close a running campaign as a result of a SQL statement failure on account of end-user syntax errors. Softdial Campaign Manager™ performs data validation in order to ensure that any SQL statement issued by Softdial Campaign Manager™ is correct; however SQL passed through to Softdial Campaign Manager™ from a customer script could cause a campaign to close.

A deadlock caused by a long-running customer query, or a sustained WAN outage, may also cause the campaign to close.

Softdial Campaign Manager™ allows transactions to be cached for up to one minute by default, before closing the campaign and writing the transaction cache to local storage for retry later.

It is not possible using a generic API (such as ODBC) to determine whether a statement failure is on account of a user syntax error or is on account of communication error, deadlock or some other environmental failure.

In order to have a scheme for rollforward on environmental failure this mechanism is also invoked if a syntax error occurs.

This is an unavoidable consequence of implementing a HA (high availability) scheme and requires customers to be diligent in testing any script configurations that call SQL statements through the Data Query and Data Lookup APIs.

Some ODBC drivers implement disconnection after a period with no transaction traffic.

It is recommended to set the option Enable automatic reconnect in the ODBC config (if present), e.g.

ODBC automatic reconnection

Fig. 1 - ODBC automatic reconnection

Softdial Campaign Manager™ Server implements a test mode API which can be used to force the database connection into a failed state in order to facilitate testing of the recovery and rollforward mechanism. The message detail is documented below.

This information is intended to provide experienced DB users with a means of making a manual test within a customer database environment.

  • In the event of a database statement execution failure, Softdial Campaign Manager™ operates in database failed mode and attempts to catch up every 5 seconds.
  • If within one minute Softdial Campaign Manager™ has not been able to catch up, the failure is deemed to be permanent and the campaign is stopped. Unprocessed database transactions are written to disk, and then read in and rolled forward on campaign restart.
  • This mechanism may be tested by sending the following ForceFailed [FF] message.
    • To force Softdial Campaign Manager™ temporarily into database failed mode (that Softdial Campaign Manager™ will recover from), send the following message in Dialer Chat:

      CM:DE:FF\TD<tenant>\CY<Campaign ID>\OIfoo

    • To force a permanent failure, issue the same message with the On/ Off (ON) parameter.
  • You will see that Softdial Campaign Manager™ raises various NotifyEvent [NE] messages during its failover and recovery.

To enable updates to be performed reliably without risk of deadlocks and data corruption, the Campaign Table must only be updated using the API provided. See Updating Campaign Tables for details.

Softdial Campaign Manager™ database columns are typed as follows:

Description Mandatory? Type created by CM Generic type
Switch Result Yes SMALLINT Signed integer 16 bits or more
Agent Result Yes SMALLINT Signed integer 16 bits or more
Result Timestamp No FLOAT Decimal number with 6.6 precision
Retry Count Yes SMALLINT Signed integer 16 bits or more
Retry Timestamp Yes FLOAT Decimal number with 6.6 precision
Retry Username Yes VARCHAR(80) Fixed or variable character string with not less than 80 chars
Callback number Yes VARCHAR(80) Fixed or variable character string with not less than 80 chars
Retry Index Yes SMALLINT Signed integer 16 bits or more
Timezone lower bound No FLOAT Decimal number with 1.3 precision
Timezone upper bound No FLOAT Decimal number with 1.3 precision
Postcode Column No VARCHAR(80) Fixed or variable character string no size constraint
Legislature (state) code No VARCHAR(80) Fixed or variable character string no size constraint
Multiple number priority No SMALLINT Signed integer 16 bits or more