DBA might need to do some user login activities analysis to realize user login frequency and behavior, I searched IQ reference manuals and did not find appropriate way to fulfill DBA's request !
I will suggest DBA to apply this solution as following:
Step 1: create conn_history table for staging
create table conn_history(Userid varchar(255), IQconnID unsigned bigint null, ConnCreateTime datetime null, CmdLine varchar(4096));
Step 2: use sp_iqconnection/sp_iqcontext and conn_history table join to filter existing user login records, then use insert..select to insert all user login into conn_history table
Syntax:
insert into conn_history select distinct a.Userid, a.IQconnID, a.ConnCreateTime, b.CmdLine from sp_iqconnection() a, sp_iqcontext() b, conn_history c where a.IQconnID = b.IQconnID and a.ConnCreateTime not in (select ConnCreateTime from conn_history);
Step 3: <<<< Testing Example >>>>
a. dbisql using new user login ‘thomas’ logon SAP Sybase IQ !!
b. executed insert..select syntax on Step 2
(DBA)> insert into conn_history select distinct a.Userid, a.IQconnID, a.ConnCreateTime, b.CmdLine from sp_iqconnection() a, sp_iqcontext() b, conn_history c where a.IQconnID = b.IQconnID and a.ConnCreateTime not in (select ConnCreateTime from conn_history);
1 row(s) inserted
Execution time: 0.046 seconds
c. confirm new login record did log into conn_history
(DBA)> select * from conn_history;
Userid IQconnID ConnCreateTime CmdLine
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBA 252118 2014-05-08 05:13:00.000 insert into conn_history select a.Userid, a.IQconnID, a.ConnCreateTime, b.CmdLine from sp_iqconnection() a, sp_iqcontext() b where a.IQconnID = b.IQconnID
robert 252434 2014-05-08 05:23:26.000 NO COMMAND
thomas 254116 2014-05-08 06:19:12.000 NO COMMAND
rachael 254455 2014-05-08 06:30:24.000 NO COMMAND
thomas 257037 2014-05-08 07:55:58.000 NO COMMAND
(5 rows)
Execution time: 0.017 seconds
d. dbisql using new user login ‘rachael’ logon SAP Sybase IQ !!
e. executed insert..select syntax on Step 2
(DBA)> insert into conn_history select distinct a.Userid, a.IQconnID, a.ConnCreateTime, b.CmdLine from sp_iqconnection() a, sp_iqcontext() b, conn_history c where a.IQconnID = b.IQconnID and a.ConnCreateTime not in (select ConnCreateTime from conn_history);
1 row(s) inserted
Execution time: 0.04 seconds
f. confirm new login record did log into conn_history
(DBA)> select * from conn_history;
Userid IQconnID ConnCreateTime CmdLine
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DBA 252118 2014-05-08 05:13:00.000 insert into conn_history select a.Userid, a.IQconnID, a.ConnCreateTime, b.CmdLine from sp_iqconnection() a, sp_iqcontext() b where a.IQconnID = b.IQconnID
robert 252434 2014-05-08 05:23:26.000 NO COMMAND
thomas 254116 2014-05-08 06:19:12.000 NO COMMAND
rachael 254455 2014-05-08 06:30:24.000 NO COMMAND
thomas 257037 2014-05-08 07:55:58.000 NO COMMAND
rachael 257100 2014-05-08 07:58:01.000 NO COMMAND
(6 rows)
Execution time: 0.015 seconds
(DBA)>
