GET STATISTICS

The GET STATISTICS statement returns statistical information for the current transaction. It is a CA IDMS extension to the SQL standard. Use this statement in SQL that is embedded in a program, in the SQL command facility, and in the command console of CA IDMS Visual DBA.
idmscu19
The GET STATISTICS statement returns statistical information for the current transaction. It is a CA IDMS extension to the SQL standard. Use this statement in SQL that is embedded in a program, in the SQL command facility, and in the command console of CA IDMS Visual DBA.
This article describes the following information:
2
2
Syntax
  ►── GET STATISTICS ── 
transaction-info
 ───────────────────────────────────────►◄  
 
Expansion of transaction-info
     ┌───────────────────────── , ──────────────────────────────────────┐ ►─ ▼ ─┬───────────────────────────────┬┬─ SQL_COMMANDS ──────────────┬┴───────►◄       ├─ 
routine-parameter
 ─────┬─ = ─┘├─ ROWS_FETCHED ──────────────┤       ├─ 
host-variable
 ─────────┤      ├─ ROWS_INSERTED ─────────────┤       └─ 
local-variable
 ────────┘      ├─ ROWS_UPDATED ──────────────┤                                        ├─ ROWS_DELETED ──────────────┤                                        ├─ SORT ──────────────────────┤                                        ├─ ROWS_SORTED ───────────────┤                                        ├─ MIN_ROWS_SORTED ───────────┤                                        ├─ MAX_ROWS_SORTED ───────────┤                                        ├─ AM_RECOMPILES ─────────────┤                                        ├─ PAGES_READ ────────────────┤                                        ├─ PAGES_WRITTEN ─────────────┤                                        ├─ PAGES_REQUESTED ───────────┤                                        ├─ CALC_TARGET ───────────────┤                                        ├─ CALC_OVERFLOW ─────────────┤                                        ├─ VIA_TARGET ────────────────┤                                        ├─ VIA_OVERFLOW ──────────────┤                                        ├─ RECORDS_REQUESTED ─────────┤                                        ├─ RECORDS_CURRENT ───────────┤                                        ├─ CALLS_DBMS ────────────────┤                                        ├─ FRAGMENTS_STORED ──────────┤                                        ├─ RECORDS_RELOCATED ─────────┤                                        ├─ TOTAL_LOCKS ───────────────┤                                        ├─ SHARE_LOCKS_HELD ──────────┤                                        ├─ NON_SHARE_LOCKS_HELD ──────┤                                        ├─ TOTAL_LOCKS_FREED ─────────┤                                        ├─ SR8_SPLITS ────────────────┤                                        ├─ SR8_SPAWNS ────────────────┤                                        ├─ SR8_STORED ────────────────┤                                        ├─ SR8_ERASED ────────────────┤                                        ├─ SR7_STORED ────────────────┤                                        ├─ SR7_ERASED ────────────────┤                                        ├─ B_TREE_SEARCH ─────────────┤                                        ├─ B_TREE_LEVELS_SEARCH ──────┤                                        ├─ ORPHANS_ADOPTED ───────────┤                                        ├─ LEVELS_SEARCH_BEST_CASE ───┤                                        ├─ LEVELS_SEARCH_WORST_CAS ───┤                                        ├─ RECORDS_UPDATED ───────────┤                                        ├─ SHARE_LOCKS_ACQ_CALL ──────┤                                        ├─ SHARE_LOCKS_FREED_CALL ────┤                                        ├─ NON_SHARE_LOCKS_ACQ_CALL ──┤                                        ├─ NON_SHARE_LOCKS_FREED_CALL ┤                                        └─           *                ┘  
Parameters
  • routine-parameter
    Identifies an SQL routine parameter that is to receive the value of the specified statistics item.
    Routine-parameter
    must be a parameter of the current SQL routine and must be compatible for assignment with the specified statistics item. See Expansion of Routine-parameter for expanded syntax.
  • host-variable
    Identifies a host variable that is to receive the value of the specified statistics item.
    Host-variable
    must be a host variable previously declared in the application program and must be compatible for assignment with the specified statistics item. See Expansion of Host-variable for expanded syntax.
  • local-variable
    Identifies a local variable of an SQL routine that is to receive the value of the specified statistics item.
    Local-variable
    must be a local variable declared in the SQL-invoked routine and must be compatible for assignment with the specified statistics item. See Expansion of Local-variable for expanded syntax.
    A
    routine-parameter
    ,
    host-variable
    or
    local-variable
    must be specified for each transaction-info when the statement is embedded in a program. Otherwise, these must not be specified.
  • transaction-info
    Identifies the type of transaction information that is to be returned. Each item has an integer data type and represents statistical information for the current transaction.
    For more information about these items, see the DCMT DISPLAY STATISTICS and DCMT DISPLAY TRANSACTION commands.
  • *
    Requests that all
    transaction-info
    items are to be retrieved. This is not allowed in combination with the specification of a
    routine-parameter
    ,
    host-variable
    , or
    local-variable
    and therefore cannot be used in a program.
Example
The SQL procedure TGETSTA1 counts the number of rows of one of four tables:
  • SYSTEM.TABLE
  • SYSTEM.COLUMN
  • SYSTEM.SCHEMA
  • DEMOEMPL.EMPLOYEE
The actual table is selected through the value of the TITLE parameter. Besides returning the count of rows, the procedure also returns the values of a number of statistical information items for the transaction:
  • SQL_COMMANDS
  • PAGES_REQUESTED
  • PAGES_READ
  • CALLS_DBMS
  • TOTAL_LOCKS
set options command delimiter '++'; drop procedure   SQLROUT.TGETSTA1++ create procedure SQLROUT.TGETSTA1   ( TITLE             char(8) with default   , P_COUNT           integer   , P_SQL_COMMANDS    integer   , P_PAGES_REQUESTED integer   , P_PAGES_READ      integer   , P_CALLS_DBMS      integer   , P_TOTAL_LOCKS     integer   )     EXTERNAL NAME TGETSTA1 LANGUAGE SQL Lab1: begin not atomic  case TITLE    when 'TABLE'      then  select count(*) into P_COUNT            from SYSTEM.TABLE;    when 'COLUMN'      then  select count(*) into P_COUNT            from SYSTEM.COLUMN;    when 'SCHEMA'      then  select count(*) into P_COUNT            from SYSTEM.SCHEMA;    when 'EMPLOYEE'      then  select count(*) into P_COUNT            from DEMOEMPL.EMPLOYEE;   end case;    get statistics     P_SQL_COMMANDS    = sql_commands   , P_PAGES_REQUESTED = pages_requested   , P_PAGES_READ      = pages_read   , P_CALLS_DBMS      = calls_dbms   , P_TOTAL_LOCKS     = total_locks; end ++ set options command delimiter default ++   call sqlrout.TGETSTA1('TABLE'); *+ *+ TITLE         P_COUNT  P_SQL_COMMANDS  P_PAGES_REQUESTED  P_PAGES_READ *+ -----         -------  --------------  -----------------  ------------ *+ TABLE             808               2                836             9 *+ *+ P_CALLS_DBMS  P_TOTAL_LOCKS *+ ------------  ------------- *+          813           1673 call sqlrout.TGETSTA1('COLUMN'); *+ *+ TITLE         P_COUNT  P_SQL_COMMANDS  P_PAGES_REQUESTED   P_PAGES_READ *+ -----         -------  --------------  -----------------   ------------ *+ COLUMN           6450               3               8953           1068 *+ *+ P_CALLS_DBMS  P_TOTAL_LOCKS *+ ------------  ------------- *+         8071           8300   call sqlrout.TGETSTA1('SCHEMA'); *+ *+ TITLE         P_COUNT  P_SQL_COMMANDS  P_PAGES_REQUESTED   P_PAGES_READ *+ -----         -------  --------------  -----------------   ------------ *+ SCHEMA             56               4                 59              2 *+ *+ P_CALLS_DBMS  P_TOTAL_LOCKS *+ ------------  ------------- *+           61            130   call sqlrout.TGETSTA1('EMPLOYEE'); *+ *+ TITLE         P_COUNT  P_SQL_COMMANDS  P_PAGES_REQUESTED   P_PAGES_READ *+ -----         -------  --------------  -----------------   ------------ *+ EMPLOYEE           55               5                 58              2 *+ *+ P_CALLS_DBMS  P_TOTAL_LOCKS *+ ------------  ------------- *+           60            128