Persistent Connections and Oracle Databases

What is a persistent connection?

A persistent connection allows database connections to remain open for other requestors. A persistent connection is useful for long-running or published workspaces using FME Flow. If this parameter is not selected, the connection to the Oracle database is closed as soon as possible after data processing is complete.

On FME Form, a persistent connection leaves the Oracle connection open until each translation finishes or in the case of FME Flow, until an engine restart. By default, the engine restart is set to 100 successful or ten failed jobs, but this can be modified by setting the MAX_TRANSACTION_RESULT_SUCCESSES parameter in the configuration.

When should I use a persistent connection?

• When you want to limit the connections to a database
• For long-running jobs where a timeout may occur
• If you have multiple small jobs which connect to the database and you want to save time by not making another connection

What is the default connection setting?

Persistent connections are the default setting for readers, writers, and transformers.
 

How long does a connection last?

For a persistent connection (default), the connection will remain until the workspace translation ends or in the case of FME Flow, until an engine restart. Additionally, the TCP connections, by default, will hold on to any port for another 2-4 minutes after Oracle and FME have closed the connections.

A non-persistent connection is closed as soon as possible after data processing is complete.
 

Do Readers, Writers and Transformers share persistent connections?

Oracle writers will create their own second connection, while Readers and Transformers will share the first connection (if all transformers and reader/writers are set to persistent connections).
 

FeatureReaders / FeatureWriters

FeatureReaders / FeatureWriters use the same connection methods as readers and writers. For the purpose of persistent connections, they should be treated the same as regular readers and writers.
 

WorkspaceRunners

Child processes sent by FME Form from a WorkspaceRunner are new instances, so unlike FME Flow, the child process will use new connections, not the parent's persistent connections.
 

FMEFlowJobSumbitter

Child jobs sent from FMEFlowJobSubmitter will use the same instance of the engine as their parent and, therefore, use the parent's persistent connections.
 

Idle Connection Considerations

Occasionally, when there are timeouts for idle connections, a long-running query executed in a transformer downstream can cause the connection to become idle. If the reader shares a connection with the transformer, it may fail due to a database or firewall timeout. In such a case, you can have the reader read everything and cache it in memory with a FeatureHolder before the transformer executes.
 

Key points

• If a Reader loses a connection, the workspace will fail.
• Readers will not cause a failure if everything is read, but the workspace is still processing.
• Transformers will cause a translation to fail if a connection is dropped during the middle of a query.
• If a connection fails between queries, the transformer will find a new connection.