ODBC Server Installation and Management Manual

Managing NonStop ODBC Server Resources
HP NonStop ODBC Server Installation and Management Manual—429395-002
5-20
Usage Considerations
the governing policy to either stop the statement execution, if possible, or lower the
priority. As with scheduling, if the priority has been changed, the priority is restored at
the end of the query to the original priority set by SCS.
All user prepared statements are governed. In this release, the governing policy can be
triggered only once; after reaching one of the limits, the query is run with new priority
(or stopped), and the resource governing facility does not manage this query again.
Controlling Runaway Queries
As mentioned previously, the NonStop ODBC Server might occasionally have no
control over a runaway query. In this case, you can find the query by using the query
status table and manually stop it by terminating the server process.
Note that ExecuteDirect statements (which are executed through the SQLExecDirect
API call), for UPDATE, DELETE, or INSERT, are not prepared beforehand by the
NonStop ODBC Server. Consequently, if you did not set up the configuration to log
every statement, no status records are generated for these statements. Therefore, no
information will be available to manually govern a runaway ExecuteDirect statement
such as INSERT...SELECT * FROM.... HP recommends you always use SQLPrepare
and SQLExecute on long-running ExecuteDirect statements other than SELECT
statements, which you can do only from a scripting or programming language. Some
tools may not give you the ability to control which ODBC API calls are performed. You
can set up the configuration to log every statement, but be aware that this incurs a
large amount of overhead writing and deleting every status record.
The Transaction Model
When AUTOCOMMIT is on, the NonStop ODBC Server starts and ends a transaction
for every statement. If statement execution exceeds the limit and execution is stopped
by the resource governing facility, the NonStop ODBC Server automatically commits or
rolls back the transaction, depending on how the governing action is defined.
When AUTOCOMMIT is off, the ODBC driver explicitly starts a transaction each time
an application submits a transaction and no transaction is open. This transaction can
consist of one or more SQL statements. If one of the SQL statements exceeds a limit
and the associated governing action is to stop the execution and either commit or roll
back the transaction, the NonStop ODBC Server does not end or abort the transaction,
but sends a warning message to the user along with the result sets. The warning
message indicates the recommended action from the policy; the user must then
explicitly commit or roll back the transaction. This behavior is the same for a Sybase
application that starts a user transaction.
The only situation in which the NonStop ODBC Server does not start a transaction is
when the transaction isolation is set to SQL_TXN_READ_UNCOMMITTED,
AUTOCOMMIT is set to off, and the SQL statement is a SELECT.