SQL statistics discrepancies

Products

• IDMS
• IDMS - Database

Issue/Introduction

This document describes why the total number of Commands in a DCMT DISPLAY STATISTICS SYSTEM output is often a lot more than the sum of the discrete functions.

Environment

Release: All supported releases.

Resolution

When issueing DCMT D STA SYS, it may be observed that the number in the Commands field is much larger than the sum of the discrete functions (Tuples Fetched, Rows Inserted, Rows Updated, Rows Deleted etc).

The statistics for the individual discrete functions are counted by IDMSHLDB, which is the module that handles the execution of all application SQL DML verbs. It is here where the numbers of FETCH, INSERT, UPDATE, DELETE, SORT etc are counted. These figures are very specific and reflect the SQL DML being executed.

The total number of commands, however, is counted by IDMSQSRV, the SQL server module. The number is incremented for each SQL request. This means that not only are all of the normal SQL DML application commands included, but also a lot of "internal" SQL commands, such as start and end transaction processing, suspend and resume processing, etc. These figures are not reflected anywhere in the discrete function counts and so the total is often much greater than the sum.