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 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');

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
        )
 SELECT
        in_seq_id         ,
        in_task_id        ,
        today             ,
        p_start_high4 + 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
    FROM sysmaster:syssqltrace_iter
    WHERE sql_id >  p_last_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
INSERT INTO mon_syssqltrace_iter
        (
	    sql_itr_sender    ,
        sql_itr_nxtsender 
        )
 SELECT 
        sql_itr_sender    ,
        sql_itr_nxtsender 
    FROM sysmaster:syssqltrace_iter
    WHERE sql_id >  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;

-- Permissions for routine "sql_showsnap"
grant execute on function sql_showsnap(integer,integer) to 'public';
insert into ph_task insert into ph_task(
     tk_id ,
     tk_name ,
     tk_description ,
     tk_type ,
     tk_result_table ,
     tk_create ,
     tk_dbs ,
     tk_monday ,
     tk_tuesday ,
     tk_wednesday ,
     tk_thursday ,
     tk_friday ,
     tk_saturday ,
     tk_sunday ,
     tk_attributes ,
     tk_group ,
     tk_enable ,
     tk_priority
) values (  0 ,"Save SQL Trace" , "Saves the current syssqltrace info to table", 
"SENSOR", 843  ,"mon_syssqltrace,mon_syssqltrace_iter,mon_syssqltrace_hvar,mon_syssqltrace_info" , "sysadmin", "sql_showsnap",
....);