Connection Cursor Control in QuerySurge

Versions:4.5+

Default Connection Configuration

As you know, your QuerySurge Agents send your QueryPair queries to your Source and Target databases and datastores using standard JDBC Connections. Under most circumstances, much of the Connection configuration is hidden from the user. Typically, the default Connection settings are sufficient for regular query execution. However, for some situations, the user may want or need greater control over the Connection configuration. In this article, we discuss how to specify the type of cursor that a specific JDBC driver uses.

The Default Behavior with Cursors

For the most part, the default cursor type is TYPE_FORWARD_ONLY. However, for some databases, we have the following specific settings:

Database	Cursor Type
default	TYPE_FORWARD_ONLY
SAP ASE, IQ (formerly Sybase)	TYPE_FORWARD_ONLY
Microsoft SQL Server	TYPE_SCROLL_INSENSITIVE
Teradata	TYPE_SCROLL_INSENSITIVE
Flat File, XML Drivers	TYPE_FORWARD_ONLY

Setting the Cursor Type

You can set specific cursors for JDBC drivers to use in the Agent config. This is done by editing the configuration XML for the Agent to set the cursor type. Note that this is done on both a per-Agent level and a per-JDBC driver level. So, if you want all of your Agents to use the same cursor type for the same driver, you'll need to edit the agentconfig.xml file for each Agent. Also, it is important to stress that once the cursor type is set for a particular driver, the driver will use that cursor for all of its Connections.

Note: Database cursors are set on both a per-Agent level and a per-JDBC driver level.

Note: Once the cursor type is set for a driver, the driver will use that cursor for all of its connections.

The steps for setting a cursor type are as follows:

1. Stop the Agent that you are planning to modify.
   
   
2. Locate the agentconfig.xml for the Agent:
   Windows: \\<QuerySurge Install Dir>\QuerySurge\agent\config\agentconfig.xml
   Linux: /<QuerySurge Install Dir>/QuerySurge/agent/config/agentconfig.xml on Linux
   
   
3. Make a copy of this file in its location.
   
   
4. Edit the agentconfig.xml file. You can use any text editor that respects Unix line termination. You may need to launch the text editor with admin rights in order to be able to edit and save the file.
   
   
5. Locate the <cursorType> tag in the agentconfig.xml. It should have commented sample <cursor> child tags:
   
   <cursorType>
<!--<cursor driver="com.microsoft.sqlserver.jdbc.SQLServerDriver">TYPE_FORWARD_ONLY</cursor>-->
<!--<cursor driver="oracle.jdbc.driver.OracleDriver">TYPE_SCROLL_INSENSITIVE</cursor>-->
</cursorType>
   
   Note that the <cursor> tags have an attribute specifying the driver class and a text value specifying the cursor type. You can add as many cursors as you'd like, by driver class and cursor type.
   
   Note: You can use values of: NONE or NOCURSOR for the text value of the <cursor> tag; with either of these values, no cursor will be created. In this case, the <cursor> tag (using the SQL Server driver as an example) looks like:
   <cursor driver="com.microsoft.sqlserver.jdbc.SQLServerDriver">NOCURSOR</cursor>
   
   
6. Once you have specified your cursors, save and close the agentconfig.xml file.
   
   
7. Restart the Agent.
   
   
8. Repeat these steps for all Agents that you need to modify. In general, it is a best practice to have your Agents all configured with the same configuration.

 Note: For those using Azure SQL Data Warehouse or Microsoft PDW (which do not accept a cursor setting) as a Source or Target, QuerySurge has a convenience setting, performed on the URL, which is described in this article. With this setup, only Azure SQL Data Warehouse or PDW connections are created with no cursor; other connections with the SQL Server driver are not affected.