on 08-28-2015 11:00 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 ??
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
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()
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
10 | |
9 | |
8 | |
6 | |
6 | |
6 | |
5 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.