analytics/informix/etc/sysadmin/sch_sqlcap.sql

{**************************************************************************}
{*                                                                        *}
{*  Licensed Materials - Property of IBM and/or HCL                                  *}
{*                                                                        *}
{*  IBM Informix Dynamic Server                                           *}
{*  (c) Copyright IBM Corporation 2001, 2015. All rights reserved.        *}
{*  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.         *}
{*                                                                        *}
{**************************************************************************}

database sysadmin;


DROP TABLE IF EXISTS mon_syssqltrace_info;
CREATE RAW TABLE mon_syssqltrace_info (	
serial_id         serial,					
ID                integer,				
task_id           integer,				
orig_sql_id       int8,					
starttime         integer);

INSERT INTO mon_syssqltrace_info VALUES (0, 0,  0,  -1, 0);

DROP TABLE IF EXISTS mon_syssqltrace_hvar;
CREATE RAW TABLE mon_syssqltrace_hvar(
ID                integer,		
task_id           integer,		
cur_date          date,			
sql_id            int8,			
orig_sql_id       int8,			
sql_address       int8,			
sql_hvar_id       int,			
sql_hvar_flags    int,			
sql_hvar_typeid   int,			
sql_hvar_xtypeid  int,			
sql_hvar_ind      int,			
sql_hvar_type     varchar(128),	
sql_hvar_data     lvarchar(8192));

CREATE INDEX mon_syssqltrace_hvar_idx1 ON mon_syssqltrace_hvar(ID, task_id, orig_sql_id);
CREATE INDEX mon_syssqltrace_hvar_idx2 ON mon_syssqltrace_hvar(cur_date);
CREATE INDEX mon_syssqltrace_hvar_idx3 ON mon_syssqltrace_hvar(sql_id);

DROP TABLE IF EXISTS mon_syssqltrace_iter;
create raw table mon_syssqltrace_iter(
ID                integer,
task_id           integer,
cur_date          date,
sql_id            int8,
orig_sql_id       int8,
sql_address       int8,
sql_itr_address   int8,
sql_itr_id        int,
sql_itr_left      int,
sql_itr_right     int,
sql_itr_cost      int,
sql_itr_estrows   int,
sql_itr_numrows   int,
sql_itr_type      int,
sql_itr_misc      int,
sql_itr_info      char(256),
sql_itr_time      float,
sql_itr_sender    int default 0,
sql_itr_nxtsender int default 0,
sql_itr_partnum   int);

CREATE INDEX mon_syssqltrace_iter_idx1 on mon_syssqltrace_iter(ID, task_id, orig_sql_id);
CREATE INDEX mon_syssqltrace_iter_idx2 on mon_syssqltrace_iter(cur_date);
CREATE INDEX mon_syssqltrace_iter_idx3 on mon_syssqltrace_iter(sql_id);

DROP TABLE IF EXISTS mon_syssqltrace;
CREATE RAW TABLE mon_syssqltrace(
ID                integer,
task_id           integer,
cur_date          date,
sql_id            int8,
orig_sql_id       int8,
sql_address       int8,
sql_sid           int,
sql_uid           int,
sql_stmttype      int,
sql_stmtname      varchar(40),
sql_finishtime    int,
sql_begintxtime   int,
sql_runtime       float,
sql_pgreads       int,
sql_bfreads       int,
sql_rdcache       float,
sql_bfidxreads    int,
sql_pgwrites      int,
sql_bfwrites      int,
sql_wrcache       float,
sql_lockreq       int,
sql_lockwaits     int,
sql_lockwttime    float,
sql_logspace      int,
sql_sorttotal     int,
sql_sortdisk      int,
sql_sortmem       int,
sql_executions    int,
sql_totaltime     float,
sql_avgtime       float,
sql_maxtime       float,
sql_numiowaits    int,
sql_avgiowaits    float,
sql_totaliowaits  float,
sql_rowspersec    float,
sql_estcost       int,
sql_estrows       int,
sql_actualrows    int,
sql_sqlerror      int,
sql_isamerror     int,
sql_isollevel     int,
sql_sqlmemory     int,
sql_numiterators  int,
sql_database      varchar(128),
sql_numtables     int,
sql_tablelist     lvarchar(4096),
sql_statement     lvarchar(16000),
sql_stmtlen       int,
sql_stmthash      int8,
sql_pdq           smallint,
sql_num_hvars     smallint,
sql_dbspartnum    int);

CREATE INDEX mon_syssqltrace_idx1 on mon_syssqltrace(ID, task_id, orig_sql_id);
CREATE INDEX mon_syssqltrace_idx2 on mon_syssqltrace(sql_stmtlen,sql_stmttype);
CREATE INDEX mon_syssqltrace_idx3 on mon_syssqltrace(cur_date);
CREATE INDEX mon_syssqltrace_idx4 on mon_syssqltrace(sql_id);

DROP FUNCTION IF EXISTS sql_showsnap(INTEGER,INTEGER);
CREATE FUNCTION sql_showsnap(in_task_id INTEGER, in_seq_id INTEGER)
   RETURNING INTEGER

DEFINE p_last_starttime    INTEGER; -- starttime from mon_syssqltrace_info
                                    -- for the last task run
DEFINE p_trace_starttime   INTEGER; -- starttime from sysmaster:syssqltrace_info

DEFINE p_last_sql_id       INT8;  -- biggest orig_sql_id from last task run

DEFINE p_new_sql_id        INT8;  -- new max(orig_sql_id) from mon_syssqltrace
                                  -- for this task run

DEFINE p_start_high4       INT8;  -- high value of sql_id in mon_syssqltrace*
                                  -- tables

DEFINE p_sql_itr_senders_exist         INTEGER;

DEFINE ins_mon_syssqltrace_iter        CHAR(512);

DEFINE sel_mon_syssqltrace_iter        CHAR(512);

DEFINE rem_mon_syssqltrace_iter        CHAR(64);

DEFINE p_host_vars         INTEGER; -- Is SQLTRACE set to collect host vars ?
DEFINE sqltrace_row_cnt    INTEGER;
DEFINE sqltrace_iter_row_cnt INTEGER;
DEFINE sqltrace_hvar_row_cnt INTEGER;
DEFINE delete_cnt            INTEGER;


ON EXCEPTION IN (-206) -- If no table was found, create one

BEGIN
  ON EXCEPTION  -- Continue trying each of these statements within the outer exception
  END EXCEPTION WITH RESUME;

    create raw table mon_syssqltrace_info
        (
        serial_id         serial,
        ID                integer,
        task_id           integer,
        orig_sql_id       int8,
        starttime         integer
        );
    insert into mon_syssqltrace_info values (0, 0,  0,  -1, 0);

    create raw table mon_syssqltrace_hvar
        (
        ID                integer,
        task_id           integer,
        cur_date          date,
        sql_id            int8,
        orig_sql_id       int8,
        sql_address       int8,
        sql_hvar_id       int,
        sql_hvar_flags    int,
        sql_hvar_typeid   int,
        sql_hvar_xtypeid  int,
        sql_hvar_ind      int,
        sql_hvar_type     varchar(128),
        sql_hvar_data     lvarchar(8192)
        );

    create raw table mon_syssqltrace_iter
        (
        ID                integer,
        task_id           integer,
        cur_date          date,
        sql_id            int8,
        orig_sql_id       int8,
        sql_address       int8,
        sql_itr_address   int8,
        sql_itr_id        int,
        sql_itr_left      int,
        sql_itr_right     int,
        sql_itr_cost      int,
        sql_itr_estrows   int,
        sql_itr_numrows   int,
        sql_itr_type      int,
        sql_itr_misc      int,
        sql_itr_info      char(256),
        sql_itr_time      float,
        sql_itr_sender    int default 0,
        sql_itr_nxtsender int default 0,
        sql_itr_partnum   int
        );

    create raw table mon_syssqltrace
        (
        ID                integer,
        task_id           integer,
        cur_date          date,
        sql_id            int8,
        orig_sql_id       int8,
        sql_address       int8,
        sql_sid           int,
        sql_uid           int,
        sql_stmttype      int,
        sql_stmtname      varchar(40),
        sql_finishtime    int,
        sql_begintxtime   int,
        sql_runtime       float,
        sql_pgreads       int,
        sql_bfreads       int,
        sql_rdcache       float,
        sql_bfidxreads    int,
        sql_pgwrites      int,
        sql_bfwrites      int,
        sql_wrcache       float,
        sql_lockreq       int,
        sql_lockwaits     int,
        sql_lockwttime    float,
        sql_logspace      int,
        sql_sorttotal     int,
        sql_sortdisk      int,
        sql_sortmem       int,
        sql_executions    int,
        sql_totaltime     float,
        sql_avgtime       float,
        sql_maxtime       float,
        sql_numiowaits    int,
        sql_avgiowaits    float,
        sql_totaliowaits  float,
        sql_rowspersec    float,
        sql_estcost       int,
        sql_estrows       int,
        sql_actualrows    int,
        sql_sqlerror      int,
        sql_isamerror     int,
        sql_isollevel     int,
        sql_sqlmemory     int,
        sql_numiterators  int,
        sql_database      varchar(128),
        sql_numtables     int,
        sql_tablelist     lvarchar(4096),
        sql_statement     lvarchar(16000),
        -- sql_statement     char(16000),
        sql_stmtlen       int,
        sql_stmthash      int8,
        sql_pdq           smallint,
        sql_num_hvars     smallint,
        sql_dbspartnum    int
        );


     CREATE INDEX mon_syssqltrace_idx1 on
                                mon_syssqltrace(ID, task_id, orig_sql_id);
     CREATE INDEX mon_syssqltrace_idx2 on
                                mon_syssqltrace(sql_stmtlen,sql_stmttype);
     CREATE INDEX mon_syssqltrace_idx3 on mon_syssqltrace(cur_date);

     CREATE INDEX mon_syssqltrace_iter_idx1 on
                                mon_syssqltrace_iter(ID, task_id, orig_sql_id);
     CREATE INDEX mon_syssqltrace_iter_idx2 on mon_syssqltrace_iter(cur_date);

     CREATE INDEX mon_syssqltrace_hvar_idx1 on
                                mon_syssqltrace_hvar(ID, task_id, orig_sql_id);
     CREATE INDEX mon_syssqltrace_hvar_idx2 on mon_syssqltrace_hvar(cur_date);

END
   END EXCEPTION WITH RESUME;


--SET DEBUG FILE TO "/tmp/debug_sql_showsnap.log";
--TRACE ON;

SET ISOLATION TO DIRTY READ;

LET p_last_starttime = 0;
LET p_trace_starttime = 0;
LET p_last_sql_id = -1;
LET p_host_vars = 0;
LET sqltrace_iter_row_cnt = 0;
LET sqltrace_row_cnt = 0;
LET sqltrace_hvar_row_cnt = 0;

LET p_trace_starttime =
                 (SELECT NVL(starttime,0) FROM sysmaster:syssqltrace_info);

IF  ( (p_trace_starttime is NULL) or (p_trace_starttime == 0) ) THEN
INSERT into mon_syssqltrace_info
                 values (0, in_seq_id, in_task_id, -1, p_trace_starttime);
RETURN 0;
END IF;


LET p_host_vars =
       (SELECT bitand(flags,8192) FROM sysmaster:syssqltrace_info);

LET p_last_sql_id = (SELECT NVL(orig_sql_id,-1) FROM mon_syssqltrace_info
                     where serial_id  =
                       (select MAX(serial_id) from mon_syssqltrace_info));


LET p_last_starttime = (SELECT NVL(starttime,0) FROM mon_syssqltrace_info
                     where serial_id  =
                       (select MAX(serial_id) from mon_syssqltrace_info));


IF (p_last_sql_id is NULL) THEN
   LET p_last_sql_id = -1;
END IF;

IF (p_last_starttime is NULL) THEN
   LET p_last_starttime = 0;
END IF;

IF  (p_last_starttime != p_trace_starttime) THEN
    LET p_last_sql_id = -1 ;
END IF;

LET p_start_high4 = p_trace_starttime * 4294967296 ;

INSERT INTO mon_syssqltrace
        (
        ID                ,
        task_id           ,
        cur_date          ,
        sql_id            ,
        orig_sql_id       ,
        sql_address       ,
        sql_sid           ,
        sql_uid           ,
        sql_stmttype      ,
        sql_stmtname      ,
        sql_finishtime    ,
        sql_begintxtime   ,
        sql_runtime       ,
        sql_pgreads       ,
        sql_bfreads       ,
        sql_rdcache       ,
        sql_bfidxreads    ,
        sql_pgwrites      ,
        sql_bfwrites      ,
        sql_wrcache       ,
        sql_lockreq       ,
        sql_lockwaits     ,
        sql_lockwttime    ,
        sql_logspace      ,
        sql_sorttotal     ,
        sql_sortdisk      ,
        sql_sortmem       ,
        sql_executions    ,
        sql_totaltime     ,
        sql_avgtime       ,
        sql_maxtime       ,
        sql_numiowaits    ,
        sql_avgiowaits    ,
        sql_totaliowaits  ,
        sql_rowspersec    ,
        sql_estcost       ,
        sql_estrows       ,
        sql_actualrows    ,
        sql_sqlerror      ,
        sql_isamerror     ,
        sql_isollevel     ,
        sql_sqlmemory     ,
        sql_numiterators  ,
        sql_database      ,
        sql_numtables     ,
        sql_tablelist     ,
        sql_statement     ,
        sql_stmtlen       ,
        sql_stmthash      ,
        sql_pdq           ,
        sql_num_hvars     ,
        sql_dbspartnum
        )
    SELECT
        in_seq_id         ,
        in_task_id        ,
        today             ,
        p_start_high4 + sql_id  ,
        sql_id            ,
        sql_address       ,
        sql_sid           ,
        sql_uid           ,
        sql_stmttype      ,
        sql_stmtname      ,
        sql_finishtime    ,
        sql_begintxtime   ,
        sql_runtime       ,
        sql_pgreads       ,
        sql_bfreads       ,
        sql_rdcache       ,
        sql_bfidxreads    ,
        sql_pgwrites      ,
        sql_bfwrites      ,
        sql_wrcache       ,
        sql_lockreq       ,
        sql_lockwaits     ,
        sql_lockwttime    ,
        sql_logspace      ,
        sql_sorttotal     ,
        sql_sortdisk      ,
        sql_sortmem       ,
        sql_executions    ,
        sql_totaltime     ,
        sql_avgtime       ,
        sql_maxtime       ,
        sql_numiowaits    ,
        sql_avgiowaits    ,
        sql_totaliowaits  ,
        sql_rowspersec    ,
        sql_estcost       ,
        sql_estrows       ,
        sql_actualrows    ,
        sql_sqlerror      ,
        sql_isamerror     ,
        sql_isollevel     ,
        sql_sqlmemory     ,
        sql_numiterators  ,
        sql_database      ,
        sql_numtables     ,
        TRIM(sql_tablelist)     ,
        TRIM(sql_statement)     ,
        sql_stmtlen       ,
        sql_stmthash      ,
        sql_pdq           ,
        sql_num_hvars     ,
        sql_dbspartnum
    FROM sysmaster:syssqltrace
    WHERE sql_id >  p_last_sql_id;

LET  sqltrace_row_cnt = DBINFO('sqlca.sqlerrd2');

LET  ins_mon_syssqltrace_iter = "INSERT INTO mon_syssqltrace_iter"      ||
        "("                                                             ||
        "ID                ,"                                           ||
        "task_id           ,"                                           ||
        "cur_date          ,"                                           ||
        "sql_id            ,"                                           ||
        "orig_sql_id       ,"                                           ||
        "sql_address       ,"                                           ||
        "sql_itr_address   ,"                                           ||
        "sql_itr_id        ,"                                           ||
        "sql_itr_left      ,"                                           ||
        "sql_itr_right     ,"                                           ||
        "sql_itr_cost      ,"                                           ||
        "sql_itr_estrows   ,"                                           ||
        "sql_itr_numrows   ,"                                           ||
        "sql_itr_type      ,"                                           ||
        "sql_itr_misc      ,"                                           ||
        "sql_itr_info      ,"                                           ||
        "sql_itr_time      ,"                                           ||
        "sql_itr_partnum    ";

LET sel_mon_syssqltrace_iter = "+ sql_id ,"                             ||
        "sql_id            ,"                                           ||
        "sql_address       ,"                                           ||
        "sql_itr_address   ,"                                           ||
        "sql_itr_id        ,"                                           ||
        "sql_itr_left      ,"                                           ||
        "sql_itr_right     ,"                                           ||
        "sql_itr_cost      ,"                                           ||
        "sql_itr_estrows   ,"                                           ||
        "sql_itr_numrows   ,"                                           ||
        "sql_itr_type      ,"                                           ||
        "sql_itr_misc      ,"                                           ||
        "sql_itr_info      ,"                                           ||
        "sql_itr_time      ,"                                           ||
        "sql_itr_partnum    ";

LET rem_mon_syssqltrace_iter = " FROM sysmaster:syssqltrace_iter"       ||
                               " WHERE sql_id > ";
	
	
LET p_sql_itr_senders_exist = (SELECT COUNT(*) FROM sysmaster:syscolumns a, sysmaster:systables b 
    WHERE a.tabid = b.tabid AND a.colname = 'sql_itr_sender' AND b.tabname = 'syssqltrace_iter');
	
IF ( p_sql_itr_senders_exist > 0) THEN

EXECUTE IMMEDIATE ins_mon_syssqltrace_iter                              || 
                ",sql_itr_sender    ,sql_itr_nxtsender) "               ||
                "SELECT "                                               ||
                in_seq_id                                               ||
                ","                                                     ||
                in_task_id                                              ||
                ","                                                     ||
                today                                                   ||
                ","                                                     ||
                p_start_high4                                           ||
                sel_mon_syssqltrace_iter                                ||
                ",sql_itr_sender    ,sql_itr_nxtsender "                ||
                rem_mon_syssqltrace_iter                                ||
                p_last_sql_id                                           ||
                ";";
ELSE

EXECUTE IMMEDIATE ins_mon_syssqltrace_iter                              || 
                ") "                                                    ||
                "SELECT "                                               ||
                in_seq_id                                               ||
                ","                                                     ||
                in_task_id                                              ||
                ","                                                     ||
                today                                                   ||
                ","                                                     ||
                p_start_high4                                           ||
                sel_mon_syssqltrace_iter                                ||
                rem_mon_syssqltrace_iter                                ||
                p_last_sql_id                                           ||
                ";";

END IF; -- IF ( p_sql_itr_senders_exist > 0)
					   
LET  sqltrace_iter_row_cnt = DBINFO('sqlca.sqlerrd2');

IF ( p_host_vars > 0  )  THEN

INSERT INTO mon_syssqltrace_hvar
        (
        ID                ,
        task_id           ,
        cur_date          ,
        sql_id            ,
        orig_sql_id       ,
        sql_address       ,
        sql_hvar_id       ,
        sql_hvar_flags    ,
        sql_hvar_typeid   ,
        sql_hvar_xtypeid  ,
        sql_hvar_ind      ,
        sql_hvar_type     ,
        sql_hvar_data
        )
 SELECT 
        in_seq_id         ,
        in_task_id        ,
        today             ,
        p_start_high4 + sql_id ,
        sql_id            ,
        sql_address       ,
        sql_hvar_id       ,
        sql_hvar_flags    ,
        sql_hvar_typeid   ,
        sql_hvar_xtypeid  ,
        sql_hvar_ind      ,
        sql_hvar_type      ,
        trim(sql_hvar_data)
    FROM sysmaster:syssqltrace_hvar
    WHERE sql_id >  p_last_sql_id;

LET  sqltrace_hvar_row_cnt = DBINFO('sqlca.sqlerrd2');


DELETE FROM mon_syssqltrace_hvar
    WHERE     ID = in_seq_id AND
              task_id = in_task_id AND
              orig_sql_id > p_last_sql_id AND
   orig_sql_id NOT IN ( SELECT orig_sql_id FROM mon_syssqltrace
                       where ID = in_seq_id AND task_id = in_task_id);

LET  delete_cnt = DBINFO('sqlca.sqlerrd2');
END IF;   -- IF ( p_host_vars > 0  )  THEN



DELETE FROM mon_syssqltrace_iter
    WHERE     ID = in_seq_id AND
              task_id = in_task_id AND
              orig_sql_id > p_last_sql_id AND
   orig_sql_id NOT IN ( SELECT orig_sql_id FROM mon_syssqltrace
                       where ID = in_seq_id AND task_id = in_task_id);
LET  delete_cnt = DBINFO('sqlca.sqlerrd2');

LET p_new_sql_id = -1;
LET p_new_sql_id = (SELECT NVL(MAX(orig_sql_id),-1) from mon_syssqltrace
                      where ID = in_seq_id AND task_id = in_task_id );

IF ( (p_new_sql_id is NULL) or (p_new_sql_id == -1) )
THEN
INSERT into mon_syssqltrace_info
         values (0, in_seq_id, in_task_id, p_last_sql_id, p_trace_starttime);
ELSE
INSERT into mon_syssqltrace_info
         values (0, in_seq_id, in_task_id, p_new_sql_id, p_trace_starttime);
END IF;

RETURN sqltrace_row_cnt;

END FUNCTION;


DELETE FROM ph_task WHERE tk_name ='Save SQL Trace';
INSERT INTO ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_start_time,
tk_stop_time,
tk_frequency,
tk_delete,
tk_result_table,
tk_attributes,
tk_enable
)
VALUES
(
'Save SQL Trace',
'SENSOR',
'PERFORMANCE',
'Saves the current syssqltrace info to table',
'sql_showsnap',
DATETIME(06:00:00) HOUR TO SECOND,
DATETIME(18:00:00) HOUR TO SECOND,
INTERVAL ( 15 ) MINUTE TO MINUTE,
INTERVAL ( 1  ) DAY TO DAY,
'mon_syssqltrace,mon_syssqltrace_iter,mon_syssqltrace_hvar,mon_syssqltrace_info',
1,
'f'
);


close database;