Persistent Database Connection for Performance Optimizat /

[roseg]

Cloverleaf v19.1.2.1P – Persistent Database Connection for Performance Optimization & Reducing Connection Overhead

Hi everyone,

We are using Cloverleaf v19.1.2.1P and facing severe performance bottlenecks because our current architecture establishes a new database connection per message to MSSQL Server.
We are looking for a persistent MSSQL connection that can be used across multiple messages without opening & closing connections for every single message.

Architecture & Current Setup
We have divided our Cloverleaf system into three dedicated sites:

1️⃣ IB-Site (Inbound-Site)
One central ADT-Thread receiving all ADT messages
Distributes messages via Static/RAW route to a Destination-Thread in the PROC-Site
2️⃣ PROC-Site (Processing-Site)
Handles business logic, Xlate transformations, routing & database interaction
50+ routes, 10-15 of them perform direct database operations
Each message is written to MSSQL via a TCL script after Xlate
Currently, every message opens & closes a new database connection
Goal: Utilize a persistent database connection for performance optimization
3️⃣ OB-Site (Outbound-Site)
External systems are connected via standard Outbound-Threads
Communication from PROC-Site to OB-Site happens via Destination-Threads
Problem: High Overhead Due to Per-Message DB Connection
Our TCL scripts in the PROC-Site establish a new database connection for each message, leading to:

1️⃣ High Total Queue Time (TQT) due to connection overhead

Each process opens a new DB connection (~100-300ms per message)
Under high load, database routing becomes overloaded
34 incoming ADT messages resulted in over 500 processed messages
Tests & Approaches
1️⃣ Persistent ODBC Connection via Windows & TCL
❌ Not possible – Each TCL execution opens a new session

2️⃣ Stored Procedures via Database-Outbound-Thread
❌ Issue: Cannot pass parameters dynamically to the stored procedure

Goals
✅ Use a persistent MSSQL database connection for all messages
✅ TCL scripts should use an existing connection instead of opening a new one per message
✅ Reduce connection overhead by a factor of 2x – 5x
✅ Significantly decrease Total Queue Time (TQT) & improve scalability

Questions to the Community
🔹 How can a persistent MSSQL connection be used across multiple message processes in Cloverleaf?
🔹 Can Cloverleaf Database-Inbound & Outbound Threads be configured to act as persistent connections?
🔹 Is there a way to use Windows ODBC sessions persistently?
🔹 How can dynamic SQL parameters be passed to a Database-Outbound-Thread?

Any insights & experiences would be greatly appreciated! 🚀

 

 

Cloverleaf v19.1.2.1P – Persistente Datenbankverbindung für Performance-Optimierung & Reduktion von Verbindungs-Overhead

Hallo zusammen,

wir arbeiten mit Cloverleaf v19.1.2.1P und stehen vor einem erheblichen Performance-Engpass, da unsere derzeitige Architektur dazu führt, dass pro Nachricht eine neue Datenbankverbindung zu MSSQL aufgebaut und wieder abgebaut wird.
Wir suchen eine Lösung, um eine persistente MSSQL-Verbindung in Cloverleaf zu nutzen, ohne pro Nachricht eine neue Verbindung zu öffnen.

Architektur & Aktuelles Setup
Unser Cloverleaf-System ist in drei dedizierte Sites aufgeteilt:

1️⃣ IB-Site (Inbound-Site)
Zentraler ADT-Thread, der alle eingehenden ADT-Nachrichten verarbeitet
Nachrichten werden über eine Static/RAW Route an ein Destination-Thread in die PROC-Site übergeben
2️⃣ PROC-Site (Processing-Site)
Hier erfolgt die Business-Logik, Xlate-Mapping, Routing & Datenbank-Interaktion
50+ Routen, wovon 10-15 direkt mit der Datenbank interagieren
Jede Nachricht wird nach einem Xlate über ein TCL-Script in die Datenbank geschrieben
Aktuell baut das TCL-Skript pro Nachricht eine neue MSSQL-Verbindung auf und wieder ab
Ziel: Eine persistente Datenbankverbindung, um die Performance zu optimieren
3️⃣ OB-Site (Outbound-Site)
Externe Systeme werden über reguläre Outbound-Threads angebunden
Die Kommunikation zwischen PROC-Site und OB-Site erfolgt über Destination-Threads
Problemstellung: Hoher Overhead durch Verbindungsaufbau pro Nachricht
Unsere TCL-Skripte in der PROC-Site führen für jede einzelne Nachricht einen vollständigen Verbindungsaufbau zur MSSQL-Datenbank durch.
Dies führt zu folgenden Problemen:

1️⃣ Hohe TQT (Total Queue Time) durch Overhead

Jeder Nachrichtenprozess baut eine neue DB-Verbindung auf (~100-300ms pro Nachricht)
Bei hoher Last sind die Datenbank-Routings überlastet
Testlauf mit 34 ADT-Eingangsnachrichten resultierte in über 500 Ausgangsnachrichten
Tests & Lösungsversuche
1️⃣ Persistente ODBC-Verbindung für MSSQL über Windows & TCL nutzen
❌ Nicht möglich → Cloverleaf öffnet bei jedem TCL-Aufruf eine neue ODBC-Session

2️⃣ Stored Procedures via Database-Outbound-Thread
❌ Problem: Übergabe von Parametern an die Stored Procedure nicht möglich

Zielsetzung: Was wir erreichen wollen
✅ Eine persistente MSSQL-Datenbankverbindung für alle Nachrichten
✅ TCL-Skripte sollen bestehende Verbindungen nutzen, anstatt sie pro Nachricht neu aufzubauen
✅ Reduktion des Verbindungs-Overheads um Faktor 2x – 5x
✅ Signifikante Reduktion der Total Queue Time (TQT) und bessere Skalierbarkeit

Fragen an die Community
🔹 Wie kann man in Cloverleaf eine persistente MSSQL-Verbindung über mehrere Nachrichtenprozesse hinweg nutzen?
🔹 Ist es möglich, die Cloverleaf Database-Inbound- und Outbound-Threads so zu konfigurieren, dass sie als persistente Verbindung genutzt werden?
🔹 Gibt es eine Möglichkeit, Windows ODBC-Sessions dauerhaft zu nutzen?
🔹 Wie kann man dynamische SQL-Parameter an einen Database-Outbound-Thread übergeben?

Wir freuen uns über jeglichen Input & Erfahrungen! 🚀

• This topic was modified 2 weeks, 1 day ago by roseg.
• This topic was modified 2 weeks, 1 day ago by roseg.

Topic

[Jim Kosloskey]

Are you using the Data Direct ODBC Drivers as distributed with Cloverleaf as an option?

If so, I have successfully dynamically passed parameters to MSSQL (and Oracle for that matter) Stored Procedures. There are considerations to be made but it can be done.

I thought you could only have one instance of the DD Drivers in one process but reference the drivers multiple time from different threads (different Tcl procs) – if that is what you are attempting. In the case where I did this though we used multiple sites although I think I did it in one site for testing. I used one generic Tcl proc though used multiple times – but I do not think that should make a difference.

The most consumptive action one can do with any I/O file or DB is to Open and, in some cases, Close. This is because a lot of overhead is expended preparing for what is anticipated to be multiple reads or writes, making the performance of the reads and writes better.

Besides what you configure in your odbc.ini file for one, persistent connection, I believe something has to be done on the DB side as well, but I do not recall. In my case we used persistent Connection specification, and it worked well. Or5 are you saying you have multiple threads accessing the same DB and you only want one connection?

Bottom line, I think this should work for you and I don’t think you need an architectural change as long as all of the ODBC work is Data Direct and in one process in the site. Otherwise, you might be well served to re-analyze the site architecture with DB interaction in mind.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[roseg]

Thank you very much for the quick response!

We have a DB server with a single database containing many tables that are accessed via Cloverleaf. Several routes therefore need to establish and tear down a connection to this DB server, the database, and the respective tables in parallel.

The central IB thread (Process1) distributes to 6 dispatcher threads (each in its own process). These dispatcher threads in turn distribute to around 10 destination threads each (in the OB site). Dispatcher and destination threads run in the same process.

In our current TCL script, we are using standard TCL methods:

tcl
package require tdbc::odbc set connStr {Driver=SQL Server;DATABASE=**********;UID=**********;PWD=**********;Server=**********}

tdbc::odbc::connection create db $connStr

…to establish and close the database connection.

Another approach we tried was using a Database-Inbound and a Database-Outbound thread. These connect via the database configurations defined in Cloverleaf (Options -> Site Preferences -> Database Configurations ->
jdbc:sqlserver://<server>:<port1433>;DatabaseName=<database>
[Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver]
[mssql-jdbc-6.2.2.jre8.jar])

Are these the “Data Direct ODBC Drivers” you were referring to?

When using the DB-Inbound and DB-Outbound threads, we are currently failing to pass an SQL statement or parameters for a stored procedure from the TCL script to the thread.

If possible, we would like to establish one database connection per site — or per process — to a shared/central database that can then be used by all threads (regardless of which process they belong to).

Any insights & experiences would be greatly appreciated! 🚀

 

Vielen Dank für die schnelle Rückmeldung!

Wir haben einen DB-Server mit einer Datenbank, die viele Tabellen enthält, auf die über Cloverleaf zugegriffen wird. Mehrere Routen müssen daher parallel eine Verbindung zu diesem DB-Server, zur Datenbank und zu den jeweiligen Tabellen aufbauen und auch wieder abbauen.

Der zentrale IB-Thread (Process1) verteilt auf 6 Dispatcher-Threads (jeweils 1 Prozess). Diese Dispatcher-Threads verteilen weiter an jeweils ca. 10 Destination-Threads (in die OB-Site). Dispatcher-Threads und Destination-Threads laufen im selben Prozess.

Im aktuellen TCL-Skript verwenden wir Standardmittel von TCL:

tcl
package require tdbc::odbc set connStr {Driver=SQL Server;DATABASE=**********;UID=**********;PWD=**********;Server=**********}

tdbc::odbc::connection create db $connStr

…um die Datenbankverbindung aufzubauen und wieder zu schließen.

Ein anderer Ansatz war, einen Database-Inbound- und einen Database-Outbound-Thread zu verwenden. Diese Threads verbinden sich über die in Cloverleaf konfigurierten Datenbanken (Options -> Site Preferences -> Database Configurations ->
jdbc:sqlserver://<server>:<port1433>;DatabaseName=<database>
[Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver]
[mssql-jdbc-6.2.2.jre8.jar])

Sind das die “Data Direct ODBC-Treiber”, die du meinst?

Bei der Verwendung von Database-Inbound- und Outbound-Threads scheitern wir aktuell an der Übergabe eines SQL-Statements bzw. an der Übergabe von Parametern für Stored Procedures aus dem TCL-Skript an den Thread.

Falls möglich, möchten wir gerne pro Site oder alternativ pro Prozess eine Verbindung zu einer gemeinsamen bzw. zentralen Datenbank aufbauen, die dann von allen Threads (egal in welchem Prozess) verwendet werden kann.

Wir freuen uns über jeglichen Input & Erfahrungen! 🚀

• This reply was modified 1 week, 6 days ago by roseg.

[Jim Kosloskey]

It does not appear you are using the Data Direct Drivers. The DB Protocol use JDBC drivers which are not the same. The Data Dirext Drivers are optional from INFOR (extra money). They are very fast and have additional functionality beyond what is normally provided by most odbc (and even the JDBC) drivers. I am not familiar with the tdbc package so I cannot comment on its potential. I can comment on the Data Direct drivers which I used before Cloverleaf and with Cloverleaf. I know I could dynamically define parameter sets for Stored Procedures using the Data Direct Drivers, but I am not sure whether that can be accomplished with the tdbc package. Based on how the Data Direct Drivers work in that regard, I think the technique is not dependent on the drivers – but I could be wrong.

I now better understand your situation wherein you have a single DB with multiple Tables and you want one connection to the DB then use that connection multiple times in different threads. I did not do that with the Data Direct Drivers. Perhaps what Rob described wherein the handle of the Connection is stored in a Global Variable may be your solution – assuming the tdbc package does not bind the handle to the instance and it can be shared across instances. I see challenges in handling DB issues which may be detected by each thread/route using this global approach, but I do not think the challenges are insurmountable. For example, if there is a DB issue experienced by one of the thread/route and the DB needs to be disconnected, how would that be communicated reliably to the other thread/routes so they do not choke trying to reference the DB? I am sure there could be other challenges. Perhaps Rob and his team solved those challenges – or maybe they do not exist. In any case, I would spend some time thinking that through.

I have not used the DB protocol for invoking a Stored Procedure but I have used the DB Lookup with Stored Procedure and I was able to define the parameters for each lookup. I suspect the technique is similar for the protocol but that is somewhat static in that each protocol instance has its set of parameters, I think. So, if you want to have a single DB protocol thread to be used with multiple Stored Procedures each having their own parameter set, I am not sure that can be done. However, if each interaction with the DB is to be done in its own thread, then I think you should be able to configure the parameter set appropriately.

I do not have a DB I can play around with (certainly not MSSQL) to exercise the potentials. Otherwise, I would do some experimentation to see what can be accomplished.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Rob Lindsey]

Back in the day we did this type of thing where there was a call to a database server on the network and we did a single call and used a Cached db connection that was opened.  So in the “start” section of the tclprocs that were using db calls, we would use a Global variable for the connection to the DB.  So if the global variable did not have anything in it… then we would do a DB open call and set the global variable and then it would be available to all of the tclprocs within that process.  Of course, you would have to put a disconnect in the shutdown section of the tclprocs so that the DB connections are closed.

[roseg]

Thank you for the feedback!

We had also already tried the global variable approach, but unfortunately we weren’t successful with it.
We even implemented a fallback that checked whether the variable had a value – and if it was empty, a new connection was established.
Unfortunately, the variable was always empty on each run of the Tcl script.

Unfortunately, we no longer have the script, but I’m quite sure we had everything inside the “run” section, which may have been the mistake?

How exactly should the code in the “start” section look?
<pre>start {
# Define a global variable
global dbConnection

# Establish database connection
if {$dbConnection eq “”} {
puts stdout “No connection found, creating new one…”
set connStr “Driver=SQL Server;DATABASE=cl_orderentry;UID=USER;PWD=PASS;Server=SQL-SERVER”
if {[catch {
tdbc::odbc::connection create dbConnection $connStr
} result]} {
puts stdout “Error: $result”
return {}
}
}
}

run {
[…]
set sqlstmnt “select Nachrichten from $dbtbl where Fallnummer = ‘$fallnr’ and Freischaltdatum is not null”
set free [dbConnection allrows $sqlstmnt]
[…]
}

shutdown {
dbConnection close
}

Vielen Dank für die Rückmeldungen!</pre>
Den Ansatz mit der globalen Variable hatten wir auch schon verfolgt, waren damit aber leider nicht erfolgreich.
Wir hatten sogar ein Fallback implementiert, das prüft, ob die Variable Inhalt hat – und wenn sie leer war, wurde eine neue Verbindung aufgebaut.
Leider war sie jedoch bei jedem Durchlauf des Tcl-Skripts immer leer.

Das ursprüngliche Skript haben wir leider nicht mehr, aber ich bin mir ziemlich sicher, dass wir alles im “run”-Teil untergebracht hatten – was eventuell schon der Fehler war?

Wie genau müsste der Teil im “start”-Abschnitt aussehen?
<pre>start {
# Definition einer globalen Variable
global dbConnection

# Datenbankverbindung aufbauen
if {$dbConnection eq “”} {
puts stdout “Verbindung nicht vorhanden, erstelle neue…”
set connStr “Driver=SQL Server;DATABASE=cl_orderentry;UID=USER;PWD=PASS;Server=SQL-SERVER”
if {[catch {
tdbc::odbc::connection create dbConnection $connStr
} result]} {
puts stdout “Fehler: $result”
return {}
}
}
}

run {
[…]
set sqlstmnt “select Nachrichten from $dbtbl where Fallnummer = ‘$fallnr’ and Freischaltdatum is not null”
set free [dbConnection allrows $sqlstmnt]
[…]
}

shutdown {
dbConnection close
}</pre>

[Jim Kosloskey]

I think what Rob was referring to is the Cloverleaf Global Variables not a Tcl Global Variable.

Look up ‘global variables’ in the Cloverleaf doc. Just read it carefully as there are ‘Virtual’ or temporary Cloverleaf Variables and ‘fixed’ Global Variables (my terms) and you need to make sure you get the variable set the correct way. I scratched my head over the use of these variables for a while before I figured that out. I think I posted somewhere on this forum I think there is a bug in the way the variables can be handled. I do not think that should impact you if you study the doc well.

What I see you using is the Tcl global which I don’t think is persistent enough for what you need.

 

 

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[roseg]

From my understanding, the following types of global variables exist:

💡 Quick Comparison:

Criterion | TCL global variable | Cloverleaf globalSet variable
———————————————————————————————–
Scope | Only during script runtime | Across messages (per process)
Visibility | Local to the thread | In the shared memory of the Cloverleaf site
Lifetime | Until script ends | Until process restart or global unset
Example | global myVar | globalSet myVar “abc”
Use for persistent DB connection? | ❌ No | ⚠️ Limited (not suitable for objects like DB handles!)
However, neither of the two options seems suitable for my goal.

Is Rob perhaps referring to a third possibility here?

Nach meinem Verständnis gibt es folgende globale Variablen:

💡 Vergleich auf einen Blick:

Kriterium | TCL global Variable | Cloverleaf globalSet Variable
———————————————————————————————–
Gültigkeit | Nur während Script-Laufzeit | Über Nachrichten hinweg (pro Prozess)
Sichtbarkeit | Lokal im Thread | Im Shared Memory der Cloverleaf-Site
Lebensdauer | Bis Script-Ende | Bis Prozess-Neustart oder global unset
Beispiel | global myVar | globalSet myVar “abc”
Nutzung für persistente DBConn? | ❌ Nein | ⚠️ Eingeschränkt (nicht für Objekte wie DB-Handles!)
Aber keine der beiden Varianten ist zielführend für mein Vorhaben.

Spricht Rob hier eventuell von einer dritten Möglichkeit?

• This reply was modified 1 week, 1 day ago by roseg.
• This reply was modified 1 week, 1 day ago by roseg.
• This reply was modified 1 week, 1 day ago by roseg.

[Jim Kosloskey]

The one I am referring to is the Cloverleaf Global Variables with the Tcl commands that begin with gv(damn I wish we could paste screen shots here) such as gvgetvar and gvsetvar with variable names beginning with $$. The Cloverleaf help has a lot of documentation on this feature – are you referring to that?

What release of Cloverleaf are you using? I am not sure what release CGV feature was introduced.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[roseg]

You’re referring to the global variables ($$) that are defined in D:\healthcomm\cloverleaf\cis19.1\integrator\master\AppDefaults\globalVariables.ini, for example.
However – as far as I understand – these variables cannot be used to store objects like DB connections.

Global variables created within a Tcl script (e.g., global dbConnection) are only valid within the scope of that specific script execution.

We’re currently using Cloverleaf 19.1 and already make use of Cloverleaf global variables ($$) in several Xlates.

Best regards,
Robert

Du meinst die Globalen Variablen ($$), die z. B. in der D:\healthcomm\cloverleaf\cis19.1\integrator\master\AppDefaults\globalVariables.ini definiert werden.
Diese Variablen können aber – nach meinem Verständnis – nicht für Objekte (z. B. DB-Verbindungen) genutzt werden.

Globale Variablen, die im TCL-Umfeld erstellt werden (z. B. global dbConnection), gelten hingegen nur innerhalb des aufgerufenen Scripts.

Wir setzen aktuell Cloverleaf 19.1 ein und nutzen bereits Cloverleaf Globale Variablen ($$) in mehreren Xlates.

Grüße
Robert

[Jim Kosloskey]

You may be correct, I have not tried that. Can you cite the authority for the global variables not being used for DB Handles?

The User Doc I see with 2209 indicates string content and does not specifically preclude object handles (but that does not mean they are not precluded). Have you tried using the global variables for the handle associated with the DB connection?

I guess as an alternative, and depending on the detail of your architecture, you could limit the connects somewhat by using the Tcl global variables at thread start. So, for those threads there would only be one open at start (more if there is an intervening drop of connection). This assumes each thread has a finite Table set it addresses, and a single connection can be effective.

Assume messages get directed in a site to multiple destinations and each destination addresses the management of a given DB Table within the thread. A single connect at the Start Context would be made and then used for as many messages traverse that destination thread. If there then 5 destinations, there would be 5 Connections for however many messages traverse instead of a connection for each message. Not what you expressed you wanted, but I think has the potential to reduce significantly the number of connections to the DB.

The above is purely speculation on my part not intimately knowing your architecture and not being familiar with the odbc package you are using. But, based on my experience with the Data Direct and assuming a like architecture the above should work.

 

 

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Charlie Bursell]

If you put the threads accessing the database in the same process you can use Tcl global values or even a namespace since they would all be using the same Tcl interpreter.

 

Just a thought

[Author]

Replies