Time Field Definitions

All of the fields listed in this appendix can be found in the RMLDATA/RMLDB synonyms.

The following field contents are based on "wall clock" time and are stored in A9 fields in date/time (HHMMSSNNN) format. DEFINE fields are provided for individual components.

Session table - SMSESS

SESSSTART - SESSSTOP

These two fields cover the time a connection request causes the Resource Management (RM) code to be called and the time the connection is terminated.

RPC table - SMRPCS

SMRPCTIME - SMRPCSTOP

These two fields cover the start and stop time of all the Remote Procedure Call (RPC) requests in a session. There is at least one RPC request per session but there can be more.

Command table - SMQUERY

SMTIME - SMSTOP

These two fields cover the start and stop time of the Command (for example, TABLE, TABLEF, and MODIFY). There can be one or more Commands processed per RPC per session.

The following fields are based on machine timers and the data is stored in 10,000th of a second. DEFINE fields are provided for times converted to seconds, Minutes, and Hours.

Session table - SMSESS

SESSDUR

This field contains the total duration of the session.

SESSCPU

This field contains the total CPU used during the session.

SMDBMSTIME

This field is the total of all SMCMDDBMSTIME values for all commands executed during the session.

RPC table - SMRPCS

SMRPCETIME

This field is the total elapsed time from the start of the RPC to the end of the RPC request.

SMRPCCTIME

This field is the total CPU time used from the start of the RPC to the end of the RPC request.

SMRPCDBMSTIME

This field is the total of all SMCMDDBMSTIME values for all commands executed by the RPC.

Command table - SMQUERY

SMELAPETIME

This field is the total elapsed time from the start of a command to the end of the command. This value contains the elapsed time of the command and also includes the elapsed time spent processing a command that uses DBMS time.

SMCPUTIME

This field is the total CPU time from the start of a command to the end of the command.

SMCMDDBMSTIME

This field is the total elapsed time from the start of the servers SQL Adapter processing to the end of the SQL adapters data retrieval.

This time field is only created at the request level and is the length of time spent by the various server processes that prepare the request, sends the request to the RDBMS server, and processes the answer set received. The DBMS time values for a SMRPCDBMSTIME and SMDBMSTIME field are accumulation values derived from all of the commands executed in the session.

When a SQL request that accesses data starts, the DBMS start timer is set to the current time in 10,000^th of a second. When the SQL request ends, the DBMS end timer is set to the current time in 10,000th of a second. The DBMS time is a subset of time spent while the SQL request was being processed between the time Resource Analyzer started monitoring the request and the time Resource Analyzer finished. The CPU time consumed by the RDBMS while processing the request is not available to RM and is not included.

There are two different ways the DMBS timer is used:

• Direct Passthru Requests
• Synonym Requests

Direct Passthru Requests:

The SMCMDDBMSTIME timer starts when the DPT processor is called and ends when the DPT processor finishes processing. The length of time spent in this process is stored in the repository and is used in reporting as HH:MM:SS.nnnn. This type of request will have a small difference between the SMELAPTIME and the SMCMDDBMSTIME because of the minimal amount of time used by the server to prepare the request for processing. As stated before, SMCMDDBMSTIME is only an elapsed time value and includes both server SQL adapter time and RDBMS server time.

Synonym Requests:

As with Direct Passthru requests, the SMCMDDBMSTIME timer starts when the request type processor is called and ends when the processor finishes processing. The length of time spent in this process is stored in the repository and is used in reporting as HH:MM:SS.nnnn. This type of request will have a larger difference between the SMELAPTIME and the SMCMDDBMSTIME because of the additional processing time used to transform and optimize the request to SQL syntax that meets the requirements of the RDBMS server. The difference also includes the processing of the synonym before transformation and optimization and processing the returned answer set.

The following image shows an example of a session that executes one RPC that executes two commands.

The session duration and CPU timers are started when the connection to the server is established and ends when the session disconnects. At session start time, no DBMS timer is started. The RPC duration and CPU timers are started when the server calls the Resource Management RPC processor. Just like the session processing no DBMS timer is started. When an RPC executes a monitored command, the Resource Management command processor is called for initialization. After initialization finishes and Resource Management returns control to the server, one of two paths will be taken, either Direct Passthru or Synonym processing.

For an SQL command that uses a Direct Passthru command, the processor starts the server DBMS timer. When the answer set is retrieved, control is returned to the server and the DBMS timer is stopped. The Resource Management command processor is then called to end its processing.

For a synonym command that accesses data on a DBMS server the request is transformed and optimized, if needed, and then calls the SQL processor, which starts DBMS timer. When the answer set is retrieved, control is returned to the server and the DBMS timer is stopped. When the answer set has been formatted, Resource Management command process is called to end its processing.

During the call to end the Resource Management command process at the end of a request, the DBMS timer value of the server is copied to the SMCMDDBMSTIME field. It is also added to the SMRPCDBMSTIME and SMDBMSTIME fields. If the current RPC processes another command, the process outlined above is repeated.