Customizing the Microsoft SQL Server Environment

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.


Top of page

x
Specifying a Timeout Limit

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.



x
Syntax: How to Issue the TIMEOUT Command
ENGINE SQLMSS SET TIMEOUT {nn|0}

where:

SQLMSS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

nn

Is the number of seconds before a time-out occurs. 30 is the default value.

0

Represents an infinite period to wait for a response.


Top of page

x
Cancelling Long Requests

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.



x
Procedure: How to Cancel Long Requests
  1. From the Web Console menu bar choose Workspace, Configuration/Monitor, Java Services, DEFAULT. Right-click on DEFAULT and select Agents.
  2. In the Java Services Agents pane, highlight a row with the jscomid you wish to kill, right-click on it and select Stop.

Top of page

x
Specifying the Cursor Type

How to:

You can use the SET CURSORS command to specify the type of cursors for retrieval.



x
Syntax: How to Specify the Cursor Type
x

The available parameters are:

ENGINE SQLMSS SET CURSORS [CLIENT|SERVER]

where:

CLIENT

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.

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.

blank

Uses client-side cursors in TRANSACTIONS AUTOCOMMITTED mode and server-side cursors otherwise. This value is the default.


Top of page

x
Specifying the Login Wait Time

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.



x
Syntax: How to Specify the Login Wait Time
ENGINE SQLMSS SET LOGINTIMEOUT|TIMEOUT {nn|0}

where:

SQLMSS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

nn

Is the number of seconds before a timeout occurs. The default value is approximately 15 seconds.

0

Represents an infinite period to wait for login response.


Top of page

x
Activating NONBLOCK Mode

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.



x
Syntax: How to Activate NONBLOCK Mode
x

The available parameters are:

ENGINE SQLMSS SET NONBLOCK {0|n}

where:

SQLMSS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

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:

  • Query has been executed.
  • Client application has requested the cancellation of a query.
  • Kill Session button on the Web Console is pressed.

Note: A value of 1 or 2 should be sufficient for normal operations.


Top of page

x
Obtaining the Number of Rows Updated or Deleted

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.



x
Syntax: How to Obtain the Number of Rows Updated or Deleted
x

The available parameters are:

ENGINE SQLMSS SET PASSRECS {ON|OFF}

where:

SQLMSS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

ON

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.

OFF

Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.


Top of page

x
Controlling Transactions

How to:

You can use the SET TRANSACTIONS command to controls how the adapter handles transactions.



x
Syntax: How to Control Transactions
x

The available parameters are:

ENGINE SQLMSS SET TRANSACTIONS {LOCAL|DISTRIBUTED|AUTOCOMMITTED}

where:

SQLMSS

Indicates the adapter. You can omit this value if you previously issued the SET SQLENGINE command.

LOCAL

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.

DISTRIBUTED

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.

AUTOCOMMITTED

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.


Top of page

x
Specifying the Transaction Isolation Level

How to:

You can specify the transaction isolation level from the Web Console or using the SET ISOLATION command.



x
Syntax: How to Specify Transaction Isolation Level From a SET Command
x

You can specify transaction isolation level by issuing the following command

ENGINE SQLMSS SET ISOLATION {RU|RC|RR|SE|CH|CS}

where:

RU

Sets the transaction isolation level to Read Uncommitted.

RC

Sets the transaction isolation level to Read Committed.

RR

Sets the transaction isolation level to Repeatable Read.

SE

Sets the transaction isolation level to Serializable Read.

CH

Sets the transaction isolation level to Chaos.

CS

Sets the transaction isolation level to Cursor Stability, which is a synonym for Read Committed.


iWay Software