sp_iqclient_lookup procedure [CR 443012]

The sp_iqclient_lookup procedure was added in Sybase IQ 12.7 ESD #2.

Allows a client application to determine the Sybase IQ user account responsible for a particular data stream, as observed in a network analyzer originating from a specific client IP address/port.

sp_iqclient_lookup [ IPaddress ], [ Port ], [ UserID ]

The sp_iqclient_lookup procedure takes the client IP address and port number and returns a single row containing Number (the connection ID), IPaddress, Port, and UserID.

1> sp_iqclient_lookup 158.76.235.71,3360
2> go

Number   IPaddress      Port    UserID
------   ---------      ----    ------
15       158.76.235.71  3360    rdeniro

An optional third argument can be passed to select just the UserID. If no arguments are passed then the procedure returns all current logins with their IP addresses and port numbers. For example:

sp_iqclient_lookup

Number   IPaddress        Port    UserID
------   ---------        ----    ------
11       162.66.131.36    2082    mbrando
21       162.66.100.233   1863    apacino
22       162.66.100.206   8080    jcaan
23       162.66.100.119   6901    rduvall
24       162.66.100.125   7001    dkeaton
25       162.66.100.124   6347    jcazale

(6 rows affected)
(return status = 0)

If a client application is not using TCP/IP or for internal connections, the address appears as 127.0.0.1.

DBA.

The sp_iqclient_lookup stored procedure may impact server performance, which will vary from one installation to another. Finding the login name entails scanning through all current active connections on the server; therefore, the impact may be greater on servers with large numbers of connections. Furthermore this information cannot be cached as it is dynamic — sometimes highly dynamic. It is, therefore, a matter for the local system administrator to manage the use of this stored procedure, as well as monitor the effects on the server, just as for any other client application that uses server facilities.

Shows IP addresses for UserID jcazale:

sp_iqclient_lookup null, null, jcazale

Number   IPaddress        Port    UserID
------   ----------       ----    ------

11       162.66.131.36    2082    jcazale
15       164.66.131.36    1078    jcazale

Shows IP addresses from client IP 162.66.131.36:

sp_iqclient_lookup '162.66.131.36'

Number   IPaddress        Port    UserID
------   ----------       ----    ------
11       162.66.131.36    2082    jcazale
12       162.66.131.36    1078    jcaan