Multiple connections in with same values in sp_iqconnection(), sp_iqcontext()

[SybDBA]

Hello Community members and Experts,

It's quite often I'm seeing on my Sybase IQ 15.4, when I am fetching connection details using below query:

select distinct X.Connhandle,"User Name"=X.userid,'I.P. Address'=X.nodeaddr,X.IQthreads,'Last Request Time'=X.lastreqtime,

'Last Command Time'=X.lastiqcmdtime,'Temp Table Space Used'=X.TempTablespaceKB,'Temp Work Space Used'=X.TempworkspaceKB,'Command'=Y.cmdline,'Last Idle'=X.lastidle,

'Login Time'=X.conncreatetime from sp_iqconnection() X,sp_iqcontext() Y where X.connhandle=Y.connhandle and Y.cmdline not like '%NO COMMAND%' and Y.cmdline is not null

and Y.cmdline <> ' ' and X.userid not in ( 'DBA','dbo')  order by X.lastreqtime

I am getting below outputs like below: (it is sample output)

Connhandle          Username          IP Address     IQthreads          Command

-----------------          -----------------        -----------------    -------------------     -------------------

86162                    john                 10.10.10.10     5                         select * from emp

86162                    john                 10.10.10.10     5                         select * from emp

86222                    mike                11.11.11.11     8                         select * from dept

86222                    mike                11.11.11.11     8                         select * from dept

86230                    mark                12.12.12.12     9                         select * from sal

86230                    mark                12.12.12.12     9                         select * from sal

------------------------------------------------------------------------------------------------------------------------------

Kindly assist me to understand this. why I am seeing identical connections more than once with same values ??

Is this a server bug or something else..??

Inputs and suggestions will be appreciated.

Thanks & Regards

pankaj

[markmumy]

This is perfectly normal.  As Remi points out, look at sp_iqcontext to see if the entry is a cursor.  With IQ you have an entry for the connection to the server.  You may see other "connections", though these are really internal and not separate connections as seen by the fact that they have the same connection ID.  These are typically seen when you run cursors inside IQ to process data.  If you leave the cursors open and have multiple cursors, it is possible to see multiple entries per physical connection.

Mark

[SybDBA]

Hi Mark,

Thanks for your promptness

With due respect I want to mention,

I have put *distinct* for ConnHandle to ensure that either connection or cursor it is, has to be presented once not as in scenario more than once.

please refer if any doc is there related to this topic, which may clear my confusion

Kindly me if I am wrong ??

Hi ,

Thanks for your promptness as well

Its not all about getting only source IP, it's a query which we are using for a long time to get the users info which are either connected or executing something(proc or query).

It lists the users who are at least connected to the server.

to get the info about query or cursor we can do like,

exec sp_iqconnection <conn_handle>

or

for details

exec sp_iqcontext <conn_handle>

I hope , am not wrong

---

with regards,

pankaj

[remi_astier]

Hello,

In sp_iqcontext, the first column describes whether it is a connection or a cursor.

Also, you probably don't need a join to get the source ip. You can use connection_property to fetch plenty of useful information for a given connection.

Try :

select *

,connection_property('ClientNodeAddress', ConnHandle) as SourceAddress

,connection_property('ClientLibrary', ConnHandle) as SourceProtocol

,connection_property('AppInfo', ConnHandle) as AppInfo

from sp_iqcontext()