In this section: |
The Adapter for Microsoft SQL Server provides several parameters for customizing the environment and optimizing performance. This topic provides an overview of customization options.
How to: |
TIMEOUT specifies the number of seconds the adapter will wait for a response after you issue an SQL request to Microsoft SQL Server.
ENGINE SQLMSS SET TIMEOUT {nn|0}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Is the number of seconds before a time-out occurs. 30 is the default value.
Represents an infinite period to wait for a response.
How to: |
You can cancel long running requests from the Web Console. Depending on the capabilities of the native JDBC driver, this action will either cancel the request entirely or break out of the fetch cycle.
How to: |
You can use the SET CURSORS command to specify the type of cursors for retrieval.
The available parameters are:
ENGINE SQLMSS SET CURSORS [CLIENT|SERVER]
where:
Uses Microsoft SQL Server client-side cursors for retrieving data. Client-side cursors normally demonstrate the best performance for data retrieval and benefit the Microsoft SQL Server process. However, except in TRANSACTIONS AUTOCOMMITTED mode, using client-side cursors prevents a server agent from simultaneously reading more than one answer set from the same instance of Microsoft SQL Server.
Uses Microsoft SQL Server server-side cursors for retrieving data. Server-side cursors demonstrate lower performance than client cursors. However, setting a high FETCHSIZE factor (100 is the adapter default) improves performance dramatically making them almost as fast as client-side cursors. Client-side cursors are recommended wherever possible to take the load off the Microsoft SQL Server process.
Uses client-side cursors in TRANSACTIONS AUTOCOMMITTED mode and server-side cursors otherwise. This value is the default.
How to: |
You can use the LOGINTIMEOUT command to specify the number of seconds the adapter will wait for a response from Microsoft SQL Server at connect time.
Note: For compatibility with previous releases of the adapter, TIMEOUT is available as a synonym for LOGINTIMEOUT.
ENGINE SQLMSS SET LOGINTIMEOUT|TIMEOUT {nn|0}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Is the number of seconds before a timeout occurs. The default value is approximately 15 seconds.
Represents an infinite period to wait for login response.
How to: |
The Adapter for Microsoft SQL Server has the ability to issue calls in NONBLOCK mode. The default behavior is BLOCK mode.
This feature allows the adapter to react to a client request to cancel a query while the adapter is waiting on engine processing. This wait state usually occurs during SQL parsing, before the first row of an answer set is ready for delivery to the adapter or while waiting for access to an object that has been locked by another application.
The available parameters are:
ENGINE SQLMSS SET NONBLOCK {0|n}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Is a positive numeric number. 0 is the default value, which means that the adapter will operate in BLOCK mode. A value of 1 or greater activates the NONBLOCK calling and specifies the time, in seconds, that the adapter will wait between each time it checks to see if the:
Note: A value of 1 or 2 should be sufficient for normal operations.
How to: |
PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.
Tip: You can change this setting manually or from the Web Console by clicking Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click menu. The Change Settings pane opens.
The available parameters are:
ENGINE SQLMSS SET PASSRECS {ON|OFF}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.
Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.
How to: |
You can use the SET TRANSACTIONS command to controls how the adapter handles transactions.
The available parameters are:
ENGINE SQLMSS SET TRANSACTIONS {LOCAL|DISTRIBUTED|AUTOCOMMITTED}
where:
Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.
Indicates that the adapter implicitly starts a local transaction on each of the connections where any work is performed. At the time of COMMIT or ROLLBACK, or at the end of the server session, the adapter commits or aborts the work on each connection consecutively. LOCAL is the default value.
Indicates that the adapter implicitly invokes Microsoft Distributed Transactions Coordinator (DTC) to create a single distributed transaction within which to perform all work on all the connections. At the time of COMMIT or ROLLBACK, or at the end of the server session, the adapter invokes DTC to execute the two-phase commit or rollback protocol. For this purpose, the DTC service must be started on the machine where the server is running and also on all the machines where involved instances of Microsoft SQL Server reside.
This mode is recommended for read-write applications that perform updates on multiple connections simultaneously.
Indicates that each individual operation with Microsoft SQL Server is immediately committed (if successful) or rolled back (in case of errors) by the SQL Server. This is recommended for read-only applications for performance considerations. It is not recommended for read-write applications because in this mode it is impossible to roll back a logical unit of work that consists of several operations.
How to: |
You can specify the transaction isolation level from the Web Console or using the SET ISOLATION command.
You can specify transaction isolation level by issuing the following command
ENGINE SQLMSS SET ISOLATION {RU|RC|RR|SE|CH|CS}
where:
Sets the transaction isolation level to Read Uncommitted.
Sets the transaction isolation level to Read Committed.
Sets the transaction isolation level to Repeatable Read.
Sets the transaction isolation level to Serializable Read.
Sets the transaction isolation level to Chaos.
Sets the transaction isolation level to Cursor Stability, which is a synonym for Read Committed.
WebFOCUS |