Shared Memory Diagnostics for Adaptive Server ODBC Driver

Adaptive Server ODBC Driver allows application users and administrators to monitor driver and database performance.

Access to this information is available both programmatically by using the application, and externally using a new utility, aseodbcstatus. To use the utility, you must configure instrumentation to use shared memory.

Enabling Adaptive Server ODBC Driver Instrumentation Without Modifying the Application

Enable ODBC instrumentation by setting either of these environment variables:

SYBASE_ODBC_FORCE_INSTRUMENTATION=1 – configures Adaptive Server ODBC Driver to enable instrumentation. If this environment variable value is not set or set to a value other than 1, you can enable instrumentation programmatically.
SYBASE_ODBC_INSTRUMENTATION_FINE=1 – configures Adaptive Server ODBC Driver to monitor network traffic at the statement level. When the variable is set, the network time is saved with the statement currently executing. Setting the environment variable to 1 means that the application cannot use multiple threads to access the Sybase ODBC library. When the environment variable is not set, network time is collected at the application level.

Configuring Shared Memory Instrumentation

Shared memory makes instrumentation data available through the aseodbcstatus utility. The shared memory segments are enabled and configured through the SYBASE_ODBC_STATEMENT_DIAGNOSTICS_SHMEM environment variable.

SYBASE_ODBC_STATEMENT_DIAGNOSTICS_SHMEM=<number of diagnostic sections to put in one shared memory segment> 
(example 512)

To enable shared memory instrumentation, set SYBASE_ODBC_STATEMENT_DIAGNOSTICS_SHMEM to a value greater than zero (the default).

Setting the environment variable to a small number may cause Adaptive Server ODBC Driver to use many shared memory segments, which in turn, depending on the operating system, might carry a performance impact. Setting the environment variable to a large value might cause the Adaptive Server ODBC Driver to use a shared memory segment larger than necessary.

If you know approximately the number of statements your application uses, set the value of SYBASE_ODBC_STATEMENT_DIAGNOSTICS_SHMEM to a few more than that number. For example, if your application uses between 250 and 350 statements, set the value of SYBASE_ODBC_STATEMENT_DIAGNOSTICS_SHMEM to 360. If your application uses a wide range of statements (for example, between 100 to 10000 statements), using the maximum number of statements may use too much memory when the application does not use all of the statements. Instead, use a smaller value and allow the number of memory blocks to increase. In this case, try using double the smallest number of statements the application typically uses.

Retrieving Instrumentation Data Using the aseodbcstatus Utility

To retrieve instrumentation data, use the aseodbcstatus utility, which connects to the shared memory segments and displays instrumentation data.

aseodbcstatus accepts these parameters:

-help – displays a list of valid parameters.
-check <memory_area> <pid> – checks availability of the specified memory area for the given process ID. If the memory area is unavailable, aseodbcstatus exits with a nonzero status.
-print <memory_area> <pid> – prints the instrumentation data contained in the specified memory area for the given process ID. If the data is unknown (for example, the version of asedobcstatus used is older than the version of the ODBC driver) asedobcstatus exits with a nonzero status.
-statement_diagnostics <pid> <sid> <filter | all> – prints out the instrumentation data for the specified statement ID (<sid>). Passing -1 for the statement ID prints the data for all statements. If filter is passed in, only instrumentation data with a nonzero count appears.

The aseodbcstatus utility has a number of memory areas that control the data that is retrieved. Possible values are:

InstrumentationTimes – global instrumentation data for the a specific ID. This is the combined data for all connections and statements used by the process.
InstrumentationTimesName – the list of names, in order, of each line of instrumentation data for both the InstrumentationTimes and statement_diagnostics.
StatementIDs – lists the statement IDs used with statement_diagnostics.

Using Instrumentation Programmatically

The application can directly use environment, connection, and statement attributes to enable and access instrumentation. The environment and connection attributes are identical, and both work globally for the application. The connection attributes are available for applications using a driver manager that does not support custom environment attributes. The attributes are:

SQL_ATTR_INSTRUMENTATION – controls the behavior of the instrumentation. Supported values include:
- SQL_INSTRUMENTATION_ENABLE – turns on instrumentation data collection.
- SQL_INSTRUMENTATION_DISABLE – turns off instrumentation data collection.
- SQL_INSTRUMENTATION_CLEAR – this is the only value supported on the statement attribute. When set on the environment or connection attribute, SQL_INSTRUMENTATION_CLEAR clears the global instrumentation data. When set on the statement attribute, SQL_INSTRUMENTATION_CLEAR clears the instrumentation data for that statement.
- SQL_INSTRUMENTATION_CLEAR_ALL – clears the global and all statement instrumentation.
- SQL_INSTRUMENTATION_FINE – enables collection of more detailed instrumentation data including locks, network, and various aspects of select statements and batches.

SQL_ATTR_INSTRUMENTATION_LOG – retrieves the instrumentation data formatted as a SQLWCHAR string. When used on the environment or a connection, SQL_ATTR_INTRUMENTATION_LOG retrieves global instrumentation data. When used on a statement, SQL_ATTR_INTRUMENTATION_LOG retrieves instrumentation data only for that statement. The string is formatted as a semicolon-separated list. The format for each item is:
```
<instrumentation name>:<time in us>,<count >
```
For example:
```
Unknown:0,0; SocketRetrieve:75,19; 
Waiting for lock XATransactionManager:0,0; 
Holding lock XATransactionManager:0,
0; SQLAllocHandle:149,20;
```