Typically, the DataServer maintains one connection to an MS SQL Server data source. In some instances, such as for joins and catalog queries, the DataServer automatically starts separate connections. However, there are circumstances in which a DataServer application might require more than one connection. For example, the DataServer cannot send a query to a data source while a stored procedure is still open unless you specify that the DataServer uses separate connections for each request. Depending on the capabilities of the ODBC driver being used, the following cases may be candidates for using additional connections to accommodate additional cursors:
Running multiple stored procedures
Running a stored procedure and a send-sql-statement simultaneously
Performing a join on the server
Creating or updating the schema image for the data source
In the first case, additional connections are necessary only if your application executes additional database requests while a cursor on a stored procedure is still open.
You can use the -Dsrv qt_separate_connection parameter or the corresponding QUERY-TUNING(SEPARATE-CONNECTION) option to specify that the DataServer uses a separate connection for each statement that requires a cursor. However, if you want to use the main connection when performing joins on the server, use the -Dsrv qt_no_separate_connection parameter when you connect. Note that using a separate connection allows only read-only access to the database. You must issue transactions that require update access to your database from your main connection.
For example, the following statement specifies that the DataServer use a separate connection for the FOR EACH Customer query:
FOR EACH Customer QUERY-TUNING (SEPARATE-CONNECTION):
DISPLAY Customer.
END.
Running multiple stored procedures