Skip to Content

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

 

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply