Closing unused database connections

By default, IBM® SPSS® Modeler caches at least one connection to a database once that connection has been accessed. The database session is held open even when streams requiring database access are not being executed.

Caching database connections can improve execution times by removing the need for IBM SPSS Modeler to reconnect to the database each time a stream is executed. However, in some environments, it is important for applications to release database resources as quickly as possible. If too many IBM SPSS Modeler sessions maintain connections to the database that are no longer used, database resources may become exhausted.

You can avoid this possibility by turning off the IBM SPSS Modeler option cache_connection in a custom database configuration file. Doing so can also make IBM SPSS Modeler more resilient to faults in the database connection (such as timeouts) that can occur when connections are used over a long period of time by an IBM SPSS Modeler session.

To cause unused database connections to be closed:

  1. Locate the [server install path]/config directory.
  2. Add the following file (or open it, if it already exists):

    odbc-custom-properties.cfg

  3. Add the following line to the file: 
    cache_connection, N
  4. Save and close the file.
  5. Restart IBM SPSS Modeler Server.
Note:

In-database caches are saved in database as either a regular table or a temporary table, depending on each database's implementation. For example, temporary tables are used for Db2, Oracle, Amazon Redshift, Sybase, and Teradata. For these databases, setting cache_connection to N does not work as expected because the temporary table is only valid within a session (it will be cleaned automatically by the database when the database connection is closed).

So when running an SPSS Modeler stream against one of these databases with cache_connection set to N, an error such as Failed to create table for in-database caching. Using file cache instead. may result. This indicates that SPSS Modeler failed to created the in-database cache. Also, in some cases for an SPSS Modeler-generated SQL query, a temporary table is used but the table is empty.

To work around this issue, you can choose to use a regular database table for in-database caches. To do this, create a custom database property configuration file that contains the following line:
table_create_temp_sql, 'CREATE TABLE <table-name> <(table-columns)>'

This forces a regular database table to be used for the in-database cache, and the table will be dropped when all connections to the database are closed or when the working stream is closed.