| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect |
![[-]](/dwicons/i_colpse.gif)
| Chapter 5 Understanding the Request process |
Chapter 5
This chapter presents an overview of the request process, including Application Program Interface (API) procedure calls and how they interact with the access service on a command-based level.
This chapter presents the following topics:
The access service processes the following types of requests:
SQL statements:
As a language command
As a cursor command (CT-Library only)
As a dynamic command
Catalog stored procedure (CSP) requests
Remote procedure call ( RPC) requests
Configuration property statements, such as:
set statement with a configuration property
select configuration property value (@@global variable)
transfer statements
Request processing follows these steps:
The client application issues a request (for example, a select statement).
The client API (for example, CT-Library, DB-Library, or ODBC) receives the request and sends it to the access service.
The access service receives the request, transforms it (if needed), and executes the request.
After the request processes, the access service converts target datatypes to Open Server datatypes and returns the results to the client application.
The client application disconnects from the access service.
The following figure shows the processing flow of the APIs through DirectConnect to DB2.
Figure 5-1: Processing flow through API to DB2
For more information about Open Client/Server products, see the Sybase Client Client-Library/C Reference Manual and the Sybase Server Server-Library/C Reference Manual.
This section describes how the following client API procedure calls interact with the access service on a command-based level:
DB-Library API
ODBC API, if the client is using an ODBC application
CT-Library API
The following examples are for language commands only. For information about specific APIs, see the Open Client Client-Library/C Reference Manual.
The following figure shows the interaction between a DB-Library client application and the access service.
Figure 5-2: DB-Library client API example
The DB-Library API processing flow includes the following steps:
The client application connects to a specific DirectConnect access service using the dbopen call. This initiates a connect event within the server, such as DB2. Then, DirectConnect logs on to the DB2 server, using the specified user ID and password.
The application builds and executes a SQL request using the dbcmd and dbsqlexec calls. The access service receives the SQL request as a language event, transforms datatypes (if needed), and passes the request on to DB2.
To prepare for the resulting data, the application assigns local variables to specific columns using the dbbind call. The dbnextrow call retrieves the resulting data (the results of the processed request made by the client) and returns the data to the application.
The client application terminates database processing using a dbclose call. This initiates a disconnect event in the DirectConnect server.
Procedure calls for the DB-Library API are explained in the Microsoft SQL Server Programmers Reference and the Sybase Open Client DB-Library/C Reference Manual.
For information about accessing database catalog information with applications that use CSPs, see Chapter 12, "Accessing catalog information with CSPs"
Sybase provides an ODBC Driver that allows ODBC applications to access DB2 through DirectConnect.
The following figure shows the interaction between an ODBC client application and DirectConnect.
Figure 5-3: ODBC client API example
The ODBC API processing flow includes the following steps:
Using the SQLConnect call, the ODBC client application initiates a connect event to a specific access service in DirectConnect. The ODBC driver uses the CT-Library API.
The ODBC application builds and executes the request using the SQLExecDirect function, initiating a language event in DirectConnect.
The ODBC API uses SQLBindCol to assign local variables to specific columns. The SQLFetch call then returns the resulting data to the application.
The ODBC API terminates the request with a SQLDisconnect call, initiating a disconnect event.
Procedure calls for the ODBC API are described in the Microsoft ODBC Programmer's Reference and SDK Guide.
The following figure shows the interaction between a Sybase Open Client CT-Library API and DirectConnect.
Figure 5-4: CT-Library client API example
The CT-Library client API processing flow includes the following steps:
When a CT-Library application issues a request, it uses the ct_connect call to initiate a connect event to a specific access service.
Using the ct_command and ct_send calls, the CT-Library API executes the request, initiating a language event in DirectConnect.
The CT-Library API uses ct_fetch to return the requested results to the client application.
The CT-Library API terminates the request with the ct_close call, initiating a disconnect event.
Procedure calls for the CT-Library API are described in the Sybase Open Client Client-Library/C Reference Manual.
To fully understand how properties in the access service interact to control the processing flow, you should first understand the following concepts:
Request
Unit of work
Short and long transactions
These concepts are discussed in the following section.
A request is one or more database operations sent by the client application as one unit to the database. (For the access service, a database operation is usually a SQL statement.) During a request, the client application gives up control to the DBMS and waits for a response.
A unit of work is one or more requests that are committed or rolled back as a group.
If all the requests process successfully, the unit of work is committed, and the requested changes to the database are permanent. Depending on the setting of the TransactionMode property, either the client application or the access service issues the commit statement.
For more information about the StopCondition property, which controls the conditions under which the access service stops processing, see page and page .
For the access service, a transaction is equivalent to a unit of work (one or more requests). A transaction can span many requests.
Depending on access service property settings, the TransactionMode property that governs transaction behavior can be set to either short or long.
Short transactionsWhen short transactions are in effect, the access service is responsible for controlling the commitment of requests. After sending the request to the database, the access service automatically issues one of the following:
A commit, if the request succeeds
A rollback, if the request fails
While in short transaction mode, the request is a unit of work.
A begin transaction phrase can affect the behavior of a short transaction. If it receives a begin transaction phrase, the access service:
Triggers a commit of all previous statements in the request.
Temporarily sets TransactionMode to long, although a select @@TransactionMode command returns an answer of short mode. While in temporary long mode, if the batch processes successfully, the SQL statements are committed.
Stays in temporary long transaction mode until a unit of work is completed with a commit or rollback. Then, the access service reverts TransactionMode to short.
Do not change configuration properties while the request is in temporary long mode. Once a commit or rollback occurs, the temporary long mode reverts to short mode.
When long transactions are in effect, the client application is responsible for controlling when the transaction ends (by issuing either a commit or a rollback).
If the access service encounters a begin transaction phrase in a request, the phrase is ignored because the phrase does not affect the unit of work management.
The client application issues a commit or rollback statement for each transaction. When the client application closes its connection, the access service issues a rollback before exiting. Therefore, the client must commit any work that should be committed.
If the client application does not issue timely commits or rollbacks, then host resources, such as the APPC session or DB2 logging and locking, are held for an indeterminate amount of time. Therefore, longtransactions can cause performance problems for other applications that need to access the same resource.
See the Managing processing section that follows for more information about the Allocate and StopCondition properties, and how they interact with the transaction mode.
You can control processing by using configuration properties to determine the following:
How many rows are returned (MaxRowsReturned)
Whether to stop when an error occurs (StopCondition)
How to allocate conversations with a target DB2 system (Allocate)
For more information about the preceding properties, see Chapter 2, "Configuring the DirectConnect Access Service Library" .
To set values for properties in the configuration file, you must know the specific category the property belongs to, verify that the category exists in the file, and enter the property value under the category.
The MaxRowsReturned property specifies the maximum number of rows retrieved in a result set. (A result set is all or part of the results from a processed SQL statement.) However, one SQL request can produce multiple result sets.
If the number of rows exceeds the value of the MaxRowsReturned property, the access service returns the maximum number of rows and issues a warning message. However, the access service issues warning messages only when the SendWarningMessages property is enabled.
The StopCondition property specifies whether the access service stops processing a request when it encounters an error or a warning. Valid values are:
error
warning
none
If you specify none, processing continues even when errors occur. The StopCondition property is useful if you batch multiple statements in a request.
The Allocate property specifies when the APPC conversation that exists between the access service and the target database system is allocated and deallocated. Valid values are connect and request.
If you specify connect (the default), the APPC conversation remains allocated until the client issues some form of deallocation, such as an exit. As a result, fewer APPC connections are available, but overhead for each client is reduced.
If you specify request, resources are generally released sooner and, as a result, more clients can access the target database at a time. However, overhead increases because of the repeated initiation of APPC conversations for each request handled. You can specify request for either short or long transactions.
The tables on the following pages show how combined configuration property settings can affect processing results. The first two tables are based on the TransactionMode property settings of short and long. The third table shows processing behavior when a begin transaction statement occurs.
The following four tables show the effects of combined StopCondition and Allocate properties, based on a TransactionMode setting of short.
The following table shows the effects of a TransactionMode property setting of short, an Allocate property of request and a StopCondition property of error.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | The transaction rolls back immediately. |
What is the status of the connection after an error occurs? | The connection ends. |
What happens if a begin transaction occurs? | A commit occurs and uses begin transaction behavior described in Table 5-9. |
When the client application issues a commit or rollback, what happens? | A commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | The request behaves the same as TransactionMode=long. |
In batch mode, what happens if an error occurs? | The batch ends and a rollback occurs. |
When completed, is a batch job committed? | The batch commits if no errors occur. |
After the batch commits, what is the status of the connection? | The connection ends. |
The following table shows the effects of a TransactionMode property setting of short, an Allocate property of request and a StopCondition property of none.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | A commit occurs and uses begin transaction behavior described in Table 5-9. |
When the client application issues a commit or rollback, what happens? | A commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | The request behaves the same as TransactionMode=long. |
In batch mode, what happens if an error occurs? | The batch continues executing. |
When completed, is a batch job committed? | The batch is always committed. |
After the batch commits, what is the status of the connection? | The connection ends. |
The following table shows the effects of a TransactionMode property setting of short, an Allocate property of connect and a StopCondition property of error.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | The transaction rolls back immediately. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | A commit occurs and uses begin transaction behavior described in Table 5-9. |
When the client application issues a commit or rollback, what happens? | A commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | The request behaves the same as TransactionMode=long. |
While in batch, what happens if an error occurs? | The batch request ends. |
When completed, is a batch job committed? | The batch commits if no errors occur. |
After the batch commits, what is the status of the connection? | The connection continues. |
The following table shows the effects of a TransactionMode property setting of short, an Allocate property of connect and a StopCondition of none.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | A commit occurs and uses begin transaction behavior described in Table 5-9. |
When the client application issues a commit or rollback, what happens? | A commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | The request behaves the same as TransactionMode=long. |
In batch mode, what happens if an error occurs? | The batch continues. |
When completed, is a batch job committed? | The batch commit occurs, if no errors occur. |
After the batch commits, what is the status of the connection? | The connection continues. |
The following four tables show the effects of StopCondition and Allocate properties, based on a TransactionMode setting of long.
The following table shows the effects of a TransactionMode property setting of long, an Allocate property of request and a StopCondition of error.
Condition/Status | Effect of Settings on Processing |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | The begin transaction is ignored. |
When the client application issues a commit or rollback, what happens? | If TransactionMode was originally short, and cursors and dynamics were all freed, the commit or rollback occurs and TransactionMode changes back to short. If TransactionMode was originally long, then a commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | If TransactionMode was originally short, and cursors and dynamics were all freed, a commit occurs and TransactionMode changes back to short. If TransactionMode was originally long, then the connection behaves the same as Allocate=request until all cursors and dynamics are freed. |
In batch mode, what happens if an error occurs? | The batch transaction ends. |
When completed, is a batch job committed? | The batch does not commit. |
After the batch commits, what is the status of the connection? | The connection ends if the commit or rollback occurs at the end of batch. Otherwise, the connection continues. |
The following table shows the effects of a TransactionMode property setting of long, an Allocate property of request and a StopCondition of none.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | The access service ignores the begin transaction command. |
When the client application issues a commit or rollback, what happens? | If TransactionMode was originally short, and cursors and dynamics were all freed, the commit or rollback occurs and TransactionMode changes back to short. If TransactionMode was originally long, then a commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | If TransactionMode was originally short, and cursors and dynamics were all freed, a commit occurs and TransactionMode changes back to short. If TransactionMode was originally long, then the connection behaves the same as Allocate=request until all cursors and dynamics are freed. |
While in batch, what happens if an error occurs? | The batch continues. |
When completed, is a batch job committed? | The batch does not commit. |
After the batch commits, what is the status of the connection? | The connection ends if the commit or rollback occurs at the end of the batch. Otherwise, the connection continues. |
The following table shows the effects of a TransactionMode property setting of long, an Allocate property of connect and a StopCondition of error.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | The access service ignores the begin transaction command. |
When the client application issues a commit or rollback, what happens? | If TransactionMode was originally short, and cursors and dynamics were all freed, the commit or rollback occurs and TransactionMode changes back to short. If TransactionMode was originally long, then a commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | If TransactionMode was originally short, and cursors and dynamics were all freed, the commit occurs and TransactionMode changes back to short. If TransactionMode was originally long, then the connection behaves the same as Allocate=request until all cursors and dynamics are freed. |
While in batch, what happens if an error occurs? | The batch request ends. |
When completed, is a batch job committed? | The batch does not commit. |
After the batch commits, what is the status of the connection? | The connection continues. |
The following table shows the effects of a TransactionMode property setting of long, an Allocate property of connect and a StopCondition of none.
Condition/Status | Effects of Settings on Processing |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if a begin transaction occurs? | The access service ignores the begin transaction command. |
When the client application issues a commit or rollback, what happens? | If TransactionMode was originally short, and cursors and dynamics were all freed, the commit or rollback occurs and TransactionMode changes back to short. If TransactionMode was originally long, then a commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | If TransactionMode was originally short, and cursors and dynamics were all freed, a commit occurs and TransactionMode changes back to short. If TransactionMode was originally long, then the connection behaves the same as Allocate=request until all cursors and dynamics are freed. |
In batch mode, what happens if an error occurs? | The batch continues. |
When completed, is a batch job committed? | The batch does not commit. |
After the batch commits, what is the status of the connection? | The connection continues. |
The following table shows the effect on processing when a begin transaction command occurs in a request.
Condition/Status | When begin transaction occurs... |
Does a rollback occur on an error? | A rollback does not occur. |
What is the status of the connection after an error occurs? | The connection continues. |
What happens if another begin transaction occurs? | The access service ignores the begin transaction command. |
When the client application issues a commit or rollback, what happens? | If TransactionMode was originally short, it changes back to short. If TransactionMode was originally long, then a commit or rollback occurs. |
If the request is a cursor or dynamic request, how does it behave? | The request continues behaving as a short setting. |
In batch mode, what happens if an error occurs? | The batch continues. |
When completed, is a batch job committed? | The batch does not commit. |
After the batch commits, what is the status of the connection? | The connection continues. |
You can troubleshoot processing problems by using server log and trace files.
Configuration properties control whether data is recorded in the server log file and server trace file for each logging and tracing option. To configure log and trace properties, edit the access service library configuration file.
For detailed information about access service library and access service logging and tracing properties, see Chapter 2, "Configuring the DirectConnect Access Service Library" in this manual.
For information about server logging and tracing properties and detailed information about the server log file and server trace file, see the DirectConnect Server Administration Guide.
Log properties allow you to record information for access service administration. Logging options are controlled by the server, access service library, and access service configuration properties. Each logging option requires the configuration property name and value.
Trace properties allow you to record troubleshooting information for Sybase Technical Support.
Warning!
To provide Sybase Technical Support with all necessary data, the server trace file will be allocated a maximum of 20 MB of space. When the server trace file exceeds the maximum it will be copied to a file with the same filename and with an "_old" extension (<filename>_old). See the DirectConnect Server Administration Guide for suggestions for deleting or backing up old log and trace files.
For information about configuration property syntax, see "Access Service Tracing properties" .
See "Sybase Technical Support" for detailed information.
|
|