Example: When Did a Query Start?
You can find out when a query started and how long it has been running.
Notes:
• This is start of execution, not parsing or optimization.
• The start time is in seconds since 1-Jan-1900 00:00:00. You may use the _date4 and _time Actian X functions to do time calculations, or you may use an ANSI interval in seconds.
• The time is not zeroed when the query ends.
The following is a sample query to run after you SQL into the imadb:
execute procedure ima_set_vnode_domain\g
select server, session_id, real_user, db_name, session_terminal, varchar(_date(query_start_secs)) + ' ' + varchar(_time(query_start_secs)) + varchar(':') + varchar(mod(query_START_secs,60)) as start_time
from ima_server_sessions
where db_name not in ('', 'imadb')\g
To find out how long a query has been running, here is a sample SQL statement that shows you the longest running query in the system:
execute procedure ima_set_vnode_domain\g
select server, db_name, session_query, (bigint((current_timestamp - timestamp_with_tz('1970-01-01 00:00:00+00:00'))/interval '1' second) - query_start_secs) as elapsedsecs
from ima_server_sessions
where db_name not in ('', 'imadb')\g
If a query is finished, it will still show in the list. In this case you can join another table and ignore queries in BIO state:
execute procedure ima_set_vnode_domain\g
select db_name, session_query, (bigint((current_timestamp - timestamp_with_tz('1970-01-01 00:00:00+00:00'))/interval '1' second) - query_start_secs) as elapsedsecs
from ima_server_sessions s,ima_server_sessions_extra e
where s.session_id = e.session_id and s.effective_user != ''
and (e.session_state != 'CS_EVENT_WAIT' or e.session_wait_reason != 'BIOR')
and db_name not in ('', 'imadb')
The previous query will show you all queries that are actually running as opposed to those already run, finished, and waiting for user input (\q etc.).
You can have a stored procedure show you elapsed time:
create procedure show_processlist as
declare
session_id = varchar(32) not null;
server = varchar(64) not null;
real_user = varchar(32) not null;
db_name = varchar(32) not null;
session_terminal = varchar(12) not null;
session_query = varchar(1000) not null;
elapsed_time = char(12) not null;
start_query = varchar(32) not null;
result = varchar(1200) not null;
err = integer not null;
begin
err = 0;
:result = 'Session ID Database User Terminal Elapsed Time(s) Session Query';
message :result;
for select
session_id, server, session_terminal, real_user, db_name, session_query,
bigint((current_timestamp - timestamp_with_tz('1970-01-01 00:00:00+00:00'))/interval '1' second) - query_start_secs,
varchar(ingresdate(varchar(_date(query_start_secs)) + ' ' + varchar(_time(query_start_secs)) + varchar(':') + varchar(mod(query_START_secs,60))))
into :session_id,:server,:session_terminal,:real_user,:db_name,:session_query,
:elapsed_time,:start_query
from ima_server_sessions
where db_name not in ('','imadb') do
IF iierrornumber > 0 THEN
err = 1;
endloop;
endif;
:result = :session_id+' '+:db_name+' '+:real_user+' '+:session_terminal+' '+:elapsed_time+:session_query;
message :result;
endfor;
RETURN err;
end;
You call it by running:
execute procedure ima_set_vnode_domain\g
execute procedure show_processlist; \g