Skip to Content
Author's profile photo Former Member

how to log user login history records in SAP IQ

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

 

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.