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.
►── 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 ┤ └─ * ┘
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 information about expanded syntax.
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.
For more information about expanded syntax, see Expansion of Host-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.
For more information about expanded syntax, see Expansion of Local-variable.
Note: 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.
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.
Note: For more information about these items, see the DCMT DISPLAY STATISTICS SYSTEM and DCMT DISPLAY TRANSACTION commands in the CA IDMS System Tasks and Operator Commands Guide.
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.
The SQL procedure TGETSTA1 counts the number of rows of one of four tables:
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:
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
|
Copyright © 2014 CA.
All rights reserved.
|
|